Posted By:
Vinod_SQL |
Jan 29th, 2008 @ 12:12 AM |
5,779 Views |
0 Comments
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.
Be the first to comment!
Be the first to comment!
Forum:
Subject:
Saving...
