June 22, 2018 at 3:23 am
Hi,
Which is the best method to reorganize sql server database.
The purpose is to reduce the size of database and increase the db performance.
Bulk amount records are deleted and updated frequently form my database.
Regards
Binu
June 22, 2018 at 3:29 am
binutb - Friday, June 22, 2018 3:23 AMHi,Which is the best method to reorganize sql server database.
The purpose is to reduce the size of database and increase the db performance.
Bulk amount records are deleted and updated frequently form my database.Regards
Binu
There's not much information to go on here but do you do any data archiving? If not that should bring immediate results to reduce the database size.
Can you provide a bit more detail please?
Thanks
June 22, 2018 at 4:24 am
Hi,
Data is not archiving.
Bulk amount of records have deleted/updated/inserted due to daily process.
Regards
Binu
June 22, 2018 at 7:29 am
binutb - Friday, June 22, 2018 3:23 AMHi,Which is the best method to reorganize sql server database.
The purpose is to reduce the size of database and increase the db performance.
Bulk amount records are deleted and updated frequently form my database.Regards
Binu
First, the size of the database has nothing to do with how well the database can perform. Even indexes with low page density will perform only with a nearly imperceptible difference in performance compared to if it has a high page density except for the first time it needs to be loaded into memory (the Read Aheads can be a killer there).
It sounds like you're talking about routine index maintenance. For that, you need to decide what the FILL FACTOR for each index needs to be and then use Ola Hallengren's index and statistics maintenance routines (unless you want to do a deep study of indexes and build your own). You can find his world class routine for that at the following URL...
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
One of the absolute MUST DOs is that you MUST decide what the correct FILL FACTOR is for your indexes before you start doing index maintenance. If you just leave all of your indexes at the default FILL FACTOR of "0" (which is the same as 100%), you will actually CAUSE performance problems with the index maintenance because of overwhelming and expensive-in-every-way page splits.
With the understanding that it is, by no means, a panacea, a reasonable rule of thumb for Non-Clustered indexes that do suffer fragmentation would be to give them an 80% FILL FACTOR and adjust over time if needed. Large Clustered Indexes need some investigation as to the distribution of new rows and whether or not there are post-INSERT updates that cause the rows to expand in size. Indexes that suffer no Logical Fragmentation can and should be left at "0" or "100" FILL FACTOR to conserve memory. Obviously, you'll need to check for things like that before you assign the correct FILL FACTOR.
OR... you can do like I've done for the last 2 and a half years. Leave the bloody indexes alone. Like I said earlier, Logical Fragmentation has absolute ZERO affect on performance except during the time when they are loaded from disk to memory where the READ AHEADS could cause a problem. But once they're in memory, Logical Fragmentation just doesn't matter at all.
Physical Fragmentation (page density or how full the pages are) doesn't matter much for performance either and for the same reasons. What DOES matter there (and it can be a HUGE problem), is the free-space on each page... that can waste a HUGE amount of memory which means less data can be loaded into memory which requires more frequent reads from disk which causes READ AHEADS which ARE a major performance problem if Logical Fragmentation is bad. The trouble is, you can't use a 100% (or even 90% for wide row indexes) FILL FACTOR to minimize wasted memory for everything because INSERTS and UPDATEs will cause rampant page splits.
With that in mind, the only thing I've occasional done for index maintenance in the last 2 and a half years is to find indexes that have a page density of less than 70% and rebuild those after some analysis.
Shifting gears a bit, properly maintaining statistics is an absolute must for SQL Server to be able to come up with decent execution plans.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2018 at 10:41 pm
This was removed by the editor as SPAM
June 25, 2018 at 6:00 am
Lj Burrows - Sunday, June 24, 2018 10:41 PMHere is the best way to reorganize the SQL Server Database. Go through it:
http://www.sqlmvp.org/how-to-rebuild-and-reorganize-index-in-sql-server/
Like so many articles on the subject of how to maintain indexes, it falls fairly well short. You can't just explain how to maintain indexes without explain the why and what happens behind the scenes.
1. It doesn't explain the ramifications of what happens when you use REORGANIZE instead of REBUILD, which can be devastating depending on the index.
2. It doesn't explain the horrible effect it can have if you REBUILD or REORGANIZE regularly fragmented indexes that have a 100% Fill Factor.
3.. It speaks little of the importance of physical fragmentation.,
4. It speaks nothing of when fragmentation actually matters and I'm not talking about 10% fragmentation... I'm speaking of why fragmentation matters and what is affected by it. Even Books Online states that the presence of logical fragmentation is not reason enough alone to do index maintenance and why (although it's not written with the necessary urgency on the subject either).
5. It speaks nothing of how to determine Fill Factor, which is quintessential in proper index maintenance lest the index maintenance cause more performance problems in an ever increasing death spiral than it fixes.
6. It speaks nothing of what actually happens to your system if you make the mistake of doing a REBUILD ALL on a table, especially with an assigned FILL FACTOR.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply