Query Response time keeps changing
Posted on 2011-09-21
Question 1:I have this read only database against which I'm running couple of queries to record their response time.
Inspite of data being static and execution plan being the same, the response time keeps varying a lot.
When I run the query immediatedly after restarting my sql server, I get 3453ms, I shut down the sql server service, restarted after sometime, and reran the query. The second time, the response time is 8399ms.
Even without restarting sql server service, the response time of same query differs between each time the query is executed.
How to baseline the response time?
What is going on?
Is there a reason for this?
Also, based on the first response time of 3453ms, I tuned the queries by
- Adding additional indexes
- Replaced some queries with indexed views
- Made the index fill factor as 100% as the table is a read-only table and the data is refreshed only once a month.
- Made the database itself as Read-only
- Gathered STATS
After doing all this query response time went down to around 2075ms.
But when I tried the same set of queries after two days, and the response time went up to 5451ms.
Nothing changed in between.
What is the reason behind this?
How to baseline if the results are so inconsistent?