none
Performance problem with varchar and utf-8 RRS feed

  • Question

  • I have a problem with a simple query that takes more than 30 second to complete. The query is a simple "SELECT" from a single table and the resultset contains 64 rows.
    Im not sure why it takes so long because the query is simple and the execution plan dont show any issues.

    I checked the Sql Server log and found this message multiple times:
    "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1142644, committed (KB): 2241136, memory utilization: 50%".
    So i checked the memory usage of the Sql Server process while the query is running and it increases from ~400MB to ~1.5GB. It seems that the OS started paging and slows down everything.
    But why?

    I have to mention that the varchar columns of the table uses the collation Latin1_General_100_CI_AI_SC_UTF8. When i switch the collation to Latin1_General_100_CI_AI_SC it runs without  any problems.

    Is this a encoding bug in the SQL Server 2019? Can anyone else reproduce this or can explain me what is going on?

    What i used:

    • SQL Server 2019, SELECT @@Version:  Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) - 15.0.4043.16 (X64)   Jun 10 2020 18:25:25   Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
    • ODBC Driver 64-bit, 2017.175.02.01
    • Microsoft SQL Management Studio v18.5, 15.0.18330.0

    The query

    SELECT
    [ID] AS [ID]
    ,[Column1]
    ,[Column2]
    ,[Column3]
    ,[Column4]
    ,[Column5]
    ,[Column6]
    ,[Column7]
    ,[Column8]
    FROM [dbo].[MyTable]
    WHERE [ID] IN (3,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81)

    The table

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[MyTable](
    	[ID] [int] NOT NULL,
    	[Column1] [varchar](250) COLLATE Latin1_General_100_CI_AI_SC_UTF8 NULL,
    	[Column2] [varchar](36) COLLATE Latin1_General_100_CI_AI_SC_UTF8 NULL,
    	[Column3] [int] NULL,
    	[Column4] [int] NULL,
    	[Column5] [int] NULL,
    	[Column6] [varchar](250) COLLATE Latin1_General_100_CI_AI_SC_UTF8 NULL,
    	[Column7] [varchar](250) COLLATE Latin1_General_100_CI_AI_SC_UTF8 NULL,
    	[Column8] [int] NULL,
     CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    The data

    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (3, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (7, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (8, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (9, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (11, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (12, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (13, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (14, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (15, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (16, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (17, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (18, N'ABC', N'abc', 1227, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (19, N'ABC', N'abc', 1227, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (20, N'ABC', N'abc', 1227, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (21, N'ABC', N'abc', 1227, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (22, N'ABC', N'abc', 1229, 6142, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (23, N'ABC', N'abc', 1257, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (24, N'ABC', N'abc', 1257, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (25, N'ABC', N'abc', 1257, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (26, N'ABC', N'abc', 1257, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (27, N'ABC', N'abc', 1230, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (28, N'ABC', N'abc', 1230, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (29, N'ABC', N'abc', 1230, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (30, N'ABC', N'abc', 1230, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (31, N'ABC', N'abc', 1231, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (32, N'ABC', N'abc', 1231, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (33, N'ABC', N'abc', 1231, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (34, N'ABC', N'abc', 1231, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (35, N'ABC', N'abc', 1233, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (36, N'ABC', N'abc', 1233, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (37, N'ABC', N'abc', 1233, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (38, N'ABC', N'abc', 1233, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (39, N'ABC', N'abc', 1232, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (40, N'ABC', N'abc', 1232, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (41, N'ABC', N'abc', 1232, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (42, N'ABC', N'abc', 1232, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (43, N'ABC', N'abc', 1228, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (44, N'ABC', N'abc', 1228, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (45, N'ABC', N'abc', 1228, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (46, N'ABC', N'abc', 1228, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (47, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (48, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (49, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (50, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (51, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (52, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (53, N'ABC', N'abc', 1227, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (64, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (65, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (66, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (67, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (68, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (69, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (70, N'ABC', N'abc', 1257, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (71, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (72, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (73, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (74, N'ABC', N'abc', 1226, 6605, 1, N'abc', NULL, 1)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (75, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (76, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (77, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (78, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (79, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (80, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    INSERT [dbo].[MyTable] ([ID], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]) VALUES (81, N'ABC', N'abc', 1226, 6103, 1, N'abc', NULL, 4)
    Friday, July 10, 2020 3:10 PM

Answers

  • Thanks for reporting this issue. We at Microsoft are aware of it, and it's already been fixed for the Cumulative Update for SQL Server 2019 (CU7).



    • Proposed as answer by Naomi NModerator Friday, July 10, 2020 10:29 PM
    • Edited by sergten Saturday, July 11, 2020 12:20 AM
    • Marked as answer by Sten K Monday, July 13, 2020 6:21 AM
    Friday, July 10, 2020 10:18 PM

All replies

  • Your server doesn't have enough physical RAM.  You need more RAM.  SQL Server requires a minimum of 4GBs to itself.

    Friday, July 10, 2020 7:24 PM
    Moderator
  • This is absolutely a bug. I was able to reproduce the problem with your excellent repro script. Great work! It is very interesting to see that if you remove a single ID from the WHERE clause, the issue does not appear, but there is also a different plan in this case.

    My speculation is that there is a pointer somewhere that goes awry and points to a virtual address outside the space actually allocated. I can see in Process Explorer, that SQL Server is page-faulting heavily while the query runs. And I guess, it is all these pages faults that results in the message in the errorlog. Because, in my case at least, it is impossible that SQL Server actually has been paged out. I have set max server memory to 32000 and I have 64 GB of RAM on my workstation.

    If this isa blocking issue for you and you need a fix, you should open a case with Microsoft.

    If you have a workaround and only want to report the problem to MS, yo ucan submit a bug on https://feedback.azure.com/forums/908035-sql-server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 10, 2020 7:56 PM
  • Do you get same error with the IDs being in the table variable?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 10, 2020 8:39 PM
    Moderator
  • Amazing issue 🤪 !!!!

    Thank you for sharing it with us 😀
    +5

    You must submit a bug in the UserVoice


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, July 10, 2020 8:54 PM
  • Thanks for reporting this issue. We at Microsoft are aware of it, and it's already been fixed for the Cumulative Update for SQL Server 2019 (CU7).



    • Proposed as answer by Naomi NModerator Friday, July 10, 2020 10:29 PM
    • Edited by sergten Saturday, July 11, 2020 12:20 AM
    • Marked as answer by Sten K Monday, July 13, 2020 6:21 AM
    Friday, July 10, 2020 10:18 PM
  • Thanks for the fast response here and in private :-)

    I sent you a followup email by the way. It's very interesting case.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, July 10, 2020 11:55 PM
  • Thanks for the answer.

    Is there a schedule, when that update (Cumulative Update for SQL Server 2019 (CU7)) is planned?

    Is there a workarround?



    • Edited by Sten K Monday, July 13, 2020 8:07 AM
    Monday, July 13, 2020 7:55 AM