Performance Monitoring with SQL Server 2008

Vinod_SQL — Tue, 29 Jan 2008 08:12:00 GMT

Since enterprise data keeps exploding and growing both in terms of size and complexity it is important that we give consistency and predictability as far as performance is concerned from data access is concerned. With SQL Server 2008 there are a number of features one might want to use to collect, analyze, monitor and report performance related data. In this blog post we will not be able to cover all the aspects but I will try to give some of the interesting ones from the good old system monitoring counters that have been added as part of this release of SQL Server.

A quick glance!!!
As I said, SQL Server 2008 is equally loaded for performance collection, monitoring and reporting. One of the important features not to be missed is “SQL Server Performance Studio” – Data Collector feature. The version 1.0 which was basic yet powerful was a project I saw over codeplex called DMVStats (http://www.codeplex.com/sqldmvstats). But must admit this one takes the whole experience to the next level and with UI, Automation and standard reporting this is simply an awesome feature. DBA’s dreams come true!!!

And the usual old-timers of SQL Server profiler, Database Engine Tuning Advisor, SQL Server Management Studio Reports and the SQL Server Management Packs still exist and are available to you. We will talk on those features maybe on a different post some other day.

PerfMon - back on track!!!
In a SQL Performance case, PerfMon is typically used to performance system-level bottleneck analysis. It is important to do this type of analysis near the beginning of troubleshooting; for example, if you don't know whether the primary bottleneck on the system is disk or CPU, how do you know whether to focus on Query A (the most expensive query in terms of CPU) or Query B (the most expensive in terms if I/O)? Hence from a getting started to performance monitoring the tool to look forward is the good old PerfMon Tool.

There are tons and tons of these counters available also on SQL Server 2005 version on various buckets like DB Engine related (Access methods, backup, buffer, CLR, cursor, mirroring, stats, locks, transactions etc), Service Broker related, Agent related, replication related, RS related, IS related, HTTP Endpoint related and so on. Getting into each and every of these is a herculean task and might not fit inside this single article. I did write some basic counter descriptions that you can take a look at: http://blogs.sqlxml.org/vinodkumar/archive/2008/01/17/sql-counters-series-1-aggregated.aspx

Moving onto SQL Server 2008 specific there are some new counters that get introduced with certain features like Resource Governor. These features are exciting and open new options to developers and administrators when working with SQL Server.
Resource Governor (RG) introduces a whole new concept of Resource Pools (http://msdn2.microsoft.com/en-us/library/bb934084(SQL.100).aspx) and Workload groups and we have monitors for the same under separate counters set. Let me outline both:

Counter		Description
Active memory grant amount (KB)	:	Total granted memory to the pool workers
Active memory Grant count	:	Count of memory grants that assigned to pool workers
Cache memory target (KB)	:	Current memory target for cache memory
Compile memory target (KB)	:	Current memory target for compile memory
CPU control effect %	:	The affect of the resource governor controls calculated as (CPU usage %) / (CPU usage % without RG)
CPU Usage %	:	CPU Usage by the active workers in the pool
CPU Usage Target %	:	The target CPU usage for the pool base on the pool configuration setting and the current activity on the SQL Server
Max Memory (KB)	:	Maximum memory the pool can have
Memory grant timeouts/sec	:	Actual timeouts for pool workers waiting for memory grants
Memory grants/sec	:	Current memory grants for workers in the pool
Pending memory grant count	:	Memory grant attempts waiting for a grant or to timeout
Query execution memory target (KB)	:	Memory broker target for query execution memory
Target memory (KB)	:	The target memory the pool is attempting to obtain
Used memory (KB)	:	Amount of memory used by the selected pool

As you can see that RG is all about throttling the Memory and CPU requirements for different workloads. And hence each of these counters can be used to drill into specific instances if required. Now moving to the next concept of Workload groups and what counters were introduced in them:

Counter

Description

Active parallel threads

:

The number of parallel threads in use by the workers in the group

Active requests

:

The number of active requests for the group

Blocked tasks

:

Number of tasks in this group that are currently blocked

CPU usage %

:

The current amount of CPU used by the requests in the group

Max request CPU time (ms)

:

Maximum amount of CPU time used by a request in the group

Max request memory grant (KB)

:

Maximum memory grant used by a request in the group

Query optimizations/sec

:

Number of query optimizations per second for the group

Queued requests

:

Number of queued requests for the group

Requests completed/sec

:

Rate of request completions for the group

Reduced memory grant/sec

:

Rate of memory grant reductions

Suboptimal plans/sec

:

Rate of suboptimal plans selected for the group

DMVs are still alive

As I said before, PerfMon really does give some great insights but to get closer to the problem statement or to get the general health of the server – DMVs give you better insight. Just to give you an insight, if you were to collect the Top 100 queries based on the time for DistributionDB reads/writes during replication - you can use the following query:

SELECT TOP 100 st.text, qp.query_plan, qs.*
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE st.text like '%distribution%'
ORDER BY qs.total_worker_time DESC
GO

This is really powerful and you can get more details by changing the where clauses like %MSmerge% for Merge replication tables, %sp_MSupd% for Distribution Agent updates on subscriber, %sp_MSins% for Distribution Agent inserts on subscriber or removing the where clause gives you the top 100 queries based on time consumed. These are the typical statements the SQL Server Management Studio Reports use behind the scenes to get you the jazzy reports.

Hope you enjoyed the post as much as I enjoyed writing the same. Catch more of me on my webspace at: www.ExtremeExperts.com and blog at: http://blogs.sqlxml.org/vinodkumar.

Counter		Description
Active parallel threads	:	The number of parallel threads in use by the workers in the group
Active requests	:	The number of active requests for the group
Blocked tasks	:	Number of tasks in this group that are currently blocked
CPU usage %	:	The current amount of CPU used by the requests in the group
Max request CPU time (ms)	:	Maximum amount of CPU time used by a request in the group
Max request memory grant (KB)	:	Maximum memory grant used by a request in the group
Query optimizations/sec	:	Number of query optimizations per second for the group
Queued requests	:	Number of queued requests for the group
Requests completed/sec	:	Rate of request completions for the group
Reduced memory grant/sec	:	Rate of memory grant reductions
Suboptimal plans/sec	:	Rate of suboptimal plans selected for the group

Entries tagged with counters - TechNet Edge

Performance Monitoring with SQL Server 2008