we have an SSIS package that starts around mid night and does bunch of things. one of the insert statement in that package is causing slowness sometimes, runs for 90 minutes and sometimes runs in 42 or 20 minutes and during weekends it runs in 7 minutes.

I have checked for any resource contention, there is nothing going on except the database maintenance that runs in parallel and with the maintenance running in parallel the insert statement sometimes runs fast.

Environment : virtual Windows 2012 R2 SQL 2014 Enterprise Memory : 120Gb, max sql memory : 112GB vCPU's : 10

Initially i thought the issue might be with the memory because of the PageIOLATCH_SH and PLE going down at the same time and after increasing the memory the performance is still bad.

I'm thinking the amount the records they insert might be different for each days but app team specified that they are almost same ?

Do you think is there any issue with the Vmware environment, i'm thinking may be something with VM NIC card limitation ? our infratructre team already checked the disk performance and it looks good.

Execution plan for the insert statement looks totally good.

Can anyone shed some light on this ?

  • Does the table you are inserting into have a clustered index defined? If so, check where you are inserting data into the clustered index. If you are putting on the end only, it should run quickly. If you are inserting it into the middle of the clustered index... this can make it run very slowly.
    – blobbles
    Oct 27, 2016 at 19:56
  • Can you run an extended events session or server side trace to determine if you are being blocked by another transaction?
    – Tom V
    Nov 2, 2016 at 13:44

2 Answers 2

  1. You may have a disk bottleneck. Run a Perfmon.exe recording specifically you want the individual "average disk read wait queue" and "average disk write wait queue" of all drives. Perfmon is part of windows functionality and is on all Windows PC / servers / etc. You will want a recording from a weekday and a recording from the weekend.

  2. Are you running DBCC database integrity check on weekday nights? While I want this running every night, it causes to much performance problems so to live in reality, mine only runs on weekends.

  • we have setup the perfmon trace and results shows nothing major except some latencies in tempdb and user database data files. We will look into this but insert runs slow even after the completion of dbcc or backups or index maintenance. Not sure why it is running slow. Am i missing any particular area ?
    – Robin16
    Nov 1, 2016 at 21:50

Two areas that I would suggest you investigate.

One, check when your statistics are updated and if your indexes are fragmented. When you do many DUI, ( delete, update, inserts) Indexes can get very fragmented which will slow down and operations on the table in question. As you add more data, the more the indexes get fragmented and the Database Engine / storage engine has to work harder to adjust the data pages to insert your data. If you don't have anything in place, check out Ola Hallengren's maintenance scripts for statistic and index rebuilds.

The second area would be parameter sniffing. If the data changes constantly and more information is added, the original execution plan may no longer be optimal to use with the insert query that is coming in. A way to test this would be to run sp_recompile for that stored proc to let it recompile the execution plan or tack on WITH RECOMPILE to the end of the insert statement. If you do the second it will up CPU time each time it is run, so I would use that last.

  • Our perfmon results still doesnt show much information, i have manually updated the stats it ran fast today but still it doesn't make sense to me as it runs fast during weekends when stats were stale. Anyone, think that some kind of load in ESX host might be causing the slowness ?
    – Robin16
    Nov 2, 2016 at 21:43
  • There are many ways of troubleshooting this and there are many scripts to run to find out what is going on. I would recommend Brent Ozar's toolkit available from GITHUB and use SP_BlitzFirst, SP_BlitzIndex and sp_BlitzCache. Brent Ozar's First Responder Kit Nov 3, 2016 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.