SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are my statistics being used?


Are my statistics being used?

Author
Message
Jon.Morisi
Jon.Morisi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 1142
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?
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5335 Visits: 4639
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.
Jon.Morisi
Jon.Morisi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 1142
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?
@SQLFRNDZ
@SQLFRNDZ
SSC Eights!
SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)

Group: General Forum Members
Points: 889 Visits: 1217
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.

statistics


--SQLFRNDZ

Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5850 Visits: 5080
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
SQLCharger
SQLCharger
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 1420
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
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12076 Visits: 10632
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
SQL Show
SQL Show
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 1078
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
Jon.Morisi
Jon.Morisi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 1142
Eric M Russell (5/2/2013)
[quote][b]
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:
http://support.microsoft.com/kb/2778088
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search