Sql-server – Table with 4 million records – different size and performance in development and production

sql serversql-server-2008sql-server-2008-r2

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?

Thanks.-

Best Answer

Never do shrink database as it increases fragmentation even more. And yes, it looks like sizes are different because of fragmentation, so you have lots of space that stores nothing (half filled pages). This is probably because of incorrect schema design and wrongly chosen clustered indexes. But fragmentation happens on production system and it's good idea to check for it and do index rebuild on a regular basis.

The error you get might appear of not enough memory so you could try SORT_IN_TEMPDB = ON option. Also consider REORGANIZE option as it is online and even if it fails, it will save all work done prior it fails. But REORGANIZE option just defragments leaf level of an index, not the entire tree.

If it doesn't help, the only way is to recreate database with scripts and fill it with production data but I hope it won't be necessary.

Related Question