Situation
There are two database administrators are in company and both are working in Shifts.First Database admin keeps his index DE fragmented in day time so that high level queries can perform well but every next day he sees logical scan fragmentation to 90%.What's going on here?
Answer
Automatic Data Shrink Job was running every night.
So the question is what is the problem with Shrinking?
When we click on Shrink Database actually we are instructing SQL to remove unused space from database files. In this process SQL does index fragmentation and that affects performance of SQL in long run.
Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of SQL Queries that search a range of Index. To eliminate the fragmentation, consider re building the indexes after shrinking.
So question arises, Should we never shrink? No, we can shrink, but the feature there is for emergency or one time events. If we get a load of 500 GB on 1 TB data once a year we can use shrink once a while.
When we shrink we should use shrink file and target specific file and for a specific reason not as a part of regular maintenance.
Happy Reading and Keep Learning
Sanjay Bakshi
mesanjaybakshi@gmail.com
There are two database administrators are in company and both are working in Shifts.First Database admin keeps his index DE fragmented in day time so that high level queries can perform well but every next day he sees logical scan fragmentation to 90%.What's going on here?
Answer
Automatic Data Shrink Job was running every night.
So the question is what is the problem with Shrinking?
When we click on Shrink Database actually we are instructing SQL to remove unused space from database files. In this process SQL does index fragmentation and that affects performance of SQL in long run.
Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of SQL Queries that search a range of Index. To eliminate the fragmentation, consider re building the indexes after shrinking.
So question arises, Should we never shrink? No, we can shrink, but the feature there is for emergency or one time events. If we get a load of 500 GB on 1 TB data once a year we can use shrink once a while.
When we shrink we should use shrink file and target specific file and for a specific reason not as a part of regular maintenance.
Happy Reading and Keep Learning
Sanjay Bakshi
mesanjaybakshi@gmail.com
No comments:
Post a Comment