Finding queries to tune

  • 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

  • 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?

  • 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:

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

  • 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???

  • 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.

  • Thanks Dave...

    One more question:

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

  • 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.

  • 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.

  • 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]

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

  • 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