SQL Server Performance Monitoring
While working with customer recently, we encountered SQL issues pegging the CPU. As it turns out, a certain query was causing this performance issue. However, it was difficult to see that without the use of some SQL management tools. This is a brief article about some of those tools.
The real key to seeing what was going on was a combination of the Task Manager and SQL Management Studio Express Edition. Task Manager was needed to show when the performance hit was taking place, and SMSEE was used to show what the active SQL process was doing.
In SQL Management Studio, connect to the database. Then go to the Monitoring folder in the tree view. This is the bottom folder. Under that folder is the Activity Monitor. Right-click on this and choose to monitor active processes.
You will then see a table of all of the processes connected to the database, including management studio. This view shows which connections are actively processing and a summary of what command SQL is currently processing for that process. Note that it is a static view and needs to be periodically refreshed by the Refresh button.
By right-clicking on the active process, you can go to a detail view that shows the full query which is currently being executed. This view is also static and must be refreshed.
When the CPU was taking a big hit in Task Manager, we would look at the detail view to see the query. It was a single query which didn’t change for the duration of the CPU activity, so we knew that was the one causing the problems.
Another tool are the performance report dashboard for management studio. This contains a number of useful canned reports for looking at average and instantaneous top resource consumers (queries) for your database. These are extremely useful and are available (free) from Microsoft at: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
There is also the SQL Database Tuning Advisor built into SQL Management Studio (not the Express edition), but it only works against SQL editions greater than Express. One method of employing this would be to convert one of our test machines from a SQL Express to a standard edition SQL Server and using it with our product long enough to generate the profile data used by the advisor to suggest optimizations.
Another set of tools is the SQL Health and History Tool as well as the SQL Best Practices Analyzer. While I haven’t had time to investigate these yet, they sound useful as well, especially the BPA.