July 28, 2010 at 2:23 pm
When i rebuild index i usually look for pages more than 1000 and frgamentation level more than 30%, but for re-org does page count matter or can i just rebuild any index whose fragmentation level is between 5 and 30 %. Please advice.thanks
July 28, 2010 at 8:05 pm
A reorganisation typically follows the same rules as a rebuild, it's just the "how" that changes (reorg shuffles stuff around, rebuild rebuilds). The main difference between the two is the amount of log activity that is generated, and whether it's an online or an offline operation. So, just like a rebuild, reorganising an index of less than 1000 pages (8MB) will have little effect on performance. That said, however, the re-org will be quick, and won't take up too transaction log space, so it's only really a concern if you have 200 of these small indexes!
July 28, 2010 at 8:27 pm
thanks
July 28, 2010 at 9:42 pm
Each database is different. There is no real set number of pages. Some indexes benefit substantially by defragging even if they have 100 pages.
The best advice is to know your database, tables, and indexes pretty well. By knowing this information you can gauge what number of pages would be a good number for each index in your database - or a general number from which to start.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply