Choosing clustered index

Dear friends:
There is some confusion here about the choice of which index should be
clustered. The choices are generally:
- the surrogate identity column.
- one or more columns that make up the natural key.
My contention has been that the latter is the obvious choice. This is the
order in which the rows are commonly placed on the screen and on reports.
The identity column really places the rows in no particular order at all,
except somewhat "sequentially" with respect to the order they were entered
(assuming an incrementing integer key).
What is the conventional wisdom about this?
Tom EllisonUsually clustered indexes should:
- Be as narrow as possible.
- Be placed on columns that would benefit the most:
-- Columns that have very high cardinality are good candidates.
-- So are columns that are searched for large ranges at a time using
operators like BETWEEN, LIKE 'x%' and >, <, etc.
-- Columns that are searched the most frequently, since they eliminate
bookmark lookups which can occur with narrow nonclustered indexes.
A lot of times the Primary Key will fit a lot of these requirements, but not
always. I usually would only make an IDENTITY column the clustered index on
supporting (lookup-type) tables, to improve JOIN performance. One or more
columns (with high cardinality) that make up the natural key on your main
tables would be good clustered index candidates.
[url]http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/05/08/599.aspx[
/url]
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:O0nC9gVdGHA.5016@.TK2MSFTNGP04.phx.gbl...
> Dear friends:
> There is some confusion here about the choice of which index should be
> clustered. The choices are generally:
> - the surrogate identity column.
> - one or more columns that make up the natural key.
> My contention has been that the latter is the obvious choice. This is the
> order in which the rows are commonly placed on the screen and on reports.
> The identity column really places the rows in no particular order at all,
> except somewhat "sequentially" with respect to the order they were entered
> (assuming an incrementing integer key).
> What is the conventional wisdom about this?
> Tom Ellison
>|||Dear Mike:
Thanks for the opinions. The article was quite helpful.
Now, how do you figure that an IDENTITY column makes a good clustered index?
I ask because:
- in my experience, processing rarely proceeds in identity number order.
- user directed searches don't proceed along these lines
- moving through the data in some sequential order, such as generating data
for the screen or a report will follow a natural key order, not an identity
order
My thought is that the identity column is assigned sequentially over time
(if auto-incremented) but does not generally follow any organization of the
data that is likely to be repeated.
The natural key of which I spoke is unique (thus, highly cardinal) and tends
to be the most commonly used sequence in processing (screens and reports).
It is often the column(s) filtered or JOINed in many of the queries used.
Thanks again,
Tom Ellison
"Mike C#" <xxx@.yyy.com> wrote in message news:pBQ8g.184$Ut2.60@.fe09.lga...
> Usually clustered indexes should:
> - Be as narrow as possible.
> - Be placed on columns that would benefit the most:
> -- Columns that have very high cardinality are good candidates.
> -- So are columns that are searched for large ranges at a time using
> operators like BETWEEN, LIKE 'x%' and >, <, etc.
> -- Columns that are searched the most frequently, since they eliminate
> bookmark lookups which can occur with narrow nonclustered indexes.
> A lot of times the Primary Key will fit a lot of these requirements, but
> not always. I usually would only make an IDENTITY column the clustered
> index on supporting (lookup-type) tables, to improve JOIN performance.
> One or more columns (with high cardinality) that make up the natural key
> on your main tables would be good clustered index candidates.
> http://blogs.sqlservercentral.com/b...99.asp
x
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:O0nC9gVdGHA.5016@.TK2MSFTNGP04.phx.gbl...
>|||Tom,
are you using non-clustered indexes? If yes, take in account that
bookmark lookups take more time if bookmarks are wider.|||I generally use IDENTITY columns as clustered indexes only when it's in
supporting (lookup/foreign-key) tables. The only reason then is to improve
JOIN performance by potentially eliminating bookmark lookups you might get
with a narrow nonclustered index. Other than that, put your clustered
indexes where they'll do the most good.
"Tom Ellison" wrote:

> Dear Mike:
> Thanks for the opinions. The article was quite helpful.
> Now, how do you figure that an IDENTITY column makes a good clustered inde
x?
> I ask because:
> - in my experience, processing rarely proceeds in identity number order.
> - user directed searches don't proceed along these lines
> - moving through the data in some sequential order, such as generating dat
a
> for the screen or a report will follow a natural key order, not an identit
y
> order
> My thought is that the identity column is assigned sequentially over time
> (if auto-incremented) but does not generally follow any organization of th
e
> data that is likely to be repeated.
> The natural key of which I spoke is unique (thus, highly cardinal) and ten
ds
> to be the most commonly used sequence in processing (screens and reports).
> It is often the column(s) filtered or JOINed in many of the queries used.
> Thanks again,
> Tom Ellison
>
> "Mike C#" <xxx@.yyy.com> wrote in message news:pBQ8g.184$Ut2.60@.fe09.lga...
>
>

Choices of creating database files

