June 13, 2011 at 1:17 pm
I inherited a proc that runs in ~1m when ANSI_WARNINGS is OFF versus ~5m when it's ON. The developer I am working with gave me the history. The folks before me decided to solve a display issue by setting ANSI_WARNINGS OFF. This means when they run the proc in SSMS in "text output mode" they do not have to delete the ansi warnings before copying and pasting the results into a text file and emailing it to a partner company :laugh: Apparently someone is working on automating the process.
Once I got over that little nugget I explained to the developer a little more about what setting ANSI_WARNINGS OFF does in terms of hiding divide by zero errors and that since this process performs calculations on financial data it might be a good thing for us to know if something like that happens.
From BOL setting the option ON hides some errors and warnings that I think we should be seeing, logging and fixing. My gut feeling says I should recommend leaving the option ON (default in SSMS) when executing the proc but the performance difference is troubling and is something that will receive pushback regardless of the consequences regarding the errors and warnings I think we should be seeing.
All of that said I am running the proc with the same inputs, once with ANSI_WARNINGS OFF and once with ANSI_WARNINGS ON, to get the actual execution plans so I can compare them side-by-side. I am not sure if I can post those without obfuscating them and that may not be practical, we'll see. I couldn't find much when searching on "ANSI_WARNINGS performance" so thought I would ask it here. Have you heard of these conditions before? And if so, what might be the underlying principle here affecting performance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 1:19 pm
PS @@VERSION = Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Aug 5 2008 12:31:12 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 1:25 pm
ANSI_WARNINGS should not be causing a 4 minute difference.
As you mentioned, but a recap for everyone, the only thing it does is fail divide by zero or overflows and warn on nulls in aggregations. http://msdn.microsoft.com/en-us/library/ms190368.aspx
I don't think we'd be too worried about the execution plan here (unless they're significantly different with it off/on) except for rowcounts. Just how many calculations is this monster doing per row? Is there a divide and conquer optimization that seems reasonable?
Can you obfuscate the T-SQL enough for us to get the idea of what this is really up to at a volume level?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2011 at 1:48 pm
Are you getting different execution plans depending on if it's on or off?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2011 at 4:02 pm
Craig Farrell (6/13/2011)
I don't think we'd be too worried about the execution plan here (unless they're significantly different with it off/on) except for rowcounts.
The proc produces one resultset and the rowcounts were the same when ON and OFF.
Just how many calculations is this monster doing per row?
Not a ton, and there are some rabbit holes I have not gone down yet in the form of scalar UDFs.
Is there a divide and conquer optimization that seems reasonable?
The proc has a ton of opportunities for improvement. Overuse of scalar UDFs is a major area of concern. Lack of indexing on work tables as well as concrete tables. Table variables are participating in multi-table JOINs...converting those to temp tables is also something I'll be looking into.
Can you obfuscate the T-SQL enough for us to get the idea of what this is really up to at a volume level?
See attached. If you see anything sensitive please call it out and I'll re-post.
Grant Fritchey (6/13/2011)
Are you getting different execution plans depending on if it's on or off?
Comparing the two sqlplan files in a text-diff tool I am seeing some differences in the plans, most notably queries 16 and 19. However the graphical plans for those two look the same in SSMS. Disclaimer: I'm only into Chapter 2 of your book on Execution Plans.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 5:08 pm
61 different statements... You're on your own.
I will note that statement 7 & 8 (two of the highest, one in ON & one in OFF) both show ANSI_WARNINGS set to FALSE. Is there a particular statement that is causing the problem and has ANSI_WARNINGS set to ON on one of the connections?
You can see these settings in the SELECT operator by the way. I say this because I don't think I emphasized that enough in the original edition of the book.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2011 at 5:37 pm
Grant Fritchey (6/13/2011)
61 different statements... You're on your own.I will note that statement 7 & 8 (two of the highest, one in ON & one in OFF) both show ANSI_WARNINGS set to FALSE. Is there a particular statement that is causing the problem and has ANSI_WARNINGS set to ON on one of the connections?
You can see these settings in the SELECT operator by the way. I say this because I don't think I emphasized that enough in the original edition of the book.
I understand. I wasn't expecting any free work. I know these queries can be better and I have some blunt tools in my toolkit to improve those. Thanks for the leads on 7 & 8. I see some scans and a fat arrow in those that I can clean up for starters.
I was really interested in finding out why performance was so different when ANSI_WARNINGS was set different. I am now looking at the raw XML and am seeing this for every statement, including in the plan where ANSI_WARNINGS was explicitly set to ON at the top of the proc:
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false"
I need to find out what is re-setting it to false.
I learned after posting that many of the procs in the system explicitly set ANSI_WARNINGS OFF :ermm:
I think this is a case of one person trying it knowing it would get rid of the red warnings messages from the SSMS output window and it "catching on" as a standard through copy-paste when making new procs + word-of-mouth from uninformed developers.
I have my work cut out for me on the technical side and the political side on this one.
I'll post back if any pertinent info reveals itself.
Thanks for taking a look gents.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 6:11 pm
And with all that, now it's going to be even harder trying to figure out why you're getting performance differences and where those differences are coming from. I hope you're consulting because this is a long term contract.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2011 at 6:15 pm
Yikes...
I'd start with a brute force timing test to figure out which of these statements are the culprits, then dig in from there. That's just too much to tackle with obfuscation and not being able to work on the source system.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 14, 2011 at 10:19 am
New info:
The StatementSetOptions in the XML are apparently wrong per this bug report:
The bug report is for one of the 2008 CTPs but the bug is there in my version of SQL 2005. Here is a POC:
DBCC USEROPTIONS
GO
SET STATISTICS XML ON
go
SELECT COUNT(*) + 1
FROM master.sys.objects
go
SET STATISTICS XML OFF
go
You can check the results of DBCC USEROPTIONS against the attributes in the StatementSetOptions element and see the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 10:24 am
opc.three (6/14/2011)
New info:The StatementSetOptions in the XML are apparently wrong per this bug report:
The bug report is for one of the 2008 CTPs but the bug is there in my version of SQL 2005. Here is a POC:
DBCC USEROPTIONSGO
SET STATISTICS XML ON
go
SELECT COUNT(*) + 1
FROM master.sys.objects
go
SET STATISTICS XML OFF
go
You can check the results of DBCC USEROPTIONS against the attributes in the StatementSetOptions element and see the issue.
Oooh... didn't know about this one. Good catch.
OK. Then the issue still comes back around to what the heck is changing. Well, for what it's worth, statement 7 in one was cheap & quick and statement 7 in the other was painful & slow. Same switch for statement 8. Not sure if that's where I'd focus, but that's the first thing I saw.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2011 at 11:35 am
I asked the developer to make the following changes so far:
> Rewrite UDFs used in SELECT column list that were using CURSORs to generate a comma-delimited list to use the set-based approach where you set a variable to a comma plus itself in a SELECT column list. Removing the CURSORs returned a lot of CPU cycles to the server. I gave two options here but while the "FOR XML,TYPE" method would have improved the situation more than the UDF rewrite it was too scary for her 🙂
> Convert all table variables to concrete temp tables.
> Declare NUMERIC types with an explicit Precision and Scale. The columns modeled were mostly NUMERIC(10,0) but the variables and temp table columns were being declared as simply NUMERIC which amounts to a NUMERIC(18,0).
Previously the proc took 4m 35s with ANSI_WARNINGS ON and 1m with ANSI_WARNINGS OFF.
After the above changes the proc now runs in 35s with ANSI_WARNINGS ON or OFF. I have not been able to isolate the reason and am not sure I'll be able to.
The plan now is to work through each of the queries and tune them individually either through code changes or by modifying the indexing in the database. My goal is to get the proc running in under 10s.
Mystery unsolved but we're running with ANSI_WARNINGS ON now and performance goals are within sight.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 1:53 pm
Grant Fritchey (6/14/2011)
OK. Then the issue still comes back around to what the heck is changing. Well, for what it's worth, statement 7 in one was cheap & quick and statement 7 in the other was painful & slow. Same switch for statement 8. Not sure if that's where I'd focus, but that's the first thing I saw.
Looking more closely it appears that Query 7 has major differences between the files. The Query 8 execution plans look identical but the weights are different within their respective batches (acceptable) and the missing index recommendations are different (any ideas what causes that? stats diffs?).
Queries 14, 16 and 19 have the next largest relative costs in their batches but the plans are identical between the files.
Query 7 looks to be a good candidate for explaining the difference in performance. Not knowing the run times however, and only looking at the execution plans, I would venture a guess (keep earlier disclaimer in mind) that the plan chosen when ANSI_WARNINGS was ON is more efficient than when it was OFF. Any comment on the quality of the two plans? Any idea why SQL Server would have chosen such vastly different plans?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 2:00 pm
Its not ANSI_WARNINGS , im guessing you are testing with SET STATISTICS TIME ON , that is your difference
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/11/24/the-observer-effect-in-action.aspx
June 14, 2011 at 2:07 pm
Dave Ballantyne (6/14/2011)
Its not ANSI_WARNINGS , im guessing you are testing with SET STATISTICS TIME ON , that is your differencehttp://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/11/24/the-observer-effect-in-action.aspx
Thanks for the info but STATISTICS TIME was OFF during these runs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply