MSDN Query Performance SectionStatistics
See here:
MSDN sql 2k statistics
and
sql 2005 stats
First see what stats exist for a given table
sp_helpstats 'olap_queue_ldn', 'ALL'
Then use DBCC SHOW_STATISTICS to retrieve detailed information:
DBCC show_statistics('olap_queue_ldn',_WA_Sys_Status_3C91E890)
UPDATE STATISTICS 'olap_queue_ldn'
WITH
* FULLSCAN forces a complete read of every row of data.
* SAMPLE lets you specify how much of the data SQL Server should sample in building statistics. By default, it will figure out its own sampling coverage.
* RESAMPLE uses the same sampling rate as the last time the statistic was gathered.
* ALL, COLUMNS, or INDEX specify whether the update should be to all statistics, column statistics, or index statistics. The default is all statistics.
* NORECOMPUTE turns off automatic statistics gathering for this table or statistic.
This is all doable through management studio statistics node of a table.
AUTOSHRINK
Autoshrink is bad - it can re-order and massively frangment indexes. In an example in a fresh db with 2 tables, after 1 is removed then the db shrunk, the index went from almost 0% fragmented to almost 100% fragmented in the remaining table– the shrink operation totally reversed the physical ordering of the leaf level of the clustered index – nasty.
One of the common maintenance operations is to run a database shrink at some interval, and I always advise against it – now you can see why. Running a regular shrink operation can cause horrible fragmentation problems. The worst problems I see are those customers with maintenance plans that rebuild all indexes and then run a shrink to remove the extra space necessary for the index rebuilds – completely undoing the effects of the index rebuild!
PROFILING
The best way to check out a stored procedure is to run it in Management Studio while running a Profiler trace which captures Performance: Show Plan All (or XML) and SP: Statement Completed. Put a filter on the SPID for your Management Studio connection, and run your stored procedure.
MSDN Query Plan Operators