Slow in the Application, Fast in SSMS. How to force the execution plan generated by SMSS
-
Hi,
This is my second case this week where the same query is fast in SSMS and slow in the application. Essentially, the execution plan generated is not the same for whatever reason. I read this very informative article Slow in the Application, Fast in SSMS? Understanding Performance Mysteries and I understand where the difference can come from.
I was able to fix my first case by creating new statistics, but I can't find what is wrong with my second case and now I would like to force the plan id generated by SSMS to the query id executed by the application. Is it possible ?
The problem I am facing is the query store does not recognize the query executed in SMSS as the same as the application. Nevertheless, I ran both query and checked the case and I can see both queries are the exact same, but SQL creates two rows instead of reusing the cache and incrementing the execution count by 1.
Considering the fact that the query is exact same including the parameters, spaces, cases, what else would else would make SQL generates a different query id ?
Thank you
- Gamleur84 - Monday, March 4, 2019 6:50 PM
It would probably be better to figure out the differences. Do you compare the set options between the two plans?
Sue
- Gamleur84 - Monday, March 4, 2019 6:50 PM
Silly question maybe, but what if you created a stored procedure and used it in both cases? When a query runs, the compiler creates a hash of it, so if the hashes don't match on the two queries, then the compiler treats them as being different. Then it won't reuse the old query, but compile the new one and use it - even if they are very close to the same.
-
Hi,
Yes I use the same set options. I use sp_executesql in SMSS in order to get the same query as the one generate by the application.
DECLARE @P1 int;
DECLARE @P2 int;
DECLARE @P3 bigint;
DECLARE @ExecStr nvarchar(max);SET @ExecStr = N'I PUT THE QUERY HERE'
SET @P1 = 2;
SET @P2 = 1;
SET @P3 = 1;DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@P1 int,@P2 int,@P3 bigint';EXECUTE sp_executesql @ExecStr,@ParmDefinition, @P1 = @P1, @P2 = @P2, @P3 = @P3
-- N'@P1 int OUTPUT', N'@P2 int OUTPUT', N'@P3 bigint';
then I use the following DMVs to find the queries in the cache
SELECT TOP 100
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.plan_handle,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%VENDTRANSOPEN%';I see the exact same query twice, but as with a different plan handle and I am wondering why.
- Sue_H - Monday, March 4, 2019 8:51 PM
The difference must be coming from the code because the query is the same. For example, I see the query executed in the application is executed in a cursor. I am not using sp_cursorfetch in SMSS
-
Certain SQL settings being different can cause a separate execution plan to be required. For example, QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF would definitely cause that. Try explicitly setting all of them in both places and then re-check the plans:
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON; /*set OFF to see row counts, such as for testing or within jobs*/
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET XACT_ABORT ON;Also, not schema-qualifying objects could cause different execution plans to be needed. That is:
FROM table1
rather than
FROM dbo.table1SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
- Gamleur84 - Monday, March 4, 2019 10:31 PM
Using the same sp_executesql doesn't mean you have the same set options. Did you check the set options using sys.dm_exec_plan_attributes?
Sue
-
Open the two plans up in Management Studio 17. You can compare them and it will identify all differences. It's either settings or statistics (if not structure or code) that will lead to differences in the plan. The compare output will tell you what those differences are.
As for plan forcing, there is a way to do this using Query Store, but you have to have both plans available in the database. You can then force the plan of your choice. You can also use plan guides, but it's notoriously difficult to get these to work, however, it's a possibility.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Thanks,
As pointed out, the queries have different attributes.
It is also explained here : https://www.sqlservergeeks.com/sys-dm_exec_plan_attributes/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply