Traces & System Stored procedures

Traces & System Stored procedures

1: What are trace flags?

Trace flags are used to temporarily set specific server characteristics or to switch On/Off a particular behavior. Trace flags are frequently used to diagnose performance issues or to debug stored procedures.

2: How to enable or disable a trace flag?

Using the DBCC TRACEON and DBCC TRACEOFF commands.

Using the -T startup option to specify that the trace flag be set on during startup.

3: How can you find out all active trace flags on a SQL server instance?

DBCC TRACESTATUS can be used to find our all active trace flags

4: What are different types of Traces?

There are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.

5: How can you disable SELECT permission for DBCC SHOW_STATISTICS?

Select permissions for DBCC Show_Statistics can be disabled using Trace flag 9854.

6: How do you prevent ad-hoc query plans to stay in cache?

By enabling Trace 253

Example DBCC Traceon (253)

7: How do you print information about the cost of using a non-clustered index or a sort to process an ORDER BY clause?

By enabling trace 325

Example DBCC Traceon (325)

8: How do you enable full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins?

By Enabling trace 330

Example DBCC Traceon (330)

9: How do you disable read ahead for the server or current connection?

Disable Read ahead for server – by enabling 652 trace. DBCC Traceon (652)

Disable Read ahead for current connection – by enabling 653 trace. DBCC Traceon (653)

10: How do you equally distribute data in all data files or make data files grow all at once?

By enabling trace 1117

Example DBCC Traceon (1117)

11: How do you collect Statistics for system tables?

By Enabling trace 2382

Example DBCC Traceon (2382)

12: Which Trace is used with DBCC CHECKTABLE to see the total count of forward records in a table?

2509

13: Which trace is used with DBCC CHECKTABLE to see the total count of ghost records in a table?

2514

14: Which trace Forces DBCC HELP to return syntax of undocumented DBCC statements?

2520

15: How can you stop sending backup entries into MSDB?

By enabling trace 3001

16: How can you Suppress BACKUP COMPLETED log entries going to Windows and SQL logs?

By enabling trace 3226

Interview questions on Stored Procedures

17: What are stored procedures?

Stored Procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do.

18: What is the difference between Stored Procedure and Function?

Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).

Functions can have only input parameters for it whereas Procedures can have input/output parameters.

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

19: Can you name 5 undocumented Stored procedures that you have used in past?

sp_who2

xp_fixeddrives

xp_enumerrorlogs

sp_MSforeachtable

sp_MSforeachDB

20: Explain the usage of below procedures?

sp_helpindex [tablename] – shows you index info (same info as sp_help)

sp_helpconstraint [tablename] – shows you primary/foreign key/defaults and other constraints

sp_depends [objname] – shows dependencies of an object.

sp_depends [tablename] – shows you what stored procs, views, triggers, UDF affect this table.

sp_depends [storedprocname] – shows what tables etc. are affected/used by this stored proc.

21: What is the difference between sp_rename and sp_renamedb?

sp_rename: Changes the name of a user-created object in the current database.

sp_renamedb: Changes the name of a database. One should use ALTER DATABASE MODIFY NAME for renaming database.

22: Which status is reset by sp_resetstatus?

It resets the status of a suspect database. One should avoid using this procedure. Use ALTER DATABASE instead.

23: What are the permissions required to run sp_who or sp_who2?

VIEW SERVER STATE permission is required on the server to see all executing sessions on the instance of SQL Server. Otherwise, the user sees only the current session.

24: What is the difference between sp_clean_db_free_space and DBCC SHRINKFILE?

sp_clean_db_free_space cleans all pages in all files of the database.

DBCC SHRINKFILE: Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same file group.

25: How can you change the option of AUTO_UPDATE_STATESTICS?

sp_autostats: Displays or changes the automatic statistics update option, AUTO_UPDATE_STATISTICS, for an index, a statistics object, a table, or an indexed view.

26: What is the purpose of sp_cycle_errorlog?

This procedure closes the current error log file and cycles the error log extension numbers just like a server restart.

27: What is the difference between EXEC and sp_executesql?

sp_executesql allows for statements to be parameterized therefore it’s more secure than EXEC in terms of SQL injection. sp_executesql can leverage cached query plans. Temp tables created in EXEC cannot use temp table caching mechanism.

28: What is the use of sp_refreshview?

Running with view name as an argument – updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

29: Name at least three output columns of sp_monitor?

This procedures displays statistics about Microsoft SQL Server

last_run: Time sp_monitor was last run.

current_run: Time sp_monitor is being run.

Seconds: Number of elapsed seconds since sp_monitor was run.

cpu_busy: Number of seconds that the server computer’s CPU has been doing SQL Server work.

io_busy: Number of seconds that SQL Server has spent doing input and output operations.

Idle: Number of seconds that SQL Server has been idle.

packets_received: Number of input packets read by SQL Server.

packets_sent: Number of output packets written by SQL Server.

packet_errors: Number of errors encountered by SQL Server while reading and writing packets.

total_read: Number of reads by SQL Server.

total_write: Number of writes by SQL Server.

total_errors: Number of errors encountered by SQL Server while reading and writing.

Connections: Number of logins or attempted logins to SQL Server.

30: What is ‘sp_refreshdatabase’ from ‘SourceBackup.bak’?

There isn’t anything like this as system stored procedures in SQL Server. This may be it a user defined function. This question is my all-time favorite. Not to criticize anyone but sometime people started giving me explanation when I asked this question during interviews.