I'm no DBA hence me visiting here. I do however manage an application which writes to a SQL database. We are experiencing poor performance with the application now. Pages taking forever to load up and unexpected odd behaviour such as pages loading that shouldn't etc.

To improve performance would I be better off defragmenting the database or shrinking it? Why is shrinking so bad? Is it possible to explain that in a couple of lines?

  • Why do you think either of these things will improve performance? – Aaron Bertrand Oct 18 '16 at 12:23
  • I would also look into index maintenance and getting that set up regularly. Look into using Ola Hallengren's scripts if you can. – Chris Aldrich Oct 18 '16 at 13:03
  • You may also need to look into adding new indexes or dropping existing indexes. Don't just do this. Read up on the nuances of this. Application behaviors can change over time and you may now need new indexes to support behavior or remove indexes to support behavior. – Chris Aldrich Oct 18 '16 at 13:04

To improve performance would I be better off defragmenting the database or shrinking it?

Shrinking will not improve performance de-fragmenting the indexes should increase the performance, if that is really the reson behind the slowness. Shrinking is going to cause massive logical fragmentation so always bear in mind to avoid it. ONLY shrink when you have freed up large amount of space in SQL Server and you need that space at all cost. For all other reasons avoid shrinking

PS: Before going to shrinking at all I would first suggest you to find the cause of problem and Analyzing SQL Server Performance should get you started.

  • Thanks guys. The Analyzing SQL Server Performance doc is a good starting point for me. What about shrinking the transaction log? I know it sounds crazy but the application's support techs have recommended shrinking the transaction log....this is not to improve performance but for another issue. Are there risks associated with shrinking the transaction log? I am using simple recovery method too. So does that mean there's nothing actually in the transaction log after each daily back up? – yermander Oct 18 '16 at 12:07
  • Simple recovery mode does store transaction data only till the end of it. Shortly speaking it means that You won't be able to recover any other data than from Your last backup. – Marcin S. Oct 18 '16 at 12:15
  • 2
    NO, shrinking transaction log is also not advised, simply because it will grow again and when it grows it takes time and with many frequent growths it can be performance decider. Yes you can shrink log file if it has really grown out of proportion but shrinking daily is blunder – Shanky Oct 18 '16 at 12:24
  • Thanks guys for the input. The more I think about it the more I think the support tech is leading me on a merry dance. We are using the simple recovery method, does this mean that the transaction log is truncated each time the DB is backed up? (In our case, daily) this would render the operation of shrinking the transaction log useless because I shrink it at 9pm then at 10pm after the back up the transaction log would be empty anyway? – yermander Oct 18 '16 at 14:21
  • In simple recovery mode log automatically gets truncated when checkpoint happens(automatically) or when log reaches 70% of its size. You cannot take transaction log backup in simple recovery and FULL backup never truncates transaction log – Shanky Oct 18 '16 at 14:31

I assume You are asking about SQL Server database, right?

Neither of those two operations is really responsible for improving performance (and shrink doesn't improve anything about database performance/speed).

  1. But why dbcc shrink is bad?

    • increases load on server and requires additional maintenanace time
    • database (indexes) get heavily fragmented as a result of shrinking algorithms - this might lead to database growth on index rebuild/reorganize
    • in FULL RECOVERY mode every page movement made by shrink is logged in transaction log, hence log file grows a lot faster.

More about cons to shrinking SQL Server databases in Brent Ozar's and Paul Randall's blog posts.

  1. Why defragmenting often doesn't improve performance?

Mostly because performance issues are hidden somewhere else and defragmenting indexes works as quick band-aid (that doesn't last long, tho). It could be issue with statistics not up to date, or other issues. Just like Brent Ozar and Kendra Little wrote in their articles.

  • Yes I am talking about SQL server DB! Thanks] – yermander Oct 18 '16 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.