Finding queries to tune

Question

Finding queries to tune

s_osborne2

SSCertifiable

Points: 6726
More actions
May 31, 2013 at 12:58 am

#394883

Comments posted to this topic are about the item Finding queries to tune

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

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

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

Sean Smith SSCertifiable Points: 5941 More actions · Answer 1

How does the time waiting indicate a query that needs tuning? Couldn't the wait time be caused by other processes, heavy load on the server, blocking issues, etc.? Just not sure how this gives any real indication that a query needs tuning. Maybe I am missing something?

webtomte Ten Centuries Points: 1135 More actions · Answer 2

I think this is a good way to start to find out bottlenecks like for instance "Table scans" in queries.

As allways it is the hint to something that thes fantastic little queries you find on the blogs that points you in the right direction, isn't it?

If you run this and look in the showplan for the queries you will probably find some nasty coding. Often made by 3rd party consultant 100 years ago.

It might be worth the effort.

Nice ....:w00t:

Sean Smith SSCertifiable Points: 5941 More actions · Answer 3

Ah, 100-year-old 3rd party consultant code... been there <SHUDDER>. LOL!

devdocs SSC Enthusiast Points: 148 More actions · Answer 4

Thanks for useful queries and suggestions.. I am writing for the first time on SQL Server Central.

I am trying to execute the query on Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) with SP2 and its giving me the following error:

Msg 102, Level 15, State 1, Line 49

Incorrect syntax near '.'.

I tried to sort out this but failed. Can you hep me out???

Dave62 SSCertifiable Points: 7459 More actions · Answer 5

Sometimes extra characters are included when copying and pasting code from SSC. You can try pasting the code into something like Notepad first and then copying from Notepad to SSMS. This will remove the extra characters.

I don't know if this is actually the problem your having but it's the first thing I would try.

devdocs SSC Enthusiast Points: 148 More actions · Answer 6

Thanks Dave...

One more question:

Will this query (Finding queries to tune) be executed in Master DB only???

Dave Vroman SSC Eights! Points: 821 More actions · Answer 7

When I was looking at the code I noticed that you were searching for "tempdb..#temp" and all other references in the script were to "#temp" without the database reference.

Sean Smith SSCertifiable Points: 5941 More actions · Answer 8

I think you need to change the DB context to have it search against whichever DB you are interested in.

As for "#temp" vs. "tempdb..#temp", this should make no difference in the code's performance.

Wayne West SSC-Insane Points: 22586 More actions · Answer 9

devdocs (6/11/2013)
Thanks Dave...
One more question:
Will this query (Finding queries to tune) be executed in Master DB only???

The DMVs look at overall cache, not just a specific database. I ran it against three different production databases and got (largely) the same results, nothing indicating that it was DB-specific.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

Thanks for the script. Just needed to run it in grid mode to better see all the columns.

Lee Linares SSCrazy Points: 2723 More actions · Answer 11

Simon,

Excellent script! Thank you for sharing it. May I suggest using this line of code to retrieve the object name:

[Object] = OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) + '.' +OBJECT_NAME(qt.objectid,qt.dbid),

That would eliminate all the cursor code used in your script.

Thanks again.

Lee