Intermittent slow query when using WHERE clause

I'm using SQL Server 2008 and just recently started having an intermittent problem while querying a database.

At least once a day I'm having timeouts with many of our applications because of a slow query. There is no particular time this happens; sometimes in the morning, sometimes afternoon. Every time I begin troubleshooting the problem, it fixes itself within minutes.

Normally I use this query:

SELECT Name FROM Demographics WHERE Name IS NOT NULL

and it runs in < 1 second. However, during these "problem times" the query will take around 3 minutes. Once the query goes through, I can run it a开发者_Python百科gain and it works just fine (almost instantly).

Also, while the query above is running, I can use this:

SELECT Name FROM Demographics

and it runs perfectly. No delay. The only difference is the WHERE clause. So, where do I begin troubleshooting? What tools should I be using to find the cause?

Thanks in advance.

The first thing to do is to look at the execution plan of the query. To do this, open a query window in Management Studio, and then choose Include Actual Execution Plan in the Query menu. Run your query, then go to the Execution Plan tab and save the plan.

When you see the performance problem, repeat these steps. Then, load both execution plans, and compare them to see what is different. If there are differences, they will probably point you in the right direction to find the problem.

Look to see if you are being blocked by another process during the trouble periods.