A Look at 3 Common SQL Server General Performance Tuning Measures

Happy 2012!

I was playing around with a couple of separate blog posting ideas last week as it was a sort of slow week in the office. I was going to link some very good other blogs as well in talking about Lock Pages in Memory, Instant File Initialization and Optimize for Ad-Hoc workloads. As I was drafting these posts, I realized that these all sort of fall into a similar broad category and rather than post them separately, I’ve decided to create a single post that allows you to look at these 3 options together as: “3 general options with SQL Server that MAY improve performance”. These are all either at the SQL Server instance level or server, so I think of these are using a sledgehammer for a problem that may actually require just a tack hammer. In other words: your mileage may vary and you should start your troubleshooting and performance tuning first at a more granular level and work your way up: schema, stored procs, database, instance, server, etc.

Lock Pages in Memory (LPIM)

It is becoming more & more common to find this as a recommendation for all SQL Server workloads. LPIM is a Windows Server level privilege is not enable by default and requires the privilege to be assigned to the SQL Server service account in order for it work. What it does is to tell the OS that it cannot page out data pages from memory to virtual OS memory on disk files, ensuring that SQL Server’s data pages will stay in memory. To set-up this option, see this MSDN page here. While there are instances where setting LPIM can be helpful, on systems where SQL Server is starved for memory, this can stop the OS from performing its normal routines the operation of your server because when it asks the SQL process to page some memory to disk, SQL will not be able to do so. And in virtual environments, make sure that you set the VM’s reservations to match the amount of RAM in the virtual machine configuration for VMWare, while in Hyper-V, LPIM is actually quite helpful for Dynamic Memory.

Instant File Initialization

Similar to LPIM above in the sense that this is actually something that you would set from Windows, not SQL, and that it will also affect the entire server, Instant Initialization is something that can provide benefits immediately to specific operations that require SQL Server to interact heavily with data files. Kimberly Tripp has the best write-up of this feature from SQL Skills here. In her review of Instant Initialize within SQL Server, note that Windows does not need to zero-out database files (this only works on database files) but this simple change in SQL Server’s file handling can result in some dramatic improvements in certain normal SQL Server activities. Notice these measurements from Kimberly:

Performance Test with Zero Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 14:02 minutes
ALTER DATABASE BY 10 GB = 7:01 minutes
RESTORE 30 GB DATABASE (EMPTY Backup) = 21:07 minutes
RESTORE 30 GB DATABASE (11GB Backup) = 38:28 minutes

Performance Test with Instant Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 1.3 seconds
ALTER DATABASE BY 10 GB = .4 seconds
RESTORE 30 GB DATABASE (EMPTY Backup) = 5 seconds
RESTORE 30 GB DATABASE (11GB Backup) = 19:42 minutes

Read more: http://sqlskills.com/BLOGS/KIMBERLY/post/Instant-Initialization-What-Why-and-How.aspx#ixzz1ihxSDtf5

Optimize for Ad Hoc Workloads

This is an option that you set at the instance level within SQL Server, see here. This one falls a little more, in my mind, into the category of performance tuning applications, T-SQL and plan caching. But from the perspective that this is an option that you can set and try it out by testing, testing, testing, then I felt it was fine to lump into this one post! With database workloads that have a lot of ad-hoc one-off queries run against the server, you can stop plan cache polluting that may be induced by SQL Server when it creates plans for those ad-hoc queries. With the option set to true for “Optimize for Ad Hoc Workloads”, SQL Server will instead create a small stub in the plan cache when the query is run for the first time. Now when that query is run for the 2nd time, then SQL Server will create a fully compiled plan, thinking that is now is valid for reuse. This can help to relieve memory pressure by not allowing the plan cache to become filled up with compiled plans that are not likely to be reused. A common workload where this may be helpful would be a transaction or operational database system for an application that requires ad-hoc real-time queries that are the types of dynamic SQL that does not lend itself well to caching re-usable plans. I would not think this would be very helpful in more predictable query-centric reporting workloads like a data warehouse.

Definitely some ideas and things for you to try out on your SQL Servers. I remember a friend once told me, back when I was a developer, that the best DBAs obsess over and spend too much time, tuning and optimizing their database servers. Hopefully for those of you that fall in that category, that you will find some of these useful.

Best, Mark


One response to “A Look at 3 Common SQL Server General Performance Tuning Measures

  1. Pingback: Link Resource # 41 : Jan 06 – Jan 13 « Dactylonomy of Web Resource

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