Working on a database structure on SQL 2000 server, I have MDF and LDF creat
ed.
I need to create NDF files to use 5 logical drives on the server. All
logical drives are located in SAN storage with RAID10. When I create the ND
F
files , should I create one file on each drive OR create multiple files on
each drive? Which way is better for SQL server performance?
Thanks for recommendation!Mike,
Like so many other concepts, it depends. What is the architecture of your
SAN? If it is of later technology, then your disk allocation may be
"virtuallized" anyway. This means that you really don't have physical
control over which drives your data goes to (within the LUN) - the SAN will
determine that. For example, the HP EVA might span a RAID 10 configuration
across 100 drives in 2K blocks if so configured, even if you are allocating
only 10 GB. If you map 5 logical drives the data corresponding to all 5
drives will be interleaved and spread out over the same 100 drives.
There may be other reasons for separating the NDF files (file backups,
process isolation, etc.). You may want to isolate the log and data for
snapshots, or other reasons, but I think any performance gain would be
nominal.
Also, allocation additional space is usually a straight forward process, but
deleting space usually requires deleting and re-adding the configured space.
If you break up your data files, undoubtedly some will be a larger size than
others and this could result in a maintenance issue. You may be in a
situation where you want to take space from drive A and add it to drive B.
There could be some nominal performance having multiple drives due to SQL
Server having more I/O buffers, but it probably would not be noticable.
Unless you want to have a more sophisticated file backup scheme, I would
start with two drives (for isolation purposes), one for the log and one for
the data, indexes and tempdb.
If your architecture is not of later technology, then it depends (again!).
What kind of SAN are you working with?
-- Bill
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:A2FD70DA-F8C1-4446-AB9F-4C5D73BB60EE@.microsoft.com...
> Working on a database structure on SQL 2000 server, I have MDF and LDF
> created.
> I need to create NDF files to use 5 logical drives on the server. All
> logical drives are located in SAN storage with RAID10. When I create the
> NDF
> files , should I create one file on each drive OR create multiple files on
> each drive? Which way is better for SQL server performance?
> Thanks for recommendation!

Choices of creating database files

Working on a database structure on SQL 2000 server, I have MDF and LDF created.
I need to create NDF files to use 5 logical drives on the server. All
logical drives are located in SAN storage with RAID10. When I create the NDF
files , should I create one file on each drive OR create multiple files on
each drive? Which way is better for SQL server performance?
Thanks for recommendation!
Mike,
Like so many other concepts, it depends. What is the architecture of your
SAN? If it is of later technology, then your disk allocation may be
"virtuallized" anyway. This means that you really don't have physical
control over which drives your data goes to (within the LUN) - the SAN will
determine that. For example, the HP EVA might span a RAID 10 configuration
across 100 drives in 2K blocks if so configured, even if you are allocating
only 10 GB. If you map 5 logical drives the data corresponding to all 5
drives will be interleaved and spread out over the same 100 drives.
There may be other reasons for separating the NDF files (file backups,
process isolation, etc.). You may want to isolate the log and data for
snapshots, or other reasons, but I think any performance gain would be
nominal.
Also, allocation additional space is usually a straight forward process, but
deleting space usually requires deleting and re-adding the configured space.
If you break up your data files, undoubtedly some will be a larger size than
others and this could result in a maintenance issue. You may be in a
situation where you want to take space from drive A and add it to drive B.
There could be some nominal performance having multiple drives due to SQL
Server having more I/O buffers, but it probably would not be noticable.
Unless you want to have a more sophisticated file backup scheme, I would
start with two drives (for isolation purposes), one for the log and one for
the data, indexes and tempdb.
If your architecture is not of later technology, then it depends (again!).
What kind of SAN are you working with?
-- Bill
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:A2FD70DA-F8C1-4446-AB9F-4C5D73BB60EE@.microsoft.com...
> Working on a database structure on SQL 2000 server, I have MDF and LDF
> created.
> I need to create NDF files to use 5 logical drives on the server. All
> logical drives are located in SAN storage with RAID10. When I create the
> NDF
> files , should I create one file on each drive OR create multiple files on
> each drive? Which way is better for SQL server performance?
> Thanks for recommendation!

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul CahillHi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegroups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>|||Hi,
I think you can go for Transaction replication.
--
Herbert
"Paul Cahill" wrote:
> We wish to look into setting up a mirrored server at another site in case we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of running
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:
>> We wish to look into setting up a mirrored server at another site in case
>> we
>> lose access to our head office.
>> The company is based in London, fairly near potential bomb targets etc.
>> There are three other locations, one having a 4GB leased line to head
>> office.
>> Assuming we are not going to jump to Yukon in the short term it looks
>> like
>> log shipping is probably the way to go but are there other alternatives?
>> Note that currently the system is not set up well for disaster recovery.
>> Simple recovery model with over night backups. No experience here of
>> running
>> proper log backups on recovery mode "full". There's probably some "select
>> into" in the code plus we do some pretty big index rebuilds over night.
>> Database is currently 80GB across raid 10 arrays of 18Gb disks.
>> Thanks
>> Paul Cahill
>>

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul CahillHi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegroups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>|||Hi,
I think you can go for Transaction replication.
--
Herbert
"Paul Cahill" wrote:

> We wish to look into setting up a mirrored server at another site in case
we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of runni
ng
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...[vbcol=seagreen]
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:
>

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul Cahill
Hi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.
|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegr oups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>
|||Hi,
I think you can go for Transaction replication.
Herbert
"Paul Cahill" wrote:

> We wish to look into setting up a mirrored server at another site in case we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of running
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>
|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...[vbcol=seagreen]
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote: