I did a query like this:
--see the top ten events your server is waiting on
SELECT TOP 10
max_wait_time_ms wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH',
...)ORDER BY wait_time_ms DESC
and found the top one now on the server is FSAgent, which is the file stream agent.
It uses 65% total waits.
Is this supposed to be right, we have one database enabled using file stream.
Could this be the culprit that causes the slow performance when restoring databases?
We are currently using SQL 2008 SP3.