Help stamp out SQL Server neglect

eI am thinking of starting a new royal society the for the protection of cruelty to SQL Server as I am seeing my favourite database suffering a lot of abuse out there as part of the blind rush to virtualisation.  A good example is, Gillian,  a dba I met at SQL Bits who was nearly in tears because she was getting loads of timeouts after her data centre administrator subjected SQL server to this.  What made her really upset was that this ‘virtualisation expert’ couldn’t see the problem, however her users certainly could and this was obviously very real for her.

I don’t exactly what the root cause of the problem is in that situation but I routinely have conversations of this kind.  so to protect and care for y0our SQL Server treat it the same way you would a pet:

Understand its dietary needs

SQL Server thrives on healthy IO with plenty of RAM and CPU (in that order).  IO is the one that’s neglected as it’s not obvious where you declare this in most hypervisors.  It is all to easy to setup a VM that is asking for lots of IO, doesn’t get and yet the actual storage in the physical environment is not stressed as in Gillian’s example.

Regular health checks

Looking at a VM from the outside is no indication of what's happening to the services on the inside. Also you need to understand what your performance is before you virtualise. Gillian did this bit fine she knew her database were all healthy on her physical environment and understood the performance of them.  However when they went virtual no attempt was made by the data centre admin to provide comparable resources or even test that performance before going live.

There are the obvious free tools like Perf Mon in the OS and MAPT (Microsoft Assessment & Planning Toolkit) which you can download. 

Regular Exercise

My favourite approach for checking the health of SQL Server and other services is to exercise them  in System Center Operations Manager by using synthetic transactions to test the response time and error conditions  on a periodic basis from a designated machine.  This allows you to test that you are achieving your SLA and warn you if you aren’t, before the help desk even rings.

Obedience Training

SQL Server 2008 has policy based management which can be used to check that all the servers databases and objects in those databases conform to rules that you define, and you can also check policies on earlier versions of SQL Server.

Treats

Show your affection for your database by giving it regular treats such as new service packs and hot fixes.  Don’t forget the same applies to the operating system and the hypervisor, as these can also significantly affect performance.  A good example is the recent service pack 1 for Windows Server 2008 R2 which adds dynamic memory. 

 Dog Whisperer

The closest thing we have to this is the SQL CAT (Customer Advisory Team), who have excellent white papers on optimising SQL Server on Hyper-V and a new one on SQL Server and the private cloud.  There are also resources on Vmware’s site for optimising SQL Server on their hypervisor, so ignorance is no excuse. Of course you can call in the many performance tuning experts in the UK and many of them have already come across virtualisation starvation issues.

 

So don’t give virtualisation or SQL Server a bad name, read the documents, do the work and listen to your DBAs.