September 22, 2005 at 2:51 pm
Using Performance Monitor I found that for a period >1 hour our Sql server 2000 ADQL was pretty big (>200). Sometimes it was so big while almost nothing was running on server (EM- CurerntActivity- ProcessInfo). What I can do to identify the reason for so big ADQL (currently it's OK or has a spikes for a short periods)? Thanks
September 22, 2005 at 4:02 pm
You can start by running sp_who2. This gives you an idea about which process is using disk time. If one really sticks out and you need more detail you can run a trace and filter by the database and/or SPID to find out exactly what it is doing. Make sure you use at least the RPC completed and Batch Completed events and the Reads and Writes data columns in your trace.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 22, 2005 at 4:24 pm
Thanks. But, probably, sp_who will return same info as ProcessInfo from EM and as I already mentioned, sometimes almost nothing is running but ADQL is pretty big. Profiler is runnibg also (use Sql:BatchCompleted) but didn't find something unusual. Thanks
September 22, 2005 at 4:34 pm
Are you running a SAN? Maybe another server is using the disk capacity. Or it might be a windows process rather than a SQL process?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 22, 2005 at 4:40 pm
I am not sure regarding SAN- will check tomorrow. Another server or windows process- what I need to pay attention for first? Thanks
September 23, 2005 at 1:04 am
The easiest way to see other windows processes use of I/O is to use Task Manager, under the View menu choose Select Columns and check the I/O Reads, I/O Writes, and I/O Other. Then look at the Processes tab and check the "Show Processes from all users" box in the bottom left. Now you should get a pretty good idea of which process is using the most I/O.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 23, 2005 at 7:12 am
Thanks a lot.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply