To what extent can we monitor jobs/suspended queries as DBO

Question

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!

Answer 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

Answer 2

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