A client reports that their SQL Server's performance is very poor, you are a SQL Tuning Guru, what configuration settings, database options, etc etc u will check first to quickly identify any poorly configured settings, before starting to record different performance counters. Thanks, Ranga Ranga
This is a very broad question, and since sql server has so many settings, most of which could affect performance in some way, it is very difficult to answer.<br /><br />My first step would be to familiarise myself with the arrangement of the database on disk, and indeed the architecture of the IO subsystem itself (uses RAID etc?)<br /><br />Then Id delve into memory configuration. Is the server restricted to the RAM it can use? How much RAM does it have? Would I estimate this to be adequate?<br /><br />Same for CPU capabilities and configuration. <br /><br />I think broadly speaking, these three factors account for the most basic of performance improvements to be had. <br /><br />At this point, assuming nothing out of the ordinary appears from these tests, I would then start looking at server activity. What sort of queries are being run against the server, and how frequently? Are these queries adhoc, or stored procedures, and are they badly written? Are the queries essentially well written, but let down by missing or badly designed table indicies? <br /><br />Id also examine performance counters at this point, to determine bottlenecks, and to verify if my estimate of adequate memory is true or not, based on cache efficiency.<br /><br />The steps after this would vary wildly depending on the results accumulated in the tests above. But this is how I approach it personally. Having said that, I dont consider myself to be a Tuning Guru yet <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br />
There is a great article on this very site: How to Perform a SQL Server Performance Audit http://www.sql-server-performance.com/sql_server_performance_audit.asp /Argyle
