indexing question

If I have a table with multiple foreign keys to various other tables,
what's the best way to index them: one index that contains all of
those columns, or multiple indexes containing one foreign key column
each. Don't know if this makes a difference, but my foreign keys are
not explicitly defined as such in the schema and most of them are
nullable.I'm not sure what you mean by saying you have foreign
keys but they aren't explicity defined in the schema,
but to answer your specific question, there is no one
right answer for any question of the form "how do I index ... ?"
It all depends on what queries you run against the data.

-- Steve Kass
-- Drew University
-- Ref: 6A4EC3A5-944A-42CD-833F-A85D797C5305

inline_four@.yahoo.com wrote:
> If I have a table with multiple foreign keys to various other tables,
> what's the best way to index them: one index that contains all of
> those columns, or multiple indexes containing one foreign key column
> each. Don't know if this makes a difference, but my foreign keys are
> not explicitly defined as such in the schema and most of them are
> nullable.|||There are a lot of points to be considered when selecting indexes.

The size of the table, if you only have at most a few hundred rows on
a table, you probably don't need any indexes as full scans will be
pretty fast. On the other hand have millions of rows and good indexes
will be essential unless you want your application to crawl.

Know you application. You would think if foreign keys are defined they
would need to be indexes, but not necessarily. Say one of your foreign
keys is only used for a report that runs once a month at a weekend.
Sure it will run faster with an applicable index, but do you need it
to? What about the overhead everytime the table is updated, and
needing to rebuild the index on a regular basis.

My approach is to build the obvious ones. If you have a customer table
for instance it's obvious that you need some kind of customer id
index. I would monitor table usage in development and even continue
into production using profiler to establish which would be the most
used indexes. If you just create all the indexes you think you might
need you could end up with 20, 30 or more indexes.

I try to be fairly ruthless with indexes, trying to keep no more than
5 or 6 max on a table. It's not always possible, but the more you add
the larger the space used, the more maint and the more overhead.

Hope this helps

John|||Thanks for the response. To be more specific: it's a very large
product table. It gets read a lot. It doesn't get written to very
often. So SELECT speed is essential. The table is fairly wide
because it contains a number of columns pointing to optional product
specification records in different tables. They are in different
tables because they have largely varying structure. Each is optional,
therefore these spec ID columns are nullable. There are also a few
columns referring to things that are constantly joined on, such as
brand ID's. The majority of queries use brand and other ID's for
inner joins and a number of other queries use left outer joins on the
spec ID columns.

To reiterate my question, I am not sure if it's better to have one
index that includes all of these spec, brand and other such columns,
or if I should have multiple indexes, each of which contains one
column, or something in between?

johnbandettini@.yahoo.co.uk (John Bandettini) wrote in message news:<ed84d349.0309170058.500e8e73@.posting.google.com>...
> There are a lot of points to be considered when selecting indexes.
> The size of the table, if you only have at most a few hundred rows on
> a table, you probably don't need any indexes as full scans will be
> pretty fast. On the other hand have millions of rows and good indexes
> will be essential unless you want your application to crawl.
> Know you application. You would think if foreign keys are defined they
> would need to be indexes, but not necessarily. Say one of your foreign
> keys is only used for a report that runs once a month at a weekend.
> Sure it will run faster with an applicable index, but do you need it
> to? What about the overhead everytime the table is updated, and
> needing to rebuild the index on a regular basis.
> My approach is to build the obvious ones. If you have a customer table
> for instance it's obvious that you need some kind of customer id
> index. I would monitor table usage in development and even continue
> into production using profiler to establish which would be the most
> used indexes. If you just create all the indexes you think you might
> need you could end up with 20, 30 or more indexes.
> I try to be fairly ruthless with indexes, trying to keep no more than
> 5 or 6 max on a table. It's not always possible, but the more you add
> the larger the space used, the more maint and the more overhead.
> Hope this helps
> John|||You need to be careful with a composite index (index of more than one
column), they need to be in the right order to be useful. For instance
if you had a composite index that was made up of three columns,
cust-id, order-no and date in that order. If you had a query that
supplied the cust-id, order-no and date, the query would be very
likely to use the index. A query where you only had the cust-id, might
use the index, depending on the spread of cust-id. If however your
query only knew order-no and date it would not use the index as it did
not have a value for the first column (cust-id).

