February 10, 2012 at 4:30 pm
Does reducing database size gives any better performance? If yes please explain?
Thanks
February 10, 2012 at 4:42 pm
It depends on what you mean by reducing the database size.
Just eliminating free space won't improve performance and most likely will hurt it because shrinking introduces fragmentation into your indexes and the DB will eventually grow which takes resources and cause fragmentation at the windows file level.
Reducing the # of rows in a table will improve performance, but that isn't always an option, businesses usually like to keep data as long as possible.
Usually you want to look at your indexing strategies and how you are querying the data to improve performance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 10, 2012 at 4:52 pm
Data is cleaned up from mdf file.so in order to get the free space to OS we need to shrinkdb right....
After shrink will it give any better performance?
Thanks
February 10, 2012 at 5:39 pm
Admingod (2/10/2012)
Does reducing database size gives any better performance? If yes please explain?Thanks
Along with all the "It Depends" explanations, I'll just come right out and say, "NO". Performance is in the design of the database, the indexes/stats and their proper maintenance, and, most of all, the way the code was written.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2012 at 7:35 pm
Admingod (2/10/2012)
Data is cleaned up from mdf file.so in order to get the free space to OS we need to shrinkdb right....After shrink will it give any better performance?
Thanks
No do not shrink the database. http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx. The author of the post in that link was on the SQL Server team and still trains people at Microsoft about SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 11, 2012 at 4:05 am
Admingod (2/10/2012)
Data is cleaned up from mdf file.so in order to get the free space to OS we need to shrinkdb right....After shrink will it give any better performance?
No. Probably worse until you rebuild your 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
February 11, 2012 at 5:55 am
Are you facing ‘low disk space’ issue on server? Or it’s a casual question?
February 11, 2012 at 2:17 pm
Disk space issue along the same lines i beleive backups will occur faster right since mdf file is reduced?
After shrink, fragmentation will be done....
Do i still see any advantages other than space??
Thanks
February 11, 2012 at 3:06 pm
Admingod (2/11/2012)
i beleive backups will occur faster right since mdf file is reduced?
No. Backups only back up used space not free space within the data file.
Do i still see any advantages other than space??
None whatsoever. In addition to the fragmentation problems, shrink too much (not leaving enough free space) and the DB will be frequently growing and that growth will slow down operations.
The best thing to do when you're low on disk space is get more disk space, not get into a repeated shrink-grow-shrink-grow cycle.
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
February 13, 2012 at 7:47 am
Thanks you guys....
February 13, 2012 at 12:50 pm
My two cents,
SQL is a big "it depends" environment which makes these questions a challenge. I am still fairly new to the SQL world so these are just my observations and opinions.
You may not get any direct performance benefit. If you are looking at removing historical data, shrink, and reindex then you just might get a little. I guess the bigger question would be, are you seeing any issues? If no then you probably won’t notice any difference. Fragmentation, proper indexing, stats, and countless other factors would give you better gains.
When you are looking at removing free space you should figure out the growth pattern.
Capture the used space everyday into a table then track growth for a month, the more data the better but a month is a good start but a year/12 would be better or could also be (avg daily*365)/12 :). A month might not be enough depending on your use of the data. Also think if that database might have any odd loads of data, like once a year a report is needed and loads several thousand rows, then you need to account for this as well. You may also need to consider space for maintenance tasks.
Sounds like a nice use of a basic cube 🙂 anyone know how :).
Once you know your growth pattern then you can start to adjust the size of your files. Always make sure you have enough space to process your data with as few file growth events as possible. If you know your growth pattern then you can adjust your files on your own time when there is idle time as there will be a hit on performance during growth. So with this said, I can shrink and reindex on my own time as well since I know the growth pattern. If the space is within reason don't worry about shrinking (always reindex after shrinking!).
In general, I prefer to keep the data file with about .5-1 month of free space and drive to have about 3 months or more free. Most tools use the basic 10% rule which can be fine for smaller databases but a 200 GB database having 20 GB free when it might only grow 5 GB in 3 months might be bit much. 10% is not a bad rule it just depends on your team's willingness. Storage teams often see from a total storage perspective where we see it from a “we” will use it eventually perspective :). You should also expand your growth pattern review process to est. a year and provide that to your storage team as they might be able to use this to help justify for new hardware 🙂 (Still working on this part myself). An analysis cube is what I am trying to figure out to measure performance and space.
February 14, 2012 at 7:08 am
GilaMonster (2/11/2012)
None whatsoever. In addition to the fragmentation problems, shrink too much (not leaving enough free space) and the DB will be frequently growing and that growth will slow down operations.The best thing to do when you're low on disk space is get more disk space, not get into a repeated shrink-grow-shrink-grow cycle.
To expand on Gail's comment, shrinking MDF's leads to internal (SQL Server level) index and heap fragmentation, while growing the MDF's leads to external (Filesystem level) file fragmentation.
Both of these can reduce performance.
February 14, 2012 at 7:20 am
Admingod (2/13/2012)
Thanks you guys....
And Gals;-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply