CODEWITHSUNDEEP
sql-serverperformanceazure-sql-databaseprocessing-efficiency
Kam Mistry
Kam Mistry

Reputation: 105

Slow query operations on Azure database

I have 1.2 million rows in Azure data table. The following command:

DELETE FROM _PPL_DETAIL WHERE RunId <> 229

is painfully slow. There is an index on RunId. I am deleting most of the data. 229 is a small number of records. It has been running for an hour now Should it take this long? I am pretty sure it will finish. Is there anything I can do to make operations like this faster? The database does have a PK, although it is a dummy PK (not used). I already saw that as an optimization need to help this problem, but it still takes way too long (SQL Server treats a table without a PK differently -- much less efficient). It is still taking 1+ hour.

Upvotes: 0

Views: 1048

Answers (3)

Alberto Morillo
Alberto Morillo

Reputation: 15618

Without knowing what database tier is using the database where that statment runs it is not easy to help you. However, let us tell you how the system works so that you can make this determination with a bit more investigation by yourself.

Currently the log commit rate is limited by the tier the database has. Deletes are fundamentally limited on the ability to write out log records (and replicate them to multiple machines in case your main machine dies). When you select records, you don't have to go over the network to N machines and you may not even need to go to the local disk if the records are preserved in memory, so selects are generally expected to be faster than inserts/updates/deletes because of the need to harden log for you. You can read about the specific limits for different reservation sizes are here: DTU Limits and vCore Limits.

One common problem is to do individual operations in a loop (like a cursor or driven from the client). This implies that each statement has a single row updated and thus has to harden each log record serially because the app has to wait for the statement to return before submitting the next statement. You are not hitting that since you are running a big delete as a single statement. That could be slow for other reasons such as:

  • Locking - if you have other users doing operations on the table, it could block the progress of the delete statement. You can potentially see this by looking at sys.dm_exec_requests to see if your statement is blocking on other locks.
  • Query Plan choice. If you have to scan a lot of rows to delete a small fraction, you could be blocked on the IO to find them. Looking at the query plan shape will help here, as will set statistics time on (We suggest you change the query to do TOP 100 or similar to get a sense of whether you are doing lots of logical read IOs vs. actual logical writes). This could imply that your on-disk layout is suboptimal for this problem. The general solutions would be to either pick a better indexing strategy or to use partitioning to help you quickly drop groups of rows instead of having to delete all the rows explicitly.

An additional strategy to have better performance with deletes is to perform batching.

Upvotes: 1

Amin Pashna
Amin Pashna

Reputation: 161

As I know SQL Server had a change and the default DOP is 1 on their servers, so if you run the query with OPTION(MAXDOP 0) could help.

Try this:

DELETE FROM _PPL_DETAIL
WHERE RunId <> 229
OPTION (MAXDOP 0);

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

How about trying something like below

BEGIN TRAN
SELECT * INTO #T FROM _PPL_DETAIL WHERE RunId = 229 
TRUNCATE TABLE _PPL_DETAIL
INSERT INTO _PPL_DETAIL
SELECT * FROM #T
COMMIT TRAN

Upvotes: 1

Related Questions