So in answer, it's would probably not be a good idea to put all of the
columns into one index, it would probably hardly ever use it. It is
more likely that you could create composite indexes of 2 or 3 columns
that may work and cut down on the number of indexes you need, but
again you really need to know your application to be sure.

One thing you can do with large composite indexes, is have 'covering'
indexes. If you have all the data columns that a query requires in an
index, the query can get its data from the index only without going to
the table itself. This can be very fast. Say you have a table with 100
columns in it and you have a query that runs a lot of times a day and
is only interested in three columns in the table. If you create an
index of those three columns, the query can get it's data from the
index without reading the table.

Hope this helps

John

indexing/backup issue with large table - transactional replication

Hello,
I have a table on a database that contains over 6 million rows. There
is one clusted and two non-clustered indexes on the table.
Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
Machine varchar (60) NULL,
LogEntry varchar (1000)NULL,
Active varchar (50) NULL,
SysInfo varchar (255),
Idle varchar (50) NULL,
IP varchar (15) NULL,
KioskDate datetime NULL,
KioskTime varchar (22) NULL,
ServerDate datetime NULL,
ServerTime datetime NULL,
Application varchar (15)NULL,
WebDomain varchar (50)NULL ,
NSCode varchar (10) NULL
pk_1 Clustered on Counter
pk_2 NC on NSCode
pk_3 Machine, KioskDate, KioskTime, NSCode
These indexes work well.
A trans log backup runs nightly apart from sunday morning when a full
backup runs. The table is also part of a transactional replication
subscription (along with two other tables). The problem occurs when
replication fails as the database is being fully backed up at the
weekend. The replication also fails when I try to run DBCC REINDEX on
the table. I have tried to remove the table from the subscription
then run the DBCC command, however this also doesn't work. I have
used DBCC INDEXDEFRAG (successfully) however would my indexes still be
effective as using DBCCREINDEX?
The solution is to ensure the table is backed up, the indexes do not
become ineffective and replication continues to work.
All ideas greatly appreciated!
Thanks
ScottAs far as the INDEXDEFRAG, it should *eventually* be as
effective as a full reindex. Because it essentially works
with smaller pieces of the index, it takes much longer to
run.
What I would highly recommend is using filegroups and
splitting off the non-clustered indexes onto a separate
disk array. That should speed up the performance of a
full reindex. I'd also reindex each index as a separate
step, rather than all of the indexes in one job.
I'd also recommend splitting the full backup into backing
up individual files or filegroups more frequently rather
than a single full backup.
Implementing something like SQL Litespeed can drastically
increase the speed at which a backup finishes.
It sounds like you just don't have a large enough
maintenance window (time). In the end, you have two
options -- decrease the maintenance or increase the time
window. Consider the latter as a possibility.
Hope that helps.
>--Original Message--
>Hello,
>
>I have a table on a database that contains over 6 million
rows. There
>is one clusted and two non-clustered indexes on the table.
>Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
>Machine varchar (60) NULL,
>LogEntry varchar (1000)NULL,
>Active varchar (50) NULL,
>SysInfo varchar (255),
>Idle varchar (50) NULL,
>IP varchar (15) NULL,
>KioskDate datetime NULL,
>KioskTime varchar (22) NULL,
>ServerDate datetime NULL,
>ServerTime datetime NULL,
>Application varchar (15)NULL,
>WebDomain varchar (50)NULL ,
>NSCode varchar (10) NULL
>
>pk_1 Clustered on Counter
>pk_2 NC on NSCode
>pk_3 Machine, KioskDate, KioskTime, NSCode
>These indexes work well.
>A trans log backup runs nightly apart from sunday morning
when a full
>backup runs. The table is also part of a transactional
replication
>subscription (along with two other tables). The problem
occurs when
>replication fails as the database is being fully backed
up at the
>weekend. The replication also fails when I try to run
DBCC REINDEX on
>the table. I have tried to remove the table from the
subscription
>then run the DBCC command, however this also doesn't
work. I have
>used DBCC INDEXDEFRAG (successfully) however would my
indexes still be
>effective as using DBCCREINDEX?
>The solution is to ensure the table is backed up, the
indexes do not
>become ineffective and replication continues to work.
>All ideas greatly appreciated!
>
>Thanks
>
>Scott
>.
>

Indexing Results of Stored Proc (or new table created by one)

Hi,

I am using data from multiple databases and/or queries. It would greatly
simplify and speed things up if I could use CONTAINS in processing the
results. However, "CONTAINS" requires the data to be indexed. Due to the
amount of processing, I think it would be faster even if I had to re-index
every time.

For example, I would like to do something like this (simplified to
illustrate the desired functionality... This should show all of the words
from one table that are not contained in their current or inflectional forms
within another table):

SELECT W1.Content
FROM
(SELECT Word AS Content
FROM MyTable) W1
LEFT OUTER JOIN
(SELECT Phrase AS Content
FROM MyOtherTable) W2
ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
WHERE W2.Content IS NULL

Can the results of a procedure be indexed? If not, can I drop the results
into a new table and trigger an automatic index of it, pausing the procedure
until the indexing is done?

Or, it there another way?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simon|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simonsql

indexing question

If I have a table with multiple foreign keys to various other tables,
what's the best way to index them: one index that contains all of
those columns, or multiple indexes containing one foreign key column
each. Don't know if this makes a difference, but my foreign keys are
not explicitly defined as such in the schema and most of them are
nullable.I'm not sure what you mean by saying you have foreign
keys but they aren't explicity defined in the schema,
but to answer your specific question, there is no one
right answer for any question of the form "how do I index ... ?"
It all depends on what queries you run against the data.

-- Steve Kass
-- Drew University
-- Ref: 6A4EC3A5-944A-42CD-833F-A85D797C5305

inline_four@.yahoo.com wrote:
> If I have a table with multiple foreign keys to various other tables,
> what's the best way to index them: one index that contains all of
> those columns, or multiple indexes containing one foreign key column
> each. Don't know if this makes a difference, but my foreign keys are
> not explicitly defined as such in the schema and most of them are
> nullable.|||There are a lot of points to be considered when selecting indexes.

The size of the table, if you only have at most a few hundred rows on
a table, you probably don't need any indexes as full scans will be
pretty fast. On the other hand have millions of rows and good indexes
will be essential unless you want your application to crawl.

Know you application. You would think if foreign keys are defined they
would need to be indexes, but not necessarily. Say one of your foreign
keys is only used for a report that runs once a month at a weekend.
Sure it will run faster with an applicable index, but do you need it
to? What about the overhead everytime the table is updated, and
needing to rebuild the index on a regular basis.

My approach is to build the obvious ones. If you have a customer table
for instance it's obvious that you need some kind of customer id
index. I would monitor table usage in development and even continue
into production using profiler to establish which would be the most
used indexes. If you just create all the indexes you think you might
need you could end up with 20, 30 or more indexes.

I try to be fairly ruthless with indexes, trying to keep no more than
5 or 6 max on a table. It's not always possible, but the more you add
the larger the space used, the more maint and the more overhead.

Hope this helps

John|||Thanks for the response. To be more specific: it's a very large
product table. It gets read a lot. It doesn't get written to very
often. So SELECT speed is essential. The table is fairly wide
because it contains a number of columns pointing to optional product
specification records in different tables. They are in different
tables because they have largely varying structure. Each is optional,
therefore these spec ID columns are nullable. There are also a few
columns referring to things that are constantly joined on, such as
brand ID's. The majority of queries use brand and other ID's for
inner joins and a number of other queries use left outer joins on the
spec ID columns.

To reiterate my question, I am not sure if it's better to have one
index that includes all of these spec, brand and other such columns,
or if I should have multiple indexes, each of which contains one
column, or something in between?

johnbandettini@.yahoo.co.uk (John Bandettini) wrote in message news:<ed84d349.0309170058.500e8e73@.posting.google.com>...
> There are a lot of points to be considered when selecting indexes.
> The size of the table, if you only have at most a few hundred rows on
> a table, you probably don't need any indexes as full scans will be
> pretty fast. On the other hand have millions of rows and good indexes
> will be essential unless you want your application to crawl.
> Know you application. You would think if foreign keys are defined they
> would need to be indexes, but not necessarily. Say one of your foreign
> keys is only used for a report that runs once a month at a weekend.
> Sure it will run faster with an applicable index, but do you need it
> to? What about the overhead everytime the table is updated, and
> needing to rebuild the index on a regular basis.
> My approach is to build the obvious ones. If you have a customer table
> for instance it's obvious that you need some kind of customer id
> index. I would monitor table usage in development and even continue
> into production using profiler to establish which would be the most
> used indexes. If you just create all the indexes you think you might
> need you could end up with 20, 30 or more indexes.
> I try to be fairly ruthless with indexes, trying to keep no more than
> 5 or 6 max on a table. It's not always possible, but the more you add
> the larger the space used, the more maint and the more overhead.
> Hope this helps
> John|||You need to be careful with a composite index (index of more than one
column), they need to be in the right order to be useful. For instance
if you had a composite index that was made up of three columns,
cust-id, order-no and date in that order. If you had a query that
supplied the cust-id, order-no and date, the query would be very
likely to use the index. A query where you only had the cust-id, might
use the index, depending on the spread of cust-id. If however your
query only knew order-no and date it would not use the index as it did
not have a value for the first column (cust-id).

So in answer, it's would probably not be a good idea to put all of the
columns into one index, it would probably hardly ever use it. It is
more likely that you could create composite indexes of 2 or 3 columns
that may work and cut down on the number of indexes you need, but
again you really need to know your application to be sure.

One thing you can do with large composite indexes, is have 'covering'
indexes. If you have all the data columns that a query requires in an
index, the query can get its data from the index only without going to
the table itself. This can be very fast. Say you have a table with 100
columns in it and you have a query that runs a lot of times a day and
is only interested in three columns in the table. If you create an
index of those three columns, the query can get it's data from the
index without reading the table.

Hope this helps

John

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/defaul...kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
(Please reply only to the newsgroup)

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
--
(Please reply only to the newsgroup)

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griff
so there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>
|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default...b;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
(Please reply only to the newsgroup)

indexing distinct queries ...

Hi,
I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.
Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:
select distinct CATEGORY from table TEST
Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.
I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.
I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.
Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?
Thanks for any hints!try creating a view with schema binding and use group by (which will return
the distinct values), then you should be able to add a unique index on the v
iew.|||Thanks, that actually seemed to work. I had tried this before but
discouraged by (yet another) error message mentioning count_big(*) missing.
Now I realized that I can just add the count_big(*) and it saved the index.
Initial Tests look promising - thanks.
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:BD3A168C-90FD-4FDC-AE1C-46618C6FF5A6@.microsoft.com...
> try creating a view with schema binding and use group by (which will
return the distinct values), then you should be able to add a unique index
on the view.

indexing distinct queries ...

Hi,
I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.
Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:
select distinct CATEGORY from table TEST
Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.
I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.
I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.
Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?
Thanks for any hints!try creating a view with schema binding and use group by (which will return the distinct values), then you should be able to add a unique index on the view.|||Thanks, that actually seemed to work. I had tried this before but
discouraged by (yet another) error message mentioning count_big(*) missing.
Now I realized that I can just add the count_big(*) and it saved the index.
Initial Tests look promising - thanks.
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:BD3A168C-90FD-4FDC-AE1C-46618C6FF5A6@.microsoft.com...
> try creating a view with schema binding and use group by (which will
return the distinct values), then you should be able to add a unique index
on the view.

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

indexes question

Assume we have a sales table, which contains a OrderID(char(8)) and a OrderDate(smalldatetime) field. Each day, hundreds of thousands of records needs to be inserted into this table. We need a daily sales report, and the OrderID is not necessarilly sequentially entered (every data operator has a range of OrderID, so for each data operator, it is sequential, but globally, it is not).

So, I would like to create a clustered index on OrderDate, and the Primary Key is on OrderID. I think it is good for generating the daily sales report. However, when an order is entered, we need to quickly check if the OrderID already exits, because the unique index on OrderID is built on the clustered index (on OrderDate), which means the Database Engine will search all the records on that day. This could be a slow process.

Is it possible to create indexes so that I can generate daily report and search for the OrderID quickly at the same time?

Thanks.

Create the clustered index on the orderDate and a second, unique non clustered index on orderId. When you check that the order id already exists, if you do a "if exists (select orderId from sales where orderId = @.orderId)", then this will not need to do a clustered index lookup as the index is covered i.e. : there is no need to go back to the base table, as the only column you are asking for (orderId) is already in the non clustered index.

The daily sales report would continue to work with the clustered index on the orderDate.

Hope this helps.

Indexes and UniqueIdentifier Fields

I have a table that contains articles (as in, newspaper articles, blog
articles, whatever). I need to use a column of type uniqueidentifier
because one of the requirements is that I be able to write the articles
out to XML or import them from XML, and references (as in, "for more
info read this: 2323-232-90934" have to still work after the export and
import).

So as a minimum, the table is going to look like this:

CREATE TABLE Articles (
ArticleID uniqueidentifier,
PublishDate datetime,
Title nvarchar (50)
ArticleContent ntext
)
GO

ALTER TABLE Articles ADD
CONSTRAINT PK_Articles
PRIMARY KEY NONCLUSTERED (ArticleID)
WITH FILLFACTOR = 100
GO

As you can see, I'm not going to use a clustered index on a column of
type UniqueIdentifier. I got that much from this newsgroup and from
websites on sql server performance tuning.

Two questions. 1: I will obviously need to list recent articles. I'll
need to do: select top 10 ArticleID, PublishDate, Title from Articles
order by PublishDate desc

Will there be any problem with an index on a datetime field to make
that query faster?

CREATE UNIQUE CLUSTERED INDEX IX_Articles_PublishDate
ON Articles (PublishDate DESC)
WITH FILLFACTOR = 100
GO

Question 2: Is there anything else that I can do here that I'm missing?
Should I maybe also have a auto-increment field and put the clustered
index on it instead?

Thanks in advance
chrischristopher.secord@.gmail.com (christopher.secord@.gmail.com) writes:
> So as a minimum, the table is going to look like this:
> CREATE TABLE Articles (
> ArticleID uniqueidentifier,
> PublishDate datetime,
> Title nvarchar (50)
> ArticleContent ntext
> )
> GO
> ALTER TABLE Articles ADD
> CONSTRAINT PK_Articles
> PRIMARY KEY NONCLUSTERED (ArticleID)
> WITH FILLFACTOR = 100
> GO
> As you can see, I'm not going to use a clustered index on a column of
> type UniqueIdentifier. I got that much from this newsgroup and from
> websites on sql server performance tuning.

Certainly clustered index on Uniqueidentifier and a fillfactor of 100
is a recipe for disaster. However, SQL Server MVP Greg Linwood pointed
out to me that with a low fill factor, uniqueidentifier very can be a
good choice for a clustered index. New rows will be inserted in existing
gaps. But this requires that you reindex when the gaps are starting
to run out.

> Two questions. 1: I will obviously need to list recent articles. I'll
> need to do: select top 10 ArticleID, PublishDate, Title from Articles
> order by PublishDate desc
> Will there be any problem with an index on a datetime field to make
> that query faster?
> CREATE UNIQUE CLUSTERED INDEX IX_Articles_PublishDate
> ON Articles (PublishDate DESC)
> WITH FILLFACTOR = 100
> GO

PublishDate could indeed be a good choice for the clustered index,
but I don't think you should make it unique. Surely, you must be able
to store two articles published the same day! (I'm assuming that you
will keep the time portion to midnight.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the advice. I've decided to add a column of type int
(indentity) and make that the clustered index. The result will be the
same as what I was looking for in indexing publishdate, but it's on an
simple integer field.

chris|||christopher.secord@.gmail.com (christopher.secord@.gmail.com) writes:
> Thanks for the advice. I've decided to add a column of type int
> (indentity) and make that the clustered index. The result will be the
> same as what I was looking for in indexing publishdate, but it's on an
> simple integer field.

Say that you have a query:

SELECT * FROM tbl WHERE publishdate BETWEEN '20050601' AND '20050630'

If you have a clustered index on publishdate, SQL Server will do a
clustered index seek in this interval and find the rows quickly. If you
cluster on the identity column instead, SQL Server will have to scan
the entire table. If you add a non-clustered index on publishdate, SQL
Server may use that index, but if the interval is too wide, it will estimate
that nc-index + bookmark lookup is too expensive, and scan the table
nevertheless.

This is because, there is no way that SQL Server can assert that the
date follows the identity column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you thought about getting a textbase (document management tool) to
do this? SQL is not the answer to everything.

Indexes & "OR"

Hi,
i have 2 tables:
table 1 - called Feed. contains the following fields:
productName
PartNumber
ManufacturerName
PartNumberManufacturerName (this field concatenates the part number field
and the manufacturer name field).
Table 2 - called Products. contains the following fields:
productID
ProductName
PartNumber1
PartNumber2
PartNumber3
ManufacturerName
Mapkey1 - (this field concatenates the PartNumber1 field and the
manufacturername field)
Mapkey2 - (this field concatenates the PartNumber2 field and the
manufacturername field)
Mapkey3 - (this field concatenates the PartNumber3 field and the
manufacturername field)
I have a query that finds the productID for the records in the feed table.
query is as follows
select a.productName, a.partnumber,
from feed a (NOLOCK),
products b (NOLOCK)
where a.partnumberManufacturerName = b.mapKey1 OR
a.partnumberManufacturerName = b.mapKey2 OR
a.partnumberManufacturerName = b.mapKey3
Indexes:
on the feed table, i have an index on the partnumberManufacturerName and on
the products table, i have multiple indexes:
Mapkey1 ,Mapkey2, and Mapkey3
and then on each individual field
Mapkey1
Mapkey2
Mapkey3
My question is which index should i keep and which index should i drop. i'm
not sure which index the query is going to use since i'm using the "OR".
thanks
rafaelwhy the concatentated columns? they don't help performance [and may
hinder it]
why the multiple part numbers in separate columns - is it possible to
have more than 3?
do you have a manufacturers table as well?
i'd seriously consider changing this un-normalized schema to have a
linking table between partnumbers and productIDs (and normalize to have
a manufacturers table)
how does the Feed table get populated?
if you fix the schema, with proper primary and foreign keys, you
probably won't need extra indexes [at least for this query].
Rafael Chemtob wrote:
> Hi,
> i have 2 tables:
> table 1 - called Feed. contains the following fields:
> productName
> PartNumber
> ManufacturerName
> PartNumberManufacturerName (this field concatenates the part number field
> and the manufacturer name field).
> Table 2 - called Products. contains the following fields:
> productID
> ProductName
> PartNumber1
> PartNumber2
> PartNumber3
> ManufacturerName
> Mapkey1 - (this field concatenates the PartNumber1 field and the
> manufacturername field)
> Mapkey2 - (this field concatenates the PartNumber2 field and the
> manufacturername field)
> Mapkey3 - (this field concatenates the PartNumber3 field and the
> manufacturername field)
> I have a query that finds the productID for the records in the feed table.
> query is as follows
> select a.productName, a.partnumber,
> from feed a (NOLOCK),
> products b (NOLOCK)
> where a.partnumberManufacturerName = b.mapKey1 OR
> a.partnumberManufacturerName = b.mapKey2 OR
> a.partnumberManufacturerName = b.mapKey3
> Indexes:
> on the feed table, i have an index on the partnumberManufacturerName and o
n
> the products table, i have multiple indexes:
> Mapkey1 ,Mapkey2, and Mapkey3
> and then on each individual field
> Mapkey1
> Mapkey2
> Mapkey3
> My question is which index should i keep and which index should i drop. i
'm
> not sure which index the query is going to use since i'm using the "OR".
> thanks
> rafael
>
>