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
Database Administration,Performance Tuning | March 13, 2012 | 4:29 pmThere 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
Database Administration,Performance Tuning | February 27, 2012 | 12:48 pmI 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”?
Database Administration,Performance Tuning | November 14, 2011 | 3:14 pmIn 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?
Database Maintenance,Indexing,Performance Tuning | August 11, 2011 | 11:16 amIndexes 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?
Database Administration,Database Maintenance,Performance Tuning | June 1, 2011 | 12:44 pmEvery 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”?
Database Administration,Performance Tuning | April 25, 2011 | 1:52 pmMost 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”?
Database Administration,Performance Tuning | March 10, 2011 | 5:52 pmLock 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
Database Administration,Performance Tuning | February 3, 2011 | 2:51 pmWhen 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
Twitter
Linked In