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 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:


We are using SQL Server 2008.
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.
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.