Thursday, March 30, 2017

Disable Indexes and Constraints in SQL Server

We know that Indexes can help queries to find data quickly in a database in SQL Server where they provide opportunities for much more efficient use of I/O because of having great potential. Downside to using an index is the performance implication on data modification statements means any time a query modifies the data in a table (INSERT, UPDATE, or DELETE) then the database needs to update all of the indexes where data has changed.
After an index is disabled in SQL Server, it remains in a disabled state until it is rebuilt or dropped.

To improve the other operations (INSERT, UPDATE, or DELETE) performance on the data table, it becomes an urgent requirement to disable or drop indexes on that table. If we are going to disable any index then we should be awarded of the following limitations and restrictions of this process –
  • SQL Server does not maintain a disabled index as well as the SQL Server Query Optimizer does not consider any disabled index in the query execution plans.
  • If any existing queries that reference these disabled indexes with a table hint must be failed.
  • SQL Server does not allow us to create an index that uses the same name as an existing disabled index and will not be allowed to drop any disabled index.
  • After disabling a unique index, all constraints such as PRIMARY KEY or UNIQUE KEY constraint and all FOREIGN KEY constraints that reference that indexed columns from other tables are also disabled.
  • After disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. 

Note: The constraint names are listed in a warning message when the index is disabled.
After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.
The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.
  • All depended nonclustered indexes are automatically disabled when the associated clustered index is disabled and cannot be enabled until either the clustered index on the table or view is enabled or the clustered index on the table is dropped.
  • Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.
  • After disabling a clustered index on a table, SQL Server automatically disables all clustered and nonclustered indexes on views that reference that table.
  • SQL Server helps us to rebuild a disabled nonclustered index online when the table does not have a disabled clustered index. Though, we must always rebuild a disabled clustered index offline if we use either the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING statement.
  • SQL Server does not allow to CREATE STATISTICS statement on a table that has a disabled clustered index. In this case, AUTO_CREATE_STATISTICS database option creates new statistics on a column when the index is disabled.

How to disable index on a table?
Before disabling any index, you must have at least ALTER INDEX permission. There are two ways to disable index in SQL Server such as through SQL Server Management Studio and T-SQL statements-
By using SQL Server Management Studio, we need to expend our index folders of the data table in the database as given below-

After expending the index folder, right click on the index and choose Disable. It will be launched Disable Indexes window where we need to click on OK button as given below-

By using Transact-SQL - In this scenario, we have to disable a particular index or all indexes on a table as given below-  
USE DEMO; 
GO 
-- Disables IndCustItems index 
-- on the dbo.TB_SalesOrders table 
ALTER INDEX IndCustItems
ON dbo.TB_SalesOrders
DISABLE; 

-- Disables all indexes on dbo.TB_SalesOrders table
ALTER INDEX ALL
ON dbo.TB_SalesOrders
DISABLE;

Conclusion
After disabling the index, it definition remains in metadata, and index statistics are kept on nonclustered indexes and after rebuilding the index, any constraints that were disabled because of disabling the index must be manually enabled.  Disabling a nonclustered or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.
Reference:https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints

Wednesday, March 22, 2017

ColumnStore Index Architecture in SQL Server 2016

Microsoft development team has been stated that Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. Columnstore indexes reduce IO and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.
ColumnStore index is one of the best features in SQL Server 2016. Before introducing this, all indexes have B-Tree structures which use rowstore for the data storage but ColumnStore indexes use both types (rowstore and columnstore) of data storage.
How does Data Storage work?
There are two compression techniques for data storages such as columnstore and rowstore which are used to reduce the IO required to execute analytics queries and therefore improve query performance.
Columnstore Data Storage – In this storage, data is logically organized as a table with rows and columns but physically stored in a column-wise data format.
A columnstore index physically stores most of the data in columnstore format. In columnstore format, the data is compressed and uncompressed as columns.
There is no need to uncompress other values in each row that are not requested by the query. This makes it fast to scan an entire column of a large table.
Rowstore Data Storage - rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data such as a heap or clustered B-Tree index.
A columnstore index also physically stores some rows in a rowstore format called a deltastore. The deltastore, also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Each delta rowgroup is implemented as a clustered B-Tree index.
deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. The deltastore is a rowstore.

References: https://msdn.microsoft.com/en-us/library/mt793289.aspx

Popular Posts