Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What do you use to find out what's slowing down your instance right now Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 4:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:03 AM
Points: 903, Visits: 1,872
The business user comes to your desk in a panic saying their app is slow, but can't elaborate any more than that. What do you do right away?
Assume in this scenario the issue is definitely down to sql load, one heavy query or tons of smaller queries, and this is not caused by external factors like other apps on the box.

I'm just curious to see if there are "tools/scripts/methodologies" I'm not considering.

In my case sp_whoisacive is run straight away.
If I'm on a server that I'm not allowed out sp_whoisactive on (they exist unfortunately) I run a query against sysprocesses and/or sys.dm_exec_requests and a few other dmvs, which is ok, but not as good as sp_whoisactive.

This is usually Good enough to identify a heavy query.


Do you have any other methods to find the culprit rogue queries?
Post #1532327
Posted Sunday, January 19, 2014 4:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
sys.dm_exec_requests is my bestest buddy in the whole world. It's my go to position for this type of scenario. Assuming no long running query or blocking scenario, I might expand out to look at system resources to see if something is going on with the server, but you've already nailed it, sys.dm_exec_request.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1532438
Posted Sunday, January 19, 2014 5:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
Shifting gears...

If I'm on a server that I'm not allowed out {sic} sp_whoisactive on ...



I'd be fighting that tooth, nail, and pork chop. You're the DBA... why aren't you allowed to install some basic DBA tools on some of the servers?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532479
Posted Monday, January 20, 2014 6:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:40 AM
Points: 49, Visits: 412
There is so many variables to your question I'm not sure where to start. How do you know it's SQL verses some other process?
How much available memory?
How much paging?
What are the processors doing?
Is there any blocking going on?
Are you running any Indexing maintenance jobs
etc, etc, etc......

You are in a bad situation my friend.
Post #1532830
Posted Monday, January 20, 2014 10:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
smitty-1088185 (1/20/2014)
There is so many variables to your question I'm not sure where to start. How do you know it's SQL verses some other process?
How much available memory?
How much paging?
What are the processors doing?
Is there any blocking going on?
Are you running any Indexing maintenance jobs
etc, etc, etc......

You are in a bad situation my friend.


That's why sp_WhoIsActive is such a valuable tool. It shows a lot of that in a nice, tight result set.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532851
Posted Tuesday, January 21, 2014 2:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:03 AM
Points: 903, Visits: 1,872
Jeff Moden (1/20/2014)
smitty-1088185 (1/20/2014)
There is so many variables to your question I'm not sure where to start. How do you know it's SQL verses some other process?
How much available memory?
How much paging?
What are the processors doing?
Is there any blocking going on?
Are you running any Indexing maintenance jobs
etc, etc, etc......

You are in a bad situation my friend.


That's why sp_WhoIsActive is such a valuable tool. It shows a lot of that in a nice, tight result set.


it was just a hypothetical question to find out what other people are doing.
If you need to know, lets imagine that some advanced users have access to run queries via SSMS and one nasty analyst has a query that is known to be awful due to select * from super massive table, bad indexing, terrible coding practices etc etc.

So someone on the team a while would see the username and know the story, but someone new might not, but a head DBA casually says, "its just a rogue query", can you identify it.

Post #1532912
Posted Tuesday, January 21, 2014 2:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
sys.dm_exec_requests join sys.dm_exec_sessions cross apply sys.dm_exec_sql_text.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532916
Posted Tuesday, January 21, 2014 4:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:40 AM
Points: 49, Visits: 412
I usually create a stored proc on all my servers using the query below and call it sp_who3. It will give you all the details you need to troubleshoot.

select
a.session_id,
start_time,
b.host_name,
b.program_name,
DB_NAME(a.database_id) as DatabaseName,
a.status,
blocking_session_id,
wait_type,
wait_time,
wait_resource,
a.cpu_time,
a.total_elapsed_time,
scheduler_id,
a.reads,
a.writes,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement

, s2.text
from
sys.dm_exec_requests a inner join
sys.dm_exec_sessions b on a.session_id = b.session_id
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS s2
Post #1532961
Posted Tuesday, January 21, 2014 7:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:37 PM
Points: 381, Visits: 359
Here is a great tool from Brent Ozar. He presented on it at PASS and the name of his session was "Why is my server slow, right now?

http://www.brentozar.com/askbrent/
Post #1533066
Posted Wednesday, January 22, 2014 10:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 158, Visits: 808
First post, long-time lurker & admirer of many of the contributors on this forum Just a point on sp_whoisactive being a procedure...

I work in on a contract basis in financial services (as I'm sure do others on this site/forum), it's a very locked down environment due to SOX, regulatory requirements, financial meltdown, paranoia, global warming, butterfly wings beating too fast/slow, etc.

I'm termed/titled as a DEV DBA/SQL Architect(?)/DB Speeder-Upper(??)/Snr SQL Dev/General DB DogsBody who also handles escalations from production support & tries to (sometimes gently) advise the offshore based DBA's on managing a range of database applications which I'm notionally responsible for.

Sp_whoisactive is a fanastic tool and all credit Mr Machanic for writing & equally as importantly in maintaining it. In no environment do I have access to the master DB to install in it - I'm not part of the official DBA production support group/organisation - nor do I want to be for multiple political/geographical reasons. I don't want to keep installing Sp_whoisactive in multipe user DB's as they get refreshed from Prod on a regular basis and on Prod systems I have R/O & DMV access only.

Changing sp_whoisactive from a procedure to a script that can run anywhere (assuming I have the underlying privileges) and therefore don't need to create it in a database took aprrox 30 minutes a few months back. This has paid handsome dividends in the knowledge gained from it's use. Sometimes, sometimes it seems too 'heavy' & doesn't return quickly enough to capture fleeting production issues or on some occaisions it may not return at all . So I tend to run a set of lightweight queries along-side it referencing; sys.dm_exec_requests join sys.dm_exec_sessions cross apply sys.dm_exec_sql_text (as mentioned by Gail and others on this thread) with a cross apply on sys.dm_exec_query_plan thrown in.

A topic that there are as many variations of answers for as there are situations to which those answers may apply.

That’s my .02c.

All the best
Michael



Post #1533767
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse