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 million rows, or backing up a 5 TB database. Whether we like it or not, we are fighting a losing battle, and DBA’s can’t reverse the information explosion. Or can we?
Category Archives: Performance Tuning
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 their transaction logs. Sadly, only about 21% said they have tuned their transaction logs in the past.
If you are one of those DBAs who don’t know transaction logs can be tuned, you need to take the time as soon as possible to learn about this topic.Why? First, every data modification made in SQL Server must be logged. Second, no transaction can complete until all of the related data modification are written to the transaction log. And third, writing to a transaction log is a single thread process. On a busy SQL Server instance, writing to the transaction log can become a bottleneck, preventing transactions from being completed on a timely basis, slowing down the entire instance.
There are many ways to tune a transaction log, some very simple, and others slightly more complicated, but it is not beyond the ability of most DBAs. To learn more about transaction log performance tuning, download my presentation slides on “Inside the SQL Server Transaction Log”, which will give you a good start on learning the basics of this important topic.
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 query compiles and recompiles that need to occur.
As with many “options” in SQL Server, whether this option should be turned on for a particular database is not a simple decision. So I was curious to see how many DBAs actually employ this option. I ran the above poll on my website, and had two surprises. The first surprise is that about 19% of responders said they use it, which I thought was high. The second surprise is that about 44% of responders have not even heard of this option.
If you are one of the 44% who aren’t familiar with forced parameterization, I suggest you read the following to learn more about it. While this database option can be very handy in specific cases, it can also hurt performance in other databases. If you try this option, be sure to perform a benchmark before implementing it, then another benchmark after implementing it, and seeing if you get the behavior you expect. Of course, perform this experiment in a test environment, not in production.
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 records are updated or inserted into a table, an index may experience page splitting. During index rebuilding or reorganizing, indexes are defragmented. During DBCC CHECK DB, indexes have to be checked for their integrity. During all of these processes, all data modifications have to be logged in the transaction log. And of course, indexes take up space in the data cache and on disk. All of these resources are wasted on unused indexes, and can hurt the overall performance of an instance of SQL Server.
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 non-contiguous manner on a disk subsystem, the file is considered to be physically fragmented. Physical file fragmentation can contribute to an additional load on your I/O subsystem and reduce performance because the disks have to work harder to read and write data. This is because the heads on disk drive have to randomly jump around to different physical locations on the disk to find all of the data, instead of reading and writing the data sequentially, as when the data is contiguous.
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 have run it longer than any other poll on the website, it has had one of the fewest response rates.
When I look at the results, my best guess is that people who have looked at the poll weren’t familiar with the question, and because of that, ignored responding to it, instead of admitting that they didn’t know what it was. Or, perhaps it is because this feature is only available in SQL Server 2008 and higher, and not everyone is running this version yet.
Brad M McGehee 