Jon.Morisi (4/30/2013)
Hi,I have a system with a lot of blocking. From the last occurrence I found a statistics update causing a lot of the blocking:
SELECT StatMan([SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], step_direction([SC0]) over (order by NULL) AS [SB0000] ...I went to the table in question and found a lot of statistics with the dta prefix meaning that someone had let DTA auto create a lot of statistics at some point.
I want to verify that these are actually being used. If I have a bunch of unused statistics which are resulting in blocking whenever auto stats update runs, I'd like to drop the stats.
Unfortunately I don't know how to check if my statistics are being used. Any ideas?
The update statistics process should be using shared locks, so I would not expect that to be the issue. However, there are circumstances where a new query is blocked, because it is waiting for update statistics to complete, so it can build an execution plan. I could see this issue being more prevalent on a large table that is updated frequently and has an extreme number of statistics.
There is a database option called AUTO_UPDATE_STATISTICS_ASYNC that may help in your situation.
http://weblogs.sqlteam.com/tarad/archive/2008/06/16/Asynchronous-Update-Statistics.aspx
Alternatively, consider turning off Auto Update of Statistics database option, and then schedule a job or maintenance plan to manually update statistics during non-peak hours.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho