Pros and Cons of Using SQL Server Table Indices
For almost every application, high performance and quick reaction times are customer demands. When working with zenon Analyzer and big databases full of SQL evacuated zenon archives with Gigabytes of entries per table, SQL Server table indices can have a huge influence on report performance. This blog entry contains some pros and cons of using such indices for zenon evacuated data to help decide whether to create those indices or not.
Query and Store Procedure Performance
If there is a usable index on the table, the query performance and therefore the stored procedure performance can be improved dramatically. As we explore the following scenario, it will become clear what improvement is possible. Let’s say there is an archive data table with data for dozens of variables for several years. The table size on the hard disk is 10 Gigabytes. The stored procedure needs data for one variable out of one month, which means that only a few Megabytes need to be read. Without a proper index, the whole table (10 GB) has to be read to get the required data. When there is a clustered index on the timestamp and variable columns, only a few 100 Kilobytes of index data have to be read to know where the required data resides and then only the required data is read. It is clear that reading a few Kilobytes and then a few Megabytes is much quicker than reading 10 Gigabytes.
As insert operations in the table also have to update the index, the insert performance might be reduced after creating indices on the table. Concerning SQL evacuated zenon data, most insert operations occur with a timestamp higher than the highest existing timestamp in the table, which means that the impact on the insert performance will be minimized when the table has a clustered index on the timestamp column with ascending order. COPA-DATA can provide indexing best practices for the optimum SQL connector performance and minimal impact on the insert performance.
As indices are additional information about a data table, they require some space to store the information. Basically, indices are a tree structure, so the level which consumes most disk space is the leaf level containing the pointers to the actual table rows. As clustered indices use the table itself as leaf level, additional space is only required for the branch and root levels of the tree, which is usually a fraction of the space consumed by the table.
Index Creation Performance
How long it takes to create an index depends on the size of the table to be indexed, on the hardware details of the database server and on other tasks running on the same server. If the database server has a constant flow of insert and query statements on the table to be indexed, indexing the table will need more time. Also creating a clustered index is more time consuming, as the complete data in the table has to be ordered according to the index settings.
Considering index maintenance, there are three facts to consider:
- External fragmentation can have a negative influence on the performance, as the index pages are spread over the hard disk and I/O operation become slower as it seeks the fragments. It is recommended that you rebuild an index when its external fragmentation is higher than 5 percent.
- Internal fragmentation can have a negative impact on performance, as there is free space in the index pages that inflate the page count of the index and lead to more I/O operations. It is recommended to reorganize an index when its internal fragmentation is higher than 5 percent and to rebuild it when its internal fragmentation is higher than 30 percent. But on the other hand, internal fragmentation can have a positive impact on insert operations in the table, as there is free space in the index pages that can be used (instead of allocating new index pages which might lead to external fragmentation) when the index needs to be updated after the insert operation.
- Index statistics are used by the query optimizer to decide whether an index should be used and which index should be used. SQL Server automatically updates index statistics as well as every reorganization and every rebuild of an index.