All we need is an easy explanation of the problem, so here it is.
I’m currently running into an issue with one of our databases in Azure where 1 table is forcing us to scale the entire database up to cope with the CPU requirements. The basic structure of the table is as follows:
column_a is unique and not nullable. column_b can be null.
This table has ~1.6 million rows in it so it’s not a large table but it is used throughout the system we have built. We have several feeds pushing data in which requires an entry in this table so for each new request we receive there is a check for an entry and then if that doesn’t exist one is created and the id returned. Once that data is received, we have subscribers which receive that data and use this table to link other information the system holds in a nice interface.
The table above shows the most expensive queries being run on this database over a 24 hour period. The one highlighted in yellow is
SELECT TOP (1) id FROM TableName WHERE column_a = @param1.
Is this just bad database architecture or are we missing a trick to optimize for the number of reads we are performing on the table? Unfortunately, the number of reads being performed is only going to increase as we are adding new data feeds every month and we are aiming to have 5 – 10x the number of feeds by the end of this year.
Any help is much appreciated and my apologies if any of the above is unclear.
Create Table query
CREATE TABLE [dbo].[TableName]( [id] [bigint] IDENTITY(1,1) NOT NULL, [column_a] [varchar](50) NOT NULL, [column_b] [varchar](50) NULL, CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [AK_Column_A] UNIQUE NONCLUSTERED ( [plate] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
there is a plan which has a predicate of CONVERT_IMPLICIT(nvarchar(50), column_a, 0) = [@param1]
Someone is passing an NVARCHAR parameter, preventing index use. Either fix the code or alter the column to NVARCHAR(50). Comparing a VARCHAR parameter to a NVARCHAR column is not problematic, as NVARCHAR has higher Data Type Precedence, so the parameter is converted instead of the column.
I would say the issue is the # of executions. When u take the time per execution for two first rows, its about 0.8 seconds per row. But the select is executed so often It eats up cpu by comparision.
In adition, what is the datatype of @param?
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