We have hosted similar SQL Server 2014 databases
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.
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 (
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.