Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Mar 22 '12 at 9:43
add comment

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

share|improve this answer
    
It does looks like I don't have enough to read the pages. I will install SQL Developer and compare the performance –  dsum Mar 22 '12 at 15:30
add comment

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.

share|improve this answer
    
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 Mar 22 '12 at 15:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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