There are many reasons why we follow best practices. My own reasons have changed over the course of my career. Early in my career as a database developer and then later as a new DBA, I followed best practices because people who claimed to know more about SQL Server said to, and I assumed that…
Tag: Performance
Optimizing TempDB Performance
Today I thought I would blog about TempDB performance. On one of my new servers, with 24 cores, I allocated eight TempDB files as that should be enough to distribute the load, reduce contention and improve performance. However, I forgot to restart the server after hours and it went on for a week and performance…
Clustered Nuts?
While looking at performance in some of my databases, I noticed there was a high number of FreeSpace scans going on. Aaargh! They are called clustered indexes, people use them! Performance, performance, performance! In most cases it is best practices to start with a clustered index on every table created. There are rare instances when…
Breaking Down TempDB Contention (part 2)
I wrote a somewhat popular script and blog post a while back called Breaking Down TempDB Contention. This post explains how to identify tempdb contention. This past Friday, Jonathan Kehayias (blog|@SQLPoolboy) contacted me about the script. Jonathan said that he thought the math was off just a little bit in the script. The original script…
The Barking Dog Analogy
A discussion started today on Twitter about CXPacket waits. There had been a heated debate on the subject on Twitter the previous night (wow, does that sound geeky), so many of us got looped into today’s discussion. Someone had experienced a problem with a delete query on a large table causing all CPUs to nearly…
What Indexes Are Not Being Used And How Big Are They?
Leafs Stanley Cup Cabinet (or Cubs World Series Trophy Cabinet)Indexes…they are your friends. They order your data nice and neatly, they can dramatically improve your query performance and can have cool names like IX_UseMeToRunFasterLikeATurboButton. Indexes…they are your enemy. They slow down your inserts, updates and deletes, and they take up valuable disk space. So having…