October 14, 2009 at 8:19 am
Hi...
I have two functions that were taking 10-27 seconds to run. They were basically each getting the row count from a table and returning this count. Running the SQL within the functions returned in less then a second, but running it from the function took 10 seconds for one and 27 seconds for another.
In testing the functions I opened each and just saved it back to the database (Alter Function) and now each run in under a second.
Can someone give me some info on why this happened? It's good that it sped up the results, but I'm not sure why it happened. Each night I run DBCC DBREINDEX(@MyTable, '', 80) on each table in our database, but do I also need to update any statistics or do any reindexing on functions or views as well? We don't use Full Text Search, so just curious. Now I'm curious to see what else I could speed up if I can figure out how to do this on all my other functions.
Thanks and take care --
Sam
October 14, 2009 at 8:28 am
Overwriting it probably just forced a rebuild on the execution plan. That's probably the only difference. I'd have to see the function and any related tables to get more specific than that, but that's almost certainly all there is to it.
That means it could slow down again if anything forces another rebuild. Lots of things can do that. Might stay fast, might slow down, no certainty on that.
Why a function to return a row count? Why not just an inline select?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2009 at 8:58 am
GSquared (10/14/2009)
Overwriting it probably just forced a rebuild on the execution plan. That's probably the only difference. I'd have to see the function and any related tables to get more specific than that, but that's almost certainly all there is to it.That means it could slow down again if anything forces another rebuild. Lots of things can do that. Might stay fast, might slow down, no certainty on that.
Why a function to return a row count? Why not just an inline select?
GSquared,
Thanks for the reply...
Are the execution plans cached when the function is built? And if so is there anyway to add a nightly process to verify the execution plans are updated? Also when you say 'rebuild' how do you mean? I do reindex all tables each night, but I don't do anything with the functions or procedures.
As for why we're doing it this way, what's happening is in the app we're developing it has a page that lists everything the employee has to do, so for example if new orders have come in for them or someone is waiting on information, this page shows this. It works through a scalar function to get a count of records for each scenario and a table function to return the dataset back for each scenario. So for example if they have 10 widgets needing review a Scalar Function has just enough logic to return 10, but clicking on that in the application runs the table function to return a full dataset with much more info.
With the table function joining to more tables then is needed for just a count, separating the logic as we're doing makes the initial screen of counts load faster.
If there's a better alternative to doing this I'm always game for new ideas... but this logic seems to work for our needs. The problem with the function slowdown is really the first burp we've ran into. Something to note though is this is a new application we're developing, so the functions may have been built a while back with the table indexes changing since the function was built. This is why I asked if the function caches the execution plan.
Thanks again and take care,
Sam
October 15, 2009 at 7:11 am
The execution plan will rebuild for a number of reasons, and one of them can certainly be when the indexes are rebuilt, since that updates table stats.
The plans aren't built when you create the function, they are built the first time it's run, or the first time it's run after things like rebuilding indexes. (That's an over-simplification, in a way, but it should give you the idea of what's going on.)
If you can provide the table definition, including indexes, and the code for the function, I can probably suggest ways to make it more efficient. The table definition would be the create script. Do you know how to get one of those?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 4:20 pm
Hi,
Actually it seems rebuilding the execution plan fixes the problem, so I'm now trying to find out what's causing the problem to begin with because as of today it's doing the same thing again. Yesterday I ran DBCC FREEPROCCACHE to clear the cache the scalar function came back instantly, but then today it was back to about 30 seconds through the function even though the SQL within comes back in under a second.
I do have these running at night to rebuild the indexes for each table:
EXEC sp_createstats;
EXEC sp_updatestats;
... but I can run these manually now and the function continues to run fine afterwords. I'm not sure what the catalyst is that's throwing this off. GSquared, I'm working on a script to send your way with more details on the code so it's not posted on the forum (company policy), but here's more info on what I've found out incase you or anyone else sees a pattern or something I'm missing.
Today while one scalar function was back to taking 30 seconds, I ran this to see what execution plans had the query listed:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%ufnCustomerList%';
It came back with 10 entries... 5 were ObjType Proc, 3 were ObjType Adhoc, and 2 were ObjType Prepared. Two of the Proc entries had the scalar function text in the Text field with one in our Dev1 database and one in Dev2 (both have identical schemas). I've been running all this in Dev1 but when I removed the entry from the Dev2 database using DBCC FREEPROCCACHE(*plan_handle was here*) the scalar function in Dev1 started running in under a second. I'm using DB_Name(dbid) to determine the database from the query above.
I've been a DBA for years, but unfortunately I've never had any training on how the execution plans work. GSquared, you sent me some great resources in PM I'll review, but for now I'm at a loss on what's causing this.
Thanks again for any insight, and is there anything else I can check?
Sam
October 16, 2009 at 11:04 am
Take a look at Gail's blog entry on parameter sniffing (http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/). That's a likely cause of the issue.
All of the things you're doing, rebuilding stats, clearing the cache, rebuilding indexes, all cause execution plans to reset.
If the next time they are run, the parameters result in a plan that will take a long time, then they'll be stuck with that plan.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 6:34 pm
Hey Sam,
I have to say it, so I'll get it out of the way up front: doing data access from a function is rarely optimal. My preference would be to use stored procedures, both for the row count and the detail records. A procedure that returns the row count in an output parameter will be just as fast as the function, and will give you more tuning options. Specifically, you could add an OPTION (RECOMPILE) within the procedure to help ensure that a good plan is generated.
On the face of it, you also seem to have callers of the function with differing SET options. Do another CROSS APPLY to sys.dm_exec_plan_attributes to check this. Also use sys.dm_exec_query_plan to check the differences in the cached plans - the reason for the slow down should be obvious from that. Usually it will be a poor choice of join strategy.
I see you are using 2008, so you might also like check the plans to see if something like a filtered index might help.
I'm sorry my answer isn't more specific, but that's just because you are constrained in how much detail you can give out.
Paul
October 19, 2009 at 10:26 am
Hi GSquared and Paul,
Thanks again for the great info, and it does sound like it's a blunder in how SQL is defining the execution plan. I could continue pursuing this from the angle of using Functions, but I'm working on refactoring this code to use stored procedures instead because that would've been my preference from the get go since this piece was initially coded by one of our other developers.
Thanks again for the great info, and as usual from these forums I've learned a great deal 🙂 I'll post back once I have the code reworked, but I assume once it's moved into a stored procedure that should fix the problem.
Thanks --
Sam
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply