Delete statement running slow

• krishna83

  Ten Centuries

  Points: 1003

  More actions

  May 10, 2017 at 4:40 am

  #321310

  Hi,

  We have been running some delete statemets on some tables  which contains nearly 20 core records in each table approximately.

  We need to run this delete statements daily.

  One day these statements executing in a hour some other day it was taking 6hours.

  I would like know why I am getting difference in comming in execution time.

  What are the steps I need to take before starting the delete statements?

  Thanks & Regards
  Krishna.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 10, 2017 at 4:51 am

  #1941949

  krishnabudampati - Wednesday, May 10, 2017 4:40 AM

  Hi,

  We have been running some delete statemets on some tables  which contains nearly 20 core records in each table approximately.

  We need to run this delete statements daily.

  One day these statements executing in a hour some other day it was taking 6hours.

  I would like know why I am getting difference in comming in execution time.

  What are the steps I need to take before starting the delete statements?

  Thanks & Regards
  Krishna.

  If there is absolutely no other activity on the server then you might expect the execution times to be quite similar. Have you looked at waits during execution? Have you tried cache priming?
  Can you post up an execution plan?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• RandomEvent

  SSCrazy

  Points: 2963

  More actions

  May 10, 2017 at 5:00 am

  #1941950

  What wait types are you seeing (any I/O related) and what does sp_locks tell you about the locking on the server at this time. Could be related to a large number of possible variables, can we have more information about what you are running, what else is running at the time e.g any other code, any SQL Jobs etc.. and what the status of the locks and wait types are at the time you notice it running slowly.

• VastSQL

  SSC-Dedicated

  Points: 36121

  More actions

  May 10, 2017 at 5:16 am

  #1941953

  krishnabudampati - Wednesday, May 10, 2017 4:40 AM

  Hi,

  We have been running some delete statemets on some tables  which contains nearly 20 core records in each table approximately.

  We need to run this delete statements daily.

  One day these statements executing in a hour some other day it was taking 6hours.

  I would like know why I am getting difference in comming in execution time.

  What are the steps I need to take before starting the delete statements?

  Thanks & Regards
  Krishna.

  How you checked whether the delete statement is blocked during execution?

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  May 10, 2017 at 6:37 am

  #1941976

  In addition to the other checks listed (blocking, waits, execution plan), are these delete statements deleting varying amounts of data? If the data changes, so will the behavior, sometimes radically in comparison to the data. Also, how up to date are your statistics? More specifically, how up to date are your statistics prior to the DELETE statements running? That could also impact what's going on (and would be evidenced in the information you need to capture above, especially the execution plan. Make sure you get a plan for the "slow" execution and the "fast" execution).

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning