0

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.

Improve this question
2
  • 1
    What are you referring to by response time? Execution time of certain queries, network round trip, IO completion?
    – Mark Storey-Smith
    Commented Dec 8, 2011 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
    Commented Dec 10, 2011 at 18:31

2 Answers 2

Reset to default
4

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.

Improve this answer
0
1

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)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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