Denali – Day 24: True Black Box Recorder – sp_server_diagnostics

Earlier sql server was having diagnoses tool.

Default trace.

SQLDiag

And other tools but could not got the one which we can all it as True Black Box Recorder
by “Bob Dorr – Principal SQL Server Escalation Engineer“” he called sp_server_diagnostics” as a true black box recorder for sql server.

“sp_server_diagnostics” is a new stored procedure introduced in Denali which captures the data of sql server activity current or with interval of 5+seconds (minimum 5 seconds).as it takes that much of time to gather required information. Default is 0, means just run once and produce the result.

Syntax:

sp_server_diagnostics [@repeat_interval =] ‘repeat_interval_in_seconds’

It reserved its own memory for execution so at any circumstances it will definitely run and produce the output like a Black Box which provides the information about the reason for the issue.

GO

Or can save the output into a sql table.

But general recommendation is to keep it outside sql server so that for any issue on sql server the Black Box will be safe and we can diagnose the issue.

CREATE EVENT SESSION [abc]

ON SERVER

           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)

           ADD TARGET [asynchronous_file_target] (set filename=’c:\abc.xel’)

GO

ALTER EVENT SESSION [abc]

      ON SERVER STATE = start

The output of sp_server_diagnostics is

• System – Information related system like CPU, and other system specific.
  
• Resource – Information related to resource like memory.
  
• Query Processing – information related to Query.
  
• IO Subsystem – information related to IO.
  
• Events – information related to stored procedure on the errors , events, security, and connectivity.
  

So using this we could diagnose the issue on sql server.

We can change the location of the log file using “ALTER SERVER CONFIGURATION”

http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx

http://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ee210585.aspx