May 31, 2013 at 12:58 am
Comments posted to this topic are about the item Finding queries to tune
June 10, 2013 at 9:56 pm
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?
June 11, 2013 at 12:52 am
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:
June 11, 2013 at 6:54 am
Ah, 100-year-old 3rd party consultant code... been there <SHUDDER>. LOL!
June 11, 2013 at 8:29 am
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???
June 11, 2013 at 8:43 am
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.
June 11, 2013 at 8:54 am
Thanks Dave...
One more question:
Will this query (Finding queries to tune) be executed in Master DB only???
June 11, 2013 at 11:35 am
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.
June 11, 2013 at 3:20 pm
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.
October 16, 2015 at 8:38 am
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]
October 21, 2015 at 7:12 am
Thanks for the script. Just needed to run it in grid mode to better see all the columns.
October 23, 2015 at 12:02 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply