As a DBA you must keep this coin with two-side: Baseline and Benchmarking, irrespective of number of instances or databases that you support.
Lets call them as B&B (not bed and breakfast), because they are essential when you are dealing any performance problems.
In general getting know more about performance monitoring methods is not quite easy as every environment is independant to each other. For your own environment you should setup a baseline & benchmarking in order to assess the system resources usage that will help to design the system for future capacity requirements. Not much technical thinking here but you must have a calculations in hand to provide for such figures if your Business functional personnel is planning for a new rollout of application or any methods that will cause huge load of data usage on the SQL Server databases.
Baseline & Benchmarking will help the Service Levels to keepup to the levels of satisfactory availability of the application, this also enbles the service orientation behaviour with a proactive manner that avoid any unprecedented activity within the database environment, find the problem with a consistency of the services. This will help you be familiar with your application operational behaviour, to streamline any issues that are persistent and also takes you to the route of performance monitoring. Wherever possible it is a good practice to document all the findings you will get during these exercises, do not leave even a small information such as starting up a tool or service. There are many good resources available on the web to search on the name of best practices, but to keep in touch you could visit SQL Server Best Practices page on Microsoft, I feel not everyone will take time to go through these pages.
When you talk about Benchmarking it is to define the business requirements, including functional, performance, and scalability and translate them into system specs. At the same time you need to generate a workload script, accounting for user load and transactions during busy times & quiet period on the databases. You need this in order to stabilize the environment of and enable monitoring on monitored server, not simply documenting or defining benchmarking would do any better. For the best results you have to execute baseline test; benchmark tests scale up in phases and assess the results. If you are in doubt you could go through your Service Management team to get more information on the Business requirements, few predicatable ones will be as follows:
- system cannot be down for more than X minutes
- data must be kept live for 3 months
- backups must be stored offsite for 7 years -- this is a MUST for financial organisations
- database size is expected to be…
- obtain industry standards test like TPC-C, TPC-E, or TPC-H
So after you have setup the baseline & benchmarking it is one of the best practices to document the process that will reduce the performance overhead and impact on the systems. So talking more about monitoring takes into usage of PERFMON (SYSMON) tool by capturing the relevant counters and SQL PROFILER for slow query performance monitoring. Always remember to minimize the number of counters you are monitoring, run counter logs from a different machine than monitored server and same time write the counter logs to a different physical disk than the one you are monitoring. When you find the system is very busy and you need a monitoring to run then better to log the results to disk than watching the System Monitor Graph, since writing the log file to disk requires fewer resources.
Then comes the proactive monitoring phase which is using system default configuration such as PERFMON set to 15 minute polling frequency. The following counters have been used by me whenever I need to take a Performance Monitoring checkout on a client's site:
Memory – Pages/sec
Network Interface – Bytes total/sec
Physical Disk – Disk Transfers/sec
Processor - % Processor Time
SQLServer:Access Methods - Full Scans/sec
SQLServer:Buffer Manager – Buffer Cache Hit Ratio
SQLServer:Databases Application Database - Transactions/sec
SQLServer:General Statistics - User Connections
SQLServer:Latches – Average Latch Wait Time
SQLServer:Locks - Average Wait Time
SQLServer:Locks - Lock Timeouts/sec
SQLServer:Locks - Number of Deadlocks/sec
SQLServer:Memory Manager - Memory Grants Pending
--you may add additional or remove above counters based upon your own baseline testing.
Once you have collected the counters within SQL Server 2005 it is very easy to correlate these counters results to SQL Profiler and using various DMVs such as sys.dm_os_memory_caches, sys.dm_os_memory_clerks & sys.dm_os_memory_objects that will relate to MEMORY usage, and also sys.dm_os_threads to figure out thread memory consumption as this will give information for the threads used by SQL Server to identify if the thread was created by SQL Server, when it was started, memory usage time in the kernel and user mode. The operating system threads information will let you understand about number of DLLs occupying the SQL Server VAS, more about VAS.
Coming to the PROFILER usage it is not a good practice to invoke whenever you get a complaint about performance loss against a database, so sometimes you might use sys.dm_exec_query_stats DMV instead of SQL PROFILER, for instance to get top x queries by average time:
SELECT TOP <replacenumber> creation_time, last_execution_time, total_clr_time,
total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_clr_time/execution_count DESC;
In this regard Books Online clearly states that using this DMV might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.
Moreover using Performance Dashboard reports will get more information on running these manual capture of counters, you might have gone through of PerfMon data to build generalized graphs of performance and SQL Profiler to drill down on possible culprits of bad performance. Incidentally October Technet magazine covers similar section of point: http://www.microsoft.com/technet/technetmag/issues/2007/10/SQLCPU/default.aspx link, also referring the related blog posts on this site - HighCPU-tags & DMV-relatedTags links.
Finally not to mention about the website that is dedicated to performance tuning related content, refer to SQL Server performance site.
I'm dedicating this blog post to the beginners that are asking very broad questions in the forums such as my system is slow how to montior the performance, start with a small set of counters on your end to get better understanding of these tools.