Search This Blog

Pages

The Continual SQL battle of Reindex and Shrink DB.

Over the weekend I went along to the fantastic DeveloperDeveloperDeveloper day held at Microsoft up in Reading. The quality of the presentations was fantastic and even for frameworks/subjects I was familar with I learnt loads. For me, the most useful presentation came at the end of the day (just as the brain was beginning to shut down due to information overload). Simon Sabin (from SQL Know How) presented "Things you should know about SQL as a developer".
A snapshot of his presentation can be found on his blog, but for me the most enlighten point was the battle between reindexing a table and shrinking a database. Typically the two feel like they should go hand in hand, but they will in fact fight each other. Re-indexing a table will create a new copy of the table in question, with the data correctly ordered to reduce fragmentation. As the original data is just marked as available, the process of re-indexing a table will potentially result in a database growing by the size of the table being re-indexed. Once the re-index process has completed the database will report all the space taken by the old copy of the table as free space.
However, if at this point if you shrink the database you will refragment your nicely re-indexed table as the shrinking process reads/moves the last row of the table first. This means your table content will end up reversed (and totally fragmented) after a shrink comand.
There are many reasons why you shouldn't shrink your database, this being just one of them!

Comments

Post a Comment

Popular posts from this blog

I'm definitely getting back into Android development, I'm remembering that feeling of 'Surely this should be easier than this!'. All I wanted to do was to schedule a local notification which behaved similar to a push notification pop-up. That is, as well as showing the small icon in the status bar I wanted it to pop up on screen to notify the end user. All seems fairly easily, I found this code for how to schedule a notification. That all worked perfectly, apart from the notification would only appear in the status bar. Searching around I found loads of different answers / solutions, mostly all saying the same thing:It only worked if you used 'NotificationCompat.Builder' in place of 'Notification.Builder', orYou had to set the priority to 'NotificationCompat.PRIORITY_HIGH'As usually happens, none of these solutions worked for me until I added in the missing piece of the jigsaw:- '.setDefaults(Notification.DEFAULT_ALL)'. For me this…

What do task hours add to the overall process in scrum?This was a question that has arisen from all team members in both instances that I've helped teams switch over to scrum. The benefits of artifacts like the comparative story point estimation, the 2 week sprints, stand-ups and the end of sprint demo have been self evident to the team, but as one I think every team member has expressed dismay when it comes to task planning and estimating each task in hours. Left unchecked there is a natural tendency for people to actually begin to dread the start of each sprint purely due to the task planning session.In my current role we've been lucky to investigate this further as a team.The team sat down to discuss the problems it was experiencing with estimating tasks in hours and the following common themes appeared:It is hard: Maybe it shouldn't be, but time estimation is hard! Story points are comparative and abstracted making them easier to determine, but time estimate is gen…

I've had this happen a couple of times now and the first time was a little worrying that I'd bricked my iPhone. Basically I was running an application on my phone via XCode and when rebuilding an updated version it failed with a "busy" error message. Stopping XCode and unconnecting my phone had no effect, the phone was stuck displaying the loading screen of the application and wouldn't respond to any key commands. To fix you have to hard reboot, holding the power and home button until the phone reboots - doesn't lose any of the data you have on your phone (a concern the first time I did it).