Click here to monitor SSC
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
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 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)
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 980
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-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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)
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 980
Ah, 100-year-old 3rd party consultant code... been there <SHUDDER>. LOL!
devdocs
devdocs
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3163 Visits: 2756
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
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

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

One more question:
Will this query (Finding queries to tune) be executed in Master DB only???
Dave Vroman
Dave Vroman
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 722
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)
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 980
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2074 Visits: 3694
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