How can I find a badly running query in SQL Server?

The symptom is that our database server occasionally gives timeout exceptions when it's running a stored procedure which selects data from a particular table. It's been doing this about once a week. We restart the database and things seem to run normally. Sometimes it recovers on its own if we just wait a few minutes.

This started happening after we changed a clustered index on the table in question, which is a large (900k rows), often-queried table.

We obviously need to mitigate this, but we can't find the scenario in which the database server starts to time out. I've examined the sys.dm_exec_query_stats table, but I can't seem to find the particular stored procedure which causes the problem, much less the values being passed to that stored procedure which cause slow performance.

Is there some sort of more granular logging in SQL server I can use to find the particular query or stored procedure t开发者_C百科hat is causing the slow performance? Then, once I find that, is there a way to see the values passed to that stored procedure or query in parameters?

One possibility is to use SQL Profiler (or some similar tool) to point out the slow ones, and focus your efforts there.

Our DBAs use an approach that effectively involves a SQL Agent job running a stored procedure (every 20 seconds or so) that logs the output of a call to sp_WhoIsActive to a table. sp_WhoIsActive can output all kinds of useful information about the currently active queries including execution time and execution plan.

It sounds like it could be numerous things. For instance, you could have insufficient indexes on the table. What does the execution plan say? Is there a table scan happening?

If the query time shifts, it sounds like outdated statistics. Try to update the stats and see if that helps.

Also, just a side note. If you don't want a timeout on your SqlCommand object, set the CommandTimeout to zero.