I perform an overview of server performance, including coding techniques, memory utilization, and indexing strategies. Using that information I will provide guidance on how to improve performance, specifically as a method to ensure costly server upgrades and other methods of “throwing money at the problem” are the proper approach before the investment is made.  While these approaches may mask the problem, it is typically a more expensive and less scalable solution than a well-tuned database.

Here are some of things I’m looking for and the reasons they matter:

Coding Techniques:

• Takes advantage of indexes – A small change in code can go from reading an entire 100 GB table to going directly to the 8 KB page you need.
• No unnecessary loops – SQL Server was made to work in sets, taking it out of its element is the easiest way to overwork it for no added benefit.
• Repetitive logic – It’s very easy to have hidden pieces that run multiple times and having expensive queries run more than required.
• Order of logic – The sooner you can eliminate work that needs to be done, the quicker the rest of the process can go.
• Cacheable plans – It takes CPU power to create your execution plans.  If they can’t be reused then you’re using more CPU to repeat the process.

Memory Utilization and Indexing Strategies:

• Unused Indexes – Indexes aren’t free, and unused indexes will slow down data manipulation, add to database size, and add to backup size.
• Duplicated Indexes – In addition to the downfalls of unused indexes, duplicated indexes also take up valuable space in memory.
• Missing Indexes – Without proper indexing queries are forced to read more information, increasing the load on the CPU, Memory, and Disk.
• Inefficient Indexes – Sometimes an index that is “almost” right is just as slow as no index at all, but small modifications can often change that.

These methods to not only improve the performance visible to the users, but also have a visible impact on the back end.  It’s not unheard of for the load on the CPU and disk to drop by 50% or for Page Life Expectancy measuring memory pressure to be over double where it started on the same hardware.

The ideal situation for me would be to work with your DBA or developer who took on that role and teach them as I go.  A typical environment would have many instances of the same issue, and a consultant can only fix so many of them.  If your staff learns what I do then they can fix more of the currently existing issues, continue working on future issues, and, most importantly, avoid many of these problems from occurring in the first place.  The best thing for everyone is that I’m needed for a couple days at the start, then less and less often as your staff develops the expertise they need to keep your servers running great.

My ultimate goal is to leave your servers running faster than you thought possible, leave your staff knowing how to keep them that way, and leave you so impressed that you introduce me to my next customer.

If you’re interested, I’m working for <a href=”https://PlusConsulting.com”>Plus Consulting</a>, and one of our offerings is a 20 hour performance and best practices assessment.  We’ll find your primary issues, get some quick changes that will make a difference for you immediately, and show you some techniques to improve performance.  Once this is done you’ll have the option to keep us on resolving issues or use our advice to continue on your own.  My biggest goal is you wanting to refer us to others.

Thank You,
Steve Hood