Update statement taking too long to execute

I am running a Simple Update statement开发者_如何学Go. Its taking too long to execute. Here are the Update and index details.

The default value of Exported column is 0

UPDATE PAR_ITM SET exported = -1 WHERE exported < 1

Indexes:

CREATE NONCLUSTERED INDEX [IX_PAR_ITM_Exported_1]
ON [dbo].[PAR_ITM] ([exported] ASC)
WITH (
  PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF,
  IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
  ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80
) ON [PRIMARY]

---

So you are setting exported to -1 where exported already equals -1. Maybe changing your were clause to WHERE exported = 0 would hit fewer rows?

---

Cardinality is low
You are suffering for a problem called low cardinality.
See wikipedia.

The problem
In short every database (I know of) will refuse to use an index on a low cardinality column.

There's more
A good database will also refuse to use an index if it guesses that many rows will be hit by the select (low cardinality of the select), even if the column itself has lots of different values (high cardinality of the column)

Why will --insert DB here-- not use an index?
Here's the problem.
If more than 50% (differs per database) of all rows in a database have the same value, the DB will not use the index, because it would be pointless to use an index.

It is pointless to do 2 reads for most of the rows (read 1 for the index, read 2 for the table), if you can do one read (on the table itself).
The DB has to read most rows anyway, so it just goes ahead and reads them.
The fact that the DB has to fall back on using its slowest access mechanism (full table scan) is what's causing your slowness.

The solution
Increase the cardinality of the column or increase the cardinality of the select.
In other works make sure you select less than 50% of all rows, and make sure than SQL-server knows (or can guess) this.

How does the DB know the cardinality of a column?
A good DB keeps stats on tables and columns whilst doing selects/updates/inserts. In this manner it has the info it needs to make informed decisions.

Will forcing the use of an index speed up the query?
No, it will slow it down.

SO links
MySQL: low cardinality/selectivity columns = how to index?
Indexing & alternatives for low-selectivity columns

---

Might be helpful to know how many records are included in this:

Select Count(*)
FROM PAR_ITM 
WHERE exported < 1

Basically how many rows are being updated. Granted, even a million rows would be done relatively quickly.

Do other types of queries execute slowly on your server?

---

I don't think you should be using a non-clustered index here, as it appears there is no uniqueness to exported at all. The rule of thumb for non-clustered indexes is 95% uniqueness, because the query optimizer may not use the index otherwise, which means that you're slowing it down for no reason.

Take a look at this article for explanations on non-clustered index:

http://www.sql-server-performance.com/2007/nonclustered-indexes/

---

You could also be involved in a blocking situation with another query.