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.

 Read More

SQL Performance tuning - Asking the right question

SQL Performance tuning - Drilling into the problem

SQL Server – SSMS query tuning – simulate the application

SQL Performance Tuning - optimizing for response time

Mobile Commerce and SQL Performance Tuning