But it is possibly one of the more useful subjects especially when you’re up at 3am watching your SQLAgent Batch process run for over an hour longer than it did yesterday and not knowing when (or if!) it will finish so you can go back to bed.
I didn’t really ‘get’ real performance tuning till a few years back when a colleague of mine and I started lunchtime training sessions with each other trying to learn all the new features in SQL 2005.
I would say that Jasper did a very good job with what is actually quite a dry topic. If like a lot of SQL DBA’s and Developers you’ve read the MS Press books on Tuning then there was obviously a lot of overlap. But what you don’t get in the books is the sense of realism based on actual examples that were used. It also a lot easier to follow when someone is taking you through it in SSMS!
We spent some time on Schedulers, CPU Affinity, Difference between NUMA and SMP configurations. Talked about CLR Processing and how SQL can force CLR performance under certain conditions. Jasper also discussed his usage of WSRM (Windows Server Resource Manager) and how he had very good results on multi-instance machines using it to assign resources and how he always left at least 1-2GB memory free for the OS (Remote Desktop Connections etc).
Next we covered Memory usage differences between 32 and 64 bit OS. Jasper then brought up the TokenAndPermUserStore cache issues he had experienced in the past.
The main empasis was placed on setting up a Monitoring Database on each server to hold collected data. (SQL 2008 does this on a per instance basis but the data can be moved to a central server).
Following this, Jasper discussed Disk IO , and the way database files should be stored (mdfs on one drive, ldfs on another, tempDB files split by core. We went in great detail about SQL Read Ahead and differences in version (EE with read and Extent ahead until it reaches max then will match Standard and read 8k chunks). We briefly touched on partition alignment with the disk subsystems and the performance gains that can be achieved through setting the disks up correctly.
(I’m looking forward to the day when the SQL DBA is actually asked what they need setup rather than asked to fix the performance issues on a badly configured server (” raid? whats raid configuration got to do with SQL Performance? Just fix it …”)
Although I enjoyed the morning session, I found the afternoon a lot more interesting. It was based around four performance killers that Jasper had come across.
- Dynamic SQL
- x = ISNULL(@value,x)
- (x is null or x = @x)
- and the coalesce version of ISNULL.
This one was an eye opener for me as I have done a fair bit of Reporting Services Over the past years and frequently find myself using the ISNULL check for dynamic searches. It turns out is was a bad choice compared to Dynamic Sql (by far the fastest but a nightmare to maintain) and (x is null or x = @x) which performed well but has issue with forced recompiles (some issues here with SQL 2008 SP1 CU4/5). Not sure what I will do in future yet!