MS SQL Server slows down over time?

We found it. It turned out that it was actually a web server that had a problem with one of it's app pools. It would get stuck running the same set of queries over and over (which happened to deal in temp tables). It would just loop and loop and eventually cause the SQL server to be sad. Once this offending machine / app pool was found and 'put down' everything was resolved.

You have to ask yourself, what happens at a SQL service restart? Lots of stuff, but two relevant points come to mind:

1) SQL memory is freed.

Its possible (not sure how likely), that if your MaxMemory setting is set too high, that the SQL service grows to use all available memory, and Windows starts to swap important stuff out to the swap file. Check to make sure that MaxMemory is set to a reasonable value, leaving enough additional memory for whatever else needs to run on that box (is it a dedicated SQL server? Or is it also the app server?)

2) TempDB is rebuilt from the default sizes.

Check your default tempdb file sizes, especially the default size and growth interval of the TempDB Log file. If the growth interval is set too LOW, then the log can build up some incredible internal fragmentation, which can dramatically slow down normal usage. See these two excellent blog articles by Kimberly Tripp.

Do you make heavy use of temporary tables or cursors? Check any cursors are being closed and deallocated correctly. Also watch out for linked servers - we've got to use a buggy driver for an old linked Informix server and it periodically means we have to reboot the server.

If it looks weird then look for the weird.

If tweaking sql server settings doesn't help try the windows task manager: go to processes tab, then options > columns > add cpu time, handles, read, write, other and the memory options.

Go back to the process list. For each column sort by highest to lowest and look at the top 5 processes. Anything out of the ordinary? e.g. A memory leak on a process will have a bizarre number of handles. We have some *ki printers which add a handle to the DCSLoader process every 2 seconds. After a few weeks a machine lists lots of free memory and cpu but a process with 100,000 handles and will barely move the mouse pointer.

Check your scheduled tasks list too. Tell your AV not to scan .mdf files.

Dave,

Have you checked the wait stats? the query you gave above lists the 'last_wait_type' column. that column may have some details regarding what the queries are waiting for (network, cpu, etc.)

If your backup "Recovery model" is FULL, then does taking a backup of the DB and then a backup of the transaction logs improve things at all? On a system that is running out of disk space, this kind of thing might explain the problem.

I seem to have a configuration very similar to yours (16Gb, upgraded to 32Gb, and MD1000 with a terabyte of disks, dual quadcore xeon).

The only thing that has helped me diagnose bizarre problems like that in the past is beta_lockinfo by Erland Sommarskog. Run it when it's slow time and compare.

Also I've had an insane amount of problems with SQL 2005 before SP2, but SP3 is really stable.

Hope this gives more useful info:

SELECT  D.text SQLStatement,
        A.Session_ID SPID,
        C.BlkBy,
        ISNULL(B.status, A.status) Status,
        A.login_name Login,
        A.host_name HostName,
        DB_NAME(B.Database_ID) DBName,
        B.command,
        ISNULL(B.cpu_time, A.cpu_time) CPUTime,
        ISNULL((B.reads + B.writes), (A.reads + A.writes)) DiskIO,
        A.last_request_start_time LastBatch,
        A.program_name
FROM    sys.dm_exec_sessions A
        LEFT JOIN sys.dm_exec_requests B
        ON A.session_id = B.session_id
        LEFT JOIN (
                   SELECT   A.request_session_id SPID,
                            B.blocking_session_id BlkBy
                   FROM     sys.dm_tran_locks AS A
                            INNER JOIN sys.dm_os_waiting_tasks AS B
                            ON A.lock_owner_address = B.resource_address
                  ) C
        ON A.Session_ID = C.SPID
        OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
WHERE   DB_NAME(B.Database_ID) = 'YourDBName' -- Comment out line for all db's
ORDER BY ISNULL(B.cpu_time, A.cpu_time) + ISNULL((B.reads + B.writes), (A.reads + A.writes)) DESC

Make sure db is ok with:

DBCC CHECKDB -- Checks the allocation and structural integrity of all the objects in the specified database.
DBCC UPDATEUSAGE (bybox) -- Reports and corrects pages and row count inaccuracies in the catalog views

Keep an eye on logspace with:

DBCC SQLPERF(LOGSPACE)

If you see expansion going on, that will definately slow things down. If you run this you will see your logspace get nearer and nearer to 100%, then the log will expand and the percentage will shrink as its got some space. Hopefully you'll never get to see it expand before your backup kicks in and clears down the log.

Mostly idiot configuration. Happens.

• First, you should actually regularly run index defrag in a maintenance run. Schedule it as activity, just before or after you make backups.

• Second, do not autogrow your database and especially do not autoshrink it. Depending on load autogrow / autoshrink are basically suicide settings.

Not seen a slowing down SQL Server like that pretty much ever. Can you post the results of that query under times of hugh stress? Sure nothing on your end overloads SQL Server at that time?