asked
How to find out the activity which increasing the response time of the Sql Server?
We are using SQL Server 2008 as our database server. Recently we came to know, by using a 3rd part tool, that the response time of our database server is increasing at a specific time for example at night 1AM to 4AM. That 3rd part 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 there 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. thanks for your valuable time & help.
Schedule a [server side trace][1] during that time frame to capture what is going on and anlyze the trace.(you can use [clear trace][2] to aggregate it for you) Also check if other resource intensive processes are scheduled at that time frame(like SQL or windows backup, integrity check and index rebuilding etc) [1]:
http://sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ [2]:
http://www.scalesql.com/cleartrace/download.aspx
Chances are you are on a SAN or possible that your SQL Server is virtualized. 1AM to 4AM is a great window for lots of different maintenance. I would run some performance counters to see what is being hit during that timeframe to see what is impacting your server. I personally would be looking at disk. Your disk que length during that timeframe. Ask your Systems folks to also research. If you are on SAN, backups could be saturating the subsystem, network, etc. What information is your tool giving you to let you know you are having response time issues?
