A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance. Here are the results: The “Other” responses were: 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script” 5 x “Combo of own and modified scripts of [...]
Paul S. Randal
In Recovery...
Easy automation of SQL Server database maintenance
Posted on: April 16, 2013 1:32 pm
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 [...]
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 [...]
Performance issues from wasted buffer pool memory
Posted on: June 1, 2011 10:13 am
Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools – how much memory is being used for data file pages and how much of that memory is storing empty space. I got back data from 1394 servers around the world – thanks! [...]
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: [...]
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 … [...]
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 [...]
Importance of choosing the right LOB storage technique
Posted on: April 3, 2009 8:46 am
Last week's survey was on how you should store large-value character data in SQL 2005+ (see here for the survey). Here are the result as of 4/3/2009 – and I think my favorite answer is starting to catch-on: My favorite answer is, of course, it depends! For all those who didn't answer 'it depends', your [...]
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 [...]
How to change a default constraint
Posted on: March 22, 2009 8:04 am
While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() - function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll [...]
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 [...]
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 [...]
