Sql Server Performance Tuning
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.
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:
System resource allocation
Rewrite sql statements
Optimize data model
Assess benefits of full-text catalog when text based searches are common
Enhance SSIS process flow
Revise SSAS design: dimensions and mdx usage