What to do if your SQL Server system is slow 0. per Madhu below if you think this is caused by a big query, and want to kill it, follow the steps in the replies below on blocking otherwise, proceed 1. run Performance monitor with the following counters: Object Counter Instance Processor % Processor Time _Total PhysicalDisk Avg. Disk sec/Read _Total PhysicalDisk Avg. Disk sec/Write main Log disk only SQLServer:GeneralStatistics Logins/sec SQLServer:SQL Statistics Batch Request/sec SQLServer:SQL Statistics SQL Compilations/sec SQLServer:SQL Statistics SQL Re-Compilations/sec SQLServer:Latches Total Latch Wait Time (ms) SQL Server:Locks Lock Wait Time (ms) _Total Watch for peaks, note the height and duration of peaks note the typical value away from peaks (not simply the average value displayed) a. if % Processor Time is high, sustained over 50%, peaks over 70% then we need to look for CPU intensive queries b. if disk read latency (Avg Disk sec/Read) is high, > 0.020 sec (or 20ms) sustained, peaks over 0.040sec then your disks are overloaded, 1st see if we can make the queries more efficient 2nd get more disks, spread data over as many physical disks as possible c. of log disk write latency is high, >0.005 (it should read 0.001 or less) then there is no option but to put the log on its own disks, and configure it right this cannot really be fixed with query or index tuning unless you count reducing the SQL that write to the database ie, turn off the transactions d. if Logins/sec is sustained high, >10 or even >1, consider using connection pooling e. if SQL Batch Requests/sec is very high, > 1000/sec be very careful about running profiler, apply the CPU filter, possibly only briefly w/o the CPU filter DO NOT capture the stmt events f. if Compiles are high, >10, use stored procedures for the high volume calls g. if Recompiles are high, > 2-5, find the stored proc that are recompiling and fix it there is a MS article on this, search: Lubor Kollar h. suddend increase in Total latch wait time could be blocking, investigate below, 2. run Profiler, start a new trace a. General Template: "SQL ProfilerStandard" Check the: "Save to file" option NEVER Save to a table on the production server, NEVER EVER b. Events: keep just Stored Procedure->RPC:Completed" & "TQL->SQL:BatchCompleted" c. DataColumns: add DatabaseID , also add EndTime if you want to use Read80Trace d. Filters: set "CPU Greater than or equal" 10 learn to parse Profiler traces search PSSDIAG, RML and Read80Trace on the MS site PSSDIAG data collection utility, the KB article is new, but the PSSDIAG download is old, feel free to bug MS for the latest version, then share with us. http://support.microsoft.com/kb/830232 Internal SQL Server Diagnostics Tools, Part 1: PSSDiag http://msdn2.microsoft.com/en-us/library/aa175399(SQL.80).aspx Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS http://support.microsoft.com/kb/887057 it is a good idea to learn how to use PSSDIAG anyways because if you need to open a case with Microsoft PSS, they will ask you to use it to collect info. Learn which items can be cleared to avoid overly large data collection 3. General information exec xp_msver GO exec sp_configure GO provide the results of each 4a. Slowness not attributed to System CPU/disks or SQL queries Was a SQL data/log file growth involved, I usually don't look for this Check the SQL Server error logs, usually in the directory: Crogram FilesMicrosoft SQL ServerMSSQLLog Look at the ERRORLOG.X and SQLDumpXXXX.txt files if there are indications of errors generated by the SQL Server engine itself, it is a good idea to open a case with Microsoft PSS 4b. Mysterious slow down -If your system runs well initially, but over time becomes horribly slow over time -Intermitent severe slow downs that clear with no obvious reason with a corresponding drop in the perf counter: Process->Sqlserv->Virtual Memory -And the problem cannot be traced to CPU, disk or network, bad query plans, compiles etc -Queries with low CPU that normally run quickly, now run slow (but cpu is still low) -Sysprocesses show many queries with wait time, but eventually going through -The SQL logs show messages like: reserve contiguous memory of Size=65536 or 131072 bytes failed (this could be any power of 2, but failure to allocate 4-8MB is probably not a severe problem -A restart of SQL Server clears this problem or a while This may be VAS problem that i have talked about in other posts there is a good test for this in SQL 2005 using DMV, for SQL 2000 see: FILE: Use Xpvmlog to Dump the Layout of Virtual Memory in SQL Server http://support.microsoft.com/kb/279113 There may not be enough virtual memory when you have a large number of databases in SQL Server http://support.microsoft.com/kb/316749 FIX: Cursor Plans Are Not Removed From the Cache When Virtual Memory Depleted http://support.microsoft.com/kb/818095 How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 http://support.microsoft.com/kb/907877 See the post below for more discussion on this http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19498 According to KB316749, the default VAS set aside as increased from 256 in SQL 7 to 384 in SQL 2000, I was not aware of this change, so if your are on SQL 2000, and have very good and strong reason to suspect VAS, try setting the startup parameter -g512 this is a band-aid, not a fix some MS articles: Troubleshooting Performance Problems in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx SQL Server 2005 Waits and Queues http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx Inside SQL Server 2000's Memory Management Facilities http://msdn2.microsoft.com/en-us/library/aa175282(sql.80).aspx I have seen these, but never used it Microsoft Product Support (MPS) Reporting tool (MPSRPT_SQL.exe) http://support.microsoft.com/kb/883724 Microsoft Best Practices Analyzer tool http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en Microsoft SQL Server 2005 Upgrade Advisor tool http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en Microsoft SQL Server Health and History (SQLH2) tool http://www.microsoft.com/downloads/details.aspx?FamilyID=eedd10d6-75f7-4763-86de-d2347b8b5f89&DisplayLang=en From the Troubleshooting article for SQL Server 2005, the DMV query quickly finds top cpu loads select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc for finding VAS issues Internal virtual memory pressure VAS consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV. VAS summary can be queries using the following view. -- virtual address space summary view -- generates a list of SQL Server regions -- showing number of reserved and free regions of a given size CREATE VIEW VASummary AS SELECT Size = VaDump.Size, Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( --- combine all allocation according with allocation base, don't take into --- account allocations with zero allocation_base SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size, region_allocation_base_address AS Base FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION --- we shouldn't be grouping allocations with zero allocation base --- just get them as is SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VaDump GROUP BY Size The following queries can be used to assess VAS state. -- available memory in all free regions SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] FROM VASummary WHERE Free <> 0 -- get size of largest availble region SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0 If the largest available region is smaller than 4 MB, we are likely to be experiencing VAS pressure. SQL Server 2005 monitors and responds to VAS pressure. SQL Server 2000 does not actively monitor for VAS pressure, but reacts by clearing caches when an out-of-virtual-memory error occurs.