long running queries

Question

Post reply

long running queries

bala2

SSCertifiable

Points: 5547
More actions
October 16, 2010 at 11:45 pm

#230535

HOW to check the long running queries or sp?.what are the steps should i follow to solve thelong running quires?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

It depends.

Sounds like an interview question (at least in combination with your other question at the very same time)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 2

I agree on the interview question!

the first one can be done a number of ways, DMV, profiler, server side trace, performance dashboard(which uses DMV) 2005 , Data collection 2008.

the 2nd question it depends is correct and you can write a book on that subject..maybe several....gots to be able to read and execution plan first you need Grants book for that....but that will get in the right direction....

upstart Ten Centuries Points: 1255 More actions · Answer 3

There are numerous ways you could do this, DMV's, build your own Proc, etc., but below I listed two queries that you can run to get a start on looking at some relevant data. I wont go into explaining all of the steps and logic, I'll leave that up to you to look into.

SELECT DISTINCT TOP 10

db_name(dbid) DatabaseName,

t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS MaxElapsedTime,

ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,

s.creation_time AS LogCreatedOn,

ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC

SELECT DATEDIFF(MINUTE, a.last_batch, GETDATE()) RunningTime,

a.spid, a.blocked, a.waittime, db_name(a.dbid) As dbname,

a.last_batch, a.hostname, a.program_name,

a.nt_username, a.loginame, b.text as sqlstatement

FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) B

WHERE A.dbid > 4

ORDER BY RunningTime DESC

GO

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

vijay82 (10/16/2010)
HOW to check the long running queries or sp?.what are the steps should i follow to solve thelong running quires?

What position are you applying for?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)