• High Performance
• Balanced
• Power Saver

While it’s hard to perform a “real-world” CPU benchmark test, Glenn suggested I try using the Geekbench CPU benchmark software. The software runs a series of CPU and memory tests and produces a Geekbench Score which provides a standardized way to compare CPU performance.

To find out how much the three OS Power Plan options affected the performance of my T610, I first set the system BIOS in the Dell Setup program to “OS Control.” Dell not only allows you to set the BIOS to “OS Control,” which means the OS controls the power savings, it also includes other power savings options that are not controlled by the OS. I only tested the “OS Control” option.

Test Hardware: Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections.

Test Operating System: 64-bit Windows 2008 R2 (with current patches).

Next, I went to the Control Panel and changed the Power Plan setting to “High Performance”.

When I ran the test using the “High Performance” option, the Geekbench Score was 10,562.

Next, I changed the Power Plan to the “Balanced” setting, and the Geekbench Score was 7,991, a drop of about 24% in performance from the “High Performance” setting.

And last, I changed the Power Plan to the “Power Saver” setting, and the Geekbench Score was 7,075, about drop of about 33% in performance from the “High Performance” setting.

While the OS Power Plan settings affect more than just CPU, it becomes obvious from this simple test that changing the Power Plan can significantly affect a CPU’s performance, which in turn could significantly impact the performance of a SQL Server instance.

Of course, these three power options are available because they allow you to make a tradeoff between performance and power savings. As the DBA, you have to determine if the power saved by running the CPU more slowly is worth the performance tradeoff. The only way to really check this out would be to use a wattmeter to determine how much electricity is saved for each different settings. I don’t have a wattmeter to try this out, but Glenn has reported in some of his testing that the power savings appears to be insignificant, especially when compared to the significant drop in CPU performance between each “Power Saver” plan.

The point of this post is that when your configure your SQL Server’s hardware, you need to investigate what the appropriate “Power Saver” plan your server should be running. Based on this simple, initial testing, I think I’ll leave mine on “High Performance”.

Next, I populated the database with over 61 million rows of data, which virtually occupied all of the available space within the newly created database. After that, I backed up the database using SSMS, and then I deleted the original database.

At this point, I restored the database from the backup using SSMS. Below, you see the typical Restore Database screen.

At the bottom, right-hand side of the screen in the Progress box, notice the “Executing (0%)” indicator. Between the time I clicked the OK button to begin the restore, and when the “Executing (0%)” counter began to move, it took about 5 minutes and 50 seconds. At that point, the counter began to increment and the database was restored.

Now I make one very small change to my SQL Server instance (I’ll describe it in just a moment), and then I repeat the above steps (after deleting the database I just restored). First, I created a new 50GB database. This time, instead of taking 5 minutes and 50 seconds to create the database, it takes just under 2 seconds, a savings of about 5 minutes and 48 seconds. Next, I populated the database with the same amount of data as before, backed it up, and then deleted the original file. When I restored the database this time around, instead of having to wait 5 minutes and 50 seconds before the backup began to restore, I only had to wait just under 2 seconds. In both of these cases, I saved a significant amount of time.

So what was the very small change that I made, and why did it radically reduce the amount of time for database creation and database restoration to occur? I turned instant file initialization on.

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.

How Do You Turn Instant File Initialization On?

Unlike most configuration features in SQL Server, there is no on/off switch for instant file initialization. Instead, you have to assign a specific user right to the SQL Server Service (mssqlserver) account. Here’s what you need to do to turn on instant file initialization.

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:

• Windows Server 2003 or
• Windows Server 2008 or
• Windows Server 2008 R2

and using:

• SQL Server 2005 (any edition) or
• SQL Server 2008 (any edition) or
• SQL Server 2008 R2 (any edition)

Second, you must assign the SQL Server Service (mssqlserver) a special user right called “Perform volume maintenance tasks”. To do this, start the Local Security Policy tool (you must be a local administrator to perform this task), then drill down to Security Settings | Local Policies | User Rights Assignment | Perform volume maintenance tasks, as you see in the screenshot below.

Once you have located “Perform volume maintenance tasks”, right-click on it and select “Properties”, and the “Perform volume maintenance tasks Properties” screen appears. Click on “Add User or Group” and then proceed through the remaining screens until you select the account that is being used as the service account for SQL Server. In the screen shot below, notice that I have added the BRADMCGEHEE\sqlserverservice account to this user rights assignment. This is the user account I use on my test server to run my SQL Server instance.

Once the SQL Server service account has been assigned this user right, you will have to restart the SQL Server service (of course, only when it is not being used), and from this point forward, instant file initialization is turned on for all MDF files in your SQL Server instance.

Note: If your SQL Server service account is a member of the local administrators group, then the account already has the “Perform volume maintenance tasks” user right and you don’t need to assign it again.

Why Isn’t Instant File Initialization Turned On by Default?

When a SQL Server instance is first installed, one of the things you must enter is a SQL Server service account. If you follow the best practice and select a domain user account to be used as the SQL Server service account, the setup process automatically assigns the domain user account with only just enough rights and permissions to run SQL Server. The “Perform volume maintenance tasks” user right is not automatically assigned during installation because it is not required to run SQL Server, and because allowing the service account to have this additional user right introduces a very small security risk.

Oh no, a security risk! Well, not really much of a security risk. Here’s the possible security risk scenario. The disk that is being used to create the new database on has been used for storing data that has been previously deleted. As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data. This occurs all the time on millions of computers throughout the world every day. And as such, any data that has been marked for deletion, but not yet overwritten, is potentially available for access if you have the right tools and know what you are doing. In fact, undelete software uses this to recover data that has been accidently deleted.

When instant file initialization is not turned on, and when SQL Server allocates space for an MDF file, each of the pages allocated for the database is zeroed out, which removes the older data, in theory, preventing it from being accessed. I say “in theory” because there are computer forensics techniques that can even recover data that has been overwritten, but that discussion is really not applicable here.

So if instant file initialization is turned on, there is a very slight risk that someone could go to the pages allocated for the new database and read any older data that still may exist there. This is essentially a non-issue in virtually every organization, other than those that require very high security. But because of this potential security issue, instant file initialization is not turned on by default.

If instant file initialization is turned on, and pages are not zeroed out when the database is initially created, SQL Server will automatically overwrite any data that might have been on those pages when SQL Server needs that space.

When Is Instant File Initialization Used?

If instant file initialization is turned on, it is used in all of these cases:

• When a database is first created
• When a an existing database’s size is manually increased
• When tempdb is recreated each time SQL Server is restarted
• When autogrowth kicks in
• When backups are restored (as the space has to be pre-allocated before a restore can occur) 

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic. In my earlier test, when I created a new 50 GB database, the MDF file was 50 GB and the log file was only 1 MB. If I had created a large log file (which is not uncommon), it would have taken awhile for the log to be created, although the MDF file would have been instantly created. This is also true when you manually increase the size of a log file, or when log file autogrowth occurs. In other words, don’t expect to have all of your databases (MDF and LDF files) created in less than 2 seconds like in my test. While the MDF will be created virtually instantly, the log file may take awhile to be created.

When I was working with SQL Server 2000 a few years back, which does not support instant file initialization, one of the things that annoyed me the most when restoring large databases was waiting for the database space to be allocated before the restore actually began. During emergency database restores, this wasted a lot of precious time, preventing me from getting the database back into production as fast as I would have preferred. If you aren’t using instant file initialization today, you are facing this same problem. That’s why I recommend all SQL Server 2005/2008 instances have instant file initialization turned on. The time saved when restoring databases is the best reason to use instant file initialization.

Check to See if Your SQL Server Instances Have Instant File Initialization Turned On

Hopefully, by now, you see the benefits of using instant file initialization. Assuming that you don’t already know if instant file initialization is turned on or off on the SQL Servers your manage, I challenge you to check and see, and if you find it turned off, turn it on and reap its many benefits.

Test Hardware

• Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections. Hyper-threading turned off.
• One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives. MDF files located on RAID 10 array with 10 spindles, LDF files on RAID 10 array with 4 spindles, backup drive on a single spindle.

• DMV#1: Are you Connected?
• DMV#2: Session Ownership
• DMV#3: Current expensive, or blocked, requests
• DMV#4: Query Stats – Find the "top X" most expensive cached queries
• DMV#5: How many single-use ad-hoc Plans?
• DMV#6: Ad-hoc queries and the plan cache
• DMV#7: Investigate expensive cached stored procedures
• DMV#8: Find Queries that are waiting, or have waited, for a Memory Grant
• DMV#9: Monitor long-running transactions
• DMV#10: Identify locking and blocking issues
• DMV#11: Find Missing Indexes
• DMV#12: Interrogate Index Usage
• DMV#13: Table Storage Stats (Pages and Row Counts)
• DMV#14: Monitor TempDB
• DMV#15: Investigate Disk Bottlenecks via I/O Stalls
• DMV#16: Investigate Disk Bottlenecks via Pending I/O
• DMV#17: Why are we Waiting?
• DMV#18: Expose Performance Counters
• DMV#19: Basic CPU Configuration
• DMV#20: CPU Utilization History
• DMV#21: Monitor Schedule activity
• DMV#22: System-wide Memory Usage
• DMV#23: Detect Memory Pressure
• DMV#24: Investigate Memory Usage Across all Caches
• DMV#25: Investigate memory use in the Buffer Pool
• DMV#26: Rooting out Unruly CLR Tasks
• DMV#27: Full Text Search
• DMV#28: Page Repair attempts in Database Mirroring

The book’s authors include Glenn Berry (Blog | Twitter), Louis Davidson (Blog | Twitter), and Tim Ford (Blog | Twitter), and you can download the eBook and scripts here.

For those of you who are not familiar with SQL Response, it is a web-based SQL Server monitoring and alerting tool that can help reduce much of the drudgery of routine DBA tasks. Here are a few examples of what it can do.

• Monitor your SQL Server’s health and performance locally from your desk, or remotely from most any computer with a web browser, including many mobile devices.
• Your SQL Server instance’s health can be monitored at a global level (all instances at once), at the individual instance level, or on a per database level.
• Monitoring screens not only provide a real-time overview of SQL Server’s health, it collects historical data so you can go back into time and check out a server’s health at any point in time. This feature is great when you are trying to track down problems and need historical diagnostic data to help you better troubleshoot problems.
• Although SQL Response has the ability for real-time monitoring, you probably won’t be spending a lot of time watching the monitoring screens. Instead, you will be working on other projects. So if a problem should occur on a SQL Server instance, and you are busy doing something else, SQL Response can send you alerts vie e-mail so you always know if there are any problems with your servers, allowing you to be a proactive DBA.

This is just a few of the things that SQL Response does, and I don’t want to waste your time describing the product in detail. If any of the above features have grabbed your attention, then download the EAP and see for yourself how the application works.

As you might imagine, the SQL Response v2 EAP is not feature complete, nor is it bug-free. If you decide to try out the software, be sure that you install in on a test SQL Server box. On the other hand, you can use the EAP to monitor production servers, without risk, because SQL Response doesn’t install any software on the SQL Servers being monitored. Instead, it polls the servers remotely, which greatly reduces the overhead of collecting monitoring and alerting data.

To download the SQL Response v2 EAP, visit this webpage and sign-up. Once you sign up, you will receive an email with a link to download the software, in addition to other links you can use to provide feedback on the release. In fact, if you provide lots of feedback on the EAP, you may even be able to win some prizes, which are described in the email you will receive. So check out the EAP today and see how you can contribute to its success.

In the past, and still today, SQLServerCentral.com has been a strong supporter of the PASS Community Summit. But because of the lack of overlap among those who attend each conference, we felt that we had been missing out on those DBAs and developers who chose to attend the SQL Server Connections event. Because of this, starting last year, SQLServerCentral.com began to offer a SQLServerCentral.com track at SQL Server Connections.

SQL Server Connections (which is a part of the larger DevConnections conference), will be held November 1-5 in Las Vegas at the Mandalay Bay Hotel and Conference Center, and will follow this schedule:

Monday, November 1, 2010: Two SQL Server pre-conference workshops:

• “Database Best Practices for the Involuntary DBA” with Paul Randal and Kimberly L Tripp
• “Plumbing the Depths of SQL Server / PowerShell Integration” with Bob Beauchemin

Tuesday, November 2, 2010: Microsoft Day

• There will be three different tracks (DBA, DEV, BI), all presented by members of the Microsoft SQL Server Team

Wednesday and Thursday, November 3-4: SQL Server Connections and SQLServerCentral.com tracks:

• There will be three DBA tracks that are managed by Paul Randal and Kimberly L. Tripp, which includes a wide variety of speakers
• There will be a single SQLServerCentral.Com track, that is managed by Brad M. McGehee, which includes four different speakers
• Attendees will be able to attend any of the sessions in these four concurrent SQL Server tracks.

Friday, November 5, 2010: One SQL Server post-conference workshop:

• “Indexing Strategies and Analysis” with Paul Randal and Kimberly L Tripp

For a list of all the speakers, you can visit this webpage, and for a list of all the sessions, you can visit this webpage.

The SQLServerCentral.com track includes four speakers presenting a total of nine different session over two days. If you recognize any of the speakers, that is because you have probably seen their contributions at SQLServerCentral.com or Simple-Talk.com.

Glenn (blog | twitter) works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and he has a large collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, full text search, and SQL Azure. Glenn is also an adjunct faculty member at University College – University of Denver, where has been teaching since 2000, and also where he recently completed the Master Teacher Program. He is the author of two chapters in the book SQL Server MVP Deep Dives, and blogs regularly.

Brad (blog | twitter) is the Director of DBA Education for Red Gate Software, and is an accomplished Microsoft SQL Server MVP with over 15 years SQL Server experience. He is a frequent speaker at SQL PASS, European PASS, SQL Server Connections, SQLTeach, devLINK, SQLBits, SQL Saturdays, TechFests, Code Camps, and SQL Server user groups. Brad is the author or co-author of more than 15 technical books and over 250 published articles. His most recent books include How to Become an Exceptional DBA (2nd Edition), Mastering SQL Server Profiler, and Brad’s Sure Guide to SQL Server Maintenance Plans. His website is at http://www.bradmcgehee.com.

Allen (blog | twitter) is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He’s spent over 35 years in IT and has been using SQL Server since 1992, and is certified as both a SQL Server MCITP and Microsoft Certified Trainer (MCT). Allen has been awarded Microsoft’s MVP Award for the last three years. He’s active in the Ohio North SQL Server User’s Group and contributes in the MSDN Forums, answering questions about SMO and PowerShell, and blogs regularly.

Buck (blog | twitter) is a SQL Server Senior Technical Specialist for Microsoft, working with enterprise-level clients to develop data platform architecture solutions within their organizations. He has over twenty years professional and practical experience in computer networks and database design. He is a popular speaker at TechEd, PASS and many other conferences; the author of over 400 articles and five books on SQL Server; and he teaches a Database Design course at the University of Washington.

If you plan to attend SQL Server Connections this year, be sure to stop by and meet our speakers, if you don’t already know them.

I finally received all the components of my SQL Server test cluster (pictured left), which includes:

–One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives.

–Two identical Dell T610 Towers, each with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one HBA (to connect to the DAS); and dual network connections.

Before I start assembling the hardware and installing the operating system, I am first setting up an older Dell tower computer I have as a domain controller. In addition, I am configuring a wireless bridge to my wireless network, as the location of my test system is not located near a convenient network connection. Once I have the domain controller and wireless bridge configured and working, then I will begin assembling and configuring the new hardware.

I’ll post regularly about the trials and tribulations of getting the system up and running.

DevLINK, August 5-7, 2010

• How and When to Use Indexed Views
• Identifying SQL Server Performance Problems Using SQL Trace
• Essential DBA Skills: Introduction to Graphical Execution Plans

St. Louis SQL Server Users Group, August 11, 2010

• Identifying SQL Server Performance Problems Using SQL Trace
• Essential DBA Skills: Introduction to Graphical Execution Plans

Baton Rouge SQL Saturday, August 14, 2010

• Best Practices Every SQL Server DBA Must Know
• Database Maintenance Essentials

If you are attending, be sure to stop me and say hello.

Brad,

Today I got MCITP in BI and I finished the check list in your article: http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/.

Well, in the next month I want to start my first book, writing about replication. Do you have some tips on how to become a great writer like you?

Thanks for all,

Rodrigo Crespi
MCP-MCDBA-MCAD-MCSD-MCTS-MCITP
www.crespi.pro.br
www.blog.crespidb.com.br