November 18, 2011 at 3:49 pm
Ninja's_RGR'us (11/18/2011)
Evil Kraig F (11/18/2011)
bridgt01 (11/18/2011)
I'm glad I can say I didn't write it. Yes, it's a mess. The function returns approximately 30,000 records. But why is it that after I run the database engine tuning advisor the stored procedure that calls this function returns in less than 2 minutes. If I recompile the function or recycle SQL Server it seems to stall and doesn't do anything? I've checked for hypothetical indexes and statistics and cleared out the trash that was left by the DETA but the problem still remains.Sounds like a bad plan is getting into the cache. Might be parameter sniffing, could be a few other things. Doing a full update on the statistics would probably help too.
Might be BAD parameter sniffing (slight difference).
Update stats could be the solution only if the data in those tables is super volatile. We'd see signs of that in the plan.
Or hasn't been done in a while and hasn't hit the necessary change levels to fire up an automatic update. Does no harm if he does it and might help, figured why not. Let it run over the weekend.
As to bad parameter sniffing, well, isn't that the only time we care about it? 😉
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
November 18, 2011 at 3:56 pm
Evil Kraig F (11/18/2011)
Ninja's_RGR'us (11/18/2011)
Evil Kraig F (11/18/2011)
bridgt01 (11/18/2011)
I'm glad I can say I didn't write it. Yes, it's a mess. The function returns approximately 30,000 records. But why is it that after I run the database engine tuning advisor the stored procedure that calls this function returns in less than 2 minutes. If I recompile the function or recycle SQL Server it seems to stall and doesn't do anything? I've checked for hypothetical indexes and statistics and cleared out the trash that was left by the DETA but the problem still remains.Sounds like a bad plan is getting into the cache. Might be parameter sniffing, could be a few other things. Doing a full update on the statistics would probably help too.
Might be BAD parameter sniffing (slight difference).
Update stats could be the solution only if the data in those tables is super volatile. We'd see signs of that in the plan.
Or hasn't been done in a while and hasn't hit the necessary change levels to fire up an automatic update. Does no harm if he does it and might help, figured why not. Let it run over the weekend.
As to bad parameter sniffing, well, isn't that the only time we care about it? 😉
No I'm mostly greatful for all the times it saves me the useless recompiles!
November 18, 2011 at 4:02 pm
So you have a WHERE clause against a 30,000 row multi-statement table valued function... To quote a famous man "Well there's your problem.[/url]"
Seriously though, you may have any number of other issues in there (and functions on columns in WHERE clauses are one of them), but that UDF, that's a non-starter and there is no way to tune it to run better. A UDF like that has no statistics. So SQL Server assumes that you have a single row. Instead of one row, you have 30,000. The execution plan for that is going to stink, no matter what you do. I wouldn't waste time trying to tune this. I'd step back and rearchitect. It's your best bet.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 20, 2011 at 7:16 pm
Grant, I didn't write this code but I'm not seeing a WHERE clause against the multi-statement table valued function.
As for updating stats, stats have been updated and indexes have been optimized.
November 21, 2011 at 3:57 am
bridgt01 (11/20/2011)
Grant, I didn't write this code but I'm not seeing a WHERE clause against the multi-statement table valued function.As for updating stats, stats have been updated and indexes have been optimized.
It's not being filtered in that place where it's called? Regardless, that's where I'd focus all the efforts.
By the way, sorry if this feels like an attack. It's not. I know exactly how you feel with a very painful piece of software that you didn't write but are expected to fix. We've all been there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 21, 2011 at 11:09 am
Grant Fritchey (11/21/2011)
By the way, sorry if this feels like an attack. It's not. I know exactly how you feel with a very painful piece of software that you didn't write but are expected to fix. We've all been there.
Yup, and we'd like to help, but that's just too much volume to do disconnected from the source... and paycheck. 😀
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
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply