1

Since we updated from SQL Server 2008 to 2019, the queries have become much slower.

The following query ran for ten minutes on version 2008. On version 2019 it runs for over 30 minutes.

Until yesterday, we always limited the WHERE-clause: where date > '20180101'

To make the script at least a bit faster, I narrowed it down from January 2019: where date > '20190101'

Now it's even slower than before. No joke! Because one year less has to be evaluated, the running time has increased by a few minutes.

I have checked all the indexes in detail and I think we have created the correct indexes. But I don't know if this is really an index problem, because in version 2008 we had the same indexes. In version 2019 I even deleted and re-created a few important indexes. But that didn't help at all.

MS-SQL Server 2019 got very slow and I don't know why.

14
  • 1
    Did you updated statistics for all the databases after moving to SQL Server 2019 ?. You might be facing what we call as CE regression. What is compatibility level of databases moved 100 or 150 ? – Shanky Feb 25 at 15:07
  • A consultant moved the database from version 2008 to 2019. I don't know whether the statistics have been updated. How can I do that? – SipCat Feb 25 at 15:10
  • I see a lot of warnings like "Type conversion in expression (CONVERT_IMPLICIT(datetime,[Salamander_DWH].[dbo].[F_BonusSWDS].[gueltig_ab],0)) may affect "Cardinality Estimate" in query plan choice.", mouse over the first block in paste the plan. – Brian Feb 25 at 15:10
  • 1
    @SipCat Please include all the additional info in the comments to the question. – LowlyDBA - John M Feb 25 at 15:43
  • 3
    There's an old saying that when you have issues with performance regarding the CE level, then you likely have issues with the query to being with. and the fact that you had decent performance earlier was just luck. Or something like that. Joining over a CONVERT is an immediate thing that sticks out. First step is to compare the plan between the old and the new. Running with old CE should give you same plan shape, but you never know. And then probably treat this as any type of performance tuning work. I.e., clean-up the query, work the plan, indexes etc. – Tibor Karaszi Feb 25 at 15:50
2

From the query plan you provided it runs a minute:

enter image description here

But it compiles 33 minutes (1992254 ms ~= 1992 s ~= 60 * 33 = 33 minutes):

enter image description here

So basically you had a problem on 2008 already. It's just exaggerated in 2019. Never experienced such an extreme case in my life, but I would try to simplify the views you are using and drop not used indexes on underlying tables. Or maybe split the query: first select view into temp table and then use the temp table in your original query.

1

The database compatibility level controls the behavior of the query optimizer.

So the recommended upgrade process is to keep the upgraded database on the lower database compatibility level initially, while you use Query Store to capture a performance baseline for the database.

See

enter image description here

Change the Database Compatibility Level and use the Query Store

1

Looking at your query execution plan it doesn't look like you database contains any indexes or they where disabled during/by the migration process.

Based on the statement in your question:

With this join, I create an index on the F_Skonto table with the three columns gueltig_ab, gueltig_bis and CD_Kunde. Is that correct?

...I'm not sure if you are aware of what an index is. The answer to your question is:

No, a JOIN does not create an index. A JOIN can cause an existing index to be used to speed up the query.

Your statement may have been based on your knowledge of the English language and thus be misleading. I'll asume you know that an index is basically a Table of Contents that the the SQL Server Database Engine can use to speed up queries by knowing where to look for the data.

In that case we may be looking at the possible issue that the indexes are all disabled. If an index is disable it can't be use for speeding up queries.

Execute the following query to see if you have indexes which have been disalbed:

SELECT s.name                  AS Schema_name,
       o.name                  AS Table_Name,
       i.name                  AS Index_Name,
       i.is_disabled           AS Index_Disabled
FROM   sys.objects             AS o
       JOIN sys.schemas        AS s
            ON  s.schema_id = o.schema_id
       JOIN sys.indexes        AS i
            ON  i.object_id = o.object_id
        JOIN sys.sysindexes as si
                   ON  si.[id] = i.object_id
            AND si.indid = i.index_id
WHERE  1=1 
       AND s.name != 'sys'           -- Exclude sys items
       AND i.is_disabled = 1         -- Show only disabled indexes
ORDER BY
1,2,3       
       

This will produce a similar output to this:

+----------------+------------+--------------------+----------------+
|  Schema_name   | Table_Name |     Index_Name     | Index_Disabled |
+----------------+------------+--------------------+----------------+
| HumanResources | Department | AK_Department_Name |              1 |
+----------------+------------+--------------------+----------------+

If any indexes have been disabled in your tables, then you will receive a list. With this list you can then go ahead and rebuild the indexes which will enable them again and which might result in a faster SELECT part of your INSERT statement.

If no disabled indexes are returned by the above statement, then we will check if you have any indexes at all by running the same statement without the AND i.is_disabled = 1 part.

Like this:

SELECT s.name                  AS Schema_name,
       o.name                  AS Table_Name,
       i.name                  AS Index_Name,
       i.is_disabled           AS Index_Disabled
FROM   sys.objects             AS o
       JOIN sys.schemas        AS s
            ON  s.schema_id = o.schema_id
       JOIN sys.indexes        AS i
            ON  i.object_id = o.object_id
        JOIN sys.sysindexes as si
            ON  si.[id] = i.object_id
            AND si.indid = i.index_id
WHERE  1=1 
       AND s.name != 'sys'           -- Exclude sys items
ORDER BY
1,2,3       
       

In my AdventureWorks2012 database provided by Microsoft, this will produce a list which looks like this:

+----------------+--------------------------+------------------------------------------------+----------------+
|  Schema_name   |        Table_Name        |                   Index_Name                   | Index_Disabled |
+----------------+--------------------------+------------------------------------------------+----------------+
| dbo            | AWBuildVersion           | PK_AWBuildVersion_SystemInformationID          |              0 |
| dbo            | DatabaseLog              | NULL                                           |              0 |
| dbo            | DatabaseLog              | PK_DatabaseLog_DatabaseLogID                   |              0 |
| dbo            | ErrorLog                 | PK_ErrorLog_ErrorLogID                         |              0 |
| dbo            | ufnGetContactInformation | NULL                                           |              0 |
| HumanResources | Department               | AK_Department_Name                             |              1 |
| HumanResources | Department               | PK_Department_DepartmentID                     |              0 |
| HumanResources | Employee                 | AK_Employee_LoginID                            |              0 |
| HumanResources | Employee                 | AK_Employee_NationalIDNumber                   |              0 |
| HumanResources | Employee                 | AK_Employee_rowguid                            |              0 |
| HumanResources | Employee                 | IX_Employee_OrganizationLevel_OrganizationNode |              0 |
| HumanResources | Employee                 | IX_Employee_OrganizationNode                   |              0 |
| HumanResources | Employee                 | PK_Employee_BusinessEntityID                   |              0 |

If the above statement does not provide any output, then your tables don't have any indexes. We will have to start creating some...

4
  • Thanks for your detailed explanation. Yes, I know what an index is, but my English is not really good. The phrase with the join was poorly worded. – SipCat Mar 1 at 8:42
  • I tried the first Select from you, but no disabled indexes are displayed. – SipCat Mar 1 at 8:42
  • I tried the second Select from you, and now indexes are displayed, but each table also has a NULL index (I added a hard copy above). Is the NULL index an error? – SipCat Mar 1 at 8:43
  • It seems to me that my indexes are all being ignored. – SipCat Mar 1 at 8:43
1

While it's true there are no indexes on the underlying tables, the query plan shows the vast majority of resources are being used for the insert to the destination table and its 3 indexes.

One way to improve performance of large/ bulk inserts is to disable indexes prior to the insert and rebuild them after. If you have a unique clustered primary key (or unique index(es)) you could keep them as non-clustered constraints for the INSERT to ensure validation rules are maintained if you're not confident data being inserted will be valid.

Following the INSERT, drop these temp versions (if used) and rebuild original indexes.

0

try to run the query setting up the legacy cardinalty extimation (ON):

enter image description here

as a second test try to turn on Query optimizer fixes (ON)

5
  • My "Database Scoped Configurations" looks exactly like in your hard copy. Which option should I adjust? – SipCat Feb 25 at 16:04
  • I think the suggestion to toggle the top option in the Database Scoped Configurations, Legacy Cardinality Estimation, to ON. – BCM Feb 25 at 17:11
  • exactly. the other one is "Query optimizer Fixes". try the first one, then the second one, then both – MBuschi Feb 25 at 19:21
  • I've tried both options, but that didn't improve performance. Do I have to restart the database for the options to take effect, or do the new options take effect immediately after the change in the SSMS? – SipCat Feb 26 at 11:42
  • it is immediate. so your problem is not related to the cardinality extimation – MBuschi Feb 26 at 15:39
0

As per your execution plan shared on Brent's paste the plan, I can only see table scan happening. If you say your indexes are in place, I doubt they are online or being used. You should start your investigation from there.

3
  • I also have my doubts whether my indexes are all set up correctly. But I always use the columns from the JOIN for this. I've given an example of this as a separate answer. – SipCat Feb 26 at 8:47
  • If the term "table scan" appears in the execution plan, is that an indication of a "full table scan", and that means that no index has been used here? – SipCat Feb 26 at 12:19
  • Yes. It indicates a full table scan and the index is not used or doesn't exist. – Shamvil Kazmi Feb 26 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.