snippetMinor
How can I disable async IO or isolate one session that slows or freezes other sessions?
Viewed 0 times
canasyncdisableoneslowsthatisolatehowsessionother
Problem
Each day I have a session that produces Avg.Disk Queue Length > 800 (5min interval).
I have found that just before this moment Sql Server optimizer have made an error: it decides to use a not-suitable plan with clustered index scan which produces about 100Mb-1GB of IO operations. Let's say that I found the reason for the queue, but it is not the whole story.
IMHO the huge IO should not necessarily freeze other users for 15 minutes. While I don't know who is responsible for creating the excessive asynchronous IO: Sql Server, the Windows filesystem, or maybe the physical IO controller, but the asynchronous IO that is produced causes a hugely abnormal queue, and doesn't leave any chances for other sessions to read from the disk.
Is there any way to control asynchronous IO? I think if I will be able to lower the queue length (btw "more real" current queue length counter shows 400 operations in the queue) those problems with query plans will not freeze other sessions.
However situation is more complex:
UPDATE:
The table is not interesting at all, excepting that it is too big (1 GB with indexes) for NAV.
An example of some of the queries:
```
declare @p1 int set @p1=180
I have found that just before this moment Sql Server optimizer have made an error: it decides to use a not-suitable plan with clustered index scan which produces about 100Mb-1GB of IO operations. Let's say that I found the reason for the queue, but it is not the whole story.
IMHO the huge IO should not necessarily freeze other users for 15 minutes. While I don't know who is responsible for creating the excessive asynchronous IO: Sql Server, the Windows filesystem, or maybe the physical IO controller, but the asynchronous IO that is produced causes a hugely abnormal queue, and doesn't leave any chances for other sessions to read from the disk.
Is there any way to control asynchronous IO? I think if I will be able to lower the queue length (btw "more real" current queue length counter shows 400 operations in the queue) those problems with query plans will not freeze other sessions.
However situation is more complex:
- Those sql queries are generated from NAV 4, so I mostly can't change anything on t-sql level.
- The server (Windows 2003 Data Center) is 32-bit and sql server (data center edition) use AWE to use additional 8 GB for buffer; Therefore there is some possibility that AWE manager flash generates this huge queue.
- There are maximum 50 sessions on the sql server. So I'm ready to see 50 queue length but not 800! BTW normal length is 0.3-4, but it's really normal since I have suitable RAID there.
- This mad session, that generates query with 1GB IO, also generates about 100000 "quick" queries in 40 sec on the same table just before sql optimizer have made an error. So sql optimizer really could be somehow misled by changes in the index selectiveness during previous operations in the same session.
UPDATE:
The table is not interesting at all, excepting that it is too big (1 GB with indexes) for NAV.
An example of some of the queries:
```
declare @p1 int set @p1=180
Solution
A single user can generate more than one queued IO. There is no correlation between the number of users and the number of IOs which are in queue. With the select * in the query, and without being able to tune the query you'll have to resort to a plan guide.
There is no way to control the IO that the SQL Server sends to the IO subsystem other than index tuning. Would it make more sense to move the clustered index so a different column or columns?
A Clustered index scan will generate a lot of IO if the table isn't already in memory, as it needs to read the data from the disk into memory to scan through it, especially if the table is large.
There is no way to control the IO that the SQL Server sends to the IO subsystem other than index tuning. Would it make more sense to move the clustered index so a different column or columns?
A Clustered index scan will generate a lot of IO if the table isn't already in memory, as it needs to read the data from the disk into memory to scan through it, especially if the table is large.
Context
StackExchange Database Administrators Q#1202, answer score: 5
Revisions (0)
No revisions yet.