It is so that nonclustered index entries can point to exactly one specific row.
Some people may argue they can create clustered index upon non-unique column and without specifying unique keyword. That's true. In that case, SQL Server adds hidden unique identifier column to the rows when necessary.
Since clustered index keys are included in every nonclustered index, so that clustered index keys are the most duplicate and redundant data in a table. The choose of clustered index keys should be narrow so that it won't take too much storage.
Clustered index keys should also static so that it won't cause data relocation and page split, as well as updating nonclustered indexes.
Unique, narrow and static are also characters when a PK is defined.
Friday, November 16, 2012
Duplicate Index
A index is duplicated if its tree structure and usage are the same as another one on the same table.
sp-helpindex can't tell you if indexes are really the same on tree structure and usage.
(
Clustered index is the data.
Non-clustered index is duplicated data. These duplicated data helps SQL Server efficiently find the real data.
)
Non-Clustered index Structure:
. Key
. A leaf level entry(actual data stored in index+lookup values+included columns)
Here lookup values are either clustered index key if table has clustered index or RID if table is a heap. It's used to look for actual data row.
A RID is an 8-byte structure consisting of 2:4:2 bytes which breakdown into 2 for the FileID, 4 for the PageID and 2 for the slot number.
For non-unique non-clustered index, clustered keys are stored in both its tree and leaf nodes.
For unique non-clustered index, clustered key are stored only in its leaf nodes.
(A nonunique nonclustered needs to have the lookup value pushed up into the tree (for navigation). A unique nonclustered index does not.)
Sequence of included columns does not matter on usage of index so that difference on the sequence will be ignored.
Credit to Kimberly. Understanding Duplicate Indexes
sp-helpindex can't tell you if indexes are really the same on tree structure and usage.
(
Clustered index is the data.
Non-clustered index is duplicated data. These duplicated data helps SQL Server efficiently find the real data.
)
Non-Clustered index Structure:
. Key
. A leaf level entry(actual data stored in index+lookup values+included columns)
Here lookup values are either clustered index key if table has clustered index or RID if table is a heap. It's used to look for actual data row.
A RID is an 8-byte structure consisting of 2:4:2 bytes which breakdown into 2 for the FileID, 4 for the PageID and 2 for the slot number.
For non-unique non-clustered index, clustered keys are stored in both its tree and leaf nodes.
For unique non-clustered index, clustered key are stored only in its leaf nodes.
(A nonunique nonclustered needs to have the lookup value pushed up into the tree (for navigation). A unique nonclustered index does not.)
Sequence of included columns does not matter on usage of index so that difference on the sequence will be ignored.
Credit to Kimberly. Understanding Duplicate Indexes
Tuesday, November 13, 2012
Identifying Unused Indexes in Database
Some indexes are never used, some indexes become useless(are forgotten by optimizer) when new indexes are created. In SQL Server 2005 and above, we have a simple way to identify those indexes no longer useful. Thanks to the very useful DMV sys.dm_db_index_usage_stats.
Limit: SQL Server has to run for a while to experience most of the regular work load in a database.
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName ,
OBJECT_NAME(i.object_id) AS TableName ,
i.name ,
ius.user_seeks ,
ius.user_scans ,
ius.user_lookups ,
ius.user_updates
FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.indexes AS i ON i.index_id = ius.index_id
AND i.object_id = ius.object_id
WHERE ius.database_id = DB_ID()
AND i.is_unique_constraint = 0 -- no unique indexes
AND i.is_primary_key = 0
AND i.is_disabled = 0
AND i.type > 1 -- don't consider heaps/clustered index
AND (
( ius.user_seeks + ius.user_scans +ius.user_lookups ) < ius.user_updates
OR
( ius.user_seeks = 0 AND ius.user_scans = 0)
)
Limit: SQL Server has to run for a while to experience most of the regular work load in a database.
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName ,
OBJECT_NAME(i.object_id) AS TableName ,
i.name ,
ius.user_seeks ,
ius.user_scans ,
ius.user_lookups ,
ius.user_updates
FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.indexes AS i ON i.index_id = ius.index_id
AND i.object_id = ius.object_id
WHERE ius.database_id = DB_ID()
AND i.is_unique_constraint = 0 -- no unique indexes
AND i.is_primary_key = 0
AND i.is_disabled = 0
AND i.type > 1 -- don't consider heaps/clustered index
AND (
( ius.user_seeks + ius.user_scans +ius.user_lookups ) < ius.user_updates
OR
( ius.user_seeks = 0 AND ius.user_scans = 0)
)
Monday, November 12, 2012
Left most index key
It should be most selective(more distinct values) because this reduces the number of database pages that must be read by the database engine while traversing the index, in order to satisfy the query.
Keep in mind that SQL Server's index has an entry for each of the row in underlying table, no matter they are unique or duplicate.
Keep in mind that SQL Server's index has an entry for each of the row in underlying table, no matter they are unique or duplicate.
About included columns
It can be only used with non-clustered indexes.
The key columns are stored in all level of an index, while included columns are stored only at leaf level of an index.
Typical usage of included columns is to create a covering index, which contains all columns required by a query, either key columns or included columns. It's a way to remove bookmark(key look up)/RID look up in execution plan. (decreasing I/O required to return data.)
Pros:
-- The non-key columns do not count towards the limitation of 900 bytes key size or 16-columns.
--The non-key columns can use data types not allowed by index key columns;
(all data types except the varbinary (max) columns that have the FILESTREAM attribute, the legacy text, ntext, and image are supported.)
-- Major solution to expensive bookmark look up
Cons:
-- Using of included columns will result in higher disk space usage in order to store the index,
-- An increase in I/O demands
-- Lower buffer cache efficiency
-- Reduced performance of data modification operations
Doomed(uncommittable) Transaction
Doomed transactions are introduced with TRY/CATCH implementation introduced since SQL Server 2005. When it happens, the request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction.
XACT_STATE() can be used to detect the situation. And it's suggested to always check its value in CATCH block.
XACT_STATE():
1: has active user transaction.@@TRANCOUNT>0
0: no active user transaction. @@TRANCOUNT=0
-1: Doomed transaction , need rollback
BEGIN TRAN
BEGIN TRY
--or use raiserror function
select convert(int,'abc')
commit tran
END TRY
BEGIN CATCH
if XACT_STATE()=-1
BEGIN
print 'uncommittable transaction, roll it back'
ROLLBACK
END
END CATCH
XACT_STATE() can be used to detect the situation. And it's suggested to always check its value in CATCH block.
XACT_STATE():
1: has active user transaction.@@TRANCOUNT>0
0: no active user transaction. @@TRANCOUNT=0
-1: Doomed transaction , need rollback
BEGIN TRAN
BEGIN TRY
--or use raiserror function
select convert(int,'abc')
commit tran
END TRY
BEGIN CATCH
if XACT_STATE()=-1
BEGIN
print 'uncommittable transaction, roll it back'
ROLLBACK
END
END CATCH
Sunday, November 11, 2012
Some Facts on Error message in SQL Server
@@ERROR function returns the most recent error code. 0 means no error.
Error level is break down to these categories:
1 - 10: warning
11- 16: errors can be corrected by user
17 - 19: more serious exceptions, such as out of memory.
20 -25: fatal connection and server level exceptions.
Who followed that? Even MS does not. So information purpose.
RAISERROR without specifying message id uses 50000 as its message id.
Error level can be overridden by specifying new number other than -1. -1 one means to use error level defined in sys.messages.
When creating custom messages, message number ranges 50000 to 2147483647. The message is added by sp_addMessage procedure. Message text and severity can be updated by also using same procedure with @replace='Replace'. sp_alterMessage can be used to change other parts of message. sp_dropMessage to drop user defined message. Apparently, the first two procedures are not well designed, bear with them and remember them.
Error can be logged to SQL Server's error log by using WITH LOG if invoked by sysadmin or user having ALTER TRACE permission.
Error level is break down to these categories:
1 - 10: warning
11- 16: errors can be corrected by user
17 - 19: more serious exceptions, such as out of memory.
20 -25: fatal connection and server level exceptions.
Who followed that? Even MS does not. So information purpose.
RAISERROR without specifying message id uses 50000 as its message id.
Error level can be overridden by specifying new number other than -1. -1 one means to use error level defined in sys.messages.
When creating custom messages, message number ranges 50000 to 2147483647. The message is added by sp_addMessage procedure. Message text and severity can be updated by also using same procedure with @replace='Replace'. sp_alterMessage can be used to change other parts of message. sp_dropMessage to drop user defined message. Apparently, the first two procedures are not well designed, bear with them and remember them.
Error can be logged to SQL Server's error log by using WITH LOG if invoked by sysadmin or user having ALTER TRACE permission.
Subscribe to:
Posts (Atom)