01 March,2013 by Jack Vamvas
SQL Server – particularly since SQL Server 2005 – has good default values. I invest time in profiling application requirements and setting up the database server from the outset . There must be a compelling reason, backed up by measurements or root cause analysis, to change a default setting .
In most database server systems there isn’t a sweet spot : maximum connections and buffer pool size combining to handle any type of database activity. In reality, SQL Server monitoring , performance metrics and awareness of resource usage to identify contention.
This leads me to asking – how does a DBA approach a troubleshooting? I’ve spent all this time setting up the server, yet performance issues start. Of course, database tasks are added and modified impacting the time they take to complete. Often I find the problem is not a performance problem but a capacity planning issue. But, from the users perspective the task is running slower compared to last week, so I better move quickly!
Performance troubleshooting is all about first gathering good quality information . Relevant data and systemactic approach. Systematic doesn’t mean a rigid checklist , it’s about keeping the process simple and focusing on facts to solve the root cause. Read more SQL Performance tuning - Asking the right question
In most cases,SQL Server offers enough information to troubleshoot . Use of undocumented features and generally hacking away is a low percentage return on investment in troubleshooting time
You may hit the jackpot and solve the problem. Trying to understand why the problem occurred – is very important in solving the problem – and deciding which SQL Server configuration values or code changes to put in place.