July 29, 2013 at 3:01 am
Good morning all. I am pulling my hair out over a problem I am having with a very slow performing stored procedure. The data is held across two servers, with different collations. One has SQL_Latin1_General_CP1_CI_AS and the other has Latin1_General_CI_AS. I am unable to change the collation of either server. The SPROC references a View which pulls data from the two servers and, for two fields, coalesces them. The data in the underlying tables is not indexed on these fields and again I have no ability to change that. If I comment out these two fields from the view, the sproc returns in just over 2 minutes, but with these two fields included it takes up to 40 minutes! I'm only dealing with a small number of result rows (normally less than 100), and the tables underneath only have a few hundred thousand records in them.
I am no DBA unfortunately and have no real experience of performance tuning as I've never been in this situation before. I have tried bringing up the execution plan. Honestly, it means nothing to me, but I can certainly see that the number of rows expected versus actual is vastly different (for example 106 actual against 80 million expected!) I am not sure that this is part of the problem, as the same discrepancy occurs in the 2 minutes version.
Are there any suggestions about the best approach I can take to resolve this issue? I appreciate that I haven't provided much information here as I don't know what is and isn't useful to you. If you need more, let me know what.
Any help gratefully received!
July 29, 2013 at 4:09 pm
From what you say, it does not seem that the collation matters, although collation mismatches certainly can cause performance issues. (But mostly they cause error messages.)
Ratherm I would think that without them, some index becomes covering which permits a faster plan.
Yes, big differences between estimated and actual values are often part of performance problems.
Could you post the query text, and attach the two query plans? (Please attatch them as .sqlplan files.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply