September 1, 2016 at 2:10 pm
hi Team,
I am troubleshooting cpu usage issue of my server.
When i tracked most cpu usage queries, in that most of the procedures are executing continuously but those are recompiling daily every one hour or some other time.
i have verified it by using the creation_time in sys.dm_exec_query_stats
The creation time gets changed frequently.
is there any way to find why these procedures are recompiling ?...
There are no recompile option was used for these procedures
September 1, 2016 at 2:59 pm
dastagiri16 (9/1/2016)
hi Team,I am troubleshooting cpu usage issue of my server.
When i tracked most cpu usage queries, in that most of the procedures are executing continuously but those are recompiling daily every one hour or some other time.
i have verified it by using the creation_time in sys.dm_exec_query_stats
The creation time gets changed frequently.
is there any way to find why these procedures are recompiling ?...
There are no recompile option was used for these procedures
If you trace SQL:StmtRecompile Event Class and include the EventSubClass column, it has an integer value indicating the reason for the recompile. You can find those values and the corresponding reasons in the documentation for SQL:StmtRecompile Event Class
https://msdn.microsoft.com/en-us/library/ms179294(v=SQL.100).aspx
Sue
September 1, 2016 at 4:02 pm
I tried it earlier but did not find event subclass column in profiler standsred edition
September 1, 2016 at 4:38 pm
It's there on 2008 - it doesn't matter if you are on Standard or Enterprise.
If you are using Profiler to set it up, after you connect and then the Trace Properties screen appears, click the Events selection tab. On the bottom Right Corner click on the check boxes for Show All Events and Show All Columns.
If you just select Show All Events, the column will not show up. You need to select Show All Columns.
Sue
September 6, 2016 at 4:06 am
If you used extended events, you don't have to track down columns. The event package will include everything. It does make things easier. However, on 2008, you'll have to consume the XML output.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2016 at 2:32 am
There's an SSMS add-in for 2008 https://extendedeventmanager.codeplex.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply