Performance Tuning - Category Archive

The following is a list of all entries from the Performance Tuning category.

A Quick Introduction to Data Compression in SQL Server 2008

There is one thing every DBA knows with certainty, and that is that databases grow with time. MDFs grow, backups grow, and it never stops. The more data we have, the more work SQL Server has to perform in order to deal with it all; whether it’s executing a query on a table with 10 ...read more


Most DBAs Don’t Seem to Know that Transaction Logs Can be Tuned

I was very surprised to see the results of my latest poll, which asked “When was the last time you tuned your transaction logs?” According to those DBAs who responded to the poll, about 60% of them said they didn’t know transaction logs could be tuned. And just over 19% said they have never tuned ...read more


Do You Use “Forced Parameterization”?

In SQL Server 2005 and later, there is a database option called “forced parameterization”. When it is turned on, it overrides the default “simple parameterization” normally used by SQL Server. It does this by forcing most SELECT, INSERT, UPDATE, and DELETE statements to be parameterized, potentially boosting performance of databases by reducing the number of ...read more


When was the Last Time You Removed an Index Because it was Never Used?

Indexes can be great for boosting the performance of a query, but if an index is never used, it can drag down an instance’s performance. This is because unused indexes must be maintained just like any index. For example, anytime a row is inserted, updated, or deleted, indexes must be maintained. If a lot of ...read more


Do You Ever Physically Defragment Your SQL Server MDF & LDF Files?

Every since the first file was written to a floppy disk drive using DOS, physical file fragmentation has been a problem. Essentially, when the OS writes a file to a disk subsystem, and if contiguous clusters are not available, they are written elsewhere on disk. So when a file is stored on disk in a ...read more


Do You Enable “Optimize for Ad Hoc Workloads”?

Most of the time when I run a poll on my blog, I run it for at least a month so that I can get enough results in order for the data to be more or less representative of the SQL Server community. What has been odd about this poll, is that even though I ...read more


Do You Enable “Lock Pages in Memory”?

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: ...read more


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 ...read more


Copyright 2009-2012 Brad M McGehee