Performance Monitoring with SQL Server 2008
Publish Date: 1/29/2008
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.
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:
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)