Sql-server – SQL Server 2008 R2 Express high insert slowdown possibly due to PAGEIOLATCH_SH

performancesql-server-2008-r2

I have a program that insert row to a table though multiple connections. For each insert, the connection would open and then close. When I run the program, the insert were fast. However, once it reach about 3000 rows or so, SQL server start slowing down. Eventually it become 30x slower than it should be.

The insert statement is NOT a stored procedure, by simply dynamic sql every time when insert into the database.

This is a SQL Express, so memory utilization is limited. CPU utilization is low when running the insert statements.

If I kill the application and run the process again, the insert will be fast again, for the first few minutes until it become a snail again.

When I ran the following SQL

select r.session_id
,status
,qt.text
, r.statement_start_offset/2
, r.statement_end_offset
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
,r.wait_type
,r.wait_time
,r.last_wait_type
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

I notice the query has wait_type of PAGEIOLATCH_SH.

In fact, I notice some other Select statements are having the same problem.

What is PAGEIOLATCH_SH?

What other information should I look for to fix to issue?

Ultimately how do I solve this performance issue?

Thanks

Best Answer

PAGEIOLATCH_SH means a page is read from the disk. There must be more at play in your workload than just INSERTs.

Simply and cheaply put you need a non-Express instance, more RAM and a faster disk. The complex and more expensive answer is you need to fix the application so that it does not read that many pages (ie. it does not do table scans).

Related Question