sql2k sp3
Two boxes. Same db's, tables, indexes, queries. Indexes
are being used by 1 but not the other. Any ideas?
Thanks in advance.I'd check the statistics on the tables in question (SELECT
STATS_DATE(id, indid) FROM sysindexes). If the table that
isn't using an index has older statistics, then update
them.
I'm assuming the data content of the table is identical
(same number of rows, same result set, etc.).
Bruce.
>--Original Message--
>sql2k sp3
>Two boxes. Same db's, tables, indexes, queries. Indexes
>are being used by 1 but not the other. Any ideas?
>Thanks in advance.
>.
>|||I tried updating the stats to no avail. I didnt realize
amount of data would have any effect. The one ignoring the
indexes has data. The one using the indexes is empty. Any
ideas?
>--Original Message--
>I'd check the statistics on the tables in question
(SELECT
>STATS_DATE(id, indid) FROM sysindexes). If the table
that
>isn't using an index has older statistics, then update
>them.
>I'm assuming the data content of the table is identical
>(same number of rows, same result set, etc.).
>Bruce.
>>--Original Message--
>>sql2k sp3
>>Two boxes. Same db's, tables, indexes, queries. Indexes
>>are being used by 1 but not the other. Any ideas?
>>Thanks in advance.
>>.
>.
>|||If the selectivity of the value is too low it may not use the index. If you
don't have any data then the selectivity is pretty high<g>.
--
Andrew J. Kelly
SQL Server MVP
"chris" <chrisr@.fingps.com> wrote in message
news:050601c3789d$d6ec6100$a101280a@.phx.gbl...
> I tried updating the stats to no avail. I didnt realize
> amount of data would have any effect. The one ignoring the
> indexes has data. The one using the indexes is empty. Any
> ideas?
>
> >--Original Message--
> >I'd check the statistics on the tables in question
> (SELECT
> >STATS_DATE(id, indid) FROM sysindexes). If the table
> that
> >isn't using an index has older statistics, then update
> >them.
> >
> >I'm assuming the data content of the table is identical
> >(same number of rows, same result set, etc.).
> >
> >Bruce.
> >
> >>--Original Message--
> >>sql2k sp3
> >>
> >>Two boxes. Same db's, tables, indexes, queries. Indexes
> >>are being used by 1 but not the other. Any ideas?
> >>
> >>Thanks in advance.
> >>.
> >>
> >.
> >|||Are the physical machines the same? Same number of CPUs
and Memory? If not then execution plans can be different.
>--Original Message--
>If the selectivity of the value is too low it may not use
the index. If you
>don't have any data then the selectivity is pretty
high<g>.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"chris" <chrisr@.fingps.com> wrote in message
>news:050601c3789d$d6ec6100$a101280a@.phx.gbl...
>> I tried updating the stats to no avail. I didnt realize
>> amount of data would have any effect. The one ignoring
the
>> indexes has data. The one using the indexes is empty.
Any
>> ideas?
>>
>> >--Original Message--
>> >I'd check the statistics on the tables in question
>> (SELECT
>> >STATS_DATE(id, indid) FROM sysindexes). If the table
>> that
>> >isn't using an index has older statistics, then update
>> >them.
>> >
>> >I'm assuming the data content of the table is identical
>> >(same number of rows, same result set, etc.).
>> >
>> >Bruce.
>> >
>> >>--Original Message--
>> >>sql2k sp3
>> >>
>> >>Two boxes. Same db's, tables, indexes, queries.
Indexes
>> >>are being used by 1 but not the other. Any ideas?
>> >>
>> >>Thanks in advance.
>> >>.
>> >>
>> >.
>> >
>
>.
>
Showing posts with label sql2k. Show all posts
Showing posts with label sql2k. Show all posts
Wednesday, March 7, 2012
indexes are slowing down my query
sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.c...ite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisRcomposite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.c...ite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisRcomposite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ
indexes are slowing down my query
sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
*** note: someone mentioned yesterday (thank you) that the
results are due to the ITW's index being a covered index.
That makes sense. But I am still not getting why my query
takes 20 seconds longer after adding individual indexes?
***
TIA, ChrisRHi,
Here is a bit of information I found in BOL:
"
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.
"
It seems that your individual indexes DO NOT cover your
query. Each time you run your query, the optimizer is
using 4 different index (one at a time) and it has
increased disk i/o. Increased disk i/o means slow query
response.
hth
DeeJay
>--Original Message--
>sql2k sp3
>Ive got a query that takes 30 seconds to run:
>
> select top 50 customerkey as customerkey from
transdtl
> where MerchName in ('DTV*DIRECTV
SERVICE','DTV*DIRECTV
>SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey
> order by CustomerKey,min(TransDate)
>
>Index Tuning Wizard wants to create an Composite Index on
>MerchName, CustomerKey, TranCode, and TransDate. There is
>already an Composite Index on TransDate, TranCode(in this
>order) but for kicks I do the Wizards deal. Pretty cool
in
>that my query speeds up to only 1 second. Any yes, I
>cleared the cache. Ive always followed the advice from
>this article:
>http://www.sql-server-
performance.com/composite_indexes.asp
>and for the most part used single column Indexes instead
>of Composites. So, I drop the newly created Composite
>Indexes, create 4 single coulmn Indexes, and expect it to
>be about the same. Not only was it slower, but it was
>slower now than when there was no Indexes on these
>columns. 50 seconds. Ive tested this alot over the last
>couple of days and its very consistant. Im getting a
table
>scan on the TranCode coulmn I noticed. I used an Index
>Hint and have the same thing. Why is the Composite
soooooo
>much faster than the individuals? Why Is a Table Scan
>occuring? Why is it slower than before? All ideas are
>appreciated.
>*** note: someone mentioned yesterday (thank you) that
the
>results are due to the ITW's index being a covered index.
>That makes sense. But I am still not getting why my query
>takes 20 seconds longer after adding individual indexes?
>***
>TIA, ChrisR
>.
>|||>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?
>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>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.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>>--Original Message--
>>sql2k sp3
>>Ive got a query that takes 30 seconds to run:
>>
>> select top 50 customerkey as customerkey from
>transdtl
>> where MerchName in ('DTV*DIRECTV
>SERVICE','DTV*DIRECTV
>>SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey
>> order by CustomerKey,min(TransDate)
>>
>>Index Tuning Wizard wants to create an Composite Index
on
>>MerchName, CustomerKey, TranCode, and TransDate. There
is
>>already an Composite Index on TransDate, TranCode(in
this
>>order) but for kicks I do the Wizards deal. Pretty cool
>in
>>that my query speeds up to only 1 second. Any yes, I
>>cleared the cache. Ive always followed the advice from
>>this article:
>>http://www.sql-server-
>performance.com/composite_indexes.asp
>>and for the most part used single column Indexes instead
>>of Composites. So, I drop the newly created Composite
>>Indexes, create 4 single coulmn Indexes, and expect it
to
>>be about the same. Not only was it slower, but it was
>>slower now than when there was no Indexes on these
>>columns. 50 seconds. Ive tested this alot over the last
>>couple of days and its very consistant. Im getting a
>table
>>scan on the TranCode coulmn I noticed. I used an Index
>>Hint and have the same thing. Why is the Composite
>soooooo
>>much faster than the individuals? Why Is a Table Scan
>>occuring? Why is it slower than before? All ideas are
>>appreciated.
>>*** note: someone mentioned yesterday (thank you) that
>the
>>results are due to the ITW's index being a covered
index.
>>That makes sense. But I am still not getting why my
query
>>takes 20 seconds longer after adding individual indexes?
>>***
>>TIA, ChrisR
>>.
>.
>|||On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:
>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
*** note: someone mentioned yesterday (thank you) that the
results are due to the ITW's index being a covered index.
That makes sense. But I am still not getting why my query
takes 20 seconds longer after adding individual indexes?
***
TIA, ChrisRHi,
Here is a bit of information I found in BOL:
"
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.
"
It seems that your individual indexes DO NOT cover your
query. Each time you run your query, the optimizer is
using 4 different index (one at a time) and it has
increased disk i/o. Increased disk i/o means slow query
response.
hth
DeeJay
>--Original Message--
>sql2k sp3
>Ive got a query that takes 30 seconds to run:
>
> select top 50 customerkey as customerkey from
transdtl
> where MerchName in ('DTV*DIRECTV
SERVICE','DTV*DIRECTV
>SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey
> order by CustomerKey,min(TransDate)
>
>Index Tuning Wizard wants to create an Composite Index on
>MerchName, CustomerKey, TranCode, and TransDate. There is
>already an Composite Index on TransDate, TranCode(in this
>order) but for kicks I do the Wizards deal. Pretty cool
in
>that my query speeds up to only 1 second. Any yes, I
>cleared the cache. Ive always followed the advice from
>this article:
>http://www.sql-server-
performance.com/composite_indexes.asp
>and for the most part used single column Indexes instead
>of Composites. So, I drop the newly created Composite
>Indexes, create 4 single coulmn Indexes, and expect it to
>be about the same. Not only was it slower, but it was
>slower now than when there was no Indexes on these
>columns. 50 seconds. Ive tested this alot over the last
>couple of days and its very consistant. Im getting a
table
>scan on the TranCode coulmn I noticed. I used an Index
>Hint and have the same thing. Why is the Composite
soooooo
>much faster than the individuals? Why Is a Table Scan
>occuring? Why is it slower than before? All ideas are
>appreciated.
>*** note: someone mentioned yesterday (thank you) that
the
>results are due to the ITW's index being a covered index.
>That makes sense. But I am still not getting why my query
>takes 20 seconds longer after adding individual indexes?
>***
>TIA, ChrisR
>.
>|||>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?
>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>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.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>>--Original Message--
>>sql2k sp3
>>Ive got a query that takes 30 seconds to run:
>>
>> select top 50 customerkey as customerkey from
>transdtl
>> where MerchName in ('DTV*DIRECTV
>SERVICE','DTV*DIRECTV
>>SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey
>> order by CustomerKey,min(TransDate)
>>
>>Index Tuning Wizard wants to create an Composite Index
on
>>MerchName, CustomerKey, TranCode, and TransDate. There
is
>>already an Composite Index on TransDate, TranCode(in
this
>>order) but for kicks I do the Wizards deal. Pretty cool
>in
>>that my query speeds up to only 1 second. Any yes, I
>>cleared the cache. Ive always followed the advice from
>>this article:
>>http://www.sql-server-
>performance.com/composite_indexes.asp
>>and for the most part used single column Indexes instead
>>of Composites. So, I drop the newly created Composite
>>Indexes, create 4 single coulmn Indexes, and expect it
to
>>be about the same. Not only was it slower, but it was
>>slower now than when there was no Indexes on these
>>columns. 50 seconds. Ive tested this alot over the last
>>couple of days and its very consistant. Im getting a
>table
>>scan on the TranCode coulmn I noticed. I used an Index
>>Hint and have the same thing. Why is the Composite
>soooooo
>>much faster than the individuals? Why Is a Table Scan
>>occuring? Why is it slower than before? All ideas are
>>appreciated.
>>*** note: someone mentioned yesterday (thank you) that
>the
>>results are due to the ITW's index being a covered
index.
>>That makes sense. But I am still not getting why my
query
>>takes 20 seconds longer after adding individual indexes?
>>***
>>TIA, ChrisR
>>.
>.
>|||On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:
>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
indexes are slowing down my query
sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisRcomposite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisRcomposite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ
indexes are slowing down my query
sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.co...te_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisR
composite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon
|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>
|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.co...te_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
TIA, ChrisR
composite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon
|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>
|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ
Sunday, February 19, 2012
indexed views... how to set up?
I've heard SQL2K can have indexed views, but I havent seen any place to set
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/d...
xedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/d...
xedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>
indexed views... how to set up?
I've heard SQL2K can have indexed views, but I havent seen any place to set
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
--
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
--
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>
Subscribe to:
Posts (Atom)