SQL Statistics - Auto Create and Auto Update

SQL statistics includes histogram which display the distribution of values and it also contains densities, information of the correlation statistics between multiple columns. These information are used by query optimizer to estimate the cardinality (number of rows) of the query result and determine an optimal query plan for better query performance based on the estimation. As a result, creating and maintaining the statistics efficiently and accurately become a critical task.

DBCC CHECKDB and BACKUP with CHECKSUM

I came across a few forums and there are several questions regarding the BACKUP with CHECKSUM and DBCC CHECKDB. People wonder if the BACKUP with CHECKSUM can replace DBCC CHECKDB. Let's find out more about what these commands actually do.

SQL Database Last Known Good DBCC CHECKDB

Database integrity check is one of the most important maintenance task in database. Most of the time, we either setup a maintenance plan or develop a custom script and scheduled it to run from SQL agent job. If we want to know if DBCC CHECKDB ran successfully, we can examine the Agent job history or log. But there is another (or better) way to look up when is the last time DBCC CHECKDB successfully ran.

DBCC SHRINKFILE TempDB

If you try to run DBCC SHRINKFILE command after a hung DBCC SHRINKFILE session (e.g. you cancel it in the middle of the shrink process) or you are shrinking the database in the middle of backup, you may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

SQL Index Maintenance - Reorganize and Rebuild Index

SQL Server database indexes are created mainly to optimize performance. Different types of indexes such as clustered index, non-clustered index are put in place to make query process run faster. However over time, DML statements (insert, update, delete) cause information scattered across database hence resulting table or index fragmentation. Fragmentation happen when the data logical order does not match with physical ordering inside the data file. These fragmentation need to be addressed according as it could significantly affect SQL Server performance.

SQL Detect Fragmentation With DMV

Dynamic management view (DMV) sys.dm_db_index_physical_stats return size and fragmentation information for data and indexes for table and view. Here is some of the details about the DMV and the script I use to identify fragmentation.