Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Response time keeps changing

Posted on 2011-09-21
5
Medium Priority
?
372 Views
Last Modified: 2012-05-12
Hello,

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?

Question 2:
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?

Thanks.
0
Comment
Question by:sath350163
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Author Comment

by:sath350163
ID: 36577541
Forgot to mention that I'm running SQL SERVER 2008 EXPRESS EDITION in Windows 7 operating system.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36577607
>>Is there a reason for this? <<
In a word cache.

>>How to baseline the response time? <<
Clear the cache and make sure stats are updated.

>>Nothing changed in between.<<
Sure it did.  To be precise 48 hours happened.

>>What is the reason behind this?
How to baseline if the results are so inconsistent?
See responses to question 1.
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1000 total points
ID: 36577837
One other thing to note.  Some databases that are created in SQL Server Express have Auto-Close turned on.  When you have that setting turned on for the database, when the last connection exits, the SQL Server engine will close the database.  When the next connection requests that database, it will open the database and the response times could vary each time.

So you are running into Cache issues as well as potentially the Auto-close problem as well.  Check your database settings in Properties.  Or look at the SQL Server Error logs to see what is going on in the SQL Server that could also be impacting things.
0
 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 36578782
It's not only for yours, max of the users are having same kind of issues. The response time is going to varie with in a showt time even, the reason for this is mainly depend on the back end processes running in OS level (and also might there will be changes in sql settings if any) will also leads to variation in response time.

Frequently update statisctics and re-indexing will give the best results.

Find out if any blockings are happening on the SQL backend and monitoring the disk space and the number os sessions running will also give us huge difference in response time.

As you mentioned the response time has been varied after few days even after making necessary changes to the query; This will due to statistics has been not updated might there will be inserts and deleted happened in the database level which will surely shows a variation in query O\P.

The below link might help you:
http://msdn.microsoft.com/en-us/library/ms172984(v=sql.100).aspx
0
 
LVL 1

Expert Comment

by:Sleepydog
ID: 36578881
Looking at graphical execution plan may be helpful determining why your query is slow. It will show where the bulk of the query time is being used (joins  index scans, sorts, etc).

Take a look at your query as well, can you modify it and get better performance?  

Can you break your query, loading some results into temp local tables, then use those in a latter part of your query?
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question