SQL Server Shrink hurting performance?
I inherited a SQL server and the performance is not the best. I noticed that the front end app that uses the database moves data between several tables when it starts each morning and then runs a shrink on the database. It looks like there is a weekly maintenance task that rebuilds indexes and then runs a shrink on the weekend as well!
When I looked at the indexes today, there are many that are heavily fragmented. Autogrowth is set to 1MB, unlimited. I know this setup has been in place for a year or so. The database is around 20GB. Large chunks of data are moved in and out of the database regularly.
If I stop the shrink operations and rebuild the indexes, will that fix my problem? Or is the database likely in a state where more drastic action is needed?
When I looked at the indexes today, there are many that are heavily fragmented. Autogrowth is set to 1MB, unlimited. I know this setup has been in place for a year or so. The database is around 20GB. Large chunks of data are moved in and out of the database regularly.
If I stop the shrink operations and rebuild the indexes, will that fix my problem? Or is the database likely in a state where more drastic action is needed?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yep - I did read that. That's why I'm wondering of the database is completely messed up and need to be redone in some form to fix it.
First thing to try, disable the daily shrink or make sure auto shrink is not set. But monitor the growth.
Look at index fragmentation.
After shrink/allocate is not the issue, see if you could identify where the performance slowness is coming from, i.e. Specific queries/pages ... Then ......
You could use SQL tunning advisor to capture/analyze the operation and see what recomendations are made.
Look at index fragmentation.
After shrink/allocate is not the issue, see if you could identify where the performance slowness is coming from, i.e. Specific queries/pages ... Then ......
You could use SQL tunning advisor to capture/analyze the operation and see what recomendations are made.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
IF only T-log than is not that bad but indeex if MDF data files are shrinked as well then indexes will be fragmented so AFTER the shrink you should run a REBUILD and SP_UPDATESTATS instead of before the shrink.
You could read a lots of good advice here:
https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/