In my Insider newsletter a couple of weeks ago, I discussed how index fragmentation is often considered when designing indexes, but index depth often isn’t. In the newsletter I said I’d do a more comprehensive blog post with some data, so this is it. Fanout and Index Depth The index depth is determined by the fanout of […]
Paul S. Randal
In Recovery...
On index key size, index depth, and performance
Posted on: October 23, 2015 1:29 pm
Low priority locking wait types
Posted on: October 16, 2015 2:48 am
[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations […]
New course: Index Fragmentation Internals, Analysis, and Solutions
Posted on: June 5, 2015 9:11 am
My latest Pluralsight course has been published! It’s called SQL Server: Index Fragmentation Internals, Analysis, and Solutions and is just over 2.5 hours long. The modules are: Introduction Index Structure and Index Uses Types of Index Fragmentation Causes of Index Fragmentation Detecting Index Fragmentation Avoiding Index Fragmentation Removing Index Fragmentation Check it out here. We now […]
How would indexes on AG readable secondaries work?
Posted on: May 14, 2015 6:52 am
Last weekend there was a suggestion on the MVP distribution list about having temporary nonclustered indexes on AG readable secondaries, in the same way that you can have temporary statistics on them. I replied that in my opinion it would be extremely difficult to do that, and said I’d blog about why. Here’s my explanation. […]
Are mixed pages removed by an index rebuild?
Posted on: June 10, 2014 10:15 am
This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer. The first 8 pages that are allocated to an allocation unit are mixed pages from mixed extents, unless trace flag 1118 is enabled. See the following blog […]
Online index rebuild corruption bug in SQL Server 2012 SP1
Posted on: June 10, 2014 8:56 am
This is a quick blog post to let you know about a bug in SQL Server 2012 SP1 that can cause data loss when performing index maintenance. The data loss issue can happen in some circumstances when you do a parallel online rebuild of a clustered index while there are concurrent data modifications happening on the table […]
DBCC CHECKDB performance and computed-column indexes
Posted on: March 25, 2013 1:46 pm
[Edit 2016: The team ‘fixed’ the problem in SQL Server 2016 by skipping consistency checking these indexes unless WITH EXTENDED_LOGICAL_CHECKS is used.] It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC […]
Tracking page splits using the transaction log
Posted on: February 7, 2013 11:43 am
Whenever I’m teaching about index fragmentation I get asked how to track page splits proactively. This can be useful to discover fragmentation occurring in indexes you didn’t know had fragmentation problems, without running the sys.dm_db_index_physical_stats DMV (see here for how that works) against all the indexes in your databases. Today this came up multiple times, both in class and in […]
How are per-column modification counts tracked?
Posted on: March 13, 2012 11:30 am
Earlier today there was a question on the MVP mailing list asking how SQL Server keeps track of per-column modification counts. From 2008 onwards, the hidden system table sys.sysrscols tracks modifications to table columns using the rcmodified column. The hidden system tables (introduced in 2005 when we rewrote the entire metadata management system) are only […]
Code to list potential cluster key space savings per table
Posted on: February 6, 2012 1:20 pm
Back in January I posted the results of the cluster key size survey I ran in 2011 and explained how the larger the cluster key is on your table, the more space is being wasted in all the nonclustered index rows. Check it out if you haven't already. I've finally put together the code that […]
How cluster key size can lead to GBs of wasted space
Posted on: January 2, 2012 6:46 am
Back in November I kicked off a survey that had you run some code to get some details about your cluster keys, nonclustered indexes, and table size. I got results from more than 500 systems across the world, resulting in 97565 lines of data – thanks! The purpose of the survey is to highlight one […]
Survey: wasted space from cluster keys (code to run)
Posted on: November 8, 2011 2:49 pm
Continuing with my "index health" series, I've got another piece of code for you to run. This time I'm interested in the number of columns in your clustered indexes and the consequent amount of nonclustered index space used by the clustered index keys. Again, you're going to be really interested to see the results on […]
Code to list index counts per table
Posted on: November 2, 2011 11:20 am
Yesterday I blogged about how having too few or too many nonclustered indexes can be a big problem for performance (see here). Today I’m posting some code you can run which will print out the number of indexes for each table in each database on an instance. I made it print a result set per […]
Over and under indexing – how bad is it out there?
Posted on: November 1, 2011 1:15 pm
Back at the start of August I kicked off a survey (see here) that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world – a big thank you […]
TechNet Magazine: September 2011 SQL Q&A column
Posted on: September 14, 2011 5:43 am
The September edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Online index operations logging changes in SQL Server 2008 Is that transaction contained in my full backup? ALTER INDEX … REBUILD vs. ALTER INDEX … REGORGANIZE Avoiding regular […]
Survey: nonclustered index counts (code to run)
Posted on: August 4, 2011 3:27 pm
Here's a survey I've been meaning to run for quite a while – that Kimberly and I are really interested in. For all the databases on your server, how many nonclustered indexes do you tables have, plus is the table a heap or a clustered index? The code below will return the following result set […]
Beware of advice from 3rd-party tools around dropping indexes
Posted on: April 4, 2011 1:12 pm
(In this post I'm not going to name-and-shame, as I'm sure the problems will be fixed in time.) I want to warn you about unthinkingly acting on advice from 3rd-party tools around dropping nonclustered indexes. One of my long-term clients recently bought and installed a new tool and has been asking me about recommendations from […]
Do changes to index keys really do in-place updates?
Posted on: February 25, 2011 9:22 am
There was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (blog|twitter)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit […]
Missing index DMVs bug that could cost your sanity…
Posted on: May 1, 2010 4:41 pm
Here's yet another reason to be very careful when using the missing index DMVs… There's a bug in the missing index DMVs that could end up causing you to knock your head against a brick wall and question your sanity. I know I did. The bug is this: the missing index code may recommend a nonclustered […]
A SQL Server DBA myth a day: (29/30) fixing heap fragmentation
Posted on: April 29, 2010 8:22 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another quickie but goodie before the finale tomorrow! Myth #29: […]
A SQL Server DBA myth a day: (25/30) fill factor
Posted on: April 25, 2010 9:51 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) I’m all mythed-out from yesterday, so today’s is a quick […]
A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations
Posted on: April 8, 2010 7:24 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another short one today as I'm still teaching a class. […]
Inside sys.dm_db_index_physical_stats
Posted on: March 9, 2010 10:18 am
Way back in the mists of time, at the end of the last century, I wrote DBCC SHOWCONTIG for SQL Server 2000, to complement my new invention DBCC INDEXDEFRAG. I also used to wear shorts all the time, with luminous orange, yellow, or green socks. Many things change – I now have (some) dress sense, […]
Where do the Books Online index fragmentation thresholds come from?
Posted on: December 8, 2009 1:23 pm
I made them up. Yup. I'm talking about the guidance which is: if an index has less than 1000 pages and is in memory, don't bother removing fragmentation if the index has: less than 5% logical fragmentation, don't do anything between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX … […]
TechNet Magazine: August 2009 SQL Q&A column
Posted on: July 17, 2009 1:57 pm
The August edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Proactive page checksum failure detection Why GUIDs make for bad clustered index keys Possible problems using a log shipping secondary for reporting Recovery model choice for log size […]
An example of a nasty cluster key
Posted on: May 20, 2009 12:45 pm
I'm teaching a class this week on database maintenance, for DBAs inside Microsoft. One of the things we're discussing today is index fragmentation and how poor cluster key choice can lead to page splits, poor performance, index fragmentation, and so on – not just in the clustered index, but also in nonclustered indexes. One of […]
Clustered or nonclustered index on a random GUID?
Posted on: May 14, 2009 9:34 am
Quickie this morning to start the day off. I saw a question on a forum: if I *have* to use a GUID and *must* have a primary key, should I make the primary key clustered or nonclustered? Now, I'm not getting into the whole GUID vs. bigint identifier, or random GUID vs. GUID generated by […]
How expensive are page splits in terms of transaction log?
Posted on: May 12, 2009 5:50 pm
Page splits are always thought of as expensive, but just how bad are they? In this post I want to create an example to show how much more transaction log is created when a page in an index has to split. I'm going to use the sys.dm_tran_database_transactions DMV to show how much more transaction log […]
Index rebuilds depend on stats, which are updated by index rebuilds?!?
Posted on: March 27, 2009 7:28 am
Jack Li, one of the Senior Escalation Engineers in Product Support, just posted details of an interesting case over on the CSS blog – his article is here. It talks about index builds and rebuilds, but the issue is the same for both, so I'll just talk about rebuilds. The jist of the problem is that […]
Importance of index maintenance
Posted on: March 21, 2009 3:50 pm
Last week’s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I’m writing around index maintenance. Here are the results as of 3/21/09 – I find them very encouraging: As you can see, about 2/5 of respondents are performing some form […]
Weekly survey: index maintenance plan
Posted on: March 13, 2009 6:06 pm
I'm about to start a new series of post about index fragmentation and removing it. For this week's survey, I'd like to know what index maintenance you do to address fragmentation (in any of its forms) – I'll report on it in a week. Thanks
TechNet Magazine: April 2009 SQL Q&A column
Posted on: March 11, 2009 12:57 pm
The April edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]
TechEd demo: nonclustered index corruption
Posted on: December 17, 2008 11:03 am
This blog post describes the demo "2 – NC Indexes" from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. Edit 6/4/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]
Corruption bug that people are hitting – Msg 2511 using DBCC DBREINDEX
Posted on: November 14, 2008 5:53 pm
Here's another corruption bug that people are hitting on 2005 SP2 – something I didn't know about until today. The situation is this: a table with a non-unique clustered index (i.e. so a hidden uniquifier column is created), and then rebuilt using DBCC DBREINDEX. Sometimes an incorrect query plan is chosen so the rows in […]
Conference Questions Pot-Pourri #8: How to move constraint indexes?
Posted on: November 10, 2008 10:38 am
It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post. This is a question that […]
RunAs Radio interview on indexes (and future projects…)
Posted on: September 30, 2008 10:28 pm
For the first time in ages, Kimberly sneaked in an interview without me! I was teaching a class on Database Maintenance for some Microsoft DBAs and Kimberly recorded a RunAs Radio interview on her favorite subject – indexes. I love listening to her talk about indexes – or maybe I just like the sound of […]
Dev Connections newsletter
Posted on: September 10, 2008 7:29 am
At the last Connections conference in April, the conference organizers tapped a bunch of speakers for interviews, articles, and other content to put into a free “newsletter” called MyDevConnections, and now it’s finally available. It covers all the Connections conferences, so isn’t just limited to SQL Server. As far as SQL is concerned, Kimberly and […]
Search Engine Q&A #26: Myths around causing corruption
Posted on: August 27, 2008 5:11 pm
Every so often I’ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption. Physical corruption This is where something has altered […]
Can GUID cluster keys cause non-clustered index fragmentation?
Posted on: August 20, 2008 7:11 pm
At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID) that are also unique. Their uniqueness is […]
User groups and classes in August and September
Posted on: August 11, 2008 6:36 pm
Fresh off a week of teaching classes on the Microsoft campus, we’ve finalized some user group dates. Here’s what we have coming up: Monday August 18th: user group meeting in Redmond .NET Developer’s Association Database Meeting at the Microsoft Redmond Campus Topic: Index Internals and Maintenance Monday September 1st to 3rd: public class in England In […]
We’re writing a book – finally!
Posted on: July 10, 2008 10:37 pm
Well, almost… Kimberly and I have agreed to co-author the upcoming SQL Server 2008 Internals book with our good friend Kalen Delaney. This is the 2008 evolution of Kalen’s Inside SQL Server series of books and will be published by MS Press around February 2009. The other authors are (also our good friends) Adam Machanic […]
Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)
Posted on: June 8, 2008 5:12 pm
Over the last few weeks I've seen (and helped correct) quite a few myths and misconceptions about index rebuild operations. There's enough now to make it worthwhile doing a blog post (and it's too hot here in Orlando for us to go sit by the pool so we're both sitting here blogging)… Myth 1: index […]
How hard is it to pick the right non-clustered indexes?
Posted on: May 14, 2008 10:13 pm
Kimberly and I were presenting at our local (Redmond) .Net Developers Association on Monday and the following question came up while Kimberly was talking about missing and extra indexes (paraphrasing): What’s the best non-clustered index to use for the query with a predicate WHERE lastname = ‘Randal’ AND firstname = ‘Paul’ AND middleinitial = ‘S’? […]
Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics
Posted on: January 27, 2008 12:01 am
It seems like all I’ve been talking about on the forums the last couple of days is the correct order of operations in a maintenance plan. The biggest confusion seems to be about the effect of rebuilding an index on statistics, with some incorrect advice being given out on what to do. Rebuilding an index […]
SQL Server 2008: Spatial indexes
Posted on: January 17, 2008 7:59 pm
One of the cool features of SQL Server 2008 for developers is spatial data support. There have been some great posts recently about using spatial (see Simon Sabin’s blog here), which is all developer stuff – but what I’m interested in are the implications of spatial support for DBAs, and they are focused on spatial […]
Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?
Posted on: December 12, 2007 6:07 pm
While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – […]
Auto-shrink – turn it OFF!
Posted on: November 13, 2007 4:28 am
I’m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum and I can’t sleep – Kimberly’s out like a light so what else is there to do except write another blog post? :-)OK – actually posting this from Barcelona on Tuesday before our first […]
Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations
Posted on: November 6, 2007 11:17 pm
A couple more questions from the last couple of classes. Q1) Why doesn’t performing an index rebuild alter the fragmentation? A1) Here are the possibilities – all of which I’ve seen happen: There isn’t an index – either DBCC DBREINDEX or ALTER INDEX … REBUILD are being run on a table that only has a heap, […]
Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION?
Posted on: November 1, 2007 2:05 am
This is a quick answer to a question I was sent today by someone who’d read Kimberly’s partitioning whitepaper – Partitioned Tables and Indexes in SQL Server 2005 – and is implementing a “sliding-window” scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into/from a partitioned production table. Insertion […]
Indexes From Every Angle: How can you tell if an index is being used?
Posted on: October 5, 2007 1:39 am
Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’. If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a […]
Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?
Posted on: October 5, 2007 12:52 am
I’d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums. What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well – it […]
