SQL Server Performance

What to do if your SQL Server system is slow

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by joechang, Feb 16, 2007.

  1. joechang New Member

    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,
    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.

    Internal SQL Server Diagnostics Tools, Part 1: PSSDiag

    Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS

    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
    exec sp_configure

    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 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
    There may not be enough virtual memory when you have a large number of databases in SQL Server
    FIX: Cursor Plans Are Not Removed From the Cache When Virtual Memory Depleted
    How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

    See the post below for more discussion on this

    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

    Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

    SQL Server 2005 Waits and Queues

    Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

    Inside SQL Server 2000's Memory Management Facilities

    I have seen these, but never used it
    Microsoft Product Support (MPS) Reporting tool (MPSRPT_SQL.exe)

    Microsoft Best Practices Analyzer tool

    Microsoft SQL Server 2005 Upgrade Advisor tool

    Microsoft SQL Server Health and History (SQLH2) tool

    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,
    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
    Size = VaDump.Size,
    Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
    --- combine all allocation according with allocation base, don't take
    --- account allocations with zero allocation_base
    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
    --- we shouldn't be grouping allocations with zero allocation base
    --- just get them as is
    SELECT CONVERT(VARBINARY, region_size_in_bytes),
    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.
  2. madhuottapalam New Member

    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....

  3. MohammedU New Member

  4. joechang New Member

    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
  5. Luis Martin Moderator

    I change this to Sticky.

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

    Luis Martin

    All in Love is Fair
    Stevie Wonder

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

  6. gkovalsk New Member

    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 :

    "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.

  7. joechang New Member

    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,

    if this is a critical server, call MS PSS first
  8. joechang New Member

    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

    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

    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

    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.

  9. MichaelB Member

    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!


    "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!"
  10. TheSQLGuru New Member

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

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

    the error("Exception of type 'System.OutOfMemoryException' was thrown) will appear.
    happen What should memeory consumtion for SQL for 4 GD RAM
  13. Luis Martin Moderator

    Welcome to the forums!.
    This thread is 5 years old.:)

Share This Page