Skip to main content
Our Tech Ideas

About Traces & System Stored procedures

Trace Flags

What are trace flags? Trace flags are used to temporarily set specific server characteristics or to switch on/off a particular behavior. They are frequently used to diagnose performance issues or debug stored procedures.

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.

How can you find all active trace flags on a SQL server instance? DBCC TRACESTATUS can be used to find out all active trace flags.

What are different types of Traces? There are two types: 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.

How can you disable SELECT permission for DBCC SHOW_STATISTICS? Select permissions for DBCC Show_Statistics can be disabled using Trace flag 9854.

Stored Procedures

What are stored procedures? Stored Procedures are batches of SQL statements that can be executed in multiple ways. They are supported by most major DBMS.

What is the difference between Stored Procedure and Function? Functions must return a value, but in Stored Procedures, it’s optional. Functions can have only input parameters, whereas Procedures can have input/output parameters. Functions can be called from Procedures, but Procedures cannot be called from Functions, etc.

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

sp_who2
xp_fixeddrives
xp_enumerrorlogs
sp_MSforeachtable
sp_MSforeachDB
SQL

Explain the usage of the following procedures:

  1. sp_helpindex [tablename]:
    • This procedure provides information about the indexes on a specified table, such as the index name, index description, index keys, and index type. It’s essentially similar to using sp_help but specifically focuses on the indexes of the table.
  2. sp_helpconstraint [tablename]:
    • When you need to understand the constraints on a table, such as primary keys, foreign keys, defaults, and other constraints, you can use this procedure. It gives a comprehensive view of the constraints applied to the specified table.
  3. sp_depends [objname]:
    • This procedure is used to find the objects that depend on a specified object. For example, if you provide the name of a stored procedure, view, trigger, or function, sp_depends will return a list of other database objects that depend on it. This could include other stored procedures, views, triggers, or user-defined functions.
  4. sp_depends [tablename]:
    • When you provide the name of a table, this procedure will show you what stored procedures, views, triggers, or user-defined functions are dependent on that table. It’s useful for understanding the impact of changes to the table structure or data.
  5. sp_depends [storedprocname]:
    • Conversely, if you provide the name of a stored procedure, this procedure will show you what tables, views, or other database objects are used within that stored procedure. It helps in understanding the dependencies of the stored procedure and identifying the data it relies on or manipulates.

What is the difference between sp_rename and sp_renamedb? sp_rename changes the name of a user-created object in the current database, while sp_renamedb changes the name of a database. ALTER DATABASE MODIFY NAME should be used for renaming databases.

Which status is reset by sp_resetstatus? It resets the status of a suspect database. However, it’s recommended to use ALTER DATABASE instead.

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.

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, whereas DBCC SHRINKFILE shrinks the size of the specified data or log file for the current database.

How can you change the option of AUTO_UPDATE_STATESTICS? Using sp_autostats, you can display or change the automatic statistics update option.

What is the purpose of sp_cycle_errorlog? It closes the current error log file and cycles the error log extension numbers, similar to a server restart.

What is the difference between EXEC and sp_executesql? sp_executesql allows for parameterized statements, making it more secure against SQL injection. It can also leverage cached query plans.

What is the use of sp_refreshview? It updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated due to changes in underlying objects.

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

What is sp_refreshdatabase ‘fromSourceBackup.bak’? There isn’t anything like this as a system stored procedure in SQL Server. It might be a user-defined function.