Performance Tuning - Category Archive
The following is a list of all entries from the Performance Tuning category.
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
Most SQL Server DBAs Still Don’t Know About Instant File Initialization
Database Administration,Database Maintenance,Performance Tuning | September 20, 2010 | 8:53 amIn 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 ...read more
Checkout SQLServerIO.com for Useful Information on SQL Server Storage Systems
Database Administration,Hardware,Performance Tuning | August 18, 2010 | 4:45 pmAt 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 ...read more
Twitter
Linked In