April 30, 2013 at 2:07 pm
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?
April 30, 2013 at 2:39 pm
Jon.Morisi (4/30/2013)
Unfortunately I don't know how to check if my statistics are being used. Any ideas?
Statistics ARE used.
To test the concept, do this.
1- Copy two related tables an write a query that would require a full scan on one and a seek on the other one.
2- Destroy statistics on the copied tables.
3- Check explain plan of the query in both, the set of tables that has statistics and the one that is statisticsless.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 30, 2013 at 2:53 pm
Let me rephrase. I'd like to determine the cost benefit of each statistic on my table. If it's costing a lot to maintain the statistic and not frequently used I'd like to drop it. I can do this with indexes
(user_updates + system_updates) as cost , (user_seeks + user_scans + user_lookups) as benefit from sys.dm_db_index_usage_stats
Is there a way to do this with statistics?
May 1, 2013 at 1:50 am
Usually statistics will be created when you created an index or when a select statement where condition column doesn't have index will also create statistics.
Statistics will occupy some disk space and will not hurt performance issue if your queries are not using them.
Performance will effect only when the optimizer uses the out dated statistics then you need to update the statistics.
May 1, 2013 at 3:06 am
Hi
Statistics can take max up to about 200 rows of data, which means they are tiny in terms of size.
I think you cannot know which statistics were used by the query optimizer. As you say you have additional statistics (not the indexes' ones) you can make a check to see which of them are already covered by the indexes' statistics. In any case multi-column statistics can be useful to the query optimizer. Maybe you can eliminate some of them and not all.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
May 1, 2013 at 6:52 am
Jon.Morisi (4/30/2013)
Let me rephrase. I'd like to determine the cost benefit of each statistic on my table. If it's costing a lot to maintain the statistic and not frequently used I'd like to drop it. I can do this with indexes(user_updates + system_updates) as cost , (user_seeks + user_scans + user_lookups) as benefit from sys.dm_db_index_usage_stats
Is there a way to do this with statistics?
No - unfortunately there is no way to report on stats usage similar to that of indexes.
You can, however, query the stats DMVs and identify/eliminate duplicate stats objects. It's better than nothing - I've found several duplicate stats on live dbs actually. Some from DTA, some from sp_create stats, others who knows...
Cheers,
JohnA
MCM: SQL2008
May 2, 2013 at 7:43 am
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
May 2, 2013 at 9:45 am
if your have sql 2008 or newer version, then
CREATE EVENT SESSION [Monitor Update Stats ] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlos.task_time,sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.transaction_sequence,
sqlserver.tsql_frame,sqlserver.tsql_stack)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%update%statistics%')))
ADD TARGET package0.event_file(SET filename=N'd:\Monitor Update Stats .xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
May 2, 2013 at 9:53 am
Eric M Russell (5/2/2013)
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.
Thanks Eric, I have been looking into AUTO_UPDATE_STATISTICS_ASYNC.
I'm a little hesitant, because it requires a CU to avoid a memory leak, but I think it's my best option at this point:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply