Windows 7 and SQL Server Prompts a Question

Posted: May 6, 2009 in Uncategorized

On April 30th it looks like a number of people ran into issues with the database that is managing the downloads for Windows 7 Release Candidate. It looks like from reading a blog that Microsoft has found the problem and the problem was within an index. If you want to know more on this story you can find some information here. I have no knowledge that this information is correct or not correct, however it did cause one of my blog readers to send me a question. Mike asked me:

My company builds and hosts many web sites powered by MS SQL 2005 DB’s, and the first part of the nightly maintenance routine is, “check integrity, rebuild index, and reorganize index”.  Why wouldn’t Microsoft be doing this?  How serious is this fragmentation issue?  Does it only affect super massive DB’s with extreme volume?  Anything else we should be doing to learn from this issue that MS had?  Maybe you could just answer me in a blog post.

Mike,

I don’t know a lot of the details and the author of that blog post Ed Bott looks like he has an additional report of what is causing the slowdown. He did mention that it was a problem with fragmentation and the problem was solved by re-indexing. My guess is that the reason they are not doing a re-index every night may be due to the size of the table. At the same time I look at the fact that there are people all around the world that use their systems. With that being said and if I am accurate, they have a large table with little or no maintenance time. Now I could be completely off on my assumption of what happened and what the schedule is like. So I would rather answer this in a more generic example. What could we learn from a large table with a number of inserts and there is not much time for maintenance.

  • First of all I would think that on a large table with a large number of inserts should have an adjusted fill factor. If the pages have more free space then there is a better chance that the data could then be inserted without causing a large number of page splits.

How serious is the issue? In my opinion it is serious for anyone who has a high demand for a database with high performance requirements. You asked if this really on impacts large databases with a high number of transactions. I can tell you that from my experience there has been a number of times that I had something occur on a large table in my database and I was not made aware of the situation. On each of these databases that I have had these problems the database has been larger than 100 Gig’s.

I think as the hardware side of the industry grows and bigger and better hardware is released then the hardware masks problems that would be evident. I have had clients that had very small databases close to 10 Gigs, and inside this database there was normally no more than 10 people connected at one time. However the code was done so poorly that the database would do much more work then it needed to. That was a design issue for sure.

I hope that helps answer your question. If not let me know.

Comments
  1. datarealized says:

    A very interesting post.

    I have a blog post up about partitioning and one of the re-indexing strategies that I’ve successfully employed at multiple clients. It simply boils down to re-indexing the partition that is fragmented. If the partition scheme was designed correctly, the majority of data is ‘at rest’ and will never need to be re-indexed. This cuts down on maintenance time, backup windows etc… all the while providing better performance.

    In the same breath, I would say that the DBA’s at MSFT are people just like the rest of us… they have to learn and keep up with their own product much in the same way that we do.

    Jeremy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s