By: Embarcadero News
Abstract: Blog post from Dan Hotka
As a DBA what is most dreaded work would be and what keeps them awake whole night. For most of them, it is the complaint from their end users that the application using SQL Server is slow. Most of the SQL Server workloads start running slow when the DBA is not monitoring them. The toughest job is to be a DBA – trust us in this. There are enough and more tools available in SQL Server and from market that helps a DBA do his work effectively. But lesser known to DBA are a bunch of out-of-box tools that they hardly know. Some of the common challenging question asked by business and junior DBAs are:
These questions go on-and-on. There is no end to such basic requirements. In this blog post, let us take an opportunity to introduce you to the standard out of box reports available with SQL Server Management Studio.
To access SQL Server Management Studio - Server Level reports, Right click on the Server Node -> Reports -> Standard Reports -> Report of your choice. These set of reports have been with SSMS since the SQL Server 2005 days. These reports gets enhanced and few reports got added with every release of SQL Server.
There are close to 23 different reports that are worth to mention. For simplicity sake we would like to group them into 3 different categories and will take a quick tour of few outputs too.
We would like to categorize the following 12 reports from Management Studio under the current activity. The activity monitoring involves understanding the current sessions, open cursors, Top transactions, blocking transactions and much more.
Let us take a simple example for the common question of what are the components that are consuming memory inside our servers. This is available from the Memory Consumption reports.
The sample output shows how the components of Lock Manager, SQL Server BufferPool, SQL SOS (SQL OS) and other components consuming memory. This is a great starting point if we are facing any memory related issues. Additional information available for each of these components include Allocated Memory, Virtual Memory Reserved, and Virtual Memory Committed, AWE Memory and Shared memory.
If you are working with SQL Server or any database for that matter, it is inevitable that we are troubleshooting some performance related problems. The fundamental building blocks generally involve around memory, CPU and IO (Reads and writes). These standard set of reports bring some of these facets making reactive or live performance tuning easy.
Let us take a simple workload on the server and if we are to troubleshoot high CPU on our servers, then we can query the “Top queries by Total CPU Time”. This will give us the Top 10 queries in both graphical view and list view. A sample output for the same looks like:
If you are looking at,
These are sample questions that can be easily answered by the CPU Time reports.
This is the third category, we would like to add the rest of the reports into this category. Having said that, some of these reports are powerful and a must even for proactive monitoring.
As mentioned above, the first report that every DBA must look at when configuring / troubleshooting any environment would be the “Server Dashboard”. In the real sense this is a dashboard of configuration changes on the server instance. We ideally look at some of the hidden values like:
Any change in configuration is worth a note here. In the above example, we can see the Memory is being capped at 15GB, default trace is off and so on. These are critical information that any DBA needs to know as soon as they troubleshoot a new environment.
As we dissect and explore more from each reports, we highly recommend to play around with these standard reports in your environments to see what values are sent out. There is yet another set of reports available on the Database Level which can give details at a specific Database level.
Since these reports are available out-of-box from SQL Server Management Studio, these can be basic and simple step for any DBA to start their troubleshooting in their environment. Use these reports as a starting point as soon as you start troubleshooting inside a new environment. Since all these reports are built using DMVs, we can always run profiler at the background to know what queries get involved inside each of these reports.
Server Response from: ETNASC01