STATISTICS in MS SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » STATISTICS in MS SQL

STATISTICS in MS SQL

Rate Topic

Display Mode

Topic Options

Author

Message

parthi-1705

Posted Friday, November 20, 2009 11:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 08, 2010 12:43 AM
Points: 106, Visits: 610

Hi

What is STATISTICS in MS SQL ?
What is CREATE STATISTICS?
what is UPDATE STATISTICS?
DBCC SHOW_STATISTICS ?
SP_updatestats

With this how we can optimize our query or time consumption
SET STATISTICS PROFILE { ON | OFF }?
SET STATISTICS IO { ON | OFF }?
SET STATISTICS TIME { ON | OFF }?

pls help me in this.

Thanks

Post #822845

GilaMonster

Posted Saturday, November 21, 2009 2:29 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 16,988, Visits: 12,113

Firstly, statistics as controlled by Create/update/drop/ DBCC SHOW_STATISTICS are completely unrelated to SET STATISTICS XX. The first are column statistics used by the optimiser, the second shows query execution statistics.

Some links (that you could easily have found with Google)
CREATE STATISTICS - http://msdn.microsoft.com/en-us/library/ms188038%28SQL.90%29.aspx
UPDATE STATISTICS - http://msdn.microsoft.com/en-us/library/ms187348%28SQL.90%29.aspx
sp_updatestats - http://msdn.microsoft.com/en-us/library/ms173804%28SQL.90%29.aspx
DBCC SHOW_STATISTICS - http://msdn.microsoft.com/en-us/library/ms174384%28SQL.90%29.aspx

SET STATISTICS XX -
http://msdn.microsoft.com/en-us/library/ms190287%28SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms184361%28SQL.90%29.aspx

Finally, there's a whitepaper on how SQL uses statistics. Long, fairly deep but good - http://technet.microsoft.com/en-us/library/cc966419.aspx and I did a presentation on Statistics at the SQL PASS Summit couple weeks back. The deck's up on my blog. http://sqlinthewild.co.za/index.php/resources/

If you still have questions after reading through all that, some back and ask.

Gail Shaw

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

Post #822864

« Prev Topic | Next Topic »

Permissions