0

On my development server I delete all data from two tables then pull fresh data from the production server via Delete From then Select Into. It's only about 100,000 rows total. On dev I run a routine that inserts and updates many rows within a transaction. The transaction seems to never end so I click the stop button in SSMS then execute just the word Rollback. Now I re-run the same insert/update code and it finishes in a split second. How can I avoid the first run from being so slow? Why is this happening? I'm concerned that I'll encounter the same problem when I run the code live. As you may guess I'm a developer and have no DBA to discuss this with. Thanks in advance.

DeveloperDan
  • 109
  • 2
  • 1
    Does this answer your question? Why is my query suddenly slower than it was yesterday? – John K. N. Jan 12 '22 at 14:19
  • On a guess, not quite enough info to be sure, you're seeing lots of statistics creations & updates, followed by statement level recompiles. The rollback stops the transaction, but all the work on stats is done, and possibly the execution plan is done, so the second execution uses that stuff. – Grant Fritchey Jan 12 '22 at 14:30
  • 2
    Understanding the waits will be key. Something like this: https://www.scarydba.com/2018/02/05/wait-statistics-query/ or this: https://www.scarydba.com/2017/12/18/query-store-wait-statistics-sys-query_store_wait_stats/ Will give you more data and you can understand what's happening. – Grant Fritchey Jan 12 '22 at 14:31
  • The comments and links give me a lot to look into, but my biggest concern is will the routine be similarly slow when run live? Will I have to stop the transaction and rollback in our live environment? I'm guessing, no. But it concerns me. – DeveloperDan Jan 12 '22 at 14:35
  • 1
    We can't say that unless we know why it took that long for the first transaction. It might have been blocking, autogrow of ldf file, stats creation, or something else. This might or might not happen in production. I.e., we quite simply don't have anything to go on here... – Tibor Karaszi Jan 12 '22 at 17:47

0 Answers0