Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Are my statistics being used? Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 2:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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?
Post #1448229
Posted Tuesday, April 30, 2013 2:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1448239
Posted Tuesday, April 30, 2013 2:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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?
Post #1448245
Posted Wednesday, May 1, 2013 1:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 17, 2014 8:42 PM
Points: 463, Visits: 1,030
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
Post #1448332
Posted Wednesday, May 1, 2013 3:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 2,929, Visits: 2,946
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
www.seavus.com
Post #1448353
Posted Wednesday, May 1, 2013 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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
Post #1448397
Posted Thursday, May 2, 2013 7:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 1,649, Visits: 4,693
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.
Post #1448815
Posted Thursday, May 2, 2013 9:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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


Post #1448869
Posted Thursday, May 2, 2013 9:53 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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
Post #1448876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse