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


Finding queries to tune


Finding queries to tune

Author
Message
s_osborne2
s_osborne2
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5549 Visits: 2292
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
Sean Smith (SSC)
Sean Smith (SSC)
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4358 Visits: 1097
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
webtomte
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 226
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 (SSC)
Sean Smith (SSC)
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4358 Visits: 1097
Ah, 100-year-old 3rd party consultant code... been there <SHUDDER>. LOL!
devdocs
devdocs
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 24
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
Dave62
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5264 Visits: 3037
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
devdocs
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 24
Thanks Dave...

One more question:
Will this query (Finding queries to tune) be executed in Master DB only???
Dave Vroman
Dave Vroman
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 728
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 (SSC)
Sean Smith (SSC)
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4358 Visits: 1097
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
Wayne West
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18190 Visits: 3710
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
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