Back in September I blogged about an old 2005 bug that prevented DBCC CHECKFILEGROUP checking the partitions of an object on the specified filegroup unless *all* partitions of the object are on the specified filegroup (not a smart way to set things up!). The bug was fixed ages ago in 2005 but has only just [...]
Paul S. Randal
In Recovery...
DBCC CHECKFILEGROUP bug fixed in 2008 SP1 CU6
Posted on: January 25, 2010 10:47 am
Interesting 2008 partitioned view perf bug fixed in SP1 CU4
Posted on: December 3, 2009 11:28 am
This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent. The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes [...]
SQL Server 2008 High Availability whitepaper published on MSDN
Posted on: September 28, 2009 12:42 pm
The 35-page whitepaper on high availability I wrote for the SQL team over the summer has been published on MSDN. It's a 2-300 level whitepaper that describes the various high-availability technologies in SQL Server 2008 and how they can be used to mitigate disasters. It's chock-full of links to other whitepapers, technical articles and Books [...]
DBCC CHECKFILEGROUP bug on SQL Server 2008
Posted on: September 23, 2009 1:54 am
Here's an interesting bug that surfaced recently, first reported by Bryan Smith on the MSDN disaster recovery/HA forum three weeks ago. One of the mechanisms I advise for running consistency checks on VLDBs with multiple filegroups is to run successive DBCC CHECKFILEGROUP operations on the filegroups holding the partitions (see CHECKDB From Every Angle: Consistency Checking Options [...]
Physical database layout vs. database size
Posted on: April 27, 2009 12:21 am
A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn't disappointed, with over 1000 responses! Here are [...]
Weekly survey: does size really matter – or is it what you do with it?
Posted on: April 10, 2009 3:05 pm
This week's survey is a little more complicated. I'm interested in the physical layout of your databases. I've got four surveys, for a variety of database sizes. Please vote multiple times in each survey, as you see fit – and by all means forward this link to your friends/clients/etc or re-blog/tweet it. I'm going to report [...]
TechNet Magazine: February 2009 SQL Q&A column
Posted on: January 16, 2009 10:15 am
OK – last content post today. I forgot that the February TechNet Magazine also has the latest edition of my regular SQL Q&A column. This month's column covers: Should backup compression be enabled at the instance level? Client redirection during database mirroring failovers Partition-level lock escalation in SQL Server 2008 Is it ever safe to [...]
TechNet Radio interview on partitioning (Kimberly)
Posted on: January 16, 2009 9:20 am
Kimberly also did an interview with TechNet Radio back in December – this time about partitioning. The links for the interview are: WMA – http://download.microsoft.com/download/F/5/2/F52567BB-D32D-437E-A9A7-60D898AA03C7/TechNetRadio-01132009-web.wma MP3 hi-bandwidth – http://download.microsoft.com/download/F/5/2/F52567BB-D32D-437E-A9A7-60D898AA03C7/TechNetRadio-01132009-hi-web.mp3 MP3 lo-bandwith – http://download.microsoft.com/download/F/5/2/F52567BB-D32D-437E-A9A7-60D898AA03C7/TechNetRadio-01132009-lo-web.mp3 Enjoy!
SQL Server 2008: Does my database contain Enterprise-only features?
Posted on: August 23, 2008 4:06 pm
Moving databases around is pretty common, as is moving databases between servers running different Editions of SQL Server, especially during a disaster recovery situation. You may not know this, but in SQL Server 2005, if you had partitioning anywhere in a database, you could only attach/restore that database using an Enterprise or Developer instance. I [...]
SQL Server 2008: How to get FILESTREAM and partitioning to work
Posted on: April 7, 2008 11:07 pm
Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week. One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get [...]
Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials
Posted on: March 12, 2008 5:23 pm
A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* [...]
SQL Server 2008 JumpStart
Posted on: February 15, 2008 12:38 am
Phew – last week Kimberly and I spent 3 days teaching the ins-and-outs of SQL Server 2008 for DBAs/IT-Pros to about 130 Microsoft SQL Server experts and MVPs (like Kalen Delaney, Adam Machanic and Ron Talmage). This was the (95% complete) Beta delivery of a course we’ve been developing for the last six months for Microsoft [...]
SQL Server 2008: Partition-level lock escalation details and examples
Posted on: January 14, 2008 10:10 pm
Back in October 2007 I blogged about partition-level lock escalation in SQL Server 2008 (see here) and I promised to do a follow-up once CTP-5 came out with syntax etc. So here it is. A brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have a [...]
Search Engine Q&A #9: How to update constraints?
Posted on: December 5, 2007 12:51 am
A quickie today to get back into the swing of things. In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this previous post for a few more details). She recommends that the constraints are extended rather than dropped [...]
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 [...]
Inside the Storage Engine: IAM pages, IAM chains, and allocation units
Posted on: October 4, 2007 12:42 am
This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called 'GAM intervals'. An IAM page tracks which extents within that [...]
SQL Server 2008: Parallelism improvements for partitioning
Posted on: October 3, 2007 10:40 pm
In SQL Server 2005, queries over partitioned tables use a single-thread per partition. This can cause performance problems under certain circumstances: On systems with many CPUs, if the table schema has less partitions than there are CPUs, then not all the CPUs will be used to process the query. Some examples: On a 32-way box, [...]
