Share via

SQL queries performing slow on SQL Server 2019.

Gaikwad, Akshay 1 Reputation point
2022-04-19T13:52:34.937+00:00

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:

  1. For SQL functions following syntax not working:
    SELECT @VarName= @VarName+ColumnName FROM TableName
  2. TempTables taking long time
  3. Insert statement taking long time
  4. 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

------------------------------------------------------------------------------------------------------------------------

========================================================================

------------------------------------------------------------------------------------------------------------------------

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
568 questions
SQL Server Other
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,206 Reputation points
    2022-04-19T18:40:22.05+00:00

    Hi :-)

    For SQL functions following syntax not working: SELECT @VarName= @VarName+ColumnName FROM TableName

    What do you mean by "not working" ?!? Do you get any error or does the result not fit your expected result?

    How can we answer it without any information about the table or the variable?!? We cannot read minds (yet)

    Your issue might be related to the data type for example.

    In general such format is allowed as you can see bellow 

    DECLARE @VarName VARCHAR(MAX)
SET @VarName = 'Ronen'
SELECT @VarName= @VarName+ [name] FROM sys.tables

SELECT @VarName
GO

    This does not mean that you should use such queries! It is highly not recommended to use such and in any case you should remember that this is a non-deterministic query which might return different result when you execute it since the order of result set is not guarantee here. You must add an ORDER BY.

    The way to concat (combine) all the values in the column in the table is to use the function STRING_AGG 

    DECLARE @VarName VARCHAR(MAX)
SET @VarName = 'Ronen'
SELECT @VarName = @VarName + STRING_AGG ([name],'') WITHIN GROUP (ORDER BY [name])
FROM sys.tables

SELECT @VarName
GO

    TempTables taking long time... Insert statement taking long time.... Complex Joins taking long time...

    I tested all your queries in the question and it takes zero time (probably since there are non). Again, we cannot read minds

    Please provide the full information to reproduce the scenario including queries to create the relevant object in the database and insert some sample data + all the queries which you try to execute + the Execution plan you get in the old and the new servers

    In general, in most cases when you upgrade the version of SQL Server then the queries run faster but this is not always the case and there are cases that we need to manipulate the query a bit (for example using queries hints). Once we will have something to work with then we might be able to provide a solution for your case.

    Note! the connection parameters and the sessions properties can be the source of the issue as well, and not only the version of the server. Compare all connection and sessions properties in your old server and new server.

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2022-04-19T22:10:40.883+00:00

    For SQL functions following syntax not working:

    SELECT @VarName= @VarName+ColumnName FROM TableName

    In addition to Ronen's post: that syntax has never worked. That is, it may have seemed to give you the desired result, but there has never been any defined correct behavior, so any result is correct so to speak. As Ronen said, use string_agg and be happy.

    As for your performance issues, I recommend that you use Query Store to track down the slow queries. The description you give is far to vague for any specific advice. It is not entirely uncommon to see performance regression when upgrading and particulary when changing the compat level. A common recommendation is to first run with compat level 110 (in your case) for some time - and with Query Store enabled. Then you flip the switch, and Query Store can help you find the regressed queries. There is also some tooling for this in SSMS.

    0 comments
  3. Seeya Xi-MSFT 16,586 Reputation points
    2022-04-20T06:25:40.577+00:00

    Hi @Gaikwad, Akshay ,

    Hope everything goes well.
    Here is the related document: Change the Database Compatibility Level and use the Query Store

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  4. Bjoern Peters 8,921 Reputation points
    2022-06-03T17:52:24.16+00:00

    Regarding your performance...

    I had run your statements on my 2 core (4Threads / 16GB Intel-NUC - docker container SQL2019)

    Started 2022-06-03 17:47:53.027

    data processed
    Param1 Param2 NewParam3 NewParam4 Param5 Param6 NewParam10 Param7 Param8 Param9 Param11
    A1 A2 A3 A4 1 1 109,75 1 1 2027-04-12 00:00:00.000 1
    B1 B2 B3 B4 2 1 19,75 1 1 2027-05-12 00:00:00.000 1
    C1 C2 C3 C4 3 1 190,75 1 1 2027-05-12 00:00:00.000 1
    D1 A2 A3 A4 1 1 109,75 1 1 2027-04-12 00:00:00.000 1
    E1 B2 B3 B4 2 1 19,75 1 1 2027-05-12 00:00:00.000 1
    F1 C2 C3 C4 3 1 190,75 1 1 2027-05-12 00:00:00.000 1
    G1 A2 A3 A4 1 1 109,75 1 1 2027-04-12 00:00:00.000 1
    H1 B2 B3 B4 2 1 19,75 1 1 2027-05-12 00:00:00.000 1
    I1 C2 C3 C4 3 1 190,75 1 1 2027-05-12 00:00:00.000 1
    J1 A2 A3 A4 1 1 109,75 1 1 2027-04-12 00:00:00.000 1
    K1 B2 B3 B4 2 1 19,75 1 1 2027-05-12 00:00:00.000 1
    L1 C2 C3 C4 3 1 190,75 1 1 2027-05-12 00:00:00.000 1
    M1 A2 A3 A4 1 1 109,75 1 1 2027-04-12 00:00:00.000 1
    N1 B2 B3 B4 2 1 19,75 1 1 2027-05-12 00:00:00.000 1
    O1 C2 C3 C4 3 1 190,75 1 1 2027-05-12 00:00:00.000 1
    P1 A2 A3 A4 1 1 109,75 1 1 2027-04-12 00:00:00.000 1
    Q1 B2 B3 B4 2 1 19,75 1 1 2027-05-12 00:00:00.000 1
    R1 C2 C3 C4 3 1 190,75 1 1 2027-05-12 00:00:00.000 1

    Query executed
    TextValue
    1

    Statements ended 2022-06-03 17:47:53.150

    Total run time: 123 ms

    I can't see why this should not work and perform as expected.
    As Ronen stated... please provide more information

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.