May 25, 2016 at 2:22 am
Shrink DB bring in cost. After it is shrunk, if no maintenance steps were taken, such as re-indexing DB and archiving data, the DB will grow back to its size. What we see it did not payoff well is, shrink would not respect "well-formed" of DB structure and usually bring in performance impact due to fragmentation after shrink.
Each DB physical disk size can be viewed into an actual data size and and available free space size. Reclaiming available free space that is allocated is not a good practice.
Index fragmentation on large tables would waste some space and you need this fixed up. More appropriately, you need to identify what are the unused index and what are the big index. Unused index may subject to dropping, large index will need close look on how it is used and rework. Typically, any big includes?
Always good to add in more disk space. Shrink is not an option, unless you know what you are doing.
May 26, 2016 at 11:14 am
aahkam (5/25/2016)
... More appropriately, you need to identify what are the unused index and what are the big index. Unused index may subject to dropping, large index will need close look on how it is used and rework. Typically, any big includes? ...
You need to be careful dropping apparently unused indexes. This is from today's newsletter from Brent Ozar:
Unused indexes are tricky. When you are analyzing this data, you have to keep in mind that this data is only available since the last restart. If you rebooted the server yesterday and are viewing the data today, you might see a lot of unused indexes in the list. But are they really unused? Or have the indexes not just been used YET? This is a very important point when deciding to disable or drop an index based on this list. If you reboot your servers monthly due to Microsoft security patches, consider reviewing the list the day prior to the reboot. I once dropped an index 3 weeks after the server was rebooted, thinking that the entire application workload must have been run by now. A few days later, I got a call on the weekend that the database server was pegged at 100% CPU utilization. I reviewed which queries were using the most CPU and found that the top query’s WHERE clause matched the index I had dropped. That query only ran once a month, which is why it hadn’t recorded any reads yet. We later moved that monthly process to another server that was refreshed regularly with production data.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 27, 2016 at 3:36 am
Certainly. Any index to be removed must need impact analysis, that is why identification of unused index is important.
May 27, 2016 at 8:23 am
If you are looking for a quick and easy magic bullet to significantly reduce your DB space used, there's only really one: use Page Compression on your (biggest/all) tables if you have not already done so. Yes it does have some real and potentially significant costs (addl. CPU overhead, primarily), but it's a one-shot fix that can have a big impact.
and of course, if you want that additional free space to be turned back to your disks, you will have to shrink (and defrag) the files some after that.
That said, the only other course is, as everyone has said, to get space back the old-fashioned way: delete/archive data. But that's real work to do it correctly so it needs to be a well thought out process.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 14, 2016 at 1:23 am
This was removed by the editor as SPAM
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply