Query performance on two databases on one server

Question

I've had a look at this: The Same SQL Query takes longer to run in one DB than another DB under the same server but am still perplexed by this one. I've tested this on both databases, have the exact same query plan, yet on the test database this query runs in under 20ms, and on the development database, this takes over 1 minute.

Thing to note is that the test database is an IDENTICAL duplicate of the development database at this current point in time (please note slight schema changes have taken place since asking this question - please see edit for more information). The query I am running is this:

SELECT 
    pn.PARTNO,
    LogisticsComment,
    Length,
    Width,
    Height,
    Weight 
FROM [partDB] pn
INNER JOIN [storeLines] sl
ON pn.PARTNO = sl.PARTNO
INNER JOIN [storeRequests] sr
ON sl.ITEMID = sr.LINEITEMS
WHERE sr.SERIAL = 'S14566'

This is the query execution plan:

I'm at a loss as to what could be causing this. Another thing to note, that linked question has 2 million records - this query currently should be returning 26 records.

Edit: Apologies for the delay, life has a habit of throwing curveballs.

As requested, please find the XML for both the live and test systems.

Development: PasteBin link

Test: PasteBin link

And the Actual Execution Plans for both live and test systems:

Development:

Test:

Edit 2: I've done a schema compare and noticed that two of the columns that are in this query, 'TMTPARTNO' and 'LogisticsComment' have differing data types - In the test system, they are varchar(50) and nvarchar(1500) respectively, and in the live system, they are char(18) and nchar(1500). Without changing data types in the live system, I do wonder if maybe the performance impact lies in the fact that there is so many bytes being used for the 'LogisticsComment' field?

Include into the question the XML of **actual** execution plans for **both** fast and slow queries. In SSMS tick in the menu Query - Include Actual Execution Plan. In the window with the query results and the plan right-click and Show Execution Plan XML. — Vladimir Baranov, Dec 03 '15 at 02:17
Hi @VladimirBaranov, apologies for the delay. I have included, as requested, the AEP and the XML of both queries. I had to remove some information from the XML due to the 30,000 character limit on SO, but please don't hesitate to let me know if you believe that the issue lies within those areas, and I will endeavour to get them posted as soon as possible. — DeeKayy90, Dec 11 '15 at 01:03
To whomever voted the 'Close', can you please let me know why/which exchange this would be more suited to? I posted here believing that the issue lay with the query, but it could be an issue with the structure - would it be more appropriate on dba? Or perhaps some other one? — DeeKayy90, Dec 11 '15 at 01:06
And to those that downvoted, please comment and let me know how I can improve the questions for future reference. No point just clicking down vote and being on your merry way, otherwise quality of questions will not improve. — DeeKayy90, Dec 11 '15 at 01:07
So, just pointing out - you mention the databases are IDENTICAL except at the end you say they are not. I would dig deeper for other subtle differences. Perhaps run generate scripts command and compare the output using a diff tool. — theMayer, Dec 11 '15 at 01:10
+1, Thank you for telling me about that @theMayer, I have used schema compare to find the differences - and these are the two that have come up. I work with some other people who may have changed these after I had posted this question, to try and solve this issue; I cannot be sure. But again, thank you for posting back why this question would get a downvote, I appreciate it. And honestly, your point does make sense - misinformation in one part as opposed to the other. Apologies if this is the case. — DeeKayy90, Dec 11 '15 at 02:15
Please note I have edited the question to reflect the fact that the schema has been indeed changed with differing datatypes for the PARTNO column and the LogisticsComment column. — DeeKayy90, Dec 11 '15 at 02:18
With regards to the downvote, there is some conflict on this site as to whether SQL performance is "programming". I upvoted it back up because posts of this quality are rare! — Nick.Mc, Dec 11 '15 at 03:00
I managed to open the first plan (but not the second- got an xml error). It might also be informative if you capture the _actual_ plan, which means press _query_ then _include actual execution plan_. Then run your query. Then for example you'll see 'estimated rows' and 'actual rows' in the plan. — Nick.Mc, Dec 11 '15 at 03:06
I also suggest you apply the prod changes back to test and see if it introduces the problem. — Nick.Mc, Dec 11 '15 at 03:09
@DeeKayy90, I can't open the provided XMLs. I expect it to start with ` — Vladimir Baranov, Dec 11 '15 at 04:06
@Nick.McDermaid Thank you for your assistance. XML errors are due to the removal of the tags - a question is limited to 30,000 characters and to show as much of the body as possible, I've removed tags I thought were redundant (i.e column names, xml header, etc.). I am unsure how I can add these back without exceeding the limit. I may put it on github but an external link may be frowned upon by SO community. — DeeKayy90, Dec 11 '15 at 04:14
@VladimirBaranov I have added them to pastebin.com, please see the revised question. Thank you for the suggestion! — DeeKayy90, Dec 14 '15 at 06:01

