Identifying Problem SQL

You may also like...

7 Responses

  1. Nish says:

    any idea on how to filter dyn sql results between specific duration? for example, I need dyn sql executed only during business hours (8 AM to 5 PM) & I don’t want sqls executed after business hours.

    • Ember Crooks says:

      That’s one of the reasons why it is critical to be saving historical performance data (even if only for a few days). Either in tables or in plain old snapshot files. Even if you don’t have space for every statement, the top statements in each category written to some kind of history table hourly are great to have. There are plenty of pay-for-use tools like performance expert or DBI Software’s tools that do this for you, or you can write your own scripts/stored procedures for it. If you’re writing your own scripts that write to table, a good starting point is:

  1. December 1, 2011

    […] sure isn’t too bad, or you’ll want to get problem SQL – possibly as defined in my post on that. You’ll also want to make sure runstats are current before running any of this, as it is […]

  2. December 6, 2011

    […] So this was emphasized to me over Cyber Monday 2011. I have a client with their own DBA, and we worked together to identify just 4 indexes that they needed to add in October. Cyber Monday rolls around, and the indexes hadn’t made it into production yet. The site was so slow as to be unusable, the over-sized database server was high on CPU and had over 200 active connections at all times. The other DBA added these 4 indexes, and immediately the active connections were between 5 and 15 (more normal for this database), and the over-sized db server was again bored stiff. That’s just 4 indexes, not even clustering ones. So you should be constantly reviewing SQL and looking for indexes to add. You never want to drop base commerce indexes, but adding indexes is certainly reasonable. See this blog entry on analyzing SQL and this one on identifying problem SQL. […]

  3. December 7, 2011

    […] most basic way to identify where indexes may be useful is to identify problem SQL, particularly problem SQL that shows up as a problem in the area of Rows Read. The main reason […]

  4. March 30, 2012

    […] Identifying Problem SQL […]

  5. March 7, 2015

    […] Identifying Problem SQL […]

Leave a Reply

Your email address will not be published. Required fields are marked *