Sql-server – Decrease Data IO operation on query to lower the DTU cost on Azure

azureoptimizationquery-performancesql server

We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer with the same data in the DB the query executes in about 2 seconds. On Azure it took almost almost 100 seconds.

We saw in the metrics on Azure that the Data IO limit hit the roof with 100% DTU. So we increased the DTU limit and the query now runs in about 48 seconds, but still hits the DTU limit for Data IO.

It's easy to say, just increase the DTU limit, but the customer isn't soo keen on paying more every month.

Soo, Is there something I can do with the below query to limit the amount of Data IO it does? Can I do it in another way? Split the query or something?

WITH CombinedTables AS 
(SELECT SourceTable,a, b, c, d, e, f, g, 
ROW_NUMBER() OVER (PARTITION BY a, b, c, d, e, f, g ORDER BY a, b, c, d, e, f, g) Row_Nbr
FROM (SELECT '2' AS SourceTable, a, b, c, d, e, f, g FROM [abc].[ExternalView]      
             UNION ALL 
             SELECT '1' AS SourceTable, a, b, c, d, e, f, g FROM [LocalTable] WITH (NOLOCK)
) AS CombinedTables GROUP BY a, b, c, d, e, f, g, SourceTable
) SELECT DISTINCT * FROM CombinedTables WHERE CombinedTables.Row_Nbr < 2 AND CombinedTables.SourceTable = '2'

Or is the solution to limit the number of rows we operate with, so that the amount of row that needs to be processed will be lower? there are about 84k rows in the table now, and it increases some every month.

edit: Added execution plan https://prnt.sc/uuys04

Best Answer

I see a table scan and no use of any indexes in the execution plan. Create indexes to support your query and consider creating one to eliminate the sort. Apply page compression to your tables and any nonclustered indexes. Make sure you are updating stats and indexes - this maintenance is still required in Azure.

Related Solutions

Sql-server – Joining table from Linked Server is very slow

Put an index on A.ID

Add a <> so it can avoid taking locks

update A
    set A.ColumnA = B.ColumnB
from #Table A
join [LinkedServer].[DB].[dbo].[Table] B 
  on A.ID = B.ID 
where A.ColumnA <> B.ColumnB or A.ColumnA is null

Azure SQL Database – Delete Query Uses 100% CPU

As you note the plan shows many index scans[1], a couple of the indexes multiple times such as Facilities.IX_CustomerID. That will be why is it spinning the CPU resource: those indexes will be in the buffer pool so little IO is needed and the CPUs are being made to chew them, probably in their entirety, repeatedly.

If this is from a simple DELETE statement and all the other activity is the engine enforcing foreign key constraints then I would check for foreign keys without supporting indexes. For instance one of the scans of [Facilities]" Index="[IX_CustomerID] has the search predicate [DataMateWeb].[dbo].[Facilities].[SurfaceCleanerID]=[DataMateWeb].[dbo].[Chemical].[ChemicalID], which suggests an index covering [Facilities].[SurfaceCleanerID] is not present or for some reason can not be used. When you define a foreign key an index is not automatically created to go with it which can surprise people and that would explain a scan of the table/cluster rather than multiple seeks on an index. In any cases where those indexes are the supporting indexes for the foreign keys, make sure that they are correctly defined and don't have badly out-of-date statistics.

If the delete operation isn't that simple then make sure any joins and sub-queries used in deciding which rows will be deleted are supported by appropriate indexes and that the joining and filtering predicates are arranged such that they are sargable where ever possible.

Failing the above (i.e. all the FKs and any other filtering & joining activity all look fine with regard to having indexes they could potentially seek on), the next straw I'd clutch for is: if you are deleting many rows at once (DELETE WHERE IN (<a result that could be a long list>)) then I've seen that make SQL Server scan the index instead of using multiple seeks. This feels a bit nasty[2] but: check this by trying to confirm the behaviour with just one row and if that changes at least some of the scans to seeks (and improves performance as a result) consider cursoring through the rows that need to be removed and doing them one at a time.

_{[1] Which as they are clustered index scans are effectively table scans which for large tables means a lot of data is being touched.}
_{[2] Because it is nasty. But if it is nasty and works, it still works!}

Related Question