SQL Server Performance

Establish Query baseline

Discussion in 'Getting Started' started by Amber Robertson, Feb 29, 2008.

  1. Amber Robertson New Member

    Dear All, I recently been asked by my boss to make Query Optimization apart of my DBA role. I have read some articles and Inside Microsoft SQL Server 2005 Query Tuning and Optimization book and now feel comfortable with using SQL Profiler and Query Execution Plans. However I am struggling to establish a query baseline to prevent myself from trying to tune queries which don't need working on. Can you give me any suggestions about establishing what acceptable running performance for a query is? Any tips or suggestions much appreciated. Kind RegardsAmber
  2. martins New Member

    I would start by looking at complaints from users about poor performance. If there aren't any, maybe running a trace for a specific period of time and then taking the top 10 longest running queries could be a good starting point.
    The easiest thing to spot within the execution plans that will definitely decrease performance are table scans, so you could have quick wins by trying to eliminate those (within long-running queries) through proper indexing.
  3. Amber Robertson New Member

    Hi Martins, thank you for the tip. Are there any other things I can look for as I am hoping to help reduce amount of complaints. We currently approach Query Optimization in a reactive way, i.e. we get told by helpdesk we have a system go slow and spend a lot of time blaming different systems etc and then establish it is a specific query causing the problem, try and fix it as quick as possible. Sometimes it gets so bad users have to move onto another system temporarily. I am trying to move towards find the identify and fix badly written queries before users feels the affect. Regards,Amber
  4. martins New Member

    To be honest, there are hundreds of things you can possibly look at...hence the fact that many papers and books have been written about the subject. I will try and give you a couple of pointers, but please remember that there might be many possible bottlenecks specific to your environment.
    The first thing to ask yourself (and check for that matter) is how your server is performing in general. Are you doing scheduled maintenance for rebuilding indexes etc. A query can be written well and use all the appropriate indexes, but if those indexes are badly fragmented the performance will still suffer. If there is no proper maintenance strategy in place, get that done first. It will be of no use to try and improve query performance if the underlying processes are not in place to support it.
    Secondly you need to know that table scans are probably the easiest way to detect a possible performance issue. A table scan does not necessarily mean the performance will be bad, but it can be a good indicator. You would not mind if a table scan occurs on a table with 10k rows, but if there is one on a 300k row table you can bet that performance will suffer.
    Generally speaking table scans perform worse than index scans, which in turn perform worse than an index seek. Outer joins will always perform worse than inner joins, but sometimes you cannot get around them. Having lots of aggregations in queries can and will have an impact on performance...sometimes it is better to do certain aggregations on the front end depending on the type of application.
    You want re-use of execution plans, so look out for things that prevent re-use like parameter sniffing (read up on that one) and conditions that will execute completely different statements which will make the saved execution plan ineffective.
    That's already a mouthful, but before I write an essay let me try and summarise.
    1. Ensure your maintenance procedures are in place. Lookout for index fragmentation.
    2. Run a trace to identify the longest running queries, or identify all queries that reference some of your bigger tables.
    3. Check your execution plans.
    4. Try and eliminate table scans on large tables.
    5. See if any index modifications can eliminate bookmark lookups that take up a large percentage of the query time.
    6. If all the above steps still do not yield better results, look at the physical code. Can it be restructured/rewritten to eliminate udf's or make better use of joins etc..
    7. The last resort would be to look at your physical physical infrastructure like disks, networks, linked servers etc to see if there could be any bottlenecks.
    Give the above a try as a start...and good luck. Hope this helps.
  5. Amber Robertson New Member

    martins, this is great, it has definately given me a starting point.
    Take care
  6. satya Moderator

  7. Greg Larsen New Member

    If your need some help finding those top queries that are burring up I/O and CPU, or have a long elapsed time you might check out my SQL Server DBA Dashboard. The dashboard provides you with reports by server and database that will help you quickly identify these resource intensive queries. The dashboard can be found here : http://sqlserverexamples.com
  8. Amber Robertson New Member

    Greg, thank you the link. I like your DBA Dashboard I have created a similar thing here at work as I found off the shelf commercial DBA Dashboard were too complicated.
    Kind Regards
  9. Greg Larsen New Member

    If you find any bugs, or would like enhancements send them my way. I gather a list of requirements for the next release 1.3.

Share This Page