Prev: Is there any way to check GRANT VIEW DEFINITION ?
Next: SQL Server 2008 - Performance Alert Recommendations
From: Colin on 9 Mar 2010 08:34
I have been asked to index the Timestamp column on our main source system
database (1.3TB). The proposed index's are to help decrease the load time of
our datawarehouse. My question is, are timestamp columns good candidates for
indexes. (The warehouse selects between two timestamp values) considering the
constant updates on the index this will cause?
From: Lutz Uhlmann on 9 Mar 2010 09:04
Am 09.03.2010 14:34, schrieb Colin:
> I have been asked to index the Timestamp column on our main source system
> database (1.3TB). The proposed index's are to help decrease the load time of
> our datawarehouse. My question is, are timestamp columns good candidates for
> indexes. (The warehouse selects between two timestamp values) considering the
> constant updates on the index this will cause?

Think about that every row change updates the timpstamp column and the
index too ... so i think it isn't a good idea.
From: Lutz Uhlmann on 9 Mar 2010 09:12
>> My question is, are timestamp columns good
>> candidates for
>> indexes. (The warehouse selects between two timestamp values)
>> considering the
>> constant updates on the index this will cause?
>
> Think about that every row change updates the timpstamp column and the
> index too ... so i think it isn't a good idea.

Sry i did not read till the end.

In this special case it could be an idea to set an index on the
timestamp column. but its not my speciality ...
From: Tom Cooper on 9 Mar 2010 12:26
Generally timestamp (aka rowversion) columns are not good choices for
indexes. Because, as you note, they change everytime you make any update to
a row, which means every update to the table will also be an update to the
index. So will the (possible) improvement to to load time of your
datawarehouse be worth the cost to your other operations? The only possible
answer is "it depends".

One of the things it will depend on is whether or not you make this new
index the clustered index for the table.

If it is a nonclustered index, then it may improve the load time for the
data load, it may not make any difference at all, and it could even make the
data load run slower. A lot depends on what percentage of the rows in the
table are selected for the dataload each time you run it. The higher the
percentage, the less useful the index will be and at about 5%, SQL Server
will stop using the index unless you use an query hint forcing SQL to use
the index. And, of course, whether the index helps the dataload or not, the
other operations that update this table will run slower.

If you make it the clustered index, then almost certainly the data warehouse
load will run much faster. But all your other operations that update this
table will run much slower. If it is the clustered index, then every time
you do an update, you not only have to update this new index, you have to
physically move the row, and update every other index you have on this
table. If you do lots of updates, the overhead may be considerable. I
would be very reluctant to go this way unless almost all the changes to this
table were inserts and/or deletes with very few updates.

The only way to know for sure is to test. Get a test copy of your table,
and run sample dataloads with and without the index. If it doesn't help or
doesn't help enough, then you know you don't want the index. If it seems to
help a lot, then you need to estimate how much the cost to your other
operations will be. That's a lot harder to test. But do the best you can.
If it seems a nonclustered index is an overall help, you could try it in
production. An advantage of a nonclustered index is that if you implement
it and find it is causing to much overhead, all you need to do is drop the
nonclustered index which is a very fast operation and the behavior of your
production system should immediately go back to the way it used to operate.
If you go the clustered index route, you must be very sure you will like the
result, because dropping a clustered index is an expensive and
time-consuming operation.

Tom

"Colin" <Colin(a)discussions.microsoft.com> wrote in message
news:AC43AFA9-1D3B-47ED-8E38-F0BEAA939261(a)microsoft.com...
>I have been asked to index the Timestamp column on our main source system
> database (1.3TB). The proposed index's are to help decrease the load time
> of
> our datawarehouse. My question is, are timestamp columns good candidates
> for
> indexes. (The warehouse selects between two timestamp values) considering
> the
> constant updates on the index this will cause?

From: Henrik Staun Poulsen on 10 Mar 2010 07:21
On Mar 9, 2:34 pm, Colin <Co...(a)discussions.microsoft.com> wrote:
> I have been asked to index the Timestamp column on our main source system
> database (1.3TB). The proposed index's are to help decrease the load time of
> our datawarehouse. My question is, are timestamp columns good candidates for
> indexes. (The warehouse selects between two timestamp values) considering the
> constant updates on the index this will cause?

hi Colin,

Are you using SQL Server2008?

If so, then Filtered indexes may be a solution for you.

HIH
Henrik Staun Poulsen
www.stovi.com
 |  Next  |  Last
Pages: 1 2
Prev: Is there any way to check GRANT VIEW DEFINITION ?
Next: SQL Server 2008 - Performance Alert Recommendations