Category Archives: Performance Tuning

Do You Enable “Lock Pages in Memory”?

Posted on by

image

Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, and depending on the circumstances, the following conditions could occur when this setting is not turned on:

1) Performance of SQL Server could suddenly decreases for no apparent reason.

2) The hardware running SQL Server could suddenly appear to stop responding for a short period of time.

3) Applications connected to SQL Server could time out.

4) Among many others.

Continue reading →

Poll Reveals DBAs Actively Tune TEMPDB

Posted on by

image

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

Posted on by

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

Posted on by

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

Posted on by

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

Posted on by

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 →