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?
LVL 1
s_sykesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Which files of the database are shrinked?
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/
0
arnoldCommented:
yes, shrinking database impacts performance if during the day the newly inserted data has to await the space allocation.

There is not enough info to answer your question.

Understanding databse use/functionality along with the app that uses it may provide reason ..

In a situation where there is a working table that one to limit the number of records to active ..
Which might be what the situation is where each morning the process goes through the working table and "archives" completed tasks to a historic table.

There are several ms example of a sp/task that can run and reorganize the index or rebuild on a per index/table basis based on index fragmentation.

Instead of shrinking, you shoukd check the file growth settings compared to the data being added.
I.e. If there is a need for space, if you set it too low it will hit performance since every third record a new space allocation is needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
s_sykesAuthor Commented:
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.
0
arnoldCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.