Sql-server – Updating an indexed column performance impact in SQL server

sql serversql-server-2008sql-server-2008-r2

I am trying to understand is there any performance impact if I am trying to update a column which is the key column of a non-clustered index in SQL server 2008? For e,g

I have a column IsActive in the table TestTable. I have created a non clustered index on column IsActive. Now I am trying to update the value of the column IsActive through an update query like

UPDATE TestTable SET IsActive= 1 WHERE IsActive = 0

I need to understand will this perform better or not having the index on it will be better. I am asking his as reindexing takes place when index vales are updated. So will this reindexing have a impact on the query performance? Note : The table has excess of 100 million rows.

Best Answer

Modifying the value of a field used in an index will certainly take more time than modifying the value of a field not involved in an index. Having said that, the WHERE clause in your UPDATE statement could be used to speed the search for matching rows when the field in question is part of an index. In this example, where you have a boolean field present in 100 million rows means the selectivity of the field is so poor that a table scan will likely be required anyway. I would estimate that updating this particular field would be quicker if there was no index on it.

However, to answer your question of whether or not there will be performance impact on your system is really only answerable by you since there are a many other variables to consider such as storage speed, RAM availability, etc.

Related Solutions

Sql-server – Indexing SQL Server datetime Columns for Performance

Adding an index will increase performance on SELECT statements, assuming your range of dates is not sufficiently large as to force an index scan as opposed to an index seek. Adding an index will decrease performance on INSERT, UPDATE, and DELETE operations, as this new index will need to be maintained. The clustered/nonclustered question is highly dependent on how many other columns from the table you are including in the SELECT clause.

If this question is more than a homework question, and you have examples you would like to include, it would help my answer to be more complete.

Hope this helps,

Matt

Sql-server – SQL Server data extract with limited performance impact

Many ways you can approach this scenario.

I need to do a data extract from the database for reporting purposes.

Backup restore approach is best suited if you want to do this on an infrequent basis and do a static reporting of data.
Database Snapshot is an enterprise version feature only and this also gives you a static point-in-time view of the database.

Note that database snapshots take up space on disk, and having too many can fill up your disk space, especially in a production environment where data is updated frequently. In addition, using database snapshots will degrade performance a bit because I/O is increased on the database due to the fact that the data pages are copied as the write operation is performed.

Refer to Microsoft SQL Server Database Snapshots and Synonyms to over come some limitations that snapshots have and workarounds.

Look into Replication especially T-Rep if you want to do near to real-time reporting and want to offload the reporting work from the primary server. T-Rep will give you the benefits of Transactional consistency, Low latency, High Throughput, Minimal overhead as well as the ability to filter rows.
Lastly if none of the options are feasible to you, then with proper testing look into changing the isolation level of the database to RCSI (Read Committed Snapshot Isolation). This will be at the COST of TEMPDB performance.
```
-- check if RCSI is enabled
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'
-- Enable RCSI
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON
```

From The Data Loading Performance Guide :

RCSI basically will prevent queries reading data to block or be blocked by other queries modifying data in the same table. It is a powerful alternative to NOLOCK because it guarantees a complete, transactionally consistent view of the data and does not require a special hint. While RCSI originally targeted scenarios common to OLTP workloads, the feature can be a powerful tool in data warehouse workloads or scenarios involving large-scale bulk insert operations.

RCSI is enabled as a database-wide setting. When enabled, reader queries do not acquire shared locks on rows, pages or tables, and as a result they are not blocked by X or BU-locks taken by others. Instead, new or modified rows in a table carry a 17-byte version identifier, and the before-images of any rows being changed by a transaction (updated or deleted) are copied to tempdb using the row versioning mechanisms within SQL Server. Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows.

Also read up on : Comparing Different Results with RCSI & Read Committed from SQL CAT team.

LASTLY, to touch on NOLOCK hint, I would suggest you to read up

Best Answer

Related Solutions

Sql-server – Indexing SQL Server datetime Columns for Performance

Sql-server – SQL Server data extract with limited performance impact

Related Question