July 23, 2013 at 6:36 am
Rob-350472 (7/23/2013)
There must be something different with connecting to a separate database either in straight tSQL vis via a procedure, not sure what though (it's very rare for a proc to be cross DB in our setup).
Not separate database, default schema possibly. The .. means default schema, you're not explicitly saying which schema you want.
Check the other DB, are there two tables tblConsolidated, one in dbo and one in another schema?
Check the default schema of the user in that DB that's running the T-SQL code. Check the default schema of the user that created the procedure (or if you have an execute as, check that)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2013 at 6:48 am
Can you also add the following to the procedure and to the ad-hoc SQL and post the output
SELECT SESSIONPROPERTY(ANSI_NULLS), SESSIONPROPERTY(ANSI_PADDING)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2013 at 7:44 am
Those queries both returned the same value, 1 in each case, for both the proc and the code (with ' around ansi_nulls).
The consolidated table only exists once on the audit database, it's a very small database tables wise, all with the default schema, I've changed the column to read Audit.dbo.tablename instead, alas it didn't make any difference.
It's almost as if the SP is running with different permissions to the straight code or something. What I don't get, if that's the case why wouldn't it throw an error that it couldn't read from the Audit database or something, or just have all nulls for that column if it couldn't evaluate it. Hmm
Looking at hte Proc the user is hte same as the user for hte straight code, it says 'execute as: caller', schema dbo. I'm not sure how I can tell what straight code is excuted as, whether it's 'caller' or not?!
July 23, 2013 at 7:48 am
There are small differences in the row counts of some of the tables - generally, the "SQL" version tables have more rows than the "PROC" version. Was the "SQL" version run after the "PROC" version with some inserts to the tables in between the two runs?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2013 at 7:51 am
Both were run against a live production server, they're running against tables which log our communications, diferences in row counts (not that significant, a few but not hundreds!) would be expected.
July 23, 2013 at 4:28 pm
I looked at the thread and the plans, and I like to throw my hat into the ring, and suggest a possibility that has not been mentioned yet: a bug in SQL Server.
First, Rob, can you post the output of "SELECT @@version"?
Next, can you tell us which is the correct output? 91 rows or 1181 rows?
Third, can you be a good boy and run both versions without those NOLOCK to see if this change matters?
Now, whether the dates are parameters or variables does per se has nothing to do with it. What matters is that with known values SQL Server chooses one plan, withunknown values, it chooses a different plan, because of different estimates. You could test this hypothesis, by adding this hint to the stored procedure:
OPTION (OPTIMIZE FOR (@MinDate UNKNOWN, @MaxDate UNKNOWN)
And you could add the same hint, but with the date values for the T-SQL query.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 24, 2013 at 1:26 am
Rob-350472 (2013-07-23)
It's almost as if the SP is running with different permissions to the straight code or something. What I don't get, if that's the case why wouldn't it throw an error that it couldn't read from the Audit database or something, or just have all nulls for that column if it couldn't evaluate it.
More importantly, that sub-select is the SELECT list and cannot affect the number of rows returned.
Looking at hte Proc the user is hte same as the user for hte straight code, it says 'execute as: caller', schema dbo. I'm not sure how I can tell what straight code is excuted as, whether it's 'caller' or not?!
It's executed AS CALLER as well. But I doubt that permissions has anything to do with it. The only kind of permission that could matter is if there is row-based security hidden in a view, but I don't see any traces of this.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 24, 2013 at 2:08 am
Hi Erland,
The results of @@Version:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
The correct outpt is via the query route at ~ 1,200 rows - that's how I was alerted to it, a colleague was alarmed when checking up on her team over a two week period! 'They must have done more than that!!' sort of thing.
I've removed hte no-lock hints from the entire SP - the results are the same, consistient with what I'd expected no-lock wise - I'd have been surprised if it made that much of a difference. (i.e. x10!)
I've added the OPTIMIZE hint to the end of the procedure - I can't quite believe this, results sets from the proc and query are IDENTICAL as a result. In one breath I'm very pleased (thank's for the suggestion!) in another I'm quite confused. I've used this OPTIMISE FOR UNKNOWN before, however, this was only to attempt to cure parameter sniffing - typical scenarios in my case are where a sproc takes seconds but an SSRS report takes minutes (well posted on here too), the optimize for unknown seems to cure or help in these situations, however, this is a cure for eratic execution times and has never changed the results set.
I'm slightly confused now, oh and I'm going to put the no-locks back (I know, I'm sorry!) to test it still returns identical results sets with those present (or 1-2 different 😉 )
Erland Sommarskog (7/23/2013)
I looked at the thread and the plans, and I like to throw my hat into the ring, and suggest a possibility that has not been mentioned yet: a bug in SQL Server.First, Rob, can you post the output of "SELECT @@version"?
Next, can you tell us which is the correct output? 91 rows or 1181 rows?
Third, can you be a good boy and run both versions without those NOLOCK to see if this change matters?
Now, whether the dates are parameters or variables does per se has nothing to do with it. What matters is that with known values SQL Server chooses one plan, withunknown values, it chooses a different plan, because of different estimates. You could test this hypothesis, by adding this hint to the stored procedure:
OPTION (OPTIMIZE FOR (@MinDate UNKNOWN, @MaxDate UNKNOWN)And you could add the same hint, but with the date values for the T-SQL query.
July 24, 2013 at 2:20 am
Rob-350472 (7/24/2013)
I've added the OPTIMIZE hint to the end of the procedure - I can't quite believe this, results sets from the proc and query are IDENTICAL as a result.
In that case I'm going to call bug on this one. SQL 2008 had a bug that could cause this, but it was fixed in 2008 SP1.
First step, patch that server to the latest SP (SP2 I think it is for 2008 R2), if the problem's still there, call CSS and open a case for this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2013 at 2:21 am
Added the no-lock hints back, thankfully this keeps the results the same, literally comment out or don't:
OPTION (OPTIMIZE FOR (@MinDate UNKNOWN, @MaxDate UNKNOWN))
And the proc results set changes from ~90 to ~1,200 results (not exact numbers because that doesn't matter when the size difference is so large), I'm baffled. A colleague thinks I'm pretty much lying (I don't blame him!) so I've sent him the SP to make sure he can replicate the behaviour.
July 24, 2013 at 2:27 am
GilaMonster (7/24/2013)
Rob-350472 (7/24/2013)
I've added the OPTIMIZE hint to the end of the procedure - I can't quite believe this, results sets from the proc and query are IDENTICAL as a result.In that case I'm going to call bug on this one. SQL 2008 had a bug that could cause this, but it was fixed in 2008 SP1.
First step, patch that server to the latest SP (SP2 I think it is for 2008 R2), if the problem's still there, call CSS and open a case for this.
If I run on a local test database (I did this yesterday but because it didn't have hte Audit database I couldn't consider it an identical test) and the results sets were the same, running @@Version locally:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
So it's a higher version compared to production. I wondre if I can find a record of this bug to persuade those who'd have to that patching is a good idea (well, it is anyway, but in this case!)
July 24, 2013 at 2:36 am
Rob-350472 (7/24/2013)
I've added the OPTIMIZE hint to the end of the procedure - I can't quite believe this, results sets from the proc and query are IDENTICAL as a result. In one breath I'm very pleased (thank's for the suggestion!) in another I'm quite confused. I've used this OPTIMISE FOR UNKNOWN before, however, this was only to attempt to cure parameter sniffing
Just to make this perfectly clear: Adding that hint should not affect the number of rows returned (for this query at least). If that happens, it's a bug.
The effect of the hint, is that you give the optimizer the same presumptions as when you use variables, and thus you get the same plan as in that case.
Action path:
1) Install SP2 for SQL 2008 R2. This may or may not resolve the issue, and it may resolve it for the wrong reasons. That is, because of other changes you get a different - and correct plan.
2) If the issue persists, try DBCC TRACEON(4199, 1). That's a trace flag that buys in all optimizer changes since RTM. You should not need this trace flag for a bug which cases incorrect results.
3) Open a case with CSS. (Which I would recommend even if the trace flag works, because you should not need it.)
4) If you don't have the time to wait for a fix, you will need to break the query apart using more intermediate results. This is a little difficult, because you cannot easily tell whether you have gotten out of the zone where you are exposed for the bug and not.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 24, 2013 at 2:50 am
I'm glad you've confirmed that - when I saw the results changed I doubted what I knew about optimize for unknown - I certainly didn't expect that. It seems I was valid in my expectations there.
I'm hoping we can just patch our production server, however, since it is production there may be timing implications of this. I'll have to speak with a couple of other people. Thanks for the second and third steps - I certainly hope I don't have to resort to CSS - a colleague did once for an infra issue and it was demanding to say the least!
This might sound dumb, but what do people actually consider the bug IS? Optimize for uknown may 'fix' the issue in this situation but what is the bug that's causing the issue? I'm assuming it's just an incorrect query plan it's cached, however, when I created a new SP from the existing one the same issue persisted from first run.
I'm dubious of how widespread it may be and how to detect it - clearly a results set difference like this is a clear indicaiton, however, might it manifest its self in a more subtle way? And in some cases a less senior person might just take whatever output a report gives as being correct :/
Many thanks for helping me track down the issue though 🙂
July 24, 2013 at 3:40 am
Rob-350472 (7/24/2013)
This might sound dumb, but what do people actually consider the bug IS?
It's an incorrect results bug, hard to say more. Looks like something in the proc is causing the optimiser to come up with a plan that is wrong. Since the optimise for 'fixes' it, probably around query simplification and parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2013 at 3:11 pm
Rob-350472 (7/24/2013)
This might sound dumb, but what do people actually consider the bug IS?
You got incorrect results. That's a bug.
Optimize for uknown may 'fix' the issue in this situation but what is the bug that's causing the issue? I'm assuming it's just an incorrect query plan it's cached, however, when I created a new SP from the existing one the same issue persisted from first run.
Yes, the optimizer went astray. It's not that OPTIMIZE FOR UNKNOWN is any guarantee to avoid bugs. It could just as well have been the other way around. The effect of OPTIMIZE FOR UNKNOWN is that it changes the estimates for the optimizer and by happenstance it avoids the dangerous territory.
If you have a test system with SQL 2008 RTM where you can reproduce the issue, you could test installing SP2 on that server before you patch production.
Yes, a session with CSS can take some time, since they will need your assistence to research the issue. Although in this case, they should just ask for a backup of the database so that they can send it to an escalation engineer. And, you should not be charged anything, since it's a bug.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply