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?