208230-probem-temptable-2019-ep.xml208247-problem-temptable-2012-ep.xml208282-problem-insertstate-2012-ep.xml208237-problem-insertstate-2019-ep.xml208193-problem-complexjoin-2012-ep.xml208283-problem-complexjoin-2019-ep.xml
---------------------------------------------------------------------------------------------------------------------------------
===========================================================================
---------------------------------------------------------------------------------------------------------------------------------
Earlier we were using SQL Server 2012 with Compatibility 110. On this environment our sql queries were working fine.
But as we migrated to SQL Server 2019 with Compatibility 150. SQL queries are taking long time.
We are hosting both DBs on cloud platform (Azure).
One other observation is if we use SQL Server 2019 with Compatibility 110 then also queries runs slow.
Currently we have set all Scoped Configurations to Default values for SQL Server 2019.
Some of the common issues are enlisted below for SQL 2019:
- For SQL functions following syntax not working:
SELECT @VarName= @VarName+ColumnName FROM TableName
- TempTables taking long time
- Insert statement taking long time
- Complex Joins taking long time
Even I have tried with using Legacy Cardinality Estimator but it did not work.
Is there any other setting that I should check OR is there any other investigation that I should do?
Below I have given sample queries which are taking long time and its prerequisite data.
I also have uploaded execution plan for all three queries on sql 2012 and sql2019.
----------------------------------------------------------------------------------------------------------------------
=====================================================================
----------------------------------------------------------------------------------------------------------------------
SQL Queries to check :
--Queries taking long time on SQL2019
-----------------------------------
--Stored Procedure
-----------------------------------
EXEC prcTestTempTable 1
-----------------------------------
--INSERT Statement
-----------------------------------
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (1000,4,1)
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (1000,5,86)
INSERT INTO InsertTest (ID1,ID2,ID4) VALUES (1000,7,1)
INSERT INTO InsertTest (ID1,ID2,TextValue) VALUES (1000,9,'1')
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (1000,57,167)
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (1000,58,167)
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (1000,59,167)
INSERT INTO InsertTest (ID1,ID2,ID5) VALUES (1000,6,33)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (1000, 10294, 1)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (1000, 11412, 1)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (1000, 10489, 0)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (1000, 10491, 0)
INSERT INTO InsertTest (ID1,ID2, ID4) VALUES (1000, 10630, 769)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (1000, 10640, 0)
INSERT INTO InsertTest (ID1,ID2, ID8) VALUES (1000, 16, 211)
---------------------------------------------------------------------
--Complex Join
---------------------------------------------------------------------
SELECT TOP 1 IT.TextValue FROM InsertTest IT FULL JOIN BaseInfo BI ON BI.ID = IT.ID2 WHERE BI.Param1 = 'A_A_9' AND IT.ID1 = 1000
------------------------------------------------------------------------------------------------------------------------
========================================================================
------------------------------------------------------------------------------------------------------------------------
Prerequisite data:
--User Defined Type
CREATE TYPE [dbo].[T_SYMBOL] FROM [varchar](20) NULL
--Table Creation
CREATE TABLE dbo.DataForTestTempTable(
ID Int Identity(1,1) NOT NULL,
Param1 VARCHAR(30),
Param2 VARCHAR(30),
Param3 VARCHAR(30),
Param4 VARCHAR(30),
Param5 Int,
Param6 BIT,
Param7 BIT,
Param8 BIT,
Param9 datetime,
Param10 FLOAT,
Param11 BIT
)
GO
CREATE TABLE dbo.InsertTest(
ID1 int NOT NULL,
ID2 int NOT NULL,
TextValue varchar(max) NULL,
DateValue datetime NULL,
RealValue float NULL,
IntegerValue int NULL,
ID3 int NULL,
ID4 int NULL,
ID5 int NULL,
ID6 int NULL,
ID7 int NULL,
ID8 int NULL,
CONSTRAINT [PK_InsertTest] PRIMARY KEY CLUSTERED
(
ID1 ASC,
ID2 ASC
)
)
GO
CREATE TABLE [dbo].[BaseInfo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Param1] [dbo].[T_SYMBOL] NULL
CONSTRAINT [PK_BaseInfo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
--INSERT Sample Data in Tables
INSERT INTO DataForTestTempTable Values('A1','A2','A3','A4',1,1,1,1,'2027-04-12 00:00:00.000',109.75,1)
INSERT INTO DataForTestTempTable Values('B1','B2','B3','B4',2,1,1,1,'2027-05-12 00:00:00.000',19.75,1)
INSERT INTO DataForTestTempTable Values('C1','C2','C3','C4',3,1,1,1,'2027-05-12 00:00:00.000',190.75,1)
INSERT INTO DataForTestTempTable Values('D1','A2','A3','A4',1,1,1,1,'2027-04-12 00:00:00.000',109.75,1)
INSERT INTO DataForTestTempTable Values('E1','B2','B3','B4',2,1,1,1,'2027-05-12 00:00:00.000',19.75,1)
INSERT INTO DataForTestTempTable Values('F1','C2','C3','C4',3,1,1,1,'2027-05-12 00:00:00.000',190.75,1)
INSERT INTO DataForTestTempTable Values('G1','A2','A3','A4',1,1,1,1,'2027-04-12 00:00:00.000',109.75,1)
INSERT INTO DataForTestTempTable Values('H1','B2','B3','B4',2,1,1,1,'2027-05-12 00:00:00.000',19.75,1)
INSERT INTO DataForTestTempTable Values('I1','C2','C3','C4',3,1,1,1,'2027-05-12 00:00:00.000',190.75,1)
INSERT INTO DataForTestTempTable Values('J1','A2','A3','A4',1,1,1,1,'2027-04-12 00:00:00.000',109.75,1)
INSERT INTO DataForTestTempTable Values('K1','B2','B3','B4',2,1,1,1,'2027-05-12 00:00:00.000',19.75,1)
INSERT INTO DataForTestTempTable Values('L1','C2','C3','C4',3,1,1,1,'2027-05-12 00:00:00.000',190.75,1)
INSERT INTO DataForTestTempTable Values('M1','A2','A3','A4',1,1,1,1,'2027-04-12 00:00:00.000',109.75,1)
INSERT INTO DataForTestTempTable Values('N1','B2','B3','B4',2,1,1,1,'2027-05-12 00:00:00.000',19.75,1)
INSERT INTO DataForTestTempTable Values('O1','C2','C3','C4',3,1,1,1,'2027-05-12 00:00:00.000',190.75,1)
INSERT INTO DataForTestTempTable Values('P1','A2','A3','A4',1,1,1,1,'2027-04-12 00:00:00.000',109.75,1)
INSERT INTO DataForTestTempTable Values('Q1','B2','B3','B4',2,1,1,1,'2027-05-12 00:00:00.000',19.75,1)
INSERT INTO DataForTestTempTable Values('R1','C2','C3','C4',3,1,1,1,'2027-05-12 00:00:00.000',190.75,1)
GO
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (2000,4,1)
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (2000,5,86)
INSERT INTO InsertTest (ID1,ID2,ID4) VALUES (2000,7,1)
INSERT INTO InsertTest (ID1,ID2,TextValue) VALUES (2000,9,'1')
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (2000,57,167)
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (2000,58,167)
INSERT INTO InsertTest (ID1,ID2,ID7) VALUES (2000,59,167)
INSERT INTO InsertTest (ID1,ID2,ID5) VALUES (2000,6,33)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (2000, 10294, 1)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (2000, 11412, 1)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (2000, 10489, 0)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (2000, 10491, 0)
INSERT INTO InsertTest (ID1,ID2, ID4) VALUES (2000, 10630, 769)
INSERT INTO InsertTest (ID1,ID2, IntegerValue) VALUES (2000, 10640, 0)
INSERT INTO InsertTest (ID1,ID2, ID8) VALUES (2000, 16, 211)
GO
INSERT INTO BaseInfo Values ('A_A_1')
INSERT INTO BaseInfo Values ('A_A_2')
INSERT INTO BaseInfo Values ('A_A_3')
INSERT INTO BaseInfo Values ('A_A_4')
INSERT INTO BaseInfo Values ('A_A_5')
INSERT INTO BaseInfo Values ('A_A_6')
INSERT INTO BaseInfo Values ('A_A_7')
INSERT INTO BaseInfo Values ('A_A_8')
INSERT INTO BaseInfo Values ('A_A_9')
INSERT INTO BaseInfo Values ('A_A_10')
GO
--Create Stored Procedures
CREATE PROCEDURE dbo.prcGetDataForTestTempTable
@ParentID INT
AS
BEGIN
Select
Param1,
Param2,
Param3,
Param4,
Param5,
Param6,
Param7,
Param8,
Param9,
Param10,
Param11
from
DataForTestTempTable
END
GO
CREATE PROCEDURE dbo.prcTestTempTable
@ParentID INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TempTable
(
Param1 VARCHAR(30),
Param2 VARCHAR(30),
Param3 VARCHAR(30),
Param4 VARCHAR(30),
Param5 Int,
Param6 BIT,
Param7 BIT,
Param8 BIT,
Param9 datetime,
Param10 FLOAT,
Param11 BIT);
INSERT INTO #TempTable
EXEC prcGetDataForTestTempTable @ParentID
SELECT Param1, Param2, Param3 AS NewParam3, Param4 AS NewParam4, Param5, Param6, SUM(Param10) AS NewParam10, Param7, Param8, Param9, Param11
FROM #TempTable
GROUP BY Param1, Param2, Param3, Param4, Param5, Param6, Param7, Param8, Param9, Param11
DROP TABLE #TempTable
END
GO
------------------------------------------------------------------------------------------------------------------------
========================================================================
------------------------------------------------------------------------------------------------------------------------