There are a lot of new features in SQL Server 2012. One of my favorite is sp_server_diagnostics. Using sp_server_diagnostics, you can collect details from your servers pertaining to the System, Resources, Query Processing, IO Subsystem and other Events detail. The procedure has one optional parameter of @repeat_interval. This is the time iteration that the procedure will use to run repeatedly, to collect the server statistics. The parm has a default of 0, which is used if you do not pass in your own value. In this case, it will collect the data only once, and then exit. I typically pass in a @repeat_interval of 5, which tells the procedure to run, collecting the statistics repeatedly, until it is cancelled. This is the output:
Very generally, these are the descriptions for each of the returned components:
System -- Returns data regarding the system, which includes CPU usage, page faults, non-yielding Tasks, severe processing conditions, access violations, and even spinlocks.
Resource -- Much like it sounds, this collects data regarding the system resources, to include physical and virtual memory, page faults, caching, and other memory objects.
Query Processing -- This component returns data pertinent to the query processing, such as wait types, worker threads and very intense query requests.
IO Subsystems -- Here we return data for the IO subsystem, to include timeouts and data for long running IO requests.
Events -- Returns data for event exceptions. This would include ring buffer and buffer pool events, memory and security exceptions, and even connection failures.
If you're responsible for any SQL Servers, I'm sure that you have often had to analyze your server performance. This is a new tool that helps you do just that very quickly.
https://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx
No comments:
Post a Comment