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.

  • 2
    One annoying problem that can cause unexpected performance problems is parameter sniffing. You might check if that applies to any of your test queries you are running. Is it possible you could provide the example queries you are using? – mellamokb Aug 12 '15 at 4:40
  • 1
    Are indexes "similar" or the "same" ? For the query that shows longer duration, is the execution plan different between DBs ? – p w Aug 12 '15 at 6:36
  • 'Almost the same'....Almost suggests they're different. How different are they? Can we have some examples? – Mark Sinkinson Aug 12 '15 at 10:46
  • @p w : The execution plans are the same. Will investigate further and reply. – NSN Aug 13 '15 at 9:20
  • @MarkSinkinson In terms of records they may have 1000 +/- records. I am pretty sure that is not an issue. – NSN Aug 13 '15 at 9:22

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.

  • Yes will post you the details. – NSN Aug 14 '15 at 2:59

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.