These last days, I have been working in several service requests with same behavior.
- Customer has an application that is searching a value per execution.
- The parameter value that is using the stored procedure is defined a nVarchar data type.
- The table field is varchar data type.
- Every execution took few milliseconds but if you execute this query in a loop or multiple times the CPU grows to 100%
After reviewing the situation, we found the performance cause is due to, for every value that we have in the index we have an implicit conversion from varchar to nvarchar to compare the results sent by the application and depending on the speed and capacity of the CPU the process will take more or less time. Even, if you change in your local environment of SQL Server the table to nvarchar the process will less that it took.
Trying to explain I created a similar scenario in our laboratory with same behavior:
- STEP 1: Created table
CREATE TABLE [dbo].[PerformanceVarcharNVarchar](
[Id] [int] NOT NULL,
[TextToSearch] [varchar](200) NOT NULL,
CONSTRAINT [PK_PerformanceVarcharNVarchar] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
- STEP 2: I Inserted 450.338 rows in the table.
DECLARE @N AS INT = 0
BEGIN TRANSACTION
WHILE(@N<= 450338)
BEGIN
SET @N=@N+1
INSERT INTO [PerformanceVarcharNVarchar](ID,TextToSearch) values(@n,'Example ' + convert(varchar(1000),@n))
END
COMMIT TRANSACTION
- STEP 3: I created the clustered index, in order to have the index without fragmentation.
CREATE NONCLUSTERED INDEX [PerformanceVarcharNVarchar_x1] ON [dbo].[PerformanceVarcharNVarchar]
(
[TextToSearch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
- STEP 4: I reviewed the execution plan of these two queries and we could see the impact, one is using Index Seek and other one is using Index Scan (read the entire index) with an implicit conversion from
-
- STEP 4.1: SELECT TOP 1 TextToSearch FROM PerformanceVarcharNVarchar Where TextToSearch = 'Example 1
-
- STEP 4.2: SELECT TOP 1 TextToSearch FROM PerformanceVarcharNVarchar Where TextToSearch = N'Example 1'
-
STEP 5: I created an application C# to reproduce the issue.
- I’m going to run 10.000 search operations changing the value by random value.
- STEP 6: Using Azure SQL Database, searching using the parameter of the code in NVarchar with implicit conversion for every row and index scan we got the CPU 99%
- STEP 7: Using Azure SQL Database, searching using the parameter of the code in Varchar without implicit conversion for every row and index seek we got the CPU +/- 33%
- STEP 8: Using SQL Server 2016 OnPremises with 4 cores Intel ® Xeon®CPU E5-2673 v4 @ 2.30 Ghz, searching using the parameter of the code in NVarchar with implicit conversion for every row and index scan we got the CPU +/- 20%
- STEP 9: If you use nVarchar in the table having same data types parameter value and table, the process will be faster using less CPU.