Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using SQL Server 2008 as our database server. Recently we came to know, by using a 3rd party tool, that the response time of our database server is increasing at a specific time for example at night 1AM to 4AM. That 3rd party tool provides no information about the activity which is causing this.

Some Facts:

  1. We are using SQL Server 2008.

  2. We have scheduled some SQL Jobs but their execution time is different; not in the range where the response time is high.

I hope my question is clear enough if not please let me know.

share|improve this question
1  
What are you referring to by response time? Execution time of certain queries, network round trip, IO completion? –  Mark Storey-Smith Dec 8 '11 at 1:44
    
What is the 3rd party tool? Do you have processes that are being impacted during that time? Just because a tool said there is a problem doesn't necessarily mean there is a problem. –  datagod Dec 10 '11 at 18:31
add comment

migrated from stackoverflow.com Dec 7 '11 at 16:14

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

I highly recommend using Adam Machanic's excellent diagnostic stored procedure sp_WhoIsActive. I've got an sp_WhoIsActive tutorial on my blog. Run it during the slow time window as I describe in the video, and it'll show the active queries, what they're waiting on, and their execution plans.

Keep in mind, though, that this is more of a DBA tool than a "here's-your-problem" tool. This is where a database administrator usually has to step in and do diagnostic work. If you're confused by the output of sp_WhoIsActive, check out the book Troubleshooting SQL Server.

share|improve this answer
    
GREAT THANKS. That's exactly what i was looking for!! I am limited by the rules to give you only one up vote otherwise at least 10 up votes answer is this. Thanks again. –  Yaqub Ahmad Jan 1 '12 at 16:01
1  
@YaqubAhmad BTW the recommended book is also available for free from Redgate here –  Martin Smith Jan 1 '12 at 16:16
    
@MartinSmith. Thanks –  Yaqub Ahmad Jan 1 '12 at 16:31
    
+1 For Adam's sp_WhoIsActive. –  Matt M Jan 2 '12 at 4:27
add comment

You note that the performance degreadtion takes place at certan times, this suggests to me that there is a process running at thoes thimes that is causeing the slowdown. So check to see if there are any backups running, or batch processes that run at that time - you may want to run a performance log to monitor CPU and disk to see if is any spike in thoes.

In SQL server, you can run a trace that will log what queries are running over time and the time they take to execute. You could run on of these to see what may be using resources or creating table locks. Start with http://msdn.microsoft.com/en-us/library/ms191006.aspx

If you have the code, you could look at the code and work out what would be running at these times, and then look to see if the things are running would cause resource use that would slow things down (so CPU, disk IO, table/index locking)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.