Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored procedure recompile is very slow Expand / Collapse
Author
Message
Posted Sunday, March 8, 2009 7:47 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:32 AM
Points: 23, Visits: 118
we have a stored procedure that is periodically causing performance issues. When the procedure is flagged for recompile, it takes 3 - 4 minutes to compile. in Profiler I see a pattern with the EventClass and EventSubClass of

SP:CacheHit --- 1 - Execution Context Hit
SP:CacheHite --- 2 - Compplan Hit
SP:Recompile --- 1 - Schema changed

Once this Compplan Hit sub class happens, the recompile takes forever. I also see CPU utilization on the server hit 40 - 50 while recompiling. This server only has one processor but it is hyperthreaded. Do you think I'm suffering from parallelism? If so, I know I can add an MAXDOP hint to the query but does that help serialize the recompile or will I need to set MAXDOP at the server level? And I should mention that the procedure is join heavy in a big way. There's also some UDFs thrown in for fun.
Post #671226
Posted Monday, March 9, 2009 6:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 15,739, Visits: 28,147
We once had a query that was joining 80 tables. It took about two minutes to recompile and, because of the nature of the data, it was recompiling about every 12-15 minutes. During the recompile, all the processes that needed that query were hung. The only way to reduce recompile time is to reduce the complexity of the procedure.

According to our local systems architect, hyper-threading is not something you should be using on a SQL Server instance. I don't know if that's your problem or not, but I thought I'd pass the recommendation along.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #671378
Posted Monday, March 9, 2009 6:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 29, 2014 9:29 AM
Points: 214, Visits: 647
Try it in some other environments and see what it does (dev, stage, etc.). He's right - Hyperthreading is a no-no for SQL Server, although I'm not sure that simply compiling a stored procedure will be affected by this...it certainly might. I don't think that a Hyperthreaded box has been offered by, say, Dell, for a couple of years now. At any rate, see what it does elsewhere to start whittling away at the problem; doing this might tell you if it's the environment.




Lee Everest

Post #671395
Posted Monday, March 9, 2009 7:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 31, 2014 10:37 PM
Points: 85, Visits: 625
michael ham (3/8/2009)
we have a stored procedure that is periodically causing performance issues. When the procedure is flagged for recompile, it takes 3 - 4 minutes to compile. in Profiler I see a pattern with the EventClass and EventSubClass of

SP:CacheHit --- 1 - Execution Context Hit
SP:CacheHite --- 2 - Compplan Hit
SP:Recompile --- 1 - Schema changed

Once this Compplan Hit sub class happens, the recompile takes forever. I also see CPU utilization on the server hit 40 - 50 while recompiling. This server only has one processor but it is hyperthreaded. Do you think I'm suffering from parallelism? If so, I know I can add an MAXDOP hint to the query but does that help serialize the recompile or will I need to set MAXDOP at the server level? And I should mention that the procedure is join heavy in a big way. There's also some UDFs thrown in for fun.


It's recompiling because the schema has changed. This may be due to declaring temp tables partway through the procedure. At least, that's where I've run into this issue. See if moving all temp table declarations to the beginning of the proc stops the recompilations. At least then you'll only have to take the compilation hit once.
Post #671478
Posted Monday, March 9, 2009 7:54 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:32 AM
Points: 23, Visits: 118
80 tables! Wow. I've known about the hyper-threading issues for a while, but never had an installation of this product really suffer from it. I think that's the next route I'm going to take. Thanks for the input.
Post #671503
Posted Monday, March 9, 2009 7:59 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:32 AM
Points: 23, Visits: 118
Well I don't get to pick how this product is deployed, I've got 3000 installations of it worldwide. IBM is still selling smaller servers with these processors. I've been going back through code revisions in source (this is not the first revision of this procedure in production) and there have been some undocumented changes. I'm going to start picking those apart and see what's causing this and work from there. Thanks for the input.
Post #671510
Posted Monday, March 9, 2009 8:07 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:32 AM
Points: 23, Visits: 118
Dean Cochrane (3/9/2009)

It's recompiling because the schema has changed. This may be due to declaring temp tables partway through the procedure. At least, that's where I've run into this issue. See if moving all temp table declarations to the beginning of the proc stops the recompilations. At least then you'll only have to take the compilation hit once.


Well I know it will recompile based on the table activity. I can't understand the 3 minute recompile though. I did catch other recompile events in my trace but only when this sequence above is encountered do we have problems. We are using a temp table and I think we can safely move to a table variable. So I might try that and see if it helps. Thanks.
Post #671517
Posted Monday, March 9, 2009 8:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 715, Visits: 2,706
michael ham (3/9/2009)
Dean Cochrane (3/9/2009)

It's recompiling because the schema has changed. This may be due to declaring temp tables partway through the procedure. At least, that's where I've run into this issue. See if moving all temp table declarations to the beginning of the proc stops the recompilations. At least then you'll only have to take the compilation hit once.


Well I know it will recompile based on the table activity. I can't understand the 3 minute recompile though. I did catch other recompile events in my trace but only when this sequence above is encountered do we have problems. We are using a temp table and I think we can safely move to a table variable. So I might try that and see if it helps. Thanks.


Be careful with table variables, there is some limitations as you cannot index them and it does not keep statistics : http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

I don't know if your temp table table was indexed at all, but it could lead to severe performance issues.

I thought table variables were memory only, but recently found this article, and it proved me wrong. Just saying it is good to know.


Cheers,

J-F
Post #671536
Posted Monday, March 9, 2009 8:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 15,739, Visits: 28,147
You do need to be very careful about using table variables. But, if you're dealing with very small data sets that would be scanned anyway, regardless of statistics, then table variables can help you avoid the recompiles associated with temp tables. But you really do need to have very small sets of data. By that I mean less 100 rows at the extreme top-end.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #671557
Posted Monday, March 9, 2009 8:37 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:32 AM
Points: 23, Visits: 118
It's likely that we'd stay under a 100 rows 95% of the time. I've just been doing some side by side tests of a previous revision of the procedure and do not see the same recompile performance problem. the joins have changed quite a bit. I'm most curious now and will post results of my findings.
Post #671564
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse