1

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

Improve this question
1
  • What's the table structure, index structure, query to insert, execution plan when this happens? You'll need those details in order to figure out what really goes on, without looking outside the SQL Server environment.
    – cairnz
    Commented Mar 22, 2012 at 9:43

2 Answers 2

Reset to default
2

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

Improve this answer
1
  • It does looks like I don't have enough to read the pages. I will install SQL Developer and compare the performance
    – dsum
    Commented Mar 22, 2012 at 15:30
0

Are you sure the connections are being closed cleanly? Check the number of connections on the server with sp_who or on the client with netstat when it has slowed down. Is there any way for you to use the same connections rather than closing?

I would also look at turning off auto update on statistics.

Improve this answer
1
  • I think statistics is on, I checked the connection. The are closed, but there are quite a number of them, something like 16 connections. I thought SQLServer can handle quite a number of connections, but it may be causing many page read for some reason.
    – dsum
    Commented Mar 22, 2012 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.