What columns to index?

[Dasiths]

Hi,

I have the following table called AssetMeterReading.

AssetMeterReading
------------------
ReadingID - PK - Indexed
AssetID
Reading
Date
------------------

A lot of my serialized transactions access this table to validate meter readings.

What Columns should I index? I put two seperate indexes on the AssetID and Date a few days back. Today I got a DeadLock error (haven't got one before). Would my indexing have caused the deadlock.

[GaryMazzone]

Indexing would not cause a deadlock. What is the database system back end? As to what columns to index that depends, what do you search on the most (the where condition in a select statement)?

[Dasiths]

I'm using SQL Server 2008. I search for something like (AssetId=1 and Date>xx/xx/xxxx).orderby(date).first in linq.

Are you saying the indexes have no contribution to deadlocking?

[techgnome]

Nope... deadlocking happens when two transaction attempt to edit a record (not necessarily the same record) at the same time... Only one of them can write to the table at a time... so one gets the lock, and the other waits... meanwhile something happens to lock up the first transation... now you've got two transactions that are locked. SQL Server at some point makes a determination to kill one of the processes. That becomes the deadlock victim. It gets terminated and reported back as a deadlock.

Indexing has nothing to do with that.

Now, that being said...
Based solely on what you have provided, here's how I'd setup the indexes on that table:
1) PKey Index, Unique, NonClustered, on the ReadingID field
2) Clustered index on AssetID, ASC and Date, ASC (BTW, using "Date" for the field name is a bad bad bad idea... it's a reserved word as well... plus it's meaningless... what does Date mean? Date created, date updated? Is it available to go out on a date?)
3) If you search on AssetID or Date alone, I'd add indexes for each of those, non-unique, non-clustered (add them before the clustered index of #2)

-tg



One more thing... in your SQL Selects... looking into using the WITH(NOLOCK) options on your tables... that will allow you to get read-only access to tables that have been locked due to an edit going on at the same time. With out it, your select request will be forced to wait if the table is being updated. Adding it, tells SQL Server, hey I'm jsut reading it, ignore the lock and gimmie the data. As long as you aren't also trying to get data from the same row(s) being updated, you should be fine.

[Dasiths]

Nope... deadlocking happens when two transaction attempt to edit a record (not necessarily the same record) at the same time... Only one of them can write to the table at a time... so one gets the lock, and the other waits... meanwhile something happens to lock up the first transation... now you've got two transactions that are locked. SQL Server at some point makes a determination to kill one of the processes. That becomes the deadlock victim. It gets terminated and reported back as a deadlock.

Indexing has nothing to do with that.

Now, that being said...
Based solely on what you have provided, here's how I'd setup the indexes on that table:
1) PKey Index, Unique, NonClustered, on the ReadingID field
2) Clustered index on AssetID, ASC and Date, ASC (BTW, using "Date" for the field name is a bad bad bad idea... it's a reserved word as well... plus it's meaningless... what does Date mean? Date created, date updated? Is it available to go out on a date?)
3) If you search on AssetID or Date alone, I'd add indexes for each of those, non-unique, non-clustered (add them before the clustered index of #2)

-tg



One more thing... in your SQL Selects... looking into using the WITH(NOLOCK) options on your tables... that will allow you to get read-only access to tables that have been locked due to an edit going on at the same time. With out it, your select request will be forced to wait if the table is being updated. Adding it, tells SQL Server, hey I'm jsut reading it, ignore the lock and gimmie the data. As long as you aren't also trying to get data from the same row(s) being updated, you should be fine.

Thanks, I am familiar with the deadlocks and how transactions work.

The columnnames are just examples. I use properly formatted names in the schema.

I'm using LINQ for querying. And set the isolation level to serialized for certian situations. (When checking certain business rules before calling DataContext.SubmitChanges())

I was informed by someone in another thread that if I were to somehow update the MeterReading table it would lock the index as well. Is that true?

How does the clustered and non clustered index make a differance? Thanks.

[FunkyDexter]

If you don't give us the actual column names and queries we can't give you concrete advice so some general advice will have to do. (Sorry, that probably reads like I'm telling you off, I'm not, just tempering expectations).

Types of index:-
A clustered index is the order rows are physically stored in (that's not strictly true but will do for now). You can only have one because rows can only be stored in one order. It will give you the fastest response.

A non clustered index is more like the index at the back of a book. You can look up the record in it and it will give you a pointer to the record on the disk. You can have as many as you want. It will give you a much better response than no index but not quite as good as a clustered index.

What to index:-
Generally you want to index fields that appear in your where clause, joins and order bys.

Covering Indexes:-
If you have a particular query that you REALLY want to be performant you might consider creating a covering index. This is an index that contains all teh fields in teh select statement as well in the joins, where and order by. This gives you the best possible response because the query can retrieve everything it needs from the index without needing to refer to the table.

Other consideration:-
Indexes aren't free - or even cheap. They expand the speed of your db file and slow down inserts and updates (if the update affects a field in the index). Create them because you need them, not because you want them. That decision's a judgement call and really just comes with experience.

[Dasiths]

If you don't give us the actual column names and queries we can't give you concrete advice so some general advice will have to do. (Sorry, that probably reads like I'm telling you off, I'm not, just tempering expectations).

Types of index:-
A clustered index is the order rows are physically stored in (that's not strictly true but will do for now). You can only have one because rows can only be stored in one order. It will give you the fastest response.

A non clustered index is more like the index at the back of a book. You can look up the record in it and it will give you a pointer to the record on the disk. You can have as many as you want. It will give you a much better response than no index but not quite as good as a clustered index.

What to index:-
Generally you want to index fields that appear in your where clause, joins and order bys.

Covering Indexes:-
If you have a particular query that you REALLY want to be performant you might consider creating a covering index. This is an index that contains all teh fields in teh select statement as well in the joins, where and order by. This gives you the best possible response because the query can retrieve everything it needs from the index without needing to refer to the table.

Other consideration:-
Indexes aren't free - or even cheap. They expand the speed of your db file and slow down inserts and updates (if the update affects a field in the index). Create them because you need them, not because you want them. That decision's a judgement call and really just comes with experience.

Sorry for not having the schema with me. I would have pasted the t-sql but can't access the work server from home.

I have created indexes on foregin keys because I often query on them.
(For ex. CustomerID on a CustomerContact table becuase I often search for customercontacts by customer)

When a clustered index is used; does the entire table get locked when a insertion or update happens? This might be why my deadlocks are occuring.

[techgnome]

"When a clustered index is used; does the entire table get locked when a insertion or update happens?"
It CAN... it depends on how much data needs to be moved around... that's the trade off... faster selects for slightly slower inserts. Indexes don't move the data itself... just creates pointers to find the data faster. Clustered indexes on the other hand... DO move the data physically around. when you insert, SQL will try to get the smallest lock possible... in normal conditions, it's a row lock - the row being inserted. For cluster index table, more often it's going to be a page lock... unless the data move spills over onto another page, then you get a table lock and this is usually where deadlocks happen. and what makes them sporadic and difficult to diagnose and track down. And is why indexing should be scrutinized heavily and not done willy-nilly (the helter skelter method is acceptable however).

A compromise (and it's a hack really) would be to set up a job on a weekly basis that adds a clustered index, allowing the data to be shuffled around... then dropped... so you get the benefit of pre-sorted data... with out the price of having data shuffled during inserts... but it depends on how big of a table it is too and if there are a lot of inserts... hmm... that's something to take into consideration... if there are more inserts than selects... you may want to have an index on the two fields, but leave them as non-clustered.

-tg

[leinad31]

It will lock at two levels. For table, row lock (affects updates)... for index page lock (affects mostly inserts but can affect updates if update-able column is indexed).