SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What do you use to find out what's slowing down your instance right now


What do you use to find out what's slowing down your instance right now

Author
Message
winston Smith
winston Smith
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 2069
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?
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43055 Visits: 32667
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91360 Visits: 41151
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
smitty-1088185
smitty-1088185
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 599
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91360 Visits: 41151
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
winston Smith
winston Smith
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 2069
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92233 Visits: 45285
sys.dm_exec_requests join sys.dm_exec_sessions cross apply sys.dm_exec_sql_text.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


smitty-1088185
smitty-1088185
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 599
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
Noetic DBA
Noetic DBA
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 420
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/
michael_monks
michael_monks
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 1250
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 w00t. 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search