Sql Server Performance Tuning | Collaborative Visionz

Capitalize on your data - measure business performances and identify strategic improvements.

Sql Server Performance Tuning

Overview

Performance optimization of a SQL Server is as much an art as it is a science.

Conflicting ideas and approaches are abandon on the Internet and Microsoft has published numerous articles for different versions.

Based on our experience performance optimization is potentially and effort intensive process with potentially diminishing return on investment.

One of the common recommendation is to upgrade server hardware by adding SAN, switching RAID type, adding more memory, and upgrading/adding processors.

Another common recommendation is to monitor performance to identify bottlenecks.

And yet another approach seems to be favoured by DBA is to run Sql Server Profiler to identify slow running queries and to optimize those.

 

Our Approach

Collect feedback from end-users of the system to narrow down the user performance experiences and impressions especially when it sounds inexact, perceptive, and even anecdotal observation – it is the end-user experience that matters the most at the end.

Confirm that hardware requirements, software requirements, and resource allocation to Sql Server meet, at very least, Microsoft requirements.

Profile Sql Server instance health to identify whether actual installation requirements are met.

Identify Sql Server bottlenecks to explain the end-user performance feedback.

Experiment with different approaches optimizing Sql Server performance and analyze the impact by performance monitoring as well as by collecting end-user feedback:

  1. System resource allocation
    http://technet.microsoft.com/en-us/library/ms178067.aspx

  2. Rewrite sql statements
    http://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx

  3. Improve indexing
    http://blog.sqlauthority.com/2007/05/08/sql-server-index-optimization-checklist/

  4. Optimize data model
    http://sqlmag.com/t-sql/performance-tuning-data-model-thinner-better

  5. Assess benefits of full-text catalog when text based searches are common
    http://technet.microsoft.com/en-us/library/ms142571.aspx

  6. Enhance SSIS process flow
    http://msbicodehelp.blogspot.ca/2011/12/performance-tuning-in-ssis.html

  7. Revise SSAS design: dimensions and mdx usage
    http://www.microsoft.com/en-ca/download/details.aspx?id=17303

Review SSRS design
http://underthehood.ironworks.com/2010/01/reporting-services-optimization-tips.html