What is Update Statistics 1

[Ovatvvon]

What exactly is Update Statistics? What do the statistics do for the database / how does updating them help?

Is this part of the optimization job / maintenance plan within SQL Server, or a separate action?


-Ovatvvon :-Q

 

[mrdenny]

The Statistics are what the SQL Server uses to generate query plans. Query plans are how SQL Server finds the correct path to the data. When the statistics are out of date SQL Server doesn't know how to get to the data fastest. When it takes a slower route the system runs slower.

Updating Statistics isn't done via the standard maintenance plan. Typically the stats don't need to be manually updated, but they can go bad in a very high load system, or in a system which is all updates and few to no inserts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

Typically the stats don't need to be manually updated, but they can go bad in a very high load system, or in a system which is all updates and few to no inserts.

Does this mean that inserts to the database would automatically update the stats?


-Ovatvvon :-Q

 

[Ovatvvon]

Also, any estimate on how long a 12 GB database may take to run update stats on?





-Ovatvvon :-Q

 

[mrdenny]

SQL automatically updates the stats on the table every x% or over few hundred inserts (I don't remember the exact numbers).

It could take an hour, depending on what kind of load the system has on it while it's running.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

Thanks for the info! =)

-Ovatvvon :-Q

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com