0

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:

Table Diagram

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.

Table of most expensive queries

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.

Edit

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

Edit 2 Please understand that I have had to redact the names. Execution plan

4
  • I've pulled the query directly from the Query Performance Insight on Azure. SELECT TOP (1) id FROM TableName WHERE column_a = @param1 is being reported as the highest cost query being run in a 24-hour window as shown above – goingrogue Apr 28 at 21:00
  • I've just taken a look and there is a plan which has a predicate of CONVERT_IMPLICIT(nvarchar(50), column_a, 0) = [@param1] and has significantly higher costs associated – goingrogue Apr 28 at 21:33
  • That’s it. Someone is passing an NVARCHAR parameter, preventing index use. Either fix the code or alter the column to NVARCHAR(50). – David Browne - Microsoft Apr 28 at 22:33
  • In that 'output list', isn't that a column that needs to be added as part of an include of an index? Some details here: sqlshack.com/… – D-K Apr 29 at 20:37
1

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?

2
  • I originally commented stating it was a varchar however I have since checked and it is indeed nvarchar. I have added the execution plan to my original post also. – goingrogue Apr 28 at 19:31
  • There is your solution. Make whatever is executing that code to do it with the right datatype, varchar instead of nvarchar. Or change the data type in the table. – Tibor Karaszi Apr 29 at 3:59

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.