5

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.-

  • 2
    Two databases/tables even though on having same /structure rows can perform differently because they might be running on machine with different CPU,memory and disk susbsystem. The one showing slow performace might have table with outdated stats. Your question is difficult to answer without complete environment information and execution plan. Why dont you analyze execution plan and see the difference – Shanky Jul 17 '14 at 14:41
  • @Natalia a few questions regarding this: Are your recovery models in your user databases the same in Production and Development? Are your user database logfiles the same size in Production and Development? Are all the same indexes in place in Production or Development? Also, I second Blam's suggestion to run the Rebuild Index as T-SQL statement, not via SSMS's GUI, on both instances. Fragmented indexes over 4M+ rows might help to explain the size consumed by the DB as well as disparity in query performance. – Robert Gannon Jul 17 '14 at 15:56
  • What are the data types of all fields? If one database had just short ints as fields and the other had long ints as fields then I can see why one database is larger. – Mike Mar 1 '15 at 5:55
  • but it gives timeout maybe you should increase the timeout then... – usr Sep 25 '15 at 11:24
3

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.

  • Also the reason of timeout could be lock timeout and in that case you may wait for off hours to do this. To make sure you can use dmv sys.dm_tran_locks. Or even better there is excellent free tool sp_whoisactive, not sure it will work on express edition though. – yahor Jul 17 '14 at 23:16
0

I suspect it is just timing out if you are using SSMS right click.
Need to run the rebuild as TSQL statement.
You can right click SSMS and get syntax for drop and rebuild indexes.

As for size a fragmented index takes more space.
Given the index rebuilds are timing out and you have the large difference in query time you more likely have severely fragmented indexes.

You should even defragment the dev database.

0

I faced a similar situation in my company where a query at development took 4 to 5 seconds and in production used to take 30 seconds.

The issue was resolved as follows:

  1. Changed MAXDOP setting at server to 4
  2. Added 3 more MDF files of Temp DB. Now the total is 4 MDF of tempDB.

Your Answer

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