Production database substantially bigger than Dev I have a Production database and a Development database on two different servers. Both databases are being fed from the same data warehouse on a staging server. There are a series of automated SQL jobs that run and populate tables in a database. The jobs were copied from Dev into Prod so they should also be the same. However, Prod is growing much faster than Dev.  

There are approximately 1 billion rows of data in the database. Total data files in Prod are about 123GB larger. Total index files are about 31GB larger. I'm new to this, but I would expect the two databases to be fairly similar in size. If anything I was expect Dev to have some extra 'junk' and potentially be the bigger database.

Any ideas how to find the source of this size difference? I can increase the disk space on Prod if needed, but indicates to me that there may be an issue that needs to be addressed. I'd like to recoup the 153GB if possible.  



I'm admittedly a rookie when it comes to this, but I checked the Index folder in each environment and they both appear to have one Index. I checked properties and they look the same as well. Would more Index also cause an increase in size in the data files?

I'm running the IndexOptimize script from Ola Hallengren in both Prod and Dev and hoping that is adequately handling any significant fragmenting. I haven't actually migrated any data from Dev to Prod. We have a staging server that hosts the data. A set of SSIS and stored procedures moves the data from staging to databases on Dev. More SSIS jobs and stores procedures populated the tables in Dev. The SSIS and stored procedures are promoted from Dev to Prod and they run on Prod independently. The Prod jobs access the same staging server as Dev.
Compression can cause you to see different table and index sizes for the same data across two tables. 

You asked in a comment if there's any reason not to apply compression. The general trade-off for page compression is that your tables take up less space and you can fit more data into memory at a cost of CPU overhead. As a very general rule, if your server has CPU to spare you might as well test your workload before and after applying compression to see what happens. There are even some workloads which will get more efficient from a CPU perspective after applying compression. In short, "it depends".

Another reason not to use compression is if you aren't licensed for it. Your development server could be using developer edition which allows data compression but your production server could be using standard edition, which doesn't allow data compression until SQL Server 2016 SP1.