Query Issues - pageiolatch_sh wait high clustered index insert cost

Question

Query Issues - pageiolatch_sh wait high clustered index insert cost

PebbleRock978

SSC Veteran

Points: 231
More actions
March 25, 2021 at 1:02 pm

#3862010

Hi
Accidental DBA here
We have an application that runs a select * on a table upon starting the application. There are 2.4 million rows in the table. Today it was reporting a pageiolatch_sh wait and took 50 minutes to complete. During this time the application cannot be used until the query completes.
Looking at the execution plan there was a high cost of 95% for a clustered index insert
Not sure where to start looking to resolve this? Happy to provide any additional info
thanks for any help

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

It's really hard to tune a query that returns everything. In fact, you can't. All you can do is throw hardware at the problem. Buy more and faster CPUs, memory, and disks. That's about it.

Instead, get a WHERE clause in there. Filter the data. Eliminate the *. Also, since there's an insert operation going on, sounds like more than just a SELECT query. If you share the query and the execution plan, more detailed answers are possible.

"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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

PebbleRock978 wrote:

Hi
Accidental DBA here
We have an application that runs a select * on a table upon starting the application. There are 2.4 million rows in the table. Today it was reporting a pageiolatch_sh wait and took 50 minutes to complete. During this time the application cannot be used until the query completes.
Looking at the execution plan there was a high cost of 95% for a clustered index insert
Not sure where to start looking to resolve this? Happy to provide any additional info
thanks for any help

It sounds to me like a poor man's attempt at fixing a performance problem by forcing the entire table into memory. I'd ask what the purpose of doing this is and, if it's like I suspect, consider turning it off and seeing what happens in the application and fix the real problem(s).

--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)