Sql-server – How to find out the activity which increasing the response time of the Sql Server

performancesql-server-2008

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.

Best Answer

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.

Related Solutions

Sql-server – Why did the UPDATE statement not complete

Check to see if these statements are blocking each other. Since they are in the same transaction. Do they somehow lock conflicting rows?

update AsyncOperationBase 
set deletionstatecode=2    
where deletionstatecode = 0 and 
statecode = 3 and 
completedon is not null and 
completedon < @p30Days   
and OperationType <> 10

update AsyncOperationBase 
set deletionstatecode=2    
where deletionstatecode = 0 and 
statecode = 3 and 
completedon is not null and 
completedon < @p3Years   
and OperationType = 10

Sql-server – Why does ‘sysfiles1’ show up in the sys.objects catalog view in SQL Server 2008

sysfiles1 is an internal catalog table. There are many of them (basically any table with object_id between 1 and 99 is, by definition, an internal catalog table). The query compiler refuses to bind queries to them under normal context, so if you try to select from them you get the error you see. DAC connections can bind to them and retrieve data, but you still won't be able to update them.

This is all described on MSDN, see System Base Tables.

sysfiles1 though is a special case, not documented in the link above. ~~This catalog table exists only in databases upgraded from SQL 2000~~. See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx:

... sysfiles1 table is a linked-heap. This table contains the locations of the files comprising the database ...

Best Answer

Related Solutions

Sql-server – Why did the UPDATE statement not complete

Sql-server – Why does ‘sysfiles1’ show up in the sys.objects catalog view in SQL Server 2008

Related Question