March 29, 2016 at 3:51 am
Hi All
1. We have a PRD-CRM database in our organization. Few days back, our application production team started complaining of slow performance. Once the case was analysed, this appeared to be occurring due to higher percentage level of fragmentation in the tables of this database. So, correspondingly, we re-indexed the database tables. Once we completed the task and checked again the avg fragmentation level, it was fine, but my database size grew by more than 100 GB.
So, my query is that "whether we can do something to resist the increase in size of database after re-indexing"
2. "Since we are defragmenting the same index, then why the extra space is being swallowed up by New index, although the previous one is being deleted automatically, once the newer index is created after defrag. If extra space is being used by new index, then where it is being used so that if needed we can recover it somehow"?
March 29, 2016 at 4:42 am
There's no problems with free space in the DB.
The extra space is there because a reindex creates the new index and then drops the old one. Hence you need free space in the data file at least as large as the largest index you're rebuilding. You can ensure the DB doesn't grow with you reindex by making sure there's enough free space in the DB for the new indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 11:45 am
You can also try to rebuild index using TempDB
March 29, 2016 at 12:11 pm
Won't change things much. Rebuild still creates the new index and drops the old. The only thing that moves to TempDB is the sort space required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 10:09 pm
mithunbhora (3/29/2016)
... "Since we are defragmenting the same index, then why the extra space is being swallowed up by New index, although the previous one is being deleted automatically, once the newer index is created after defrag..
Defragmenting does not delete indexes and creates new ones.Index rebuilds does that.Index rebuilds moves the data to a new set of allocated pages.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply