Index Rebuilding

Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am going to Rebuild the index on tables of production database.
> >
> > I will use 'dbcc dbreindex' command to do this index rebuilding.
> >
> > Now, I want to know what are the Prerequisites should i take to do
> > this job.
> >
> > And also what are the Precautions shuld i take.
> >
> > Please guide me in this regard.
> >
> >
> > Thanks & Regards,
> > Sajid.
> >
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
> > DBREINDEX rebuilds indexes completely and is resource intensive. This may
> > cause blocking and performance issues so you should run it during minimal
> > activity. Also, DBREINDEX is fully-logged regardless of your database
> > recovery model so make sure you have sufficient log space available.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <csajid@.gmail.com> wrote in message
> > news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I am going to Rebuild the index on tables of production database.
> > >
> > > I will use 'dbcc dbreindex' command to do this index rebuilding.
> > >
> > > Now, I want to know what are the Prerequisites should i take to do
> > > this job.
> > >
> > > And also what are the Precautions shuld i take.
> > >
> > > Please guide me in this regard.
> > >
> > >
> > > Thanks & Regards,
> > > Sajid.
> > >
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.

Index Rebuilding

Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
[vbcol=seagreen]
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
>
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE])))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if there
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
Ramu
Adding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE])))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
>OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
>SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
>Lookup takes about 9.82. Overall select statement takes estimated cost about
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu
|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DATE]
)))
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[IX_CALL_TB])
,
SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if ther
e
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
RamuAdding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE[Expr1002]=MIN([CALL_TB].[STMT_DA
TE])))
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
>OBJECT[CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT[CALLDB].[dbo].[CALL_TB].[
;IX_CALL_TB]),
>SEEK[CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmar
k
>Lookup takes about 9.82. Overall select statement takes estimated cost abou
t
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if the
re
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:

>The below query is running slow in production.
What do you mean, "slow"?

>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if the
re
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

index question

Hi,
The below query is running slow in production.
SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
PHONE_ID (IX_CALL_TB)
The execution plan shows:
SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
|--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE])))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
|--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
Lookup takes about 9.82. Overall select statement takes estimated cost about
9.83
To reduce the estimated cost, I have modifed the non-clustered index on
PHONE_ID to include the column STMT_DATE. In order words, I made it as
composite index. First column in the index is PHONE_ID and second column is
STMT_DATE.
Since I made it as covering index, the query estimated cost took about
0.008, which is a significant improvement. I have a question here...Does
this change affects any other query to run slow? I mean, for example if there
is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
Thanks,
RamuAdding the second column to the index will not visibly change the
performance when only the first column is used. The index tree will
be a tiny bit deeper but that will not amount to anything worth
worrying about.
Roy Harvey
Beacon Falls, CT
On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:
>Hi,
>The below query is running slow in production.
>SELECT MIN(STMT_DATE) as STATEMENT_DATE FROM CALL_TB WHERE PHONE_ID = ?
>CALL_TB table has three indexes. (1) primary key on CALL_ID (2)
>non-clustered index on STMT_DATE (IX_CALL_TB2) (3) non-clustered index on
>PHONE_ID (IX_CALL_TB)
>The execution plan shows:
>SELECT MIN(STMT_DATE) STMT_DATE FROM CALL_TB WHERE PHONE_ID =171
> |--Stream Aggregate(DEFINE:([Expr1002]=MIN([CALL_TB].[STMT_DATE])))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
>OBJECT:([CALLDB].[dbo].[CALL_TB]) WITH PREFETCH)
> |--Index Seek(OBJECT:([CALLDB].[dbo].[CALL_TB].[IX_CALL_TB]),
>SEEK:([CALL_TB].[PHONE_ID]=171) ORDERED FORWARD)
>
>Index seek on PHONE_ID takes estimated cost about 0.00881, where as Bookmark
>Lookup takes about 9.82. Overall select statement takes estimated cost about
>9.83
>To reduce the estimated cost, I have modifed the non-clustered index on
>PHONE_ID to include the column STMT_DATE. In order words, I made it as
>composite index. First column in the index is PHONE_ID and second column is
>STMT_DATE.
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
>Thanks,
>Ramu|||On Sun, 14 Jan 2007 19:16:01 -0800, Ramu
<Ramu@.discussions.microsoft.com> wrote:
>The below query is running slow in production.
What do you mean, "slow"?
>Since I made it as covering index, the query estimated cost took about
>0.008, which is a significant improvement. I have a question here...Does
>this change affects any other query to run slow? I mean, for example if there
>is some query like this, SELECT a.BIRTH_DATE, b.DOB from CALL_TB a,
>CALL_DETAIL b WHERE a.PHONE_ID = ? and b.PHONE_ID will get affected?
So it runs about 10% faster? OK.
But it was already pretty efficient, as I presume phone_id is already
highly selective. The covering index means it can all be done in the
index instead of scanning the data. This might be more significant as
your database size grows, if it's not already fully populated.
Yes, it will cause some other queries to run a percent or three slower
because a few more pages of index will be needed, with the fatter
two-field key.
J.

Index playing hide and seek game

Hi,
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
KillerHi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:

> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>

Index playing hide and seek game

Hi,
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
Killer
Hi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:

> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>
sql

Index playing hide and seek game

Hi,
I am playing game hide and seek with microsoft sql server 2000 sp4.
I had transfered few schemas of my production server in my OLAP server.
After the transfer completes i transfer data in a BCP files.
Now when i load data from BCP file to my OLAP server table .
It gives erro that duplicate value cannot insert due to index.
so i execute my code to delete the index.
drop index tablename.index
but it doesnot have index.
Now i directly transfered the data from the Production server to OLAP
server throught DTS.
and it finishes fine.
When i query in sysindex i see one entry but no name of the index.
Can any one tell me why it is happening with BCP files.
Hope u will help me
than x
from
KillerHi
Posting DDL and sample data may help to explain this.
What is the ID of the entry in sysindexes and what does the following give?
SELECT OBJECT_NAME(id),* FROM sysindexes WHERE ISNULL(name,'') = ''
John
"doller" wrote:
> Hi,
> I am playing game hide and seek with microsoft sql server 2000 sp4.
> I had transfered few schemas of my production server in my OLAP server.
> After the transfer completes i transfer data in a BCP files.
> Now when i load data from BCP file to my OLAP server table .
> It gives erro that duplicate value cannot insert due to index.
> so i execute my code to delete the index.
> drop index tablename.index
> but it doesnot have index.
> Now i directly transfered the data from the Production server to OLAP
> server throught DTS.
> and it finishes fine.
> When i query in sysindex i see one entry but no name of the index.
> Can any one tell me why it is happening with BCP files.
> Hope u will help me
> than x
> from
> Killer
>

Index Creation = 100% CPU Utilization

I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavance
Set the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance

Index Creation = 100% CPU Utilization

I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavanceSet the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
--
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance