February 9, 2011 at 9:38 am
Sorry for bombarding with so many quesitons;-).I have a proc which runs about 200 times in an hour. I see two different exec plan for the same proc inside cache.There are about five statements inside the proc. The second statement (select distinct) shows 95% of cost. Now i have two questions based on the given below data:
i) The create date is same for the four but if you notice it is different for the second one in each. Does that mean second statement wasnt used until (2011-02-09 08:24:57.303)?
ii) I see two exec plans and both look pretty much same. Do i need to worry about anything?
CreateDate Late execution_time
Delete Statement - 2011-02-06 11:23:22.3102011-02-09 11:21:56.340
Select Distinct 2011-02-09 08:24:57.3032011-02-09 11:21:56.340
Insert Into 2011-02-06 11:23:22.3102011-02-09 11:21:54.763
Select 2011-02-06 11:23:22.3072011-02-09 11:21:54.347
Insert into 2011-02-06 11:23:22.3072011-02-09 11:21:54.347
Delete Statement - 2011-02-06 08:03:18.6172011-02-08 22:40:32.107
Select Distinct 2011-02-08 22:40:29.0902011-02-08 22:40:32.107
Insert Into 2011-02-06 08:03:18.6172011-02-08 22:40:31.117
Select 2011-02-06 08:03:18.6132011-02-08 22:40:30.717
Insert into 2011-02-06 08:03:18.6132011-02-08 22:40:30.717
February 9, 2011 at 12:54 pm
Not seeing the code, I'm guessing, but I'll bet one or more of your queries are dynamic using EXEC or sp_executeSQL. That would explain why you have two apparent plans for the same query.
"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
February 9, 2011 at 1:15 pm
Grant Fritchey (2/9/2011)
Not seeing the code, I'm guessing, but I'll bet one or more of your queries are dynamic using EXEC or sp_executeSQL. That would explain why you have two apparent plans for the same query.
Thanks but that is just part of my question. How about the first one. The second statement in the query recompiles the same plan?
P.S: I am getting your book on execution plans !!!.
February 10, 2011 at 1:13 pm
Grant what do you mean by "your queries are dynamic using EXEC "? When a task is selected from the application the sql behind the scene's is executed in " exec my_proc @name='Test'". Is this what you meant?
February 10, 2011 at 6:02 pm
I think it's possible that might lead to multiple plans (sort of, actually you're likely to see multiple plans for the different strings, but a single plan for the proc), but I meant that you had some string built out by code an executed using EXEC 'myselectstring'
"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
February 11, 2011 at 6:33 am
To answer the question (Is there something I should worry about)...I would say no...mostly. SQL Server will not keep multiple plans in the cache. This means that you must have some kind of dynamic SQL or something that forces a recompile when that section of the procedure is reached. Without the actual plan or samples of the code, it is very difficult to say what is happening.
I do agree, it is likely you have dynamic sql (or some DDL statement). The dynamic SQL can cause recompilation and the DDL will always cause recompilation.
Example Dynamic SQL:
executeSQL('update schema.table set field1 = ' + @Parameter1 + ' where field2 = ' + @Parameter2)
Example DDL:
Create Table #ProcessTable
(
Field1 as int,
Field2 as varchar(50)
)
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
February 11, 2011 at 10:11 am
Thanks for your replies. Some more info on the issue
i) there are 5 different DML( NO DDL) going on with in the proc which i am working on
ii) each DML does work on a dynamic table
iii) I use the mentioned below query :
SELECT a.execution_count ,
OBJECT_NAME(objectid) Name,
query_text = SUBSTRING(
b.text,
a.statement_start_offset/2,
( CASE WHEN a.statement_end_offset = -1
THEN len(convert(nvarchar(max), b.text)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset)/2
) ,
b.dbid ,
dbname = db_name(b.dbid) ,
b.objectid ,
a.creation_time,
a.last_execution_time,
a.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) = 'myproc'
ORDER BY a.last_execution_time DESC
i see five records (i think because there are 5 different DML i see five different records). The creation time is same for 4 except one but execution time is same for all five with the same plan number. So the one which has different creation_time is being re-compiled for the same plan. Can a sql recompile for the same plan?
February 11, 2011 at 10:21 am
SQL can recompile specific lines within a stored procedure, which is what I think you are experiencing here. I would not worry about it unless there is a performance problem related to it.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
February 11, 2011 at 12:54 pm
ChazMan (2/11/2011)
SQL can recompile specific lines within a stored procedure, which is what I think you are experiencing here. I would not worry about it unless there is a performance problem related to it.
Ok, however the line which is being re-compiled is showing the most cost ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply