August 23, 2010 at 11:34 am
I have to share this because I just can't believe it even though I;ve read it several times. There is an online Developers Guide to SQL Indexes that appears to be a developer targeted How To & Help on working with Indexes. This sounded like a good idea to me when i saw it since I know developer types on average don't put enough effort into learning how to work with and use Databases in general. However it didn;t take long before I came across an item that just blew me away; a 'SQL Myths' section of the Guide that talked about the mytho f Rebuilding Indexes.
I will admit I'm not the most knowledgeable person in the DB community about Indexes but I like to think I know a little. Can anyone think of a reason why someone would make the following claim about SQL Indexes (under a current version of SQL Server; 2000-2008)?
The most prominent myth is that an index can become degenerated after a while and must be re-built regularly.
I realize that not every Index is handled teh smae way but in general can anyone think of a scenario where a borad statement about indexes like this one coudl be correct?
Kindest Regards,
Just say No to Facebook!August 23, 2010 at 12:36 pm
For one thing indexes on SQL server do not get 'degenerated'. They can suffer from excessive fragmentation or even just plain worthlessness.
Since indexes in SQl server are implemented as B-trees they are maintained balanced for good efficiency. But as they grow (due to the underlying data growing or changing) the space they take up can end up getting fragmented over time. The extent of this fragmentation depends on how much the underlying data grows.
SQL Server also maintains statistics for each index to help it make decisions regarding how useful it would be if used in a query. These statistics can also become incorrect.
For the reasons stated it can be useful to perform periodic index operations such as re calculating statistics, re organizing indexes or rebuilding indexes.
For the most part I find that the need for index maintenance is inversely proportional to the quality of the overall database architecture and quality of the overall design.
The probability of survival is inversely proportional to the angle of arrival.
August 23, 2010 at 12:38 pm
Do you have a link to the article so we can put the statement into context?
August 23, 2010 at 12:55 pm
sturner (8/23/2010)
For one thing indexes on SQL server do not get 'degenerated'. They can suffer from excessive fragmentation or even just plain worthlessness.Since indexes in SQl server are implemented as B-trees they are maintained balanced for good efficiency. But as they grow (due to the underlying data growing or changing) the space they take up can end up getting fragmented over time. The extent of this fragmentation depends on how much the underlying data grows.
SQL Server also maintains statistics for each index to help it make decisions regarding how useful it would be if used in a query. These statistics can also become incorrect.
For the reasons stated it can be useful to perform periodic index operations such as re calculating statistics, re organizing indexes or rebuilding indexes.
For the most part I find that the need for index maintenance is inversely proportional to the quality of the overall database architecture and quality of the overall design.
Thanks sTurner and man how I wish I had a better DB Deign to deal with. The monster I deal with was built (somewhat piece meal over time) solely by procedural programmers with no professional DB/DBA involved.
Kindest Regards,
Just say No to Facebook!August 23, 2010 at 12:58 pm
UPDATE:
I have to apolagize for wasting anyone times who read over this post. I just found out the article was specific to ORACLE, a DB platform that I can not speak to or about as I have no experinece with it althougfh I'm still suprised that even ORACLE would not need any index maintenance.
I had gotten a link to this thing from one of my SQL Server newleterrs (NOT from SQLServerCenrtal.com) and so I assumed (I know I shouldn't have) that it would be rleated to SQL Server.
Kindest Regards,
Just say No to Facebook!Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply