SQL Server Performance Forum – Threads Archive
create vs update statistics????
Hello, I am new to DBA area and I have database with big table about 10Million rows. We are facing some performance bottel neck for one of our report. I would like to find out how statstics works as there are indexes created on the table and being used but statstics are updated weekly. The real problem is as this is archive table we upload thousands of row every night and remove thousand (as it keeps only 60 days worth of data). My questions are – Does this affect stats for indexes?– What is the difference between update stats and create stats?
– Would we be better of droping the stats and create every night or weekly update stats would help?
– Is there way to update stats for new rows and deleted rows every night? This is on SQL server 2000 with SP4 Thanks for your help in advance.
Based on the activity on the table you must perform UPDATE STATISTICS on day to day basis during quieter times of access. – Create Stats is nothing but creating the statistics based on set of columns.
– Update stats is nothing updating the creates stats on a particular table. All indexes have distribution statistics that describe the selectivity and distribution of the key values in the index. Selectivity is a property that relates to how many rows are typically identified by a key value. The distribution statistics are used to estimate how efficient an index would be in retrieving data associated with a key value or range specified in the query. Further to achieve optimum performance for the query optimizer, distribution statistics must be kept reasonably current. The distribution statistics should be refreshed anytime significant numbers of changes to keys occur in the index. So Update statistics would come into the picture here, based on the changes happening on a particular table it is better to schedule it as a job. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks Satya for quick response.
Satya, One confirmation so what you are saying is that if I don’t create stats on the table and col and if I run update stats than it will create stats or I have explicitly create stats on the col first than I can schedule update stats on regular basis? Do I have to drop stats and create from scratch i.e. fragmentation for data files, is there fragmented stats? Thanks!
Yes, the selectivity is based and key values of the index. KBAhttp://support.microsoft.com/kb/195565 fyi and you could also check out Kalen’s excellent book Inside SQL Server 2000.
Run DBCC SHOWCONTIG to see what is the fragmentation and alsohttp://www.sql-server-performance.com/sj_detect_fragmentation.asp for more information. http://www.sql-server-performance.com/statistics.asp fyi on tips. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks Satya.
]]>