finding slow running queries

Question

finding slow running queries

kvr1985

SSCommitted

Points: 1628
More actions
February 16, 2011 at 3:53 am

#142365

hi every one,
I am production support DBA .application team are running queries and ssis packages.and i just want know how to find the slow running queries and slow running jobs..
Regards
venkat

Viewing 12 posts - 1 through 11 (of 11 total)

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

You want to set up a server side trace to do that. I have a small article here on SSC[/url] describing how to do it.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

kvr1985 SSCommitted Points: 1628 More actions · Answer 2

thank you for information

i will read the article later .how can find the slow running queries.

Regards
venkat

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 3

kvr1985 (2/16/2011)
thank you for information
i will read the article later .how can find the slow running queries.

Did you read the article ?

Clear Sky SQL
My Blog[/url]

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

kvr1985 (2/16/2011)
thank you for information
i will read the article later .how can find the slow running queries.

Try reading the article that Grant helpfully linked to.

Or, just carrying on ignoring anyone that replies. Your choice.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

kvr1985 (2/16/2011)
thank you for information
i will read the article later .how can find the slow running queries.

You can find slow running queries by using a server-side trace to gather metrics and then move those metrics into a table where you can run queries against them.

Another option, not as effective is to use the DMO sys.dm_exec_query_stats to see what is running the slowest of the queries that are currently in cache. But this is extremely dependent on your cache, so can very easily miss long running queries that have aged out of cache.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

kvr1985 SSCommitted Points: 1628 More actions · Answer 6

kvr1985

SSCommitted

Points: 1628

February 16, 2011 at 5:37 am

#1287579

thank you

Regards
venkat

SkyBVI SSCoach Points: 15115 More actions · Answer 7

also, u can right click on server, reports-- standard reports

and then see the --Top queries by average CPU time...and other more information.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

SKYBVI (2/16/2011)
also, u can right click on server, reports-- standard reports
and then see the --Top queries by average CPU time...and other more information.
Regards,
Sushant

That uses the DMVs that Grant mentioned earlier

Another option, not as effective is to use the DMO sys.dm_exec_query_stats to see what is running the slowest of the queries that are currently in cache. But this is extremely dependent on your cache, so can very easily miss long running queries that have aged out of cache.

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

kvr1985 SSCommitted Points: 1628 More actions · Answer 9

thank you guys. it's working.

i have a one more doubt how to do server wide trace?

Regards
venkat

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

kvr1985 (2/16/2011)
thank you guys. it's working.
i have a one more doubt how to do server wide trace?

Please read the article I posted. That's how you do a trace.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Fatherjack SSCommitted Points: 1575 More actions · Answer 11

If its working then that is what you want. You have a trace running. What more are you expecting?