In our production server, we are running SQL Profiler to find which SQL are non performant, to increase our overall performance and cpu usage of SQL Server.
During our investigations, we found that a SYSTEM request is executed exactly every 2 minutes, and uses a lot of CPU.
Our SA login is disabled, but the request runs on the SA account.
The spid of the process is low, always between 10 and 20, so it'as a system process.
When it executes, it gets 100% cpu for 10 secondes, blocking every databases. Every 2 minutes exactly (2 minutes after the end, every 2 minutes 10 seconds)
We have 4500 databases in our system but they are not very big. They use 28 gigabytes, but only 30 of them are over 100 megabytes.
Our system is performing well. I only want to know what happen every 2 minutes.
This is the query I see in profiler, running in the MASTER database.
We are using SQL2005 sp3 64 bits on Win2008 sp2 64 bits
NTILE(100) OVER(ORDER BY metrics) AS nt
mig.index_group_handle AS group_handle,
mig.index_handle AS index_handle,
View Complete Post