Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
StackOverflow's database servers are hosted in a colo facility. They pay by the byte for transfer in and out of their servers, so it's really crucial to compress the database backups as much as possible. The smaller the backups, the less they pay. They're already using backup compression, but they wanted to explore more ways to cut their backup sizes.
Sometimes less is more - and indexes are definitely one of those times. Learn how to identify overlapping indexes and why they're so bad for performance, and learn about SQL Server 2005's new INCLUDE feature.
My gauge of an amazing book is simple: if I've got a question, and I reach for the book BEFORE I search the web, then it's an amazing book. Several times in the last two weeks, I reached for this book first.
Last week, I blogged about the basics of SQL Server index fragmentation: why it happens, how to fix it, and how often people are fixing it. I left you with a cliffhanger: it seemed that the frequency of defrag jobs didn't appear to affect fragmentation levels:
Databases with no index defragmentation were an average of 5% fragmented
Monthly - 17% fragmented
Weekly - 3% fragmented
Daily - 6% fragmented
We asked the community for index fragmentation information for data mining purposes, and the results are in - and interesting. Over the next couple of weeks, I'll talk about some of the results we found in the field.
What Is SQL Server Fragmentation?
When SQL 2005 was in beta, I clapped my hands and jumped for joy the first time I played with partitioning. I've done a lot of data warehouse work, and partitioning lets the DBA break down massive tables into smaller, easy-to-manage chunks. We can put heavily accessed parts of the table onto fast storage, and…
When I'm doing performance tuning on an application in the early stages of its lifecycle (or any app that's never had DBA attention before), I end up with a ton of recommendations within the first day of performance tuning. The resulting to-do list can seem overwhelming to project managers and developers, so I include one of the following two sentences as a part of each recommendation:
I had to do some developer training last week and I wrote up a paper on the basics of primary keys and indexes. Sure, there's tons of similar stuff around the net, but this is MINE, baby.
Our Table: Phone Numbers
For our evil training purposes, let's say we work for the phone company, and we need a database table with phone numbers. We need to track:
