SQLBits V Session Review : Practical Performance Tuning and Monitoring For SQL Server with Jasper Smith

So its obviously not the most exciting subject when it comes to SQL Server (how many years of running sp_who2 for blocking, profiler runs, deadloack trace flags etc etc…).

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!

What was covered
The first half of the day was based mainly around ‘Core Concepts’ including the three main hardware aspects of performance and how SQL uses them.

 

  • CPU
  • IO
  • Memory

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.

Parameterised Queries
This was related to the way SQL deals with parameter changes and selectivity related to statistics. Jasper covered the FORCE RECOMPILE options available and how parameterising query plans helped this. The real eye openeer for me was the ADO.Net comment about how ADO.Net parameters are passed as variable length data types and frequently cause both implicit conversions in the stored procedures and many different versions of essentially the same query plan. (I’ll be revisiting a C# helper class in a BizTalk orchestration which loads SAP Customers into SQL next week to sort this one out!!!)

 

UDF in a View
A silent performance killer that does not show up in a query plan. A simple case statement in a scalar function kills performance due to the way the optimiser uses it.

 

Dynamic Search
This was a good one. Jasper showed us 4 different ways to run the normal dynamic search type stored proc.

 

  1. Dynamic SQL
  2. x = ISNULL(@value,x)
  3. (x is null or x = @x)
  4. 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!

Covering Indexes
This one I have been through many times in indexing situation but was covered well and as with all of Jaspers training was literally proven by him during the session.

 

Summary
A very good start to SQL Bits V and as its my first I was very impressed. Good trainer(many thanks Jasper!) , nice hotel, good food and coffee. Sign me up for next year ! Part 2 Tomorrow …

 

Advertisements

2 responses to “SQLBits V Session Review : Practical Performance Tuning and Monitoring For SQL Server with Jasper Smith

  1. Pingback: Twitted by philcarter

  2. Pingback: Tweets that mention SQLBits V Session Review : Practical Performance Tuning and Monitoring For SQL Server with Jasper Smith « -- Topsy.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s