Sql-server – CPU and Hard Drive Spike for 20-30 Minutes

monitoringperformancesql serversql-server-2008

I'm collecting data on the performance of our Microsoft SQL 2008 server in preparation for moving it to Azure. It's a very low load server (currently max concurrent transactions max out at 3), however, about once a day there's a CPU and hard drive activity spike for about 20-30 minutes. Transaction activity during this time is the same as other times, however, the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly). Free pages fluctuate during the spike and after the spike is done the number of free pages is higher than before. Any way to find out what might be causing this spike? Could this be some sort of garbage collection? Anything specific I could monitor via Performance Monitor to give me a better idea of what's going on?

Best Answer

...once a day there's a CPU and hard drive activity spike for about 20-30 minutes...the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly).

This spike is almost certainly due to daily maintenance tasks - specifically, index rebuilds and DBCC CHECKDB. These are very resource-intensive operations. For instance, the buffer cache hit ratio change you mentioned is likely due to index rebuilds reading every page out of various indexes as they are processed.

Any way to find out what might be causing this spike?

Look for SQL Server Agent jobs that are configured to start during this time period. You can see an overview of scheduled jobs on the instance by double-clicking "Job Activity Monitor" in SSMS under the SQL Server Agent node.

You can also check for Maintenance Plans under the "Management" -> "Maintenance Plans" node (also in SSMS).

I'm collecting data...in preparation for moving it to Azure

In terms of cost-saving in the cloud, it's possible you could forego some, or all, of the index maintenance. One way to do this would be to use Ola Hallengren's Index and Statistics Maintenance procedure, which will skip rebuilds on indexes that are small or not-very-fragmented.

Related Solutions

Sql-server – SQL Server 2012 CPU spike due to LinkedServer – how to find the offending query

To answer the question: How to find the offending query:

Since the spikes in the graph you posted last for several minutes you have plenty of time to use the following method:

Download sysinternals process explorer

start process explorer and find the SQL Server process.
right click and select properties
look at the thread tab.
Sort on the CPU column and note the thread id (TID) that is consuming the most CPU.

Use this query and lookup the query that is currently being executed by that thread:

SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <thread id>

Postgresql – Postgres 9.3 – deletes terribly slow for 10 minutes after dropping indexes/constraints

First, sorry if I have a bad English.

Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of the deletion that you're trying to do.

Create indexes on the foreign keys of the child columns that are pointed to the main table you are trying to delete, this will solve the problem and improve performance for inner join also. Remember PostgreSQL will not create indexes for the foreign keys automatically.

Best Answer

Related Solutions

Sql-server – SQL Server 2012 CPU spike due to LinkedServer – how to find the offending query

Postgresql – Postgres 9.3 – deletes terribly slow for 10 minutes after dropping indexes/constraints

Related Question