The ability to create and update statistics is a crucial liberty for the query optimizer. The optimizer needs to know how it is going to query underlying tables, and when the statistics do not match the data, it is very probable that it will choose a non-efficient method for querying. I’ve seen and heard a [...]
Archive of posts filed under the Query Optimizer category.
Using DBCC UPDATEUSAGE
When DBCC UpdateUsage is run, page and row counts are updated. When this information is incorrect, it can give incorrect reports for how much space is used by tables and indexes. The most likely cause for incorrect usage stats is when a database is upgraded to a new compatibility level of SQL Server (i.e. 2000 [...]
SQL Server Join Algorithms
If you read execution plans enough, you’ve probably realized that when SQL Server joins tables together, it uses different internal algorithms. The three algorithms are: Loop Join Merge Join Hash Join These alogorithms that are used are based upon factors of the underlying data. Merge Join For the most part, this is the most efficient [...]
Using DBCC FREEPROCCACHE
The DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. This command should be used with caution, however in most circumstances it will not bring a server to its knees. In order to execute in SQL 2005+, you need to have the server-level permissions of ALTER SERVER STATE. To use [...]
Find Missing Table and Index Statistics
SELECT
Last_Updated = STATS_DATE(si.id, si.indid)
,TableName = object_name(si.id)
,Name = RTRIM(si.name)
,Size = DATALENGTH (si.statblob)
FROM
sysindexes si WITH (nolock)
WHERE OBJECTPROPERTY(si.id, N’IsUserTable’) = 1
–AND INDEXPROPERTY (si.id , si.name , ‘IsAutoStatistics’ ) = 0
order by last_updated, tablename
