Create as Clustered Index Error

Hi,
I am getting an error message when I try to make an already existing,
unique index, clustered. I have other databases with the same core
design, some will allow the clustered index to be created, others
display the same error. Could anyone tell me what the error message is
refering to?
Details are:
SQL2000 SP3
The column is of data type varchar.
I am using Enterprise Manager and trying to change the index through the
design - properties window.
There are no nulls in this column.
There are relationships with six other tables, all bound to this column.
Enforce relationship for replication and Enforce relationship for
INSERTs and UPDATEs are checked.
Error Message:
'Tracks' table
- Unable to create index 'PK_Tracks'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location:
statisti.cpp:3222
Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
SPID: 51
Process ID: 828
Many thanks
Gareth KingGareth King (QBVBKADUOIUJ@.spammotel.com) writes:
> I am getting an error message when I try to make an already existing,
> unique index, clustered. I have other databases with the same core
> design, some will allow the clustered index to be created, others
> display the same error. Could anyone tell me what the error message is
> refering to?
> Details are:
> SQL2000 SP3
> The column is of data type varchar.
> I am using Enterprise Manager and trying to change the index through the
> design - properties window.
> There are no nulls in this column.
> There are relationships with six other tables, all bound to this column.
> Enforce relationship for replication and Enforce relationship for
> INSERTs and UPDATEs are checked.
> Error Message:
> 'Tracks' table
> - Unable to create index 'PK_Tracks'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> [Microsoft][ODBC SQL Server Driver][SQL Server]Location:
> statisti.cpp:3222
> Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
> SPID: 51
> Process ID: 828
That seems like an assertion error. An assertion error is a check
that the programmer adds to his code, to be sure that some condition
is true at this point. If they are not, he aborts, because if he
continued he could cause a big mess.
Or more concisely: this is a bug in SQL Server.
What you could try if you want to create this index as quick as
possible, is to configure SQL Server to only use one single processor
and then run the CREATE INDEX statement. These errors are often due
to problems with parallelism.
If that does not work out, I would suggest that you open a case with
Microsoft. Since this is a bug in SQL Server, any expenses you may
be charged initially, should be refunded.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server
.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that mos
t
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100
%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?
Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

cpu going high (100%) after Index is created

Hi,
Any idea why CPU is going to 100% utilization as soon as i created index
(non clustered) on a table which has indexes already on the table.
before creating index the CPU is about 10 to 20% busy, after creating index
it is going to 100% consistently for about 10 minutes then i dropped index
cpu usage came down to normal
Thanks,
SUBBU.creating index is a resource intensive process, that is one of the
reason people normally find a off peak window for reindexing.
Resources varies depends on how many rows you have of the table and how
many columns you include in the non-clustered index and what are they
(data type).
If you can provide clustered index columns (if any), non-clustered
index columns (the one you intended to put), we can find out more.
Mel|||Hi
I understand creating CPU is resource intensive. CPU is going high after
index is created not while being created.
As of now the table has 98000 rows, aprox 800 pages, 1 clustered composite,
6 non clustered composite.
Data is very static in nature
Thanks,
Subbu.
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> creating index is a resource intensive process, that is one of the
> reason people normally find a off peak window for reindexing.
> Resources varies depends on how many rows you have of the table and how
> many columns you include in the non-clustered index and what are they
> (data type).
> If you can provide clustered index columns (if any), non-clustered
> index columns (the one you intended to put), we can find out more.
> Mel
>|||You might just have gotten a bad plan. I would try it again.
--
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hi
> I understand creating CPU is resource intensive. CPU is going high after
> index is created not while being created.
> As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> composite,
> 6 non clustered composite.
> Data is very static in nature
> Thanks,
> Subbu.
>
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>> creating index is a resource intensive process, that is one of the
>> reason people normally find a off peak window for reindexing.
>> Resources varies depends on how many rows you have of the table and how
>> many columns you include in the non-clustered index and what are they
>> (data type).
>> If you can provide clustered index columns (if any), non-clustered
>> index columns (the one you intended to put), we can find out more.
>> Mel
>|||Sorry misunderstood your question in the first place.
One thing to bear in mind that putting index is NOT costless. In fact,
having more than enough indexes can cause problem, particularly if you
enviornment is OLTP. It will help for SELECT but not
UPDATE/INSERT/DELETE (as the index will need to be updated at the same
time).
My rule is always have a one good clustered index and 1-3 non-clustered
indexes should do the job.
Mel|||(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I understand creating CPU is resource intensive. CPU is going high after
> > index is created not while being created.
> >
> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > composite,
> > 6 non clustered composite.
> >
> > Data is very static in nature
> >
> > Thanks,
> > Subbu.
> >
> >
> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> creating index is a resource intensive process, that is one of the
> >> reason people normally find a off peak window for reindexing.
> >>
> >> Resources varies depends on how many rows you have of the table and how
> >> many columns you include in the non-clustered index and what are they
> >> (data type).
> >>
> >> If you can provide clustered index columns (if any), non-clustered
> >> index columns (the one you intended to put), we can find out more.
> >>
> >> Mel
> >>
> >
> >
>|||How many CPU's do you have? It's possible that parallelism is being used
suboptimally. I'd use the Profiler to find which queries are being
affected. For those, you may want to use OPTION (MAXDOP 1).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I understand creating CPU is resource intensive. CPU is going high after
> > index is created not while being created.
> >
> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > composite,
> > 6 non clustered composite.
> >
> > Data is very static in nature
> >
> > Thanks,
> > Subbu.
> >
> >
> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> creating index is a resource intensive process, that is one of the
> >> reason people normally find a off peak window for reindexing.
> >>
> >> Resources varies depends on how many rows you have of the table and how
> >> many columns you include in the non-clustered index and what are they
> >> (data type).
> >>
> >> If you can provide clustered index columns (if any), non-clustered
> >> index columns (the one you intended to put), we can find out more.
> >>
> >> Mel
> >>
> >
> >
>|||Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> > You might just have gotten a bad plan. I would try it again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "subbu" <subbaiahd@.hotmail.com> wrote in message
> > news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > > Hi
> > >
> > > I understand creating CPU is resource intensive. CPU is going high
after
> > > index is created not while being created.
> > >
> > > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > > composite,
> > > 6 non clustered composite.
> > >
> > > Data is very static in nature
> > >
> > > Thanks,
> > > Subbu.
> > >
> > >
> > > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> > >> creating index is a resource intensive process, that is one of the
> > >> reason people normally find a off peak window for reindexing.
> > >>
> > >> Resources varies depends on how many rows you have of the table and
how
> > >> many columns you include in the non-clustered index and what are they
> > >> (data type).
> > >>
> > >> If you can provide clustered index columns (if any), non-clustered
> > >> index columns (the one you intended to put), we can find out more.
> > >>
> > >> Mel
> > >>
> > >
> > >
> >
> >
>|||Well, it's not parallelism, then. I'd still use the profiler to figure out
where the bones are buried.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:eoh%23Ks9XGHA.4248@.TK2MSFTNGP05.phx.gbl...
Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> > You might just have gotten a bad plan. I would try it again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "subbu" <subbaiahd@.hotmail.com> wrote in message
> > news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > > Hi
> > >
> > > I understand creating CPU is resource intensive. CPU is going high
after
> > > index is created not while being created.
> > >
> > > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > > composite,
> > > 6 non clustered composite.
> > >
> > > Data is very static in nature
> > >
> > > Thanks,
> > > Subbu.
> > >
> > >
> > > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> > >> creating index is a resource intensive process, that is one of the
> > >> reason people normally find a off peak window for reindexing.
> > >>
> > >> Resources varies depends on how many rows you have of the table and
how
> > >> many columns you include in the non-clustered index and what are they
> > >> (data type).
> > >>
> > >> If you can provide clustered index columns (if any), non-clustered
> > >> index columns (the one you intended to put), we can find out more.
> > >>
> > >> Mel
> > >>
> > >
> > >
> >
> >
>|||If you query originally took 9 seconds and now takes 1 then you must be
returning a lot of rows. The CPU going to 100% is probably just a sign that
it is now able to process more efficiently and thus faster. If no one lese
is on the server and you issue the query and the CPU spikes to 100% for 1
second and then drops to 0 everything is OK.
--
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
> I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
>> You might just have gotten a bad plan. I would try it again.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "subbu" <subbaiahd@.hotmail.com> wrote in message
>> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> > Hi
>> >
>> > I understand creating CPU is resource intensive. CPU is going high
>> > after
>> > index is created not while being created.
>> >
>> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
>> > composite,
>> > 6 non clustered composite.
>> >
>> > Data is very static in nature
>> >
>> > Thanks,
>> > Subbu.
>> >
>> >
>> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
>> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>> >> creating index is a resource intensive process, that is one of the
>> >> reason people normally find a off peak window for reindexing.
>> >>
>> >> Resources varies depends on how many rows you have of the table and
>> >> how
>> >> many columns you include in the non-clustered index and what are they
>> >> (data type).
>> >>
>> >> If you can provide clustered index columns (if any), non-clustered
>> >> index columns (the one you intended to put), we can find out more.
>> >>
>> >> Mel
>> >>
>> >
>> >
>>
>|||Sorry for responding late.
Query is returning only few rows around 25. After query executed even then
cpu is not coming down, query executed in one second cpu is 100% for almost
10 minutes then i dropped new indexes created , and immeadiately cpu came
down to normal (20% ).
Thanks,
Subbu.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJF9hdAYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> If you query originally took 9 seconds and now takes 1 then you must be
> returning a lot of rows. The CPU going to 100% is probably just a sign
that
> it is now able to process more efficiently and thus faster. If no one lese
> is on the server and you issue the query and the CPU spikes to 100% for 1
> second and then drops to 0 everything is OK.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> >
> > (BI = Before Index Created
> > AI = After Index created)
> >
> >
> > The query for which i have created index is executing faster AI. the
query
> > which was going for index scan (BI) now going for index seek (AI), and
> > total execution time came down from 9 sec to 1 sec. But CPU is going to
> > 100%, I am guessing does the new index made all other queries to run
slow.
> > I
> > dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> > (the data is very static). Is SQL Server not intelligent to ignore
indexes
> > that are not useful in plan preparation. If i create a new index it
looks
> > like other queries which joins this table are going to wrong execution
> > plans. Please help.
> >
> > Thanks,
> > Subbu
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> >> You might just have gotten a bad plan. I would try it again.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "subbu" <subbaiahd@.hotmail.com> wrote in message
> >> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> >
> >> > I understand creating CPU is resource intensive. CPU is going high
> >> > after
> >> > index is created not while being created.
> >> >
> >> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> >> > composite,
> >> > 6 non clustered composite.
> >> >
> >> > Data is very static in nature
> >> >
> >> > Thanks,
> >> > Subbu.
> >> >
> >> >
> >> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> >> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> >> creating index is a resource intensive process, that is one of the
> >> >> reason people normally find a off peak window for reindexing.
> >> >>
> >> >> Resources varies depends on how many rows you have of the table and
> >> >> how
> >> >> many columns you include in the non-clustered index and what are
they
> >> >> (data type).
> >> >>
> >> >> If you can provide clustered index columns (if any), non-clustered
> >> >> index columns (the one you intended to put), we can find out more.
> >> >>
> >> >> Mel
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Again, use the profiler to determine the ling-running queries, as well as
those that take a lot of CPU.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OBoqVgiYGHA.3328@.TK2MSFTNGP02.phx.gbl...
Sorry for responding late.
Query is returning only few rows around 25. After query executed even then
cpu is not coming down, query executed in one second cpu is 100% for almost
10 minutes then i dropped new indexes created , and immeadiately cpu came
down to normal (20% ).
Thanks,
Subbu.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJF9hdAYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> If you query originally took 9 seconds and now takes 1 then you must be
> returning a lot of rows. The CPU going to 100% is probably just a sign
that
> it is now able to process more efficiently and thus faster. If no one lese
> is on the server and you issue the query and the CPU spikes to 100% for 1
> second and then drops to 0 everything is OK.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> >
> > (BI = Before Index Created
> > AI = After Index created)
> >
> >
> > The query for which i have created index is executing faster AI. the
query
> > which was going for index scan (BI) now going for index seek (AI), and
> > total execution time came down from 9 sec to 1 sec. But CPU is going to
> > 100%, I am guessing does the new index made all other queries to run
slow.
> > I
> > dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> > (the data is very static). Is SQL Server not intelligent to ignore
indexes
> > that are not useful in plan preparation. If i create a new index it
looks
> > like other queries which joins this table are going to wrong execution
> > plans. Please help.
> >
> > Thanks,
> > Subbu
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> >> You might just have gotten a bad plan. I would try it again.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "subbu" <subbaiahd@.hotmail.com> wrote in message
> >> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> >
> >> > I understand creating CPU is resource intensive. CPU is going high
> >> > after
> >> > index is created not while being created.
> >> >
> >> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> >> > composite,
> >> > 6 non clustered composite.
> >> >
> >> > Data is very static in nature
> >> >
> >> > Thanks,
> >> > Subbu.
> >> >
> >> >
> >> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> >> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> >> creating index is a resource intensive process, that is one of the
> >> >> reason people normally find a off peak window for reindexing.
> >> >>
> >> >> Resources varies depends on how many rows you have of the table and
> >> >> how
> >> >> many columns you include in the non-clustered index and what are
they
> >> >> (data type).
> >> >>
> >> >> If you can provide clustered index columns (if any), non-clustered
> >> >> index columns (the one you intended to put), we can find out more.
> >> >>
> >> >> Mel
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

cpu going high (100%) after Index is created

Hi,
Any idea why CPU is going to 100% utilization as soon as i created index
(non clustered) on a table which has indexes already on the table.
before creating index the CPU is about 10 to 20% busy, after creating index
it is going to 100% consistently for about 10 minutes then i dropped index
cpu usage came down to normal
Thanks,
SUBBU.creating index is a resource intensive process, that is one of the
reason people normally find a off peak window for reindexing.
Resources varies depends on how many rows you have of the table and how
many columns you include in the non-clustered index and what are they
(data type).
If you can provide clustered index columns (if any), non-clustered
index columns (the one you intended to put), we can find out more.
Mel|||Hi
I understand creating CPU is resource intensive. CPU is going high after
index is created not while being created.
As of now the table has 98000 rows, aprox 800 pages, 1 clustered composite,
6 non clustered composite.
Data is very static in nature
Thanks,
Subbu.
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> creating index is a resource intensive process, that is one of the
> reason people normally find a off peak window for reindexing.
> Resources varies depends on how many rows you have of the table and how
> many columns you include in the non-clustered index and what are they
> (data type).
> If you can provide clustered index columns (if any), non-clustered
> index columns (the one you intended to put), we can find out more.
> Mel
>|||You might just have gotten a bad plan. I would try it again.
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hi
> I understand creating CPU is resource intensive. CPU is going high after
> index is created not while being created.
> As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> composite,
> 6 non clustered composite.
> Data is very static in nature
> Thanks,
> Subbu.
>
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>|||Sorry misunderstood your question in the first place.
One thing to bear in mind that putting index is NOT costless. In fact,
having more than enough indexes can cause problem, particularly if you
enviornment is OLTP. It will help for SELECT but not
UPDATE/INSERT/DELETE (as the index will need to be updated at the same
time).
My rule is always have a one good clustered index and 1-3 non-clustered
indexes should do the job.
Mel|||(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>|||How many CPU's do you have? It's possible that parallelism is being used
suboptimally. I'd use the Profiler to find which queries are being
affected. For those, you may want to use OPTION (MAXDOP 1).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>|||Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
after[vbcol=seagreen]
how[vbcol=seagreen]
>|||Well, it's not parallelism, then. I'd still use the profiler to figure out
where the bones are buried.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:eoh%23Ks9XGHA.4248@.TK2MSFTNGP05.phx.gbl...
Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
after[vbcol=seagreen]
how[vbcol=seagreen]
>|||If you query originally took 9 seconds and now takes 1 then you must be
returning a lot of rows. The CPU going to 100% is probably just a sign that
it is now able to process more efficiently and thus faster. If no one lese
is on the server and you issue the query and the CPU spikes to 100% for 1
second and then drops to 0 everything is OK.
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
> I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
>