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:
C:\Program Files\Microsoft SQL Server\MSSQL\Log

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.

probably... you can start with SP_Who/SP_WHO2 to see the blocking and Also error log for any abonormalities... Then have a look at TaskManager to see the resources usage....

Madhu

ClearTrace can used in place of Read80trace, ClearTrace works with 2000 and 2005...
http://www.cleardata.biz/cleartrace/default.aspx

How to monitor blocking in SQL Server 2005 and in SQL Server 2000
http://support.microsoft.com/kb/271509


MohammedU.
Moderator
SQL-Server-Performance.com

sp_who2 is the system procedure version of SELECT xx FROM sysprocesses

if you have good reason to believe that your system is slow because of high volume, ie, its consistently slow during the busy periods of the day
stick with Profiler, skip this

if there is reason to believe your system is slow because of a specific big query, ie, the system is intermittently slow
you can use sp_who2, direct query to sysprocesses
or sp_blocker_pss80 in the above MS KB article

the general idea is: for the queries being blocked,
find the blocking queries, which in turn may also be blocked
until you find the blocker, ie, a query that is running,
but not being blocked

use DBCC INPUTBUFFER to see the SQL for the blocker
the trick with this approach is that you will only see the blocker query if it is still blocking when you run DBCC INPUTBUFFER

so it really only works if its a big query
otherwise it might send you down the not most correct track

I change this to Sticky.

If moderators don't think so, let me know.


Luis Martin
Moderator
SQL-Server-Performance.com

All in Love is Fair
Stevie Wonder


All postings are provided “AS IS” with no warranties for accuracy.

Hello Joe,

We are experincing issues pretty similar to which you described however in our case CPU is high 130% out of 200 (there is 2 CPUs) and there are no 65536 messages during these outages :


Quaote:
"4b. Mysterious slow down
-If your system runs well normally,
but over time becomes horribly slow
and the problem cannot be traced to CPU or disk or network,
-Queries with low CPU that normally run quickly, now run slow
-Sysprocesses show many queries with wait time, but eventually going through
-The SQL logs show messages like allocatee 65536 bytes failed
-a restart of SQL Server clears this problem or a while"


The question is how can I troubleshoot VAS problems?
The links you provided doesn't bear any helpful information in terms of VAS isues.

Thanks,
Gary

provide more info,
the output of xp_msver would help

the troubleshooting doc does discuss VAS for SQL 2005,
but you have to actually look for it, and read it

i am not inclined to point to a VAS problem if there are no allocation failures in the SQL logs,
does your app use API Server cursors (sp_cursor, sp_prepare etc)
does it use xprocs
the sp_OAxxx stuff

does a restart of SQL clear this issue?

to actually prove a VAS problem in SQL 2000 involves writing a xproc that walks the address space of the SQL process, which is scary on a production server

just remembered, xpvmlog,
http://support.microsoft.com/kb/279113

if this is a critical server, call MS PSS first

additional note on VAS from MS KB articles:

There may not be enough virtual memory when you have a large number of databases in SQL Server
http://support.microsoft.com/kb/316749

WORKAROUND
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.
Back to the top

MORE INFORMATION
On a computer with 2 GB or more of RAM, SQL Server reserves all but 256 MB (SQL Server 7.0) or 384 MB (SQL Server 2000) of virtual address space during the startup process for use by the buffer pool. Additionally, to storing the data and procedure cache, SQL Server uses the buffer pool memory to service most other memory requests from SQL Server processes that are less than 8 KB. The remaining unreserved memory is intended for use with other allocations that cannot be serviced from the buffer pool. These allocations include, but are not limited to:
• Stacks and the associated thread environment block for any threads that SQL Server creates. After SQL Server creates all 255 worker threads, this is approximately 140 MB.
• Allocations that are made by other DLLs or processes that are running in the SQL Server address space (which varies from system to system), such as:
• OLE DB providers from any linked servers.
• COM objects that are loaded by use of the sp_OA system stored procedures or extended stored procedures.

• Any images (.exe or .dll) that are loaded in the address space, which commonly use 20 to 25 MB, but possibly more if you are using linked servers, sp_OA, or extended stored procedures.
• The process heap and any other heaps that SQL Server might create. During the startup process, this is typically 10 MB, but may be more if you are using linked servers, sp_OA, or extended stored procedures.
• Allocations from SQL Server processes that are greater than 8 KB, such as those required for large query plans, send and receive buffers if the network packet size configuration option is close to 8 KB, and so on. To see this number, look for the OS Reserved value that is reported in DBCC MEMORYSTATUS and that is reported as number of 8-KB pages. Typical values for this are 5 MB.
• An array to track status information for each buffer that is in the buffer pool. This is typically about 20 MB, unless SQL Server is running with Address Windowing Extensions (AWE) enabled, in which case it can be significantly higher.


FIX: Cursor Plans Are Not Removed From the Cache When Virtual Memory Depleted
http://support.microsoft.com/kb/818095

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
Back to the top

SQL Server tries to use memory from the buffer pool for most internal allocations, but requests greater than 8 KB are routed to the normal operating system allocators. By default, SQL Server leaves a limited amount of address space for these types of allocations and also for things such as thread stack space, COM objects, extended stored procedures, and so on. You can modify the size of this region by using the -g command line parameter. For more information about the -g parameter, see SQL Server Books Online.

When SQL Server tries to allocate virtual memory for one of these large allocations, and that operation fails, it will try to remove cached query plans in hopes of freeing some of this memory. Before SQL Server 2000 Service Pack 3 (SP3), this operation would remove all query plans from cache, even if they were not using any of this memory. SQL Server 2000 SP3 introduced a change whereby only query plans that are known to be using this memory are removed from cache. This change introduced a problem where some query plans for cursors were not being removed.

Even with this fix, you might see the error message occasionally. Over time (minutes to hours) there may be additional cached plans that build up and the message occurs again. This can be normal and by itself should not be taken as a sign of a problem.

This is a good sticky. Thanks joe for doing this. I think many will benefit from its one-stop shop when they need some basic direction!

Michael
MCDBA

"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"

I think people who are having significant performance issues should simply contact me and ask me do a review and some mentoring for them!!  :-))

It will be good to know if you offer those services for 'free' ;-), as it is not easy to perform so.