score 2 · Accepted Answer · answered Dec 14 '15 at 11:33

In the beginning of the question you are talking about Test vs Development databases; later you refer to Test vs Live. It is confusing.

When looking at both execution plans there is a warning:

Live - Cardinality Estimate Expression="CONVERT_IMPLICIT(nchar(18),[pn].[PartNumber],0)"

Test - Cardinality Estimate Expression="CONVERT_IMPLICIT(nvarchar(50),[pn].[PartNumber],0)"

What is the type of the fields pn.PartNumber and sl.PartNumber in both databases ?

Apparently they don't match in both cases, which is not good. It could be that in one case mismatch leads to more serious consequences. The estimated number of rows is slightly different (3 vs 4) and actual number of returned rows is 26.

Another difference in the plans:

Live plan uses index on STORES-REQ-LINK that is called STORES-REQ-LINK_SerialIndex.

Test plan uses index on STORES-REQ-LINK that is called STORES-REQ-LINK_Index02122015.

Are these indexes the same?

There are slight differences in table cardinalities: 110077 vs 106488, 97535 vs 92892, which explains the slight difference in estimated number of rows.

So, the databases are not identical, both in structure and in actual data. It would make sense to make them identical and compare performance again.

Having said all this, your tables are not big (~100K rows), so this kind of query should not take minutes.

These two databases are on the same server, so CPU and memory should be the same. Are they located on the same hard disks?

Still, the plans are very similar, actual data size processed by the queries is tiny (77KB vs 39KB), so this drastic difference in performance is likely to be caused by some blocking/locking. The slow query is likely to be simply waiting for some resource. What kind of activity do you have on both databases?

Apologies about the inconsistent naming - I have fixed this in the original post. As for the field types of sl.PartNumber and pn.PartNumber, indeed they do not match in the development system; however they are close - but not identical - in the test system. The indexes are the same, however the name in the live/development plan was named whilst creating, but in the test it was just left with a datestamp index (again, for testing). Yes, they are on the same disks - the development system receives much more traffic than the test system, however. Approximately 20x the connections than the test. — DeeKayy90, Dec 15 '15 at 05:24
Thank you for your answer Vladimir, and for your time. I will study the XML more thoroughly to understand it, but out of curiousity, what do you look for in particular when comparing the two execution plans? I hope to learn so that next time I don't ask a question without some basis of knowledge. Thanks! — DeeKayy90, Dec 15 '15 at 05:30
@DeeKayy90, in this case the plans indeed look very similar, which suggests that the slow system must be waiting on something. Or there is some other external cause. If the difference in performance is caused by substantial differences in the plans or data you'd see the difference in the size of the actual data processed; IO (number of reads); plan shape. You looked just at the plan shape, next step is to check the IO (reads), number of rows, bytes processed. Having looked at all this you can confirm that the problem is somewhere else. Then look at wait stats, overall perf stats of the server. — Vladimir Baranov, Dec 15 '15 at 22:31
Thank you for explaining this in further detail @VladimirBaranov, I appreciate it. I will have a look and see if the performance bottlenecks are stemming from the system or if there is a process that it is waiting on. — DeeKayy90, Dec 16 '15 at 01:40

Query performance on two databases on one server

1 Answers1