Identical SQL Server databases hosted in the same physical DB server

Posted on

Question :

enter image description hereWe have hosted similar SQL Server 2014 databases TestDB and TESTDB_Copy on the same physical server. The two databases are almost the same and there may be minor differences in the number of records (less than 1000) in few tables.

One database is responding quickly and other is very slow. etc., Query in TestDB takes 1 sec, but TestDB_copy takes 5-10 seconds. We have ensured the indexes are similar in both the databases and the rebuilt the index in both the databases.

Queried master.sys.dm_os_performance_counters and sys.dm_exec_cached_plans and they are almost the same for both the databases.

What else would be the issue with the slow responding database (TESTDB_Copy)?

Finding 1) The clustered index seek is 16% in fast database and 27% in slow database.

The no of rows is close to 400k in the table undergoing index seek.

Answer :

If these are quite large databases I would ask if they are on different physical disks, with one disk significantly slower or busier than the other? (i.e. one is SSD, and the other is a standard HDD).

For smaller databases though, the data will quickly be cached, and so speed of the disk would stop being relevant at that point.

I’d suggest turning on Client Statistics and Show Actual Execution Plan to narrow down where the queries against the two databases are not aligned.

One possible reason could be locking/blocking. If there is lots of activity going on in your ‘Slow’ DB, your queries might be waiting for long time before being able to acquire the required locks.

Leave a Reply

Your email address will not be published. Required fields are marked *