Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.

OLEDB Wait Stats in SQL Server 2005

Over the past several months, I’ve started diving into the world of wait stats on my instances. An ever present phenomenon is that there are always a ton of waits with a wait_type of “OLEDB”. On my production servers, this count is always the highest and generally eclipses the closest second by a factor of two or three.

I was initially concerned, so I asked the “World’s Smartest DBA” (aka Google) and found little to help. I read the description from the horse’s mouth (http://tinyurl.com/3d2hym) and saw nothing too horrible nor helpful. Eventually, I chose to ignore it as an anomaly and went on in my blissful ignorance.

That was, until attending the PASS Summit. I had the pleasure of attending the “Corruption Survival Techniques” session where Paul Randal made a passing statement, which I’m certain most people didn’t even notice, that DBCC CHECKDB uses OLEDB to talk to the storage engine. A light went on and I had to do some tests as soon as I got back to the hotel room.

I took a snapshot of the waits from sys.dm_os_wait_stats. After recording the OLEDB waits, I ran an integrity check on an 8GB database. After the CHECKDB finished, I had racked up an additional 8 MILLION (yes, six zeros) OLEDB waits and a total of 65 seconds of wait time. The total integrity check only took 85 seconds. I ran it again, another 8 million waits.

Thinking I might have some obscure setting that is causing my waits to grow out of control, I asked Paul and he took the time to respond to my request for clarification. To my surprise, this is acceptable as long as CHECKDB isn’t completing in an unreasonable amount of time.

If you’re ever curious to know why OLEDB is racking up so many waits, you should see how many waits your integrity checks are creating. Although other issues can be hidden in OLEDB waits, you may be able to justify this wait stat down to an inconsequential value.

Comments

Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...