April 27, 2009 at 1:08 pm
I'm not new to sprocs, but I'll (sheepishly) admit that I've never really spent much time thinking about how SQL optimizes them after they've been created.
First question (an easy one): Do existing sprocs get marked for recomiplation automatically after they have been ALTERED? It seems like they should, but I don't see it explicitly stated anywhere or in BOL (maybe the answer is just too obvious).
Second: Some of the sprocs I've been asked to maintain access tables that are refreshed daily with new data (a lot of data...well, 100k medium sized records or so). The tables aren't indexed. I was thinking that indexing the tables might help improve performance, but I'm wondering if the gain will be offset by the cost incurred by the table having to essentially rebuild indexes with each refresh. Not only that, but I think the sprocs that access these tables will get recompiled each time they are run since there were a lot of changes to the indexes and data in these tables?
Any advice?
April 27, 2009 at 1:23 pm
Mike Baria (4/27/2009)
First question (an easy one): Do existing sprocs get marked for recomiplation automatically after they have been ALTERED? It seems like they should, but I don't see it explicitly stated anywhere or in BOL (maybe the answer is just too obvious).
Yup. Altering a procedure invalidates that procedure's cached execution plan. The next time it runs a new plan will be compiled. Likewise, altering any object that the procedure depends upon (table, view) invalidates the cached plan
Second: Some of the sprocs I've been asked to maintain access tables that are refreshed daily with new data (a lot of data...well, 100k medium sized records or so). The tables aren't indexed. I was thinking that indexing the tables might help improve performance
Very likely
, but I'm wondering if the gain will be offset by the cost incurred by the table having to essentially rebuild indexes with each refresh.
What's often done is the indexes dropped right before the refresh, the data reloaded and then the indexes recreated. It may sound expensive, but if you're reading the table more than once between the daily reloads you'll likely benefit from good indexes
Not only that, but I think the sprocs that access these tables will get recompiled each time they are run since there were a lot of changes to the indexes and data in these tables?
Recompiles don't make indexes useless.
Are the procs in question run just once a day after the data reload or are they run repeatedly during the day? How often does the data that they depend on change during the day?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 2:00 pm
Thanks for the fast reply!
The sprocs are run only once during the refresh job, but some of the tables are read for other reasons throught the day (auditing, reporting services, etc.). In those cases, I'm sure an index is the way to go.
I wasn't really thinking that indexes would be useless because of the potential sproc recompile, but that the cost to the overall job might outweigh the gain to the individual sprocs that will access the data later in the job...or maybe just offset it. Seems unlikely, but then, if you factor in the cost of a daily sproc recompile with a daily index rebuild - could that push the total cost to the job over the edge? I've never attempted to quantify the cost of a recompile, much less the cost of a recompile vs. a table scan on a non-indexed table. I'd imagine that unless the sproc were crazy long and complex, the recompile wouldn't really hurt too much....as long as the resulting resource locks don't cause problems elsewhere. I have had cases where (re)building indexes takes a very long time. So adding indexing costs to recompile costs....seem like it COULD get pricey (maybe not).
What about nested sprocs/views - i.e. sprocs/views within sprocs? Does manually recompiling a sproc have any affect on the nested sprocs/views? My immediate guess is that there is no effect on the nested sprocs/views (as to whether they'll need to be recompiled or not)....but then again, seems like SQL needs to evaluate the nested sprocs/views somehow if it is going to be able to optimize....and so will need an updated execution plan from each. Or maybe it just uses what ever execution plan info is in cache?
April 27, 2009 at 2:19 pm
Mike Baria (4/27/2009)
I wasn't really thinking that indexes would be useless because of the potential sproc recompile, but that the cost to the overall job might outweigh the gain to the individual sprocs that will access the data later in the job...or maybe just offset it. Seems unlikely, but then, if you factor in the cost of a daily sproc recompile with a daily index rebuild - could that push the total cost to the job over the edge?
Compile times are measured in milliseconds, not minutes.
I've never attempted to quantify the cost of a recompile, much less the cost of a recompile vs. a table scan on a non-indexed table.
Unless you're talking about a 10 row table, there's no comparison. Besides, the complete reload of the table will force a recompile of all queries thta reference it anyway due to updated statistics.
What about nested sprocs/views - i.e. sprocs/views within sprocs? Does manually recompiling a sproc have any affect on the nested sprocs/views?
Nested procedures are unaffected by the recompile of a parent. Their plans are looked up or compiled only when they are run.
Nested views are a completely different story. Since views are just saved select statements, they don't have plans themselves. As part of the parse and bind, the definition of the view replaced the name of the view and the resulting select is then compiled as a unit.
Grant wrote about that recently - http://scarydba.wordpress.com/2009/04/24/unpacking-the-view/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 2:29 pm
Thank you so much for the info, Gail ( I can't bring myself to call you Gila Monster 😉 )!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply