From Jeff Garbus, Founder and CEO of Soaring Eagle Data Solutions.
I’m a database performance and tuning geek. It’s what I do, and I love it. Across my career I have saved tens of millions of dollars for my clients (hundreds of millions?) who thought they needed new hardware (or more recently cloud CPU) to handle the volume of data they were using.
Proper indexing of the SQL server can reduce the workloads on the hardware, which prevents the need to buy more.
Here’s an example of some indexing work I did this week…
DISCLAIMER it gets technical-
This was a case where Identifying missing indexes was easier than finding a window to add the indexes.
In an environment running SQL Server Standard, creating an index will lock a table for the duration of the index build, which might be seconds, minutes, or hours.
This means everybody is blocked waiting for index creations to complete. (This is where a lot of problems occur in SQL Performance.)
This week, at my client’s busy 24x7 manufacturing facility, I found it was also necessary to temporarily change the AG from synchronous to asynchronous failover… this changed some of the index creations from timing out at 20 minutes to finishing in less than 5 (with no interruptions).
This work took server load from 70% down to 30%. Blocking and Deadlocks were reduced by 75%
Indexes. Don’t skip them.
Note- Running Enterprise, this can be performed using the “with online” predicate which limits blocking (while using a lot more log space, but who cares?)
