Insert statement hung but select works normally

Question

Insert statement hung but select works normally

JasonO

SSC-Addicted

Points: 484
More actions
March 3, 2020 at 3:44 am

#3730433

I have a question thrown to me by my developers, according to them, they notice that on a high load SQL server, their insert jobs is taking a long time to complete however all their select statement doesn't have any impact at all as the select completed very quickly.
I told them, most select statement will utilize available statistics & indexes to perform the select jobs and normally when SQL uses all properly tuned indexes or even stats it tend to use less I/O thus doesn't have much impact compare to insert jobs. Is it true? Or is there any other detail explanation?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 1

Without more information, it's hard to say. An INSERT requires exclusive access while a SELECT can used shared access, or, if you have it enabled, snapshot copies of the data. This alone can explain why a given INSERT might take longer than a given SELECT. However, there's so much more to it than just that. Are they inserting a single row or large batches? What are the waits on the INSERT statements? Are you seeing blocked processes? All this information can go a long ways towards explaining what's going on.

"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

Your Name Here SSCrazy Points: 2344 More actions · Answer 2

I'm with Grant, this post doesn't have enough information. Among the potential causes are blocking, locks, triggers firing, calculated columns with functions, I/O bottlenecks, page splits, linked servers, circus bears riding bicycles, ...

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

It sounds like you may have a fair number of indexes on your tables. Of course, those can make SELECTs absolutely fly. For INSERTs, that can really slow things down because every INSERT has to update every normal index. Since most indexes are not in the same order as the INSERTs, there can be massive numbers of page splits which cause extra log file usage especially if your index plans make the mistake of using REORGANIZE or they REBUILD indexes with a "0/100" Fill Factor without actually knowing the insert pattern of the index. There's also FKs and all of the stuff that "Your Name Here" mentioned above... well, except for one thing...

The circus bears don't ride bicycles... they ride unicycles. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JasonO SSC-Addicted Points: 484 More actions · Answer 4

Thanks all for the replies!

Unfortunately I don't have much info which I could share. The database itself doesn't have any tracing or monitoring turned on plus this issue happens on & off, so usually when I receive the complains the whole problem already grew soo big that it causes the database to be super duper slow. The easiest & fastest way to recover from this is rebooting the whole sql services since we don't have any tracing, once the DB recovered all the historical data is lost.

The only info I have is from the developer's observation as they're managing the application which connects to the database. The insert itself is a batch insert & what the developer suspect is when 1 of the insert running slow, users keep retrying to insert again thus causes massive queues to the DB. However any select DML task doesn't have any impact during that period.

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 5

Without data, we're talking nothing but guesses, and vague bad guesses at that. Go and turn on monitoring on the server. Set up Query Store. Enable an Extended Events session. Capture wait statistics. Get data.

Imagine a car. Got it? My car isn't running fast. Why? My car ran fast a few days ago, but now it doesn't. What's the cause?

Without information, is there gas, are you towing a mobile home, is the engine in pieces behind the car, is a yugo. You have to have information to make decisions.

"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

Your Name Here SSCrazy Points: 2344 More actions · Answer 6

Random thought to the group: I wonder if he could grab the system trace files, throw them somewhere to take them out of play, then "fn_trace_gettable" them into a table and do some slice-n-dice on the data. I know it's rudimentary and he'd have to filter out a -lot- of white noise but that might give CPU, I/O, the basics. My ears perked up when I read "...users keep retrying to insert again..." but heart sank when I read "...rebooting SQL Server...". Just. No.

BTW: I see what you did there: bears on unicycles, Yugo = "Clown car."