At my company we use SQL Express 2008 R2, which supports databases up to 10GB.
We have 2 databases with almost the same number of rows in all tables. But, one of them is 4.5 GB in size (which is in the development department) and the other (the one in production) is 9.87GB.
The database under development was auto generated by inserting rows in bulk for testing purposes. While the database in production, slow almost 1 year to generate that amount of data.
One table in particular, has 4,700,000 rows, and make the following query:
SELECT colum1,column2 FROM table WHERE column1 = 2
Takes about 2 minutes to respond on the production database and 20 seconds in the development database.
I did a shrink in production database and it took about 20 minutes to complete, but after that, the size of the database was reduced to 7 GB. I tried to rebuild the indexes, with right click, but it gives timeout and fails to finish the reconstruction.
My question is: Why would 2 databases with the same amount of records be so different in size and what can I do to improve performance on production database?