November 3, 2015 at 12:49 pm
I am using SQL Server 2008, Enterprise Edition
I have a piece of code that, when filled in with all the proper pieces and ran as static SQL from within SSMS, it runs in a sub-second timeframe.
I have that same piece of code, in a stored procedure, nested within a while loop to loop through a set of days as dynamic SQL. When I execute that stored procedure, one execution of a single day takes approximately 2.5 minutes.
To analyze why I appear to be getting such a worse plan for my dynamic SQL I am attempting to retrieve the plan from cache by executing the following code:
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE TEXT LIKE N'%@currentYear AS DataYear%'
ORDER BY TEXT
*"@currentYear AS DataYear" is indeed within the dynamic SQL I am creating
Only three records come back:
1, the stored procedure where I build the dynamic SQL in the loop
2, the static SQL which runs sub-second
3, my query above seeking out the plans
I do not see a plan cached for my long-running dynamic SQL.
I am executing the dynamic SQL with the following code:
EXEC [sys].[sp_executesql]
@sqlQueryParams,
@currentYear = @currentYear
Any help you can provide would be greatly appreciated, thank you!
November 3, 2015 at 3:37 pm
I was able to get the bad plan out of the plan cache and found that for some reason my dynamic SQL is not respecting my partition and thus trying to join my main table against 38 million rows, rather than the 6000 I actually need. Still trying to figure out why it's ignoring that, if anyone has any suggestions, I would appreciate the input.
November 3, 2015 at 5:54 pm
Well, not much we can do about your query since we can't see it or the DDL for the tables it is running against.
Please take the time to read the first two articles I have referenced below in my signature block. It will show you what and how to post the information we need to actually help you with your problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply