Poll Reveals DBAs Actively Tune TEMPDB


When I manage a SQL Server instance, I always make an extra effort to optimize the performance of TEMPDB, so I decided to do a poll to see if other DBAs did the same.

When the results came in, I was a little surprised, as about 91% of those who responded to the poll said that they do take steps to optimize TEMPDB. When you look at the results, you can see that about 9% of the respondents said that they did not take any action to boost the performance of TEMPDB.

The remaining 91% of the respondents had the option to select one or more TEMPDB tuning options. Of the options they selected, three of the options were very close in the number of responses (the top three in the graph), with only the use of solid state storage coming in last. Because the top three options are so close, I am guessing that most of the people who responded to the poll probably perform all three, which is something that I do myself.

Of course, there are many other things you can do to help optimize TEMPDB, the ones listed here are only the tip of the iceberg. If you are one of those DBAs who still haven’t taken any action to optimize TEMPDB on your SQL Servers, you might want to download my presentation (a PDF file) on “How to Optimize TEMPDB Performance”.

Most SQL Server DBAs Still Don’t Know About Instant File Initialization

In my most recent poll, I asked DBAs if they have turned on instant file initialization on their SQL Servers. I was a little surprised by the results. Just over half of the DBAs who responded to the poll said that they don’t know what instant file initialization is, and another 13% said that they don’t use it. Only about a third said they have it turned on.

Instant file initialization seems to be one of SQL Server’s most hidden performance gems, and should be turned on for virtually every SQL Server. To learn more about instant file initialization, see my blog post: “Instant File Initialization Speeds SQL Server”.

Checkout SQLServerIO.com for Useful Information on SQL Server Storage Systems

At the recent SQLSaturday #28 in Baton Rouge, LA, I had the opportunity to meet Wes Brown (Blog | Twitter), and attend his session on “Understanding Storage Systems and SQL Server”. I enjoyed his presentation and also discovered that Wes has a blog where he shares his deep knowledge of SQL Server I/O. If you are interested in learning more about SQL Server I/O, his blog offers useful posts, presentations, and some software tools he has written for himself. For example, he has a Disk Drive RAID Configuration Tool that you can use to help give you an idea of RAID performance based on drive characteristics and other factors.

How Do You Defragment Your SQL Server Indexes

I give a lot of presentations based on best practices, and in all of them I stress the importance of regularly rebuilding or reorganizing indexes. Both the REBUILD and the REORGANIZE options have their pros and cons, so I thought it would be interesting to do a poll to see which method(s) was preferred by DBAs. The results are to the left.

After seeing the results of the poll (which was not scientifically designed), I was rather impressed with how many DBAs use a combination of both the REBUILD and the REORGNIZE methods to defragment their indexes. To me, this indicates a high level of knowledge of how to best defragment indexes, as using a combination of both options allows the DBA to choose which method is best for a particular index, rather than taking the brute force method of using either REBUILD or REORANIZE to defragment all of their indexes.  I was also impressed that only a very small percentage of the poll’s respondents didn’t defragment their indexes, or know what index defragmentation was.

Continue reading

How to Performance Tune a VLDB on your Desktop

Recently, I wrote a review of Red Gate Software’s new SQL Virtual Restore software. SQL Virtual Restore allows you to quickly turn a compressed backup into a live, virtual database, with the advantage that the virtual database is substantially smaller than the original database, while at the same time acting identically to the production database.

Continue reading

Windows OS Power Saving Feature Directly Affects CPU Performance

While I have always known that the Windows operating system Power Plan options affect a server’s performance, I had not realized how much until SQL Server MVP Glenn Berry (Blog | Twitter) suggested I try out my new Dell T610 test servers under the three available power savings plans:

  • High Performance
  • Balanced
  • Power Saver

Continue reading

PAL 2.0 Beta Released

Back in 2008, I wrote a blog post about version 1 of the Performance Analysis of Logs (PAL) tool. This free tool, available from pal.codeplex.com, has been enhanced, and Version 2.0, Beta 1.1, is currently available for download. I have just downloaded and installed it myself, although I haven’t had much chance to use it yet. When I have time, I will write a review of the new version. In the meantime, I recommend you download it and give it a try. It’s a great tool for analyzing Performance Monitor Logs, and it is also a great source for determining which Performance Monitor counters, and thresholds, should be used when analyzing SQL Server’s performance.