The Same SQL Query takes longer to run in one DB than another DB under the same server

Question

I have a SQL database server and 2 databases under it with the same structure and data. I run the same sql query in the 2 databases, one of them takes longer while the other completes in less than 50% of the time. They both have different execution plans.

The query for the view is as below:

SELECT DISTINCT  i.SmtIssuer, i.SecID, ra.AssetNameCurrency AS AssetIdCurrency, i.IssuerCurrency, seg.ProxyCurrency, shifts.ScenarioDate, ten.TenorID, ten.Tenor, 
                      shifts.Shift, shifts.BusinessDate, shifts.ScenarioNum
FROM         dbo.tblRrmIssuer AS i INNER JOIN
                      dbo.tblRrmSegment AS seg ON i.Identifier = seg.Identifier AND i.SegmentID = seg.SegmentID INNER JOIN
                      dbo.tblRrmAsset AS ra ON seg.AssetID = ra.AssetID INNER JOIN
                      dbo.tblRrmHistSimShift AS shifts ON seg.Identifier = shifts.Identifier AND i.SegmentID = shifts.SegmentID INNER JOIN
                      dbo.tblRrmTenor AS ten ON shifts.TenorID = ten.TenorID INNER JOIN
                      dbo.tblAsset AS a ON i.SmtIssuer = a.SmtIssuer INNER JOIN
                      dbo.tblRrmSource AS sc ON seg.SourceID = sc.SourceID
WHERE     (a.AssetTypeID = 0) AND (sc.SourceName = 'CsVaR') AND (shifts.SourceID =
                          (SELECT     SourceID
                            FROM          dbo.tblRrmSource
                            WHERE      (SourceName = 'CsVaR')))

The things i have already tried are - rebuild & reorganize index on the table (tblRRMHistSimShifts - this table has over 2 million records), checked for locks or other background processes or errors on server, Max degree of parallelism for the server is 0.

Is there anything more you can suggest to fix this issue?

Maybe run it through the Database Engine Tuning Advisor. The faster DB may have some stats and indexes that the slower DB doesn't have. — PaulG, Mar 18 '13 at 16:22
Try adding (nolock) on the table where you are doing joins. And see if it improves the performance. — Praveen Mitta, Mar 18 '13 at 16:23
How many data/log files are there are how are they set up? Does this differ between databases? — Eric J. Price, Mar 18 '13 at 16:34
Can you post both [query plans](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan). Is the data the same in both databases? — Justin, Jun 04 '13 at 15:22

score 1 · Answer 1 · answered Mar 18 '13 at 18:32

The fact that you have two databases on same server and with same data set (as you said) does not ensure same execution plan.

Here are some of the reasons why the query plan may be different:

mdf and ldf files (for each database) are on different drives. If one drives is faster, that database will run the query faster too.
stalled statistics. If you have one database with newer stats than the other one, SQL has better chances of picking a proper (and
faster) execution plan.
Indexes: I know you said they both are identical, but I would check if you have same type of Indexes on both.

Focus on see why the query is running slow or see the actual execution plan, instead of comparing. Checking the actual execution plan for the slow query will give you a hint of why is running slower.

Also, I would not add a NO LOCK statement to fix the issue. In my experience, most slow queries can be tuned up via code or Index, instead of adding a NO LOCK hint that may get you modified or old result sets, depending of your transactions.

I tried removing the redundant subquery and restarting the SQL service, no luck yet. I checked the query plan for the one that takes a long time to run, the Clustered Index Seek on table - tblRrmHistSimShift has costs 54%, this is the table with abt 2 million records. — Divya, Mar 20 '13 at 09:52
Restarting the SQL server may not change the execution plan if schema and stats remain the same, it only clears the cache. Actually, a bad idea on a production server. I would suggest, as I mentioned before, take an in-depth look on your execution plan, for the query that you said it runs slow. But if you found an Index Seek, there is not much that you can do to make it faster. Try to archive that table (move old records) if you can. An Index seek on less records is always faster. — Jose, Mar 20 '13 at 22:06

Aleksandr Fedorenko · Answer 2 · 2013-03-19T07:43:07.293

Best way is rebuild & reorganize your request

SELECT DISTINCT  i.SmtIssuer, i.SecID, ra.AssetNameCurrency AS AssetIdCurrency, i.IssuerCurrency, seg.ProxyCurrency, shifts.ScenarioDate, ten.TenorID, ten.Tenor, 
                 shifts.Shift, shifts.BusinessDate, shifts.ScenarioNum
FROM dbo.tblRrmIssuer AS i INNER JOIN dbo.tblRrmSegment AS seg ON i.Identifier = seg.Identifier AND i.SegmentID = seg.SegmentID 
                           INNER JOIN dbo.tblRrmSource AS sc ON seg.SourceID = sc.SourceID
                           INNER JOIN dbo.tblRrmAsset AS ra ON seg.AssetID = ra.AssetID 
                           INNER JOIN dbo.tblRrmHistSimShift AS shifts ON seg.Identifier = shifts.Identifier AND i.SegmentID = shifts.SegmentID AND shifts.SourceID = sc.SourceID
                           INNER JOIN dbo.tblRrmTenor AS ten ON shifts.TenorID = ten.TenorID 
                           INNER JOIN dbo.tblAsset AS a ON i.SmtIssuer = a.SmtIssuer 
WHERE (a.AssetTypeID = 0) AND (sc.SourceName = 'CsVaR')

The Same SQL Query takes longer to run in one DB than another DB under the same server

2 Answers2

Linked