Basic question.... on Index....

Why the name clustered is used in the clustered index? What is exactly
clustered here?
Thanks,
Jessy
Hi,
Physical ordering of data inside the data page will be same as the Index key
order.
The pages in the data chain and the records on the pages are ordered on the
value of the clustered index key. All inserts are made at the point the key
value in the inserted row fits in the ordering sequence.
See more details of cluetsred index in books online.
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
|||Clustered means the index keys and the table are clustered in the same page?
Jessy
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Physical ordering of data inside the data page will be same as the Index
key
> order.
> The pages in the data chain and the records on the pages are ordered on
the
> value of the clustered index key. All inserts are made at the point the
key
> value in the inserted row fits in the ordering sequence.
> See more details of cluetsred index in books online.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>
|||Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/de...es_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>
|||"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
As an example...
A clustered index is sort of like a dictionary. The keys (dictionary words)
are stored in sorted order. If you need to add a new row called Alligator,
then it would be placed in the correct location in the dictionary.
A non-clustered index is more like the index you find at the back of a book.
The index at the back of the book is stored in sorted order, however, it has
page numbers which tell you to go to page xx to find your word. When you
insert a row here, the data will be appended to the end of the table (unless
you have a clustered index on it), and the index at the back of the book
will be updated appropriately.
One thing to note is that when you have a nonclustered index on a table that
has a clustered index, the "page numbers" don't actually point at the pages,
but rather point at the clustered index key (dictionary word from above).
When you do an insert, the nonclustered index has it's row modified and it
then points to the clustered index key and the row is added in the
dictionary order.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Excellent explanation. This is the sort of explanation I was looking
Thank you very much,
Jessy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYPXjysZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> As an example...
> A clustered index is sort of like a dictionary. The keys (dictionary
words)
> are stored in sorted order. If you need to add a new row called
Alligator,
> then it would be placed in the correct location in the dictionary.
> A non-clustered index is more like the index you find at the back of a
book.
> The index at the back of the book is stored in sorted order, however, it
has
> page numbers which tell you to go to page xx to find your word. When you
> insert a row here, the data will be appended to the end of the table
(unless
> you have a clustered index on it), and the index at the back of the book
> will be updated appropriately.
> One thing to note is that when you have a nonclustered index on a table
that
> has a clustered index, the "page numbers" don't actually point at the
pages,
> but rather point at the clustered index key (dictionary word from above).
> When you do an insert, the nonclustered index has it's row modified and it
> then points to the clustered index key and the row is added in the
> dictionary order.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data
is stored on the same or side by side pages. An IO of 1 page will return
multiple rows that satisfy such a query, reducing IO time in a big way.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
|||Hari:
I'm not sure what you mean by this statement. Your statement is true for
non-clustered indexes, but for the clustered index, the index key and the
data are on the same pages and ARE the data pages. The clustered index does
not use Index pages; they are the data pages, at the leaf level.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Tcu7vsZFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/de...es_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>
sql

Basic question.... on Index....

Why the name clustered is used in the clustered index? What is exactly
clustered here?
Thanks,
JessyHi,
Physical ordering of data inside the data page will be same as the Index key
order.
The pages in the data chain and the records on the pages are ordered on the
value of the clustered index key. All inserts are made at the point the key
value in the inserted row fits in the ordering sequence.
See more details of cluetsred index in books online.
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Clustered means the index keys and the table are clustered in the same page?
Jessy
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Physical ordering of data inside the data page will be same as the Index
key
> order.
> The pages in the data chain and the records on the pages are ordered on
the
> value of the clustered index key. All inserts are made at the point the
key
> value in the inserted row fits in the ordering sequence.
> See more details of cluetsred index in books online.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Why the name clustered is used in the clustered index? What is exactly
> > clustered here?
> >
> > Thanks,
> >
> > Jessy
> >
> >
>|||Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Physical ordering of data inside the data page will be same as the Index
> key
>> order.
>> The pages in the data chain and the records on the pages are ordered on
> the
>> value of the clustered index key. All inserts are made at the point the
> key
>> value in the inserted row fits in the ordering sequence.
>> See more details of cluetsred index in books online.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
>> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>> > Why the name clustered is used in the clustered index? What is exactly
>> > clustered here?
>> >
>> > Thanks,
>> >
>> > Jessy
>> >
>> >
>>
>|||"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
As an example...
A clustered index is sort of like a dictionary. The keys (dictionary words)
are stored in sorted order. If you need to add a new row called Alligator,
then it would be placed in the correct location in the dictionary.
A non-clustered index is more like the index you find at the back of a book.
The index at the back of the book is stored in sorted order, however, it has
page numbers which tell you to go to page xx to find your word. When you
insert a row here, the data will be appended to the end of the table (unless
you have a clustered index on it), and the index at the back of the book
will be updated appropriately.
One thing to note is that when you have a nonclustered index on a table that
has a clustered index, the "page numbers" don't actually point at the pages,
but rather point at the clustered index key (dictionary word from above).
When you do an insert, the nonclustered index has it's row modified and it
then points to the clustered index key and the row is added in the
dictionary order.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent explanation. This is the sort of explanation I was looking
Thank you very much,
Jessy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYPXjysZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Why the name clustered is used in the clustered index? What is exactly
> > clustered here?
> >
> > Thanks,
> >
> > Jessy
> >
> >
> As an example...
> A clustered index is sort of like a dictionary. The keys (dictionary
words)
> are stored in sorted order. If you need to add a new row called
Alligator,
> then it would be placed in the correct location in the dictionary.
> A non-clustered index is more like the index you find at the back of a
book.
> The index at the back of the book is stored in sorted order, however, it
has
> page numbers which tell you to go to page xx to find your word. When you
> insert a row here, the data will be appended to the end of the table
(unless
> you have a clustered index on it), and the index at the back of the book
> will be updated appropriately.
> One thing to note is that when you have a nonclustered index on a table
that
> has a clustered index, the "page numbers" don't actually point at the
pages,
> but rather point at the clustered index key (dictionary word from above).
> When you do an insert, the nonclustered index has it's row modified and it
> then points to the clustered index key and the row is added in the
> dictionary order.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data
is stored on the same or side by side pages. An IO of 1 page will return
multiple rows that satisfy such a query, reducing IO time in a big way.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Hari:
I'm not sure what you mean by this statement. Your statement is true for
non-clustered indexes, but for the clustered index, the index key and the
data are on the same pages and ARE the data pages. The clustered index does
not use Index pages; they are the data pages, at the leaf level.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Tcu7vsZFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Physical ordering of data inside the data page will be same as the Index
> key
>> order.
>> The pages in the data chain and the records on the pages are ordered on
> the
>> value of the clustered index key. All inserts are made at the point the
> key
>> value in the inserted row fits in the ordering sequence.
>> See more details of cluetsred index in books online.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
>> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>> > Why the name clustered is used in the clustered index? What is exactly
>> > clustered here?
>> >
>> > Thanks,
>> >
>> > Jessy
>> >
>> >
>>
>

Basic question.... on Index....

Why the name clustered is used in the clustered index? What is exactly
clustered here?
Thanks,
JessyHi,
Physical ordering of data inside the data page will be same as the Index key
order.
The pages in the data chain and the records on the pages are ordered on the
value of the clustered index key. All inserts are made at the point the key
value in the inserted row fits in the ordering sequence.
See more details of cluetsred index in books online.
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Clustered means the index keys and the table are clustered in the same page?
Jessy
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Physical ordering of data inside the data page will be same as the Index
key
> order.
> The pages in the data chain and the records on the pages are ordered on
the
> value of the clustered index key. All inserts are made at the point the
key
> value in the inserted row fits in the ordering sequence.
> See more details of cluetsred index in books online.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>|||Hi,
No. There are seperate Index and Data pages. read the below article.
_05_5h6b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>|||"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
As an example...
A clustered index is sort of like a dictionary. The keys (dictionary words)
are stored in sorted order. If you need to add a new row called Alligator,
then it would be placed in the correct location in the dictionary.
A non-clustered index is more like the index you find at the back of a book.
The index at the back of the book is stored in sorted order, however, it has
page numbers which tell you to go to page xx to find your word. When you
insert a row here, the data will be appended to the end of the table (unless
you have a clustered index on it), and the index at the back of the book
will be updated appropriately.
One thing to note is that when you have a nonclustered index on a table that
has a clustered index, the "page numbers" don't actually point at the pages,
but rather point at the clustered index key (dictionary word from above).
When you do an insert, the nonclustered index has it's row modified and it
then points to the clustered index key and the row is added in the
dictionary order.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent explanation. This is the sort of explanation I was looking
Thank you very much,
Jessy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYPXjysZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> As an example...
> A clustered index is sort of like a dictionary. The keys (dictionary
words)
> are stored in sorted order. If you need to add a new row called
Alligator,
> then it would be placed in the correct location in the dictionary.
> A non-clustered index is more like the index you find at the back of a
book.
> The index at the back of the book is stored in sorted order, however, it
has
> page numbers which tell you to go to page xx to find your word. When you
> insert a row here, the data will be appended to the end of the table
(unless
> you have a clustered index on it), and the index at the back of the book
> will be updated appropriately.
> One thing to note is that when you have a nonclustered index on a table
that
> has a clustered index, the "page numbers" don't actually point at the
pages,
> but rather point at the clustered index key (dictionary word from above).
> When you do an insert, the nonclustered index has it's row modified and it
> then points to the clustered index key and the row is added in the
> dictionary order.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data
is stored on the same or side by side pages. An IO of 1 page will return
multiple rows that satisfy such a query, reducing IO time in a big way.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Hari:
I'm not sure what you mean by this statement. Your statement is true for
non-clustered indexes, but for the clustered index, the index key and the
data are on the same pages and ARE the data pages. The clustered index does
not use Index pages; they are the data pages, at the leaf level.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Tcu7vsZFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
No. There are seperate Index and Data pages. read the below article.
_05_5h6b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>

Bad executed Plan and wrong Result by SQL

I have one query that executes many times in a week.
I created one Maintenances plan that Rebuild all index in my Database that
has been executed at 23:40 Saturday until stop finished at Sunday.

However at middle of week (Wednesday or Thursday), that query dont return
result like that must be. The time exceeded and the result are total wrong.

I compare the normal executed plan and the crazy one that SQL create to
mount result.

The normal is nested with index seek (very fast, the wrong is Merger with
hash aggregate (very slow). After Index Rebuild, the executed plan bring
result that must be, but when the merge plan are executed with many updates
on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, the
result are total wrong, with many rows back.

I recommended Index Seek force by coalesce function on one column
aggregate, but everyone here were very panic with that behavior of SQL
Server.

Please , anyone help me to explain that!

Krisnamourt!

P.S: Attachments :

--Force Index Query with coalesce
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL

--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL

--
Message posted via http://www.sqlmonster.comStmtText
-----------------------
-----------------------
------------
--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
option(merge join)

(1 row(s) affected)

StmtText
-----------------------
-----------------------
----------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM([partialagg1003])))
|--Parallelism(Gather Streams)
|--Merge Join(Inner Join, MERGE:([G].[HANDLE])=([E].[GUIA])
, RESIDUAL:([G].[HANDLE]=[E].[GUIA]))
|--Parallelism(Distribute Streams, PARTITION COLUMNS:
([G].[HANDLE]))
| |--Index Seek(OBJECT:([Saude].[dbo].[SAM_GUIA].
[AX_1603PEG] AS [G]), SEEK:([G].[PEG]=736740) ORDERED FORWARD)
|--Sort(ORDER BY:([E].[GUIA] ASC))
|--Hash Match(Aggregate, HASH:([E].[GUIA]),
RESIDUAL:([E].[GUIA]=[E].[GUIA]) DEFINE:([partialagg1003]=COUNT(*)))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([E].[GUIA]))
|--Clustered Index Scan(OBJECT:([Saude]
..[dbo].[SAM_GUIA_EVENTOS].[PK__SAM_GUIA_EVENTOS__68736660] AS [E]), WHERE:(
[E].[CLASSEGERENCIALPAGTO]=NULL))

(10 row(s) affected)

--
Message posted via http://www.sqlmonster.com|||I Mean...the wrong result bring back many row with E.CLASSEGERENCIALPAGTO
not null(this column shows many data )...CRAZY!!!

Anyone help me to explain that!!

Kris

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I have one query that executes many times in a week.
> I created one Maintenances plan that Rebuild all index in my Database that
> has been executed at 23:40 Saturday until stop finished at Sunday.
> However at middle of week (Wednesday or Thursday), that query don't
> return result like that must be. The time exceeded and the result are
> total wrong.
> I compare the normal executed plan and the "crazy" one that SQL create to
> mount result.
> The normal is nested with index seek (very fast, the wrong is Merger
> with hash aggregate (very slow). After Index Rebuild, the executed plan
> bring result that must be, but when the merge plan are executed with
> many updates on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of
> week, the result are total wrong, with many rows back.
> I recommended Index Seek force by coalesce function on one column
> aggregate, but everyone here were very panic with that behavior of SQL
> Server.

Do I understand you clarifiation in the other article correctly, that
when you say "results are total wrong", you do in fact mean the query
plan? If you really get incorrect resuls from the query, this is a
serious bug, and you should definitely open a case with Microsoft to
have it investigate.

If the problem is "only" the incorrect query plan, and the slow execution
time, this is more "normal" behaviour.

Recall that SQL Server uses a cost-based optimizer that estimates the
cost of various query plans from statistics about the data. A small
error in the estimate can have serious consequences.

Since you have good performance after index rebuild, it might be a good
idea to schedule index rebuild on these two tables daily.

I also notice that the bad plan involves parallelism. If you add
OPTION (MAXDOP 1), you tell SQL Server not to use parallelism. This
is often enough to get a good plan.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The real problem is incorret result. I cant rebuild index on these two
table , because our scenario works 24 hours by day. These table are too big
(17 Gbytes one and 4 Gbytes other), with many Index. The Index Rebuild only
can do at weekends. I intend to eliminated some Index that are redundant(I
just begun), but that bug is very crazy. That became SQL Server not a good
solution for OLTP that grows up strongly. I saw many scenarios like
that...bad performance when the Database became too large.

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.SQLMonster.com) writes:
> The real problem is incorret result. I cant rebuild index on these two
> table , because our scenario works 24 hours by day. These table are too
> big (17 Gbytes one and 4 Gbytes other), with many Index. The Index
> Rebuild only can do at weekends. I intend to eliminated some Index that
> are redundant(I just begun), but that bug is very crazy. That became SQL
> Server not a good solution for OLTP that grows up strongly. I saw many
> scenarios like that...bad performance when the Database became too
> large.

Looking at your query, the incorrect results may be a known issue.
I think I recognize the type of query. I would suggest that you open
a case with Microsoft to investigate this.

If there is a fix, it is likely to be available in SP4 which was recently.
Unfortunate there is an issue which concerns AWE which I would expect to
concern you, given your table sizes. I would expect Microsoft to have a fix
for this issue soon, though. See further
http://www.microsoft.com/sql/downloads/2000/sp4.asp.

Note that SP4 is only likely to address the incorrect result. The query
plan and the fragmentation is less likely to improve.

Some questions:
o Do you have autostats enabled on these tables? (Maybe you should turn
them off)
o What actual fragmentation do you have by the middle of the week?

If the fragmentation increases rapidly, maybe you should look at changing
the clustered index to one that is less prone to fragmentation given
the update pattern. But here is of course a tradeoff with queries
that may depend on the clustered index.

Could you post the CREATE TABLE and CREATE INDEX statements for the
two tables?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp