December 31, 2010 at 5:59 am
Hi,
I'm having a table value functions which needs around 10 seconds if it runs with "set ansi_padding off" while it takes up to 10 minutes with "set ansi_padding on".
My first idea was that some of the underlying tables within the function are set to ansi padding off, and therefore SQL Server needs to trim all columns before joining or inserting them into return table.
The first strange thing is: there are no varchar columns, all tables are using nvarchar, so ansi_padding shouldn't have any effect.
The second strange thing is: If I check the column properties using this:
SELECT COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'UsesAnsiTrim')
it returns null, because the column is of nvarchar, which doesn't have that property. So far so good. But if I check the column properties with SSMS for some tables it shows Ansi status false (which doesn't make sense as it should always be true for nvarchar) for some nvarchar columns while it also shows true for some other nvarchar columns.
Any ideas why SSMS is showing that strange behavior?
Best regards and "Guten Rutsch",
Jan
January 3, 2011 at 6:25 am
First thing that comes to mind is that setting the ANSI_PADDING value within the procedure or the function will cause recompiles. So that could affect you. How long does the procedure take to compile? Most of the standard ANSI settings will affect execution plan reuse and recompiles. They can also affect execution plan generation. It's best to have a common, approved set of ANSI settings and use them the same way everywhere, set through the connection, not in code.
By the way, is this a multi-statement table valued function? If so, those are notorious performance problems because they have no statistics, so if you're using that to join on other tables, filtering data within it, or generally working with more than a few dozen rows of data, that alone could be the source of the problem.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply