January 17, 2014 at 4:19 pm
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?
January 19, 2014 at 4:03 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2014 at 5:13 pm
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
Change is inevitable... Change for the better is not.
January 20, 2014 at 6:13 pm
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.
January 20, 2014 at 10:31 pm
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
Change is inevitable... Change for the better is not.
January 21, 2014 at 2:19 am
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.
January 21, 2014 at 2:25 am
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
January 21, 2014 at 4:49 am
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
January 21, 2014 at 7:39 am
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?
January 22, 2014 at 10:59 am
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
January 22, 2014 at 2:19 pm
This is not a DMV I wrote (can't remember where I found it, honestly) but it is the 1st thing I ran to find out what query is slowing down my system:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [Spid] = session_Id
,ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID)-- Ignore this current statement.
ORDER BY 1, 2
I like it because it shows the spid plus the actual T-SQL statement.
January 22, 2014 at 4:56 pm
GilaMonster (1/21/2014)
sys.dm_exec_requests join sys.dm_exec_sessions cross apply sys.dm_exec_sql_text.
That and a quick trip to the desk of the person who invoked the rogue query along with the appropriate pork chop launcher and a bat to help tenderize the target. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2014 at 2:47 am
sql-lover (1/22/2014)
WHERE session_Id > 50 -- Ignore system spids.
Careful, spid < 50 indicating a system process hasn't been true since SQL 2000.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply