May 22, 2013 at 6:39 am
We have vendor software that uses a lot of resources on our production server. As part of analysis to improve performance, I've noticed that there exist multiple entries in the plan cache that seem identical, with different use counts and plan handles. The SQL text is too long to post here, but there are no hard-coded literal values, extra spaces, different SET options, etc. that would cause different plans to be generated. Most have a parameter value substituted into an EXEC statement, such as EXEC('SELECT x,y,z FROM sometable WHERE cola = ' + @parm1 + ' ORDER BY x,y'). I'm under the impression that using parameters or local variables in this way would NOT cause a different plan to be generated.
Any ideas are welcome. Thanks.
~ Jeff
May 22, 2013 at 6:43 am
Those aren't parameters, they're being concatenated into th strong hence are essentially literal values once the concatenation is done. Concatenated into the string as they are they'll result in multiple different ad-hoc SQL statements and hence multiple plans.
Parameters are like
SELECT x,y,z FROM sometable WHERE cola = @parm1 ORDER BY x,y
That will have a single plan.
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
May 22, 2013 at 6:50 am
jhager,
If the EXEC are calling inside a Stored Procedure then this may be because of Paramete r Sniffing, alter the stored procedure with 'WITH RECOMPILE' and try once.
May 22, 2013 at 6:52 am
Thanks for replying; more info to add to my growing (yet still horribly inadequate) knowledge base.
Upon further digging, I've also come across a large number like this:
CREATE PROCEDURE nl_storage_items_update_items
@wstrListName nvarchar(256)
AS SET NOCOUNT ON
DECLARE @nListId INT;
exec nl_storage_add
@wstrListName = @wstrListName,
@idResult = @nListId OUTPUT;
IF( @@ERROR = 0 AND @nListId IS NOT NULL)
BEGIN
BEGIN TRANSACTION nl_storage_items_update_items
UPDATE #nl_storage_items_upd
SET #nl_storage_items_upd.bNew = 0
FROM #nl_storage_items_upd
INNER JOIN nl_storage_items WITH (TABLOCKX, HOLDLOCK)
ON nl_storage_items.binItemId = #nl_storage_items_upd.binItemId
AND nl_storage_items.nHostId = #nl_storage_items_upd.nHostId
WHERE nl_storage_items.nListId = @nListId
if(@@ERROR = 0)
UPDATE nl_storage_items
SET
nl_storage_items.lItemHash = #nl_storage_items_upd.lItemHash,
nl_storage_items.wstrItemId = #nl_storage_items_upd.wstrItemId
FROM #nl_storage_items_upd INNER JOIN nl_storage_items
ON #nl_storage_items_upd.binItemId = nl_storage_items.binItemId
AND nl_storage_items.nHostId = #nl_storage_items_upd.nHostId
WHERE #nl_storage_items_upd.bNew = 0 AND nl_storage_items.nListId = @nListId
if(@@ERROR = 0)
DELETE FROM #nl_storage_items_upd
WHERE NOT EXISTS( SELECT * FROM Hosts WITH(UPDLOCK) WHERE #nl_storage_items_upd.nHostId = Hosts.nId );
if(@@ERROR = 0)
INSERT INTOnl_storage_items
(
nListId,
nHostId,
binItemId,
wstrItemId,
lItemHash
)
SELECT
@nListId,
#nl_storage_items_upd.nHostId,
#nl_storage_items_upd.binItemId,
#nl_storage_items_upd.wstrItemId,
#nl_storage_items_upd.lItemHash
FROM #nl_storage_items_upd
WHERE #nl_storage_items_upd.bNew = 1;
if(@@ERROR = 0)
BEGIN
COMMIT TRANSACTION nl_storage_items_update_items;
END
else
ROLLBACK TRANSACTION nl_storage_items_update_items;
INSERT INTO#nl_storage_items_hosts(nHostId)
SELECT DISTINCT nHostId FROM #nl_storage_items_upd
exec [dbo].[nl_storage_hosts_hash_mark_invalid] @nListId;
END
There are (currently) 54 single-use plans with this exact text. Does this match the same pattern as you responded to? If so, I'm not seeing it.
~ Jeff
May 22, 2013 at 6:53 am
Thanks for replying...unfortunately, this is vendor-supplied software, so we can only affect performance by changing db options/parameters, indexing, etc.
~ Jeff
May 22, 2013 at 7:11 am
jhager (5/22/2013)
Thanks for replying; more info to add to my growing (yet still horribly inadequate) knowledge base.Upon further digging, I've also come across a large number like this:
...
There are (currently) 54 single-use plans with this exact text. Does this match the same pattern as you responded to? If so, I'm not seeing it.
That looks like the plan is for the creation of the stored procedure, not the execution. It looks as if your application is creating stored procedures on the fly, since the procedure definition references temp tables that aren't created there.
With regard to your original post, you could probably improve plan caching by using sp_executesql instead of EXEC. Since it's a vendor-supplied application, your only option is to suggest it to them. Good luck with that - I've run up against similar brick walls myself!
John
May 22, 2013 at 7:34 am
Thanks for replying, I also didn't think about temp tables. Always great (and useful) replies on this site...my top "must read".
~ Jeff
May 22, 2013 at 8:02 am
shettybhas (5/22/2013)
If the EXEC are calling inside a Stored Procedure then this may be because of Paramete r Sniffing, alter the stored procedure with 'WITH RECOMPILE' and try once.
Parameter sniffing won't create multiple plans.
Parameter sniffing is when a SINGLE plan is generated with an uncommon parameter value is then reused by a call with a different parameter value that results in a very different number of rows affected.
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
May 22, 2013 at 8:08 am
John Mitchell-245523 (5/22/2013)
jhager (5/22/2013)
Thanks for replying; more info to add to my growing (yet still horribly inadequate) knowledge base.Upon further digging, I've also come across a large number like this:
...
There are (currently) 54 single-use plans with this exact text. Does this match the same pattern as you responded to? If so, I'm not seeing it.
That looks like the plan is for the creation of the stored procedure, not the execution. It looks as if your application is creating stored procedures on the fly, since the procedure definition references temp tables that aren't created there.
Procedure creation doesn't get plans. Since there's only one way for SQL to execute a CREATE PROCEDURE, DDL does not go through the optimiser, does not get plans and does not get anything added to the plan cache.
That's the plan for the execution of the procedure. Any time you use sys.dm_exec_sql_text and pass it a sql_handle or plan_handle, you get the entire procedure's definition back if that handle referred to a procedure. It's done this way because getting back EXEC SomeOldProcedure isn't at all useful in seeing what statement is running and because you can use the stmt_start and stmt_end to get the exact statement within the procedure that's running.
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
May 22, 2013 at 8:13 am
GilaMonster (5/22/2013)
shettybhas (5/22/2013)
If the EXEC are calling inside a Stored Procedure then this may be because of Paramete r Sniffing, alter the stored procedure with 'WITH RECOMPILE' and try once.Parameter sniffing won't create multiple plans.
Parameter sniffing is when a SINGLE plan is generated with an uncommon parameter value is then reused by a call with a different parameter value that results in a very different number of rows affected.
Thanks... Gail... That's something corrected my assumptions today...
May 22, 2013 at 8:14 am
jhager (5/22/2013)
There are (currently) 54 single-use plans with this exact text.
There's a lot of things that could be going on here and it's hard to tell what without a lot more info.
Firstly, sys.dm_exec_query_stats (and sys.dm_exec_procedure_stats) have one row per statement within the procedure, not one row per plan in cache.
You could be seeing statement-level recompiles from the temp table usage.
You could have procedures with different set options (those will result in entire different plans)
Can you post what query it is you're running that shows you multiple plans in cache?
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply