User Statistics Removal

Question

User Statistics Removal

Chandhini

Ten Centuries

Points: 1243
More actions
October 23, 2009 at 1:24 am

#212515

Hi,
I could see some statistics are available in my tables started with '_WA_Sys_ .....'.
The statistics section has index statistics and these indexes for each column.
Is it mandatory to have all of these ( '_WA_Sys_ ). or can we remove them?
if it is removed, is there any performance issue will come?
where these statistics are stored? in Temp DB?
Thanks in Advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andrew Gothard-467944 Hall of Fame Points: 3908 More actions · Answer 1

The statistics are created when you have Auto Create Statistics set to on. They are used by the optimiser when it's working out which way of running a query has the lowest cost. If you delete then they will just be recreated (with associated overhead) when the optimiser needs the stats to generate a query plan.

The following link covers the optimiser's use of Stats

http://technet.microsoft.com/en-gb/library/cc966419.aspx

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Chandhini (10/23/2009)
Is it mandatory to have all of these ( '_WA_Sys_ ). or can we remove them?
if it is removed, is there any performance issue will come?

You can remove them, but having accurate statistics is essential for the optimiser to come up with good execution plans. If you remove them then, assuming that auto_create_statistics is on for the DB, the optimiser will request that they are recreated next time it needs them. That means that the query that needs them will have to wait a couple seconds for the stats to be created before the optimiser generates an exec plan and lets the query run.

Why do you want to delete them?

where these statistics are stored? in Temp DB?

In one of the system tables in the database itself. Not sure which system table, haven't been able to find the statsblob yet on 2005+. On SQL 2000 it was stored in sysindexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chandhini Ten Centuries Points: 1243 More actions · Answer 3

Thanks a lot for your responses.

i thought it consumes memory so that we can get rid of them.

Thanks.