Well, it's been a crazy few weeks! Paul and I have been booked solid with client engagements, Immersion Events, conferences and other things (like chapter editing for the upcoming SQL Server Deep Dives Volume 2) and so I've been a bit behind with blogging. I have a ToDo list of items to blog about but I've got one that's fairly urgent. I'll mention a few issues here but above all – don't trust anyone.
OK, I know that sounds a bit paranoid. Not meaning to be but this is a post that I'm adding to my "Just because" series as well as my "Are you kidding me?" series. That's how bad this is… almost as bad as these "stupid bacon related tatoos" – really, someone has this (forever!):
As a DBA (and/or SQL Architect/Database Designer/Developer) we're often tasked with making changes to databases and ideally, we want to use tools to make it easier. While I do agree that many tools help tremendously, I've been shocked lately by a few that have done the wrong thing. Paul blogged about a third party tool that didn't analyze indexes correctly here: Beware of advice from 3rd-party tools around dropping indexes (ultimately, the recommendations for what to drop – were wrong). And, recently, I've been shocked to learn that some of the schema change options in the database designer (in SSMS) are less than optimal when changing index definitions (especially those that handle changes to the clustering key).
Specifically, we had a customer that was doing everything right by testing the changes on a secondary system but SSMS was doing everything wrong (the order in which it made the changes and how it made the changes – were horrible). The end result is that if you're going to make schema changes, you really need to get more insight into what the application is doing. And, many tools have an option to script out the changes rather than execute them. So, my "quick tip" is to do that and see what's going on.
And, if you're going to make schema changes in ANY tool – then work through what those changes are going to do. Most important – consider re-arranging the SQL (especially wrt indexes). If you're ever changing the CL index you need to do the following:
- Drop nonclustered indexes first
- Drop the clustered next
- IMPORTANT NOTES: If you're completely changing it you definitely need to drop the nonclustered and then drop the CL. BUT, if you're keeping the name and only changing the definition (and it's NOT a constraint-based index) then you can use WITH DROP_EXISTING. If you use DROP_EXISTING you do NOT need to drop the nonclustered indexes first.
- Create the new clustered
- Recreate the nonclustered indexes
Some of the weird things that we've seen:
- Some changes to table structure (changing from a heap to a clustered or a clustered to a heap) cause disabled nonclustered to get rebuilt (and re-enabled). That's not even SSMS – that's a general SQL Server problem. In my opinion this means that disabling indexes is somewhat broken. So… gotta use drop/create (which is a bummer!).
- Changes to the clustering key in SSMS are sometimes done by dropping the clustered and then recreating the clustered. This will cause your nonclustered indexes to be rebuilt TWICE!
- If you have col1 that's currently clustered and col2 is currently nonclustered but you want to swap them… you can do that in the UI and then save but here's what they do:
- They drop the nonclustered
- They drop the clustered (this causes all of the remaining nonclustered to get rebuilt)
- They create the nonclustered
- They create the clustered (this causes all of the remaining AND the new nonclustered to get rebuilt… again)
This is only a short list of some of the crazy things that we've seen. I'm working on a more comprehensive overview of all of these things but I wanted to get a few of them out there. BEWARE OF THE TOOLS and always use scripting and testing to make sure that things are doing what you think. If you're EVER going to make changes to a critical system – this is exactly what I'd do:
- Script out *JUST* the objects and their definitions from the production environment
- Take *just* the schema and then go through and make your changes in the designer. Instead of saving the changes (which would immediately implement them), script out the changes and review the SQL.
- Run through the SQL and see what it does (but, this is an empty database so time isn't going to be as much of a factor here). If you think there's something wrong – ask around (colleagues/twitter/forums)…
- Then, once you feel you have a good version of the script THEN backup/restore the production database to your test system (I hope that you have this??) and THEN see how long it takes. If you have new concerns then ask around again!
- Next, before you consider if for production – you need to thoroughly test your applications. Are they affected by these changes?
- Then, and only when you've thoroughly tested it – you can consider it for production.
Picky yes… surprises NO. On a critical system you CANNOT afford surprises that create downtime – or worse, data loss.
Thanks for reading!!