1

Short Background:

I work for company A, at company A we have a DBA that should not be. When we are tasked with items for troubleshooting which require quick turn around, it generally takes quite a bit longer than it should.

For this reason, we asked him to grant me DBO access to certain servers (Biztalk SQL servers). I'm the one who is dropping files and monitoring this stuff, sometimes overnight, and lately we have been experiencing system slow down.

So with these issues with Biztalk, I've gone into the SQL server and tried to dig around but it seems most of them require higher priveleges then I have. I've looked for suspended queries, killed them, but as DBO, I was wondering what's the extent I can monitor and find out about the jobs currently running/suspended.

I am familiar with exec sp_who2 and kill %% but other than that I've been hitting a wall...

My question:

Are there any useful features I can utilize as DBO that will assist in identifying the cause of these suspended queries? Other than who and who2?

Any homegrown queries you folks have wrote for identifying such causes of these errors?

Any help is much appreciated!

1

Though logging into the machine and analyzing the wait chain on the Resource Monitor could give some extra info (for instance if sqlservr.exe thread is blocked waiting for lsass.exe it could mean it is blocked at a authentication validation), what you'd really want is to be granted the VIEW SERVER STATE permission so you can view the DMVs that might help you, like this ones, for instance:

SELECT sqltext.TEXT,
 req.session_id,
 req.status,
 req.command,
 req.cpu_time,
 req.total_elapsed_time
 FROM sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
order by cpu_time desc
  • Something to note about the above query, it will be interesting to also order by total_elapsed_time desc, as blocked ones may have small cpu_time but large total_elapsed_time – JoseTeixeira Mar 5 '15 at 18:53
  • Great suggestion, haven't been granted access yet, but good info, thanks! – Hituptony Mar 5 '15 at 19:06
  • That was what I was looking for. Got some good info from this. – Hituptony Mar 5 '15 at 19:13
0

I would suggest using sp_WhoIsActive - provided you have access to DMV data.

Alternatively, you can use diagnostic queries from Glen Berry or sp_AskBrent.

A good start would be A Performance Troubleshooting Methodology for SQL Server

  • Thanks @Kin, great resources, I will keep these in mind and check out WhoIsActive – Hituptony Mar 5 '15 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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