0

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.

Improve this question
5
  • 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
    Commented Aug 12, 2015 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
    Commented Aug 12, 2015 at 6:36
  • 'Almost the same'....Almost suggests they're different. How different are they? Can we have some examples?
    – Mark Sinkinson
    Commented Aug 12, 2015 at 10:46
  • @p w : The execution plans are the same. Will investigate further and reply.
    – NSN
    Commented Aug 13, 2015 at 9:20
  • @MarkSinkinson In terms of records they may have 1000 +/- records. I am pretty sure that is not an issue.
    – NSN
    Commented Aug 13, 2015 at 9:22

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • Yes will post you the details.
    – NSN
    Commented Aug 14, 2015 at 2:59
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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