procedure runtime exploded after installing SP4
-
We have this fine procedure here, that builds a dependency list of the tables contained in the database. It works with dynamic SQL statements, trying to find tables from sysobjects, referenced in sysreferences, and builds a temptable showing the different levels of referecing.
Under SP3 this procedure took only bits of seconds, but in SP4 it takes up to 10 minutes to complete. The problem seems to be that, after having retrieved all tables with a zero reference level (those not used as primary table in any foreign key), the next levels join over the temptable to exclude the links from previous referencing levels, and this now always takes multiple minutes to complete, despite the fact we're only talking about a few hundred tables (ie records in the #table).
Anyone any ideas
? -
This was removed by the editor as SPAM
-
Hi
Not really an expert on this sort of thing, but since no-one has responded and everyone else but the poor DBA is at the Xmas do...
An upgrade to SP4 probably changed the system tables you are querying, so it might be worth trying recompiling your stored procedure using sp_recompile procname
BOL says
"The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries."
However, it also notes that SPs are recompiled when it is 'advantageous to do so'.
Not sure how this would work if your SP is all dynamic SQL?
David
If it ain't broke, don't fix it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply