Auto Update Statistics
in [Server Configuration]
|
Prev: Pages for Database's Indexes.
Next: How much memory should be free on a Server with SQL Server installed?
From: cbrichards via SQLMonster.com on 4 Aug 2010 18:15 We are running SQL Server 2008 SP2 Enterprises. We have a table with a column that identifies individual clients, such as Client_UID, which is an int data type. So, if the table has 1 million records, as an example, and we have 10 clients represented in this table, let us assume each client has 100,000 records. Each night in this table we loop through the 10 Client_UID's and zero out various financial columns in the table and then refresh those values to begin the day. We are finding this to be problematic and are exploring more optimal methods. One way was updating the table all at once, that is, not looping through the Client_UID's, but doing the UPDATE in one statement. An interesting thing happens regarding Auto Update Statistics when looping through the Client_UID's and updating versus doing the update once taking in all the Client_UID's. When updating the one time, taking in all the Client_UID's, AUTO UPDATE STATISTICS kicks in. That is, I see a bunch of SELECT StatMan entries in my profile trace and the execution takes about 25 percent longer and about 50 percent more reads as compared to the loop method. In the loop method AUTO UPDATE STATISTICS does not fire. I am somewhat familiar with the threshold values that triggers AUTO UPDATE STATISTICS to kick in, but I cannot explain why, in this instance, AUTO UPDATE STATISTICS is firing when the UPDATE takes place all at once, versus incrementally, when the outcome is the same. Any explanation about the AUTO UPDATE STATISTICS behavior? -- Message posted via http://www.sqlmonster.com
From: Erland Sommarskog on 5 Aug 2010 14:59
cbrichards via SQLMonster.com (u3288(a)uwe) writes: > When updating the one time, taking in all the Client_UID's, AUTO UPDATE > STATISTICS kicks in. That is, I see a bunch of SELECT StatMan entries in > my profile trace and the execution takes about 25 percent longer and > about 50 percent more reads as compared to the loop method. In the loop > method AUTO UPDATE STATISTICS does not fire. > > I am somewhat familiar with the threshold values that triggers AUTO > UPDATE STATISTICS to kick in, but I cannot explain why, in this > instance, AUTO UPDATE STATISTICS is firing when the UPDATE takes place > all at once, versus incrementally, when the outcome is the same. I don't really have an idea. I suspect that there is some detail missing. When you have an empty table, autostats first kicks in after 500 rows. From there, autostats sets in when 20% of the rows have been modified. This is measure from some rowmodctr. This has the effect that big tables with a monotonously growing key where queries goes against the trunk have their statistics updated far too rarely. Whereas small tables where all rows are updated frequently, or where or rows are added and deleted, have their stats updated very often, which may cause recompilation issues. Have you looked at this white paper: http://technet.microsoft.com/sv-se/library/cc966419%28en-us%29.aspx There may be some more details there which I don't recall right now, but which may explain what you are seeing. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |