CREATE DATABASE failed: index

Showing posts with label index. Show all posts

Thursday, March 29, 2012

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

CREATE an INDEX when using CREATE TABLE?

Is there a way to create a index with create table when it is not a primary
key or unique?
Please see below, to see where I am getting lost.
I can create the index using the code below, but I can not see how to create
the index using CREATE TABLE.
drop table customer
-- Customer Table
CREATE TABLE customer
( personID int not null,
userID varchar(10) null
-- cust_userID_ind NONCLUSTERED (userid)
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
,
since smalldatetime not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
users(userID)
)
go
CREATE INDEX cust_userID_ind
ON customer (userID)
goHi,
Only the Indexes created based on the constraints (PRIMARY AND UNIQUE) will
be created using a
Create Table command.
Other Indexes needs to be created using CREATE INDEX command.
Thanks
Hari
SQL Server MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHs5M65uFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Is there a way to create a index with create table when it is not a
> primary key or unique?
> Please see below, to see where I am getting lost.
> I can create the index using the code below, but I can not see how to
> create the index using CREATE TABLE.
> drop table customer
> -- Customer Table
> CREATE TABLE customer
> ( personID int not null,
> userID varchar(10) null
> -- cust_userID_ind NONCLUSTERED (userid)
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
> ,
> since smalldatetime not null,
> notes varchar(1000) null,
> lastupdate smalldatetime not null,
> updateby varchar(10) not null,
> CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
> CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
> person(personID),
> CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
> users(userID)
> )
> go
> CREATE INDEX cust_userID_ind
> ON customer (userID)
> go
>|||Thank you
"Hari Pra

" <hari_pra

_k@.hotmail.com> wrote in message
news:uWYaqA6uFHA.740@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Only the Indexes created based on the constraints (PRIMARY AND UNIQUE)
> will be created using a
> Create Table command.
> Other Indexes needs to be created using CREATE INDEX command.
> Thanks
> Hari
> SQL Server MVP
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHs5M65uFHA.2312@.TK2MSFTNGP14.phx.gbl...
>

Create an index in a PDF file

Hi!!

I have a report that is exported to a PDF file... Is possible to get an index in the beginning of the file? I have the page number at the bottom of each page but i would need an index...

Thx in advance!!

Any idea? Isn′t possible to do it? At the moment, I havent found anything linked with this...|||

Hi Sergio,

Do you mean a document map? Check out this link: http://msdn2.microsoft.com/en-us/library/ms156383.aspx

|||

Hi Brad!!

not exactly... i would like to appear something similar but in a page in the beginning of the PDF document including the page number where each element is, i.e. the tables of the document... is there any option to do this? it′s very similar to the document map but with the page number...

I see two problems:

- I was thinking of using "=Globals.PageNumber" but i can′t, it says me that only can be placed in the header or the footer...

- and the other problem is setting the content of the document map in a page of the PDF... could I do this?

Perhaps, this is a bit difficult but I am always looking for challenges ... any suggestions? thx in advance!!

Sergio

|||I know of no way to accomplish exactly what you are looking for sorry to say. The document map links to items as repeated on pages. But I am not sure what you mean by "setting the content of the document map in a page of the PDF". If you mean as a page, there is no way to control this.|||

Hi Brad,

that was exactly i ment: i want to show it as a page, but if there is no way to do it... anyway, thank you for your help...

Sergio

Sunday, March 25, 2012

create a page index for a long crystal report

I have to create a page index for a long crystal report which is grouped by product group. Is there any way to create a page index using crystal report to identify the page number in which the product group is appear in the reportHi,

I think you can create Table of contents for the report in the Report header. I haven't worked on this as it requires CR 10... Im working on CR 8.x.. You can refer this article, which explains how to do it.

http://support.businessobjects.com/library/kbase/articles/c2011950.asp

Let me know if it works.

Bye,

Praveen|||Hi,

yes it can possible in CR 9 and CR 10 version,but we are using CR 8.x
is it possible in CR 8.x

thanks

sujesh

Thursday, March 8, 2012

CPU Usage vs Catalog Population

I have an SQL account that uses FTS on SQL 2000.
I originally used Incremental Population to index the work done during the
day.
However, the indexing process was carrying over into the next work day.
Therefore, last week I changed the method to Change Tracking with Update
Index in Background (with John Kane's urging) to try to alleviate the loss
of performance being experienced during the day by the system's users.
It is my understanding that CT w/UIB would only perform indexing when the
CPU is idle and resources are available. By switching to this method, I
thought the overall performance would improve.
This morning I checked the SQL Server, CPU usage is averaging 60% and the
catalog status reports Population in Progress.
Consequently, all users are unable to perform logins, searches, ... because
of timeouts against the database.
I am remote to the application and finally got the menu up to issue the
command to stop population.
Q1:
I thought the Change Tracking with Update Index in Background option is to
prevent high CPU utilization.
Should I be seeing such high utilization with this option selected to the
point that other applications are timing out when trying to make a
connection to the database? Enterprise manager also times out when trying to
connect.
Q2:
The stop command has finally executed and the catalog status now reports
idle.
It has been over an hour and the CPU usage is still very high (50 - 60%).
Is this continued usage the result of the indexing process?
Is there residual processing that could go on for hours after the status
reports idle?
Q3:
Related to Q1, since the population status reports idle but I still see high
CPU utilization, how do I isolate what in particular is keeping the usage so
high? How to prove it is mssearch and not something else going on with the
database?
I see the sqlserver and mssearch processes listed in task mgr with high cpu
& memory usage.
Update index in background does not necessarily index when the CPU is idle
or resources are available. Rather it polls a table every second and indexed
rows which are marked to be re-indexed.
You might want to try a scheduled Update Index at intervals that work for
your requirements. For instance update the index every 1/2 hour.
If your table you are indexing is heavily updated you may run into these
problems with relatively high CPU utilization, you will also run into these
problems if your content is in German or Far East languages.
High CPU utilization just means your processor is busy, it isn't by itself a
bad thing. When it starts to cause execessive paging or create queueing is
when it can degrade overall system performance.
To isolate particular processes using high cpu you can use Performance
Monitor or simply sort the processes you find in Task Manager by CPU. You
can also run sp_who2 to get an idea of cumulative CPU and disk IO.
"RGondzur" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:es8K7UqFEHA.3336@.TK2MSFTNGP12.phx.gbl...
> I have an SQL account that uses FTS on SQL 2000.
> I originally used Incremental Population to index the work done during the
> day.
> However, the indexing process was carrying over into the next work day.
> Therefore, last week I changed the method to Change Tracking with Update
> Index in Background (with John Kane's urging) to try to alleviate the loss
> of performance being experienced during the day by the system's users.
> It is my understanding that CT w/UIB would only perform indexing when the
> CPU is idle and resources are available. By switching to this method, I
> thought the overall performance would improve.
> This morning I checked the SQL Server, CPU usage is averaging 60% and the
> catalog status reports Population in Progress.
> Consequently, all users are unable to perform logins, searches, ...
because
> of timeouts against the database.
> I am remote to the application and finally got the menu up to issue the
> command to stop population.
>
> Q1:
> I thought the Change Tracking with Update Index in Background option is to
> prevent high CPU utilization.
> Should I be seeing such high utilization with this option selected to the
> point that other applications are timing out when trying to make a
> connection to the database? Enterprise manager also times out when trying
to
> connect.
>
> Q2:
> The stop command has finally executed and the catalog status now reports
> idle.
> It has been over an hour and the CPU usage is still very high (50 - 60%).
> Is this continued usage the result of the indexing process?
> Is there residual processing that could go on for hours after the status
> reports idle?
>
> Q3:
> Related to Q1, since the population status reports idle but I still see
high
> CPU utilization, how do I isolate what in particular is keeping the usage
so
> high? How to prove it is mssearch and not something else going on with the
> database?
> I see the sqlserver and mssearch processes listed in task mgr with high
cpu
> & memory usage.
>
>

Wednesday, March 7, 2012

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

Friday, February 24, 2012

covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
Sri
Hi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>
|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
SriHi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
SriHi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

Covering Index Question

we have a table with the following structure (not full but should give an
idea how it looks like)
CREATE TABLE [dbo].[JK_Product] (
[Creator] [varchar] (50) NOT NULL ,
[ID] [int] NOT NULL ,
[Product] [int] NOT NULL ,
[PartNum] [char] (32) NOT NULL ,
..
..
..
[CheckShipping] [char] (1) NULL
) ON [PRIMARY]
GO
We have these 3 indexes already on this table
CLUSTERED INDEX1
([Creator], [Product])
PRIMARY KEY NONCLUSTERED INDEX1
([Creator],[ID])
NONCLUSTERED INDEX2
([Creator], [Product], [PartNum])
ITW suggested me these 3 new indexes based on some workload i gave
NONCLUSTERED INDEX3
([ID])
NONCLUSTERED INDEX4
([PartNum])
NONCLUSTERED INDEX5
([Product], [Creator])
is it necessary to create INDEX3 and INDEX4 since we already have those
covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
use of the existing indexes effectively? what if we create a covering NCI
consisting of Creator, ID, Product, PartNum columns and drop other NCIs
TIAHi
You need not create Index 3 and 4 as Index 1 and 2 takes care of it.
Creating an Index depends on the query that you frequently use.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"para

a" wrote:

> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||First, it is meaningless to talk about a clustered index covering a query. A
clustered indexes
covers all queries against the table.
So lets focus on the other indexes. If you don't have what you search for in
the first column of the
index, SQL Server cannot s

for that condition. It can scan the index, but
not s

. That might be
why it suggested an index in the ID, PartNum and Product, Creator. In short,
without knowing your
workload, selectivity etc, we cannot say whether the recommendations from IT
W are good
recommendations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"para

a" <para

a@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer
> make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||tibor thx for the reply. yeah, i know a clustered index covers all queries
against the table. i was more interested to find out if instead of having 3-
4
small NCIs to cover some X queries, wouldn't it be better to have one long
NCI in general (I haven't fixed the order of columns in it yet...and maybe
the order can be fixed on further analysis of %age of its use in those X
queries)?
"Tibor Karaszi" wrote:

> First, it is meaningless to talk about a clustered index covering a query.
A clustered indexes
> covers all queries against the table.
> So lets focus on the other indexes. If you don't have what you search for
in the first column of the
> index, SQL Server cannot s

for that condition. It can scan the index, b
ut not s

. That might be
> why it suggested an index in the ID, PartNum and Product, Creator. In shor
t, without knowing your
> workload, selectivity etc, we cannot say whether the recommendations from
ITW are good
> recommendations...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "para

a" <para

a@.discussions.microsoft.com> wrote in message
> news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
>|||The ordering of the columns is one big issue. If you have a query with a whe
re clause in which you
only search for colA, then an index on (colB, colA) will not be searchable.
SQL Server can still
scan the index (assuming it covers the query), but a search is much better (
especially if the index
covers the query as no bookmark lookups are needed).
So, you could say that it is better to create a bunch of one-column indexes
and let SQL Server join
them as the query is processed (index intersection) so that the indexes toge
ther will cover the
query. Now you can search for whichever column you have ion the indexes and
you can always have a
s

. OTOH, you will pay the price for the index intersections as the query
is processed (in the
cases then SQL Server joins the indexes in run-time).
So, we cannot say which is better. You can, but either analyzing the queries
etc. Or by running a
load test for your particular load and see which index alternative is pest f
or your load test.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"para

a" <para

a@.discussions.microsoft.com> wrote in message
news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
> tibor thx for the reply. yeah, i know a clustered index covers all queries
> against the table. i was more interested to find out if instead of having
3-4
> small NCIs to cover some X queries, wouldn't it be better to have one long
> NCI in general (I haven't fixed the order of columns in it yet...and maybe
> the order can be fixed on further analysis of %age of its use in those X
> queries)?
> "Tibor Karaszi" wrote:
>|||In addition you may consider the space requirements for the nonclustered
indexes. This can vary but can be high for a table with a large number of
rows and although not recommended - a large clustered index key (will be
dupped in each nc index).
HTH
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1CSKoZzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> The ordering of the columns is one big issue. If you have a query with a
> where clause in which you only search for colA, then an index on (colB,
> colA) will not be searchable. SQL Server can still scan the index
> (assuming it covers the query), but a search is much better (especially if
> the index covers the query as no bookmark lookups are needed).
> So, you could say that it is better to create a bunch of one-column
> indexes and let SQL Server join them as the query is processed (index
> intersection) so that the indexes together will cover the query. Now you
> can search for whichever column you have ion the indexes and you can
> always have a s

. OTOH, you will pay the price for the index
> intersections as the query is processed (in the cases then SQL Server
> joins the indexes in run-time).
> So, we cannot say which is better. You can, but either analyzing the
> queries etc. Or by running a load test for your particular load and see
> which index alternative is pest for your load test.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "para

a" <para

a@.discussions.microsoft.com> wrote in message
> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>

covering index query

hi all,
can anybody help me out with a covering index query and
say how exactly it helps out in data retreival?
thanks,
james bondFrom the SQL 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_05_2ri0.htm">
Covered queries can improve performance. Covered queries are queries where
all the columns specified in the query are contained within the same index.
For example, a query retrieving columns a and b from a table that has a
composite index created on columns a, b, and c is considered covered.
Creating indexes that cover a query can improve performance because all the
data for the query is contained within the index itself; only the index
pages, not the data pages, of the table must be referenced to retrieve the
data, thereby reducing overall I/O. Although adding columns to an index to
cover queries can improve performance, maintaining the extra columns in the
index incurs update and storage costs.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jamesbond" <anonymous@.discussions.microsoft.com> wrote in message
news:063201c3b57d$0b2e0290$a301280a@.phx.gbl...
> hi all,
> can anybody help me out with a covering index query and
> say how exactly it helps out in data retreival?
> thanks,
> james bond

Covering Index

Hi,
How can one create covering index and what are the benefits of having covering index?
Thanks much in advance
Thank
GYKCheck out: http://www.sql-server-performance.com/covering_indexes.asp
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
> How can one create covering index and what are the benefits of having
covering index?
> Thanks much in advance.
> Thanks
> GYK|||Hi,
There is no seperate syntax for Covering Index. Covering index is nothing
but, if your query (Select statement) reads the data from Index page
rather than scanning data page.
For result, Since the Query reads the Index page the execution will be much
faster.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
> How can one create covering index and what are the benefits of having
covering index?
> Thanks much in advance.
> Thanks
> GYK|||Hi Hari,
Thanks for your reponse. But the way you have explained, it looks more like a non-clustered index. Even in non-clustered index, the data is first scanned through the index pages(pointers) and then the data is fetched. Am not able to understand the exact difference between them.
Thanks
GYK
-- Hari wrote: --
Hi,
There is no seperate syntax for Covering Index. Covering index is nothing
but, if your query (Select statement) reads the data from Index page
rather than scanning data page.
For result, Since the Query reads the Index page the execution will be much
faster.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
>> How can one create covering index and what are the benefits of having
covering index?
>> Thanks much in advance.
>> Thanks
> GYK|||It is a bit similar to a clustered index, actually. Imagine you have an NC
index which contains all the columns you are referring to in your query.
Then there is no need for SQL Server to go an visit the data pages. The
index pages has all the information that the query refers to!
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:A3193BD2-EA46-4299-9ED1-6A2DA35C8E86@.microsoft.com...
> Hi Hari,
> Thanks for your reponse. But the way you have explained, it looks more
like a non-clustered index. Even in non-clustered index, the data is first
scanned through the index pages(pointers) and then the data is fetched. Am
not able to understand the exact difference between them.
> Thanks
> GYK
> -- Hari wrote: --
> Hi,
> There is no seperate syntax for Covering Index. Covering index is
nothing
> but, if your query (Select statement) reads the data from Index page
> rather than scanning data page.
> For result, Since the Query reads the Index page the execution will
be much
> faster.
>
> Thanks
> Hari
> MCDBA
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> > Hi,
> >> How can one create covering index and what are the benefits of
having
> covering index?
> >> Thanks much in advance.
> >> Thanks
> > GYK
>
>|||Hi,
Thanks guys, I think i got the concept :-)
Basically I guess it is just a concept, where in all the information provided by the users (in the where clause) is covered by the non-clustered index, and it refers to the index pages rather than the data pages. Hope am right here
Thanks very muc
Yogish|||Hi Yogesh,
Like Tibor mentioned , not only the where clause even the contents in select
clause will be picked from Index page.
eg:
If You have an index on empno, empname,empsex field in empdetails table,
you query is:
select empno,empname,empsex from empdetails where empno betweeen 100 and 110
and empsex='m'
In this case empno , empname and empsex will be picked from Index page, this
select statement never reads the data page to extract emp info.
Tibor,
Correct me if I am wrong.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:43255A14-BA5A-4003-805F-57B2E9190AD6@.microsoft.com...
> Hi,
> Thanks guys, I think i got the concept :-)
> Basically I guess it is just a concept, where in all the information
provided by the users (in the where clause) is covered by the non-clustered
index, and it refers to the index pages rather than the data pages. Hope am
right here?
> Thanks very much
> Yogish|||> In this case empno , empname and empsex will be picked from Index page,
this
> select statement never reads the data page to extract emp info.
> Tibor,
> Correct me if I am wrong.
Correctomundo! :-)
Also, SQL Server can even combine several NC indexes so that the combination
of those covers a query. So we might want to differentiate between a covered
index (the index covers the whole query) and a query which is covered by
indexes (where (possibly) several indexes in combination covers a query).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23spIxkQqDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Hi Yogesh,
> Like Tibor mentioned , not only the where clause even the contents in
select
> clause will be picked from Index page.
> eg:
> If You have an index on empno, empname,empsex field in empdetails table,
> you query is:
> select empno,empname,empsex from empdetails where empno betweeen 100 and
110
> and empsex='m'
> In this case empno , empname and empsex will be picked from Index page,
this
> select statement never reads the data page to extract emp info.
> Tibor,
> Correct me if I am wrong.
>
> Thanks
> Hari
> MCDBA
>
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:43255A14-BA5A-4003-805F-57B2E9190AD6@.microsoft.com...
> > Hi,
> >
> > Thanks guys, I think i got the concept :-)
> >
> > Basically I guess it is just a concept, where in all the information
> provided by the users (in the where clause) is covered by the
non-clustered
> index, and it refers to the index pages rather than the data pages. Hope
am
> right here?
> >
> > Thanks very much
> > Yogish
>|||Hi Hari, Tibor
Thanks a lot. I get the picture now...
GYK|||I have a question to what Hari has sai
Hari said, the following
Table - Empdetail
Query - Select empno,empname,empsex from empdetails where empno betweeen 100 and 11
and empsex='m
Index - empno, empname,empse
My questio
=======Index is on 3 columns, but "Where" clause is on 2 columns, will still the index be used|||Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:A200A69F-42AF-4233-AAA1-7A44C300E59C@.microsoft.com...
> I have a question to what Hari has said
> Hari said, the following
> Table - Empdetails
> Query - Select empno,empname,empsex from empdetails where
empno betweeen 100 and 110
> and empsex='m'
> Index - empno, empname,empsex
> My question
> ========> Index is on 3 columns, but "Where" clause is on 2 columns, will still the
index be used?
>

Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks
Some indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks

Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
ThanksSome indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks

Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
ThanksSome indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks

Thursday, March 29, 2012

Create as Clustered Index Error

CREATE an INDEX when using CREATE TABLE?

Create an index in a PDF file

Sunday, March 25, 2012

create a page index for a long crystal report

Thursday, March 8, 2012

CPU Usage vs Catalog Population

Wednesday, March 7, 2012

cpu going high (100%) after Index is created

cpu going high (100%) after Index is created

Friday, February 24, 2012

covering indexes

covering indexes

covering indexes

Covering Index Question

covering index query

Covering Index

Covered Index

Covered Index

Covered Index

CREATE DATABASE failed

Blog Archive

About Me