Reply to: Calling all SQL experts - To shrink or not to shrink?

[BigRed]

I'd never shrink unless I had a pressing need for the space or knew that it would never ever grow that big again. Each time it grows there is the potential for physical fragmentation. Backups don't copy empty extents. To prove this look at your backup stats before and after the purge. The job will report xxx pages backed up in xxx seconds. If you are having performance issues you would benefit from getting a performance tuning expert in as it sounds like you are doing the general basic stuff.

[suityou01]

Largely databases are split into two separate areas, OLAP (Reporting) and OLTP (Transational day to day stuff).

What tends to happen on the more organically grown systems is they start out as a few tables for transactional stuff and then as the database grows it gets hammered with the BI stick by well meaning department heads wanting meaningful reports to help manage/grow the business.

Quick wins for database performance can be to start a warehouse project, shovel (ETL) the data in to the warehouse and run the reports from there. The sell is that the warehouse gives time slices (snapshots) of the business so can provide richer reports. The management would struggle to justify the cost as warehouses are huge and consume many acres of disk space rapidly.

Or just stage the data for the reports elsewhere, run a nightly hose down and fresh ETL, so no time slices but freshly staged DB. You could sell this as a PreProd environment or DR system in the making.

The important point is you get reporting load off your DB.

Then you can run a profiler trace to identify slowly performing queries and get your DBA to help you identify when to daub some indexes or rewrite some of the queries to perform better.

Of course rebuilding indexes and updating statistics is bread and butter and it sounds very much like you're already doing that. The key here (badoom tish) is that just because a table is large (deep) doesn't mean it has to perform badly. This isn't 1970.

[SimonMac]

Simple enough for me to understand though?

[mudskipper]

I can explain it with smarties

[DimPrawn]

But is it only a temporary performance drop? You mention the indexes, but they are scheduled to be rebuilt anyway, so does this negate the loss of performance

Rebuilding the indexes after the shrink will move all the pages around again, the DB will then grow much larger during this process. OS Disk fragmentation will get even worse. But if it makes you feel like you're making things better and can invoice why the hell not?

[LondonManc]

No, this is Dynamics Batch history, yeah it is taking a while, only doing a month at a time but making a big improvement on size so far at least.

Sounds similar to the SSIS batch job history. Had fun with that last year.
One thing for you to look for - there may be a retention parameter or you might need to set up a job to periodically purge the batch history.

[SimonMac]

Like I said, shrinking a database will ruin the performance, so it's up to you.

But is it only a temporary performance drop? You mention the indexes, but they are scheduled to be rebuilt anyway, so does this negate the loss of performance

[DimPrawn]

Its clear in my mind at least

I am removing data, that bit I am happy with, I want to know if this alone will improve performance of backup/indexing etc or will I need to shrink the database as well

Like I said, shrinking a database will ruin the performance, so it's up to you.

[SimonMac]

You seem to be confusing removing data vs shrinking database. By all means remove old data, just shrinking the DB will move all the pages around, causing table and index fragmentation and OS disk fragmentation.

Its clear in my mind at least

I am removing data, that bit I am happy with, I want to know if this alone will improve performance of backup/indexing etc or will I need to shrink the database as well

[SimonMac]

Be careful with job history, it takes a fair while to clear down.

If it's SQL Server (which Dynamics AX tends to sit on), then do you mean the SQL Server job history table? There's a retention parameter which sets the number of days to keep. Reducing this will mean that the clear up process will suddenly dump the extra days - make sure you do this at a quiet time (create some SQL to run after your weekly backup).

No, this is Dynamics Batch history, yeah it is taking a while, only doing a month at a time but making a big improvement on size so far at least.

[DimPrawn]

Indexes are rebuilt weekly, but if there is less data in the tables, the rebuilt indexes etc will be completed quicker?

You seem to be confusing removing data vs shrinking database. By all means remove old data, just shrinking the DB will move all the pages around, causing table and index fragmentation and OS disk fragmentation.

[LondonManc]

Not SAP (Dynamics AX), that is exactly what I did, 4 of the biggest tables are the job history from when the system was launched in 2014 and not done with anything since

Be careful with job history, it takes a fair while to clear down.

If it's SQL Server (which Dynamics AX tends to sit on), then do you mean the SQL Server job history table? There's a retention parameter which sets the number of days to keep. Reducing this will mean that the clear up process will suddenly dump the extra days - make sure you do this at a quiet time (create some SQL to run after your weekly backup).

[SimonMac]

Shrinking destroys the performance because it fragments indexes and causes disk fragmentation. You then have to rebuild, which grows the database.

Indexes are rebuilt weekly, but if there is less data in the tables, the rebuilt indexes etc will be completed quicker?

[DimPrawn]

I saw that article, which is why I mentioned about the database growing over time to undo the work of the shrink, but if the regrowth is the only issue then shrinking the database after doing something so drastic is acceptable given how long it would take for the database to grow again at current rates.

Would I still notice improvements in the database if I remove data, but don't shrink if my main objective is to increase performance, not reclaim disk space

Shrinking destroys the performance because it fragments indexes and causes disk fragmentation. You then have to rebuild, which grows the database.

[SimonMac]

https://www.brentozar.com/archive/20...seriously-now/

I saw that article, which is why I mentioned about the database growing over time to undo the work of the shrink, but if the regrowth is the only issue then shrinking the database after doing something so drastic is acceptable given how long it would take for the database to grow again at current rates.

Would I still notice improvements in the database if I remove data, but don't shrink if my main objective is to increase performance, not reclaim disk space