asked
Where do statistics go when Index is dropped?
I have several large (between 1GB and 8GB) tables in my warehousing process that are currently loaded on a Trunc-Load-Index approach. The sequence of operations is - Drop Indexes - Trunc table - Load Table - Create Indexes Here is how I came by my indexes: These tables are used in INNER JOINs in queries. I analyzed the queries in "Database Engine Tuning Advisor". It gave me some suggestions for indexes and statistics. I followed those suggestions and got some really good results - the queries ran incredibly fast afterwards. My questions are: In a scenario where a 20GB table has multiple ***multi-column inclusive indexes*** and ***multiple statistics***: - What happens to the statistics when a Trunc-Load-Index operation is carried out? - What kind of maintenance do I have to do on these statistics after the indexes are built?
First of all I must state that statistics can be made manually on (a) column/s whether it/they is/are indexed or not. Moreover, when you create index on column/s, respective statistics are created. So Dropping indexes of a table would not always mean that all the statistics for that table would be dropped. Only the statistics for the index/es would be dropped Now, coming to your question 1. For Trunc-Load-Index (Assuming you dropped the indexes before), the statistics of the indexes dropped would be dropped as well. But any other statistics which were made manually will remain there. 2. After indexes are rebuild, the statistics for the index/es would be up to date. But for manual statistics, I personally would update those statistics with FULL SCAN mode. So I would have the most optimal execution plans. Cheers.
sp_updatestats make statistics with a sample of the data. So for skewed data, it may be not good. I personally prefer update statistics with full scan (only stats created manually in your case) after a total reload of data as you do not know whether the sample of data would be good enough to make an optimal plan. Moreover, such data re-loads mean that the data in such tables would remain static most of the times. Hence, update..full scan with (STATISTICS_NORECOMPUTE = ON) seems to be a better option. At the same time, sp_updatestats is less resource intensive than FULL SCAN and will update the whole database statistics (where required). If sp_updatestats suits your environment then fine (where you cannot take the risk of updating stats of a huge table), otherwise, use FULL SCAN method as it gives you more leverage to work on specific statistics. Auto-update-statistics cannot always make sure that your statistics would be up to date. Following is a good link to understand this issue (In SQL Server 2012 it is different)
http://support.microsoft.com/kb/195565
