oledb wait type

We have a production server that's cpu is throttled, and requests are timing
out. After running the get_waitstats sp, OLEDB is at the top of the list in
wait type (99%).
How do I determine which spid is involved in an oledb wait type?
This is a Windows 2000 sp 3 box, running sql 2000.If you query master..sysprocesses, you can get the spids,
wait types and wait times.
If you want to see what the spids are actually executing,
pre-SP3, you can use:
dbcc inputbuffer(spid)
If you are on SP3, you can use:
declare @.Handle binary(20)
select @.Handle = sql_handle
from sysprocesses
where spid = <spid>
select * from ::fn_get_sql(@.Handle)
-Sue
On Mon, 7 Feb 2005 14:51:02 -0800, Ken
<Ken@.discussions.microsoft.com> wrote:

>We have a production server that's cpu is throttled, and requests are timin
g
>out. After running the get_waitstats sp, OLEDB is at the top of the list in
>wait type (99%).
>How do I determine which spid is involved in an oledb wait type?
>This is a Windows 2000 sp 3 box, running sql 2000.
>|||OLEDB waittype is an overloaded waittype to begin with, it is set for:
* linked server calls
* bulk insert
* running SQL Trace
* running 6.x to 7.0 or 2000 conversion imports
* materializing virtual tables like select * from master.dbo.sysprocesses
and select * from master.dbo.syslockinfo
Also this wait state is set differently, since it is set when the call is
entered and unset when the call is done, so when you start a BULK INSERT it
will be set, when you are done it is unset, bt you are not really waiting,
you are doing real work, only inside an OLE DB provider, for example BULK
INSERT is hosted in the IMPROV.DLL provider, SQL Trace and materializing
virtual tables are internal providers.
In other words, high OLEDB waitstats are not very often a problem.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4utf015ilc6kaha46e8c5pme6972q7gdo7@.
4ax.com...
> If you query master..sysprocesses, you can get the spids,
> wait types and wait times.
> If you want to see what the spids are actually executing,
> pre-SP3, you can use:
> dbcc inputbuffer(spid)
> If you are on SP3, you can use:
> declare @.Handle binary(20)
> select @.Handle = sql_handle
> from sysprocesses
> where spid = <spid>
> select * from ::fn_get_sql(@.Handle)
> -Sue
> On Mon, 7 Feb 2005 14:51:02 -0800, Ken
> <Ken@.discussions.microsoft.com> wrote:
>
>