Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
Showing posts with label statistics. Show all posts
Showing posts with label statistics. Show all posts
Friday, March 30, 2012
Index Statistics Degredation
Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritte
n.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the com
plex
query runs in about 4 seconds. This can be repeated throughout the day, wher
e
the query degrades and then I run update statistics [tablename] with ful
lscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.c...agmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>sql
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritte
n.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the com
plex
query runs in about 4 seconds. This can be repeated throughout the day, wher
e
the query degrades and then I run update statistics [tablename] with ful
lscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.c...agmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>sql
Index Statistics Degredation
Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
Index Statistics are Missing
SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
database/log/tempdb files distributed across multiple spindles.
I've got a table with 3GB/a bit over 41 million rows. It has two
non-clustered indexes. There are no statistics for these indexes
(sysIndexes.statblob is null). If I drop and recreate the indexes,
statistics are recalculated... but if (when, it's part of a regular
job) I run UPDATE STATISTICS, they disappear (get nulled out again).
Moreover, when I have statistics on one of these indexes, it is not
used by the query compiler ("select col, count(*) group by col" where
col is indexed is processed by a table scan, not an index scan).
Putting in a table hint to force usage gets the index scan, and better
performance.
This just cropped up recently. I've restored and played around with a
copy of the database from last week (only fractionally smaller), and it
does not exhibit this behavior (statistics exist, do not get wiped when
recalced).
Nosing around, I've found that there are several tables (all containing
data) that appear to have the same problem, though I've only got the
one big guy. Any ideas what can cause this behavior?
PhilipMaybe the indexes were created with the STATISTICS_NORECOMPUTE option? I
haven't tested it but it sounds feasible.
--
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147793033.972043.153060@.u72g2000cwu.googlegroups.com...
> SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
> hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
> database/log/tempdb files distributed across multiple spindles.
> I've got a table with 3GB/a bit over 41 million rows. It has two
> non-clustered indexes. There are no statistics for these indexes
> (sysIndexes.statblob is null). If I drop and recreate the indexes,
> statistics are recalculated... but if (when, it's part of a regular
> job) I run UPDATE STATISTICS, they disappear (get nulled out again).
> Moreover, when I have statistics on one of these indexes, it is not
> used by the query compiler ("select col, count(*) group by col" where
> col is indexed is processed by a table scan, not an index scan).
> Putting in a table hint to force usage gets the index scan, and better
> performance.
> This just cropped up recently. I've restored and played around with a
> copy of the database from last week (only fractionally smaller), and it
> does not exhibit this behavior (statistics exist, do not get wiped when
> recalced).
> Nosing around, I've found that there are several tables (all containing
> data) that appear to have the same problem, though I've only got the
> one big guy. Any ideas what can cause this behavior?
> Philip
>|||In my tests, I dropped and recreated my "new" indexes several times
(scripted in Query Analyzer, not in any GUI tool. I did not specify
that option; is there any way this could have been configured as the
default for this command? Once built, the indexes had statistics. As
soon as I issued UPDATE STATISTICS, they disappeared.
Philip|||There is no default that I am aware of. Are you running any trace flags?
What does DBCC TRACESTATUS(-1) give you? What service pack are you on? If
you are not on the latest I would suggest testing to see if that may help.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147881230.970958.76080@.38g2000cwa.googlegroups.com...
> In my tests, I dropped and recreated my "new" indexes several times
> (scripted in Query Analyzer, not in any GUI tool. I did not specify
> that option; is there any way this could have been configured as the
> default for this command? Once built, the indexes had statistics. As
> soon as I issued UPDATE STATISTICS, they disappeared.
> Philip
>|||We are not intentionally running any trace flags. When I run DBCC
TRACESTATUS (-1), I get back:
Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
which I'd guess means that no trace flags are running.
The Production server where the database was hosted is a two-node
active/passive cluster, running service pack 4; the box I'm doing the
testing on is a single server, sp3, standard edition. (The server is
slated to be rebuilt any day now.)
Philip|||Sorry but not sure what else to tell you other than to open a case with MS
PSS if this continues.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147987042.392899.6900@.j33g2000cwa.googlegroups.com...
> We are not intentionally running any trace flags. When I run DBCC
> TRACESTATUS (-1), I get back:
> Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> which I'd guess means that no trace flags are running.
> The Production server where the database was hosted is a two-node
> active/passive cluster, running service pack 4; the box I'm doing the
> testing on is a single server, sp3, standard edition. (The server is
> slated to be rebuilt any day now.)
> Philip
>
hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
database/log/tempdb files distributed across multiple spindles.
I've got a table with 3GB/a bit over 41 million rows. It has two
non-clustered indexes. There are no statistics for these indexes
(sysIndexes.statblob is null). If I drop and recreate the indexes,
statistics are recalculated... but if (when, it's part of a regular
job) I run UPDATE STATISTICS, they disappear (get nulled out again).
Moreover, when I have statistics on one of these indexes, it is not
used by the query compiler ("select col, count(*) group by col" where
col is indexed is processed by a table scan, not an index scan).
Putting in a table hint to force usage gets the index scan, and better
performance.
This just cropped up recently. I've restored and played around with a
copy of the database from last week (only fractionally smaller), and it
does not exhibit this behavior (statistics exist, do not get wiped when
recalced).
Nosing around, I've found that there are several tables (all containing
data) that appear to have the same problem, though I've only got the
one big guy. Any ideas what can cause this behavior?
PhilipMaybe the indexes were created with the STATISTICS_NORECOMPUTE option? I
haven't tested it but it sounds feasible.
--
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147793033.972043.153060@.u72g2000cwu.googlegroups.com...
> SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
> hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
> database/log/tempdb files distributed across multiple spindles.
> I've got a table with 3GB/a bit over 41 million rows. It has two
> non-clustered indexes. There are no statistics for these indexes
> (sysIndexes.statblob is null). If I drop and recreate the indexes,
> statistics are recalculated... but if (when, it's part of a regular
> job) I run UPDATE STATISTICS, they disappear (get nulled out again).
> Moreover, when I have statistics on one of these indexes, it is not
> used by the query compiler ("select col, count(*) group by col" where
> col is indexed is processed by a table scan, not an index scan).
> Putting in a table hint to force usage gets the index scan, and better
> performance.
> This just cropped up recently. I've restored and played around with a
> copy of the database from last week (only fractionally smaller), and it
> does not exhibit this behavior (statistics exist, do not get wiped when
> recalced).
> Nosing around, I've found that there are several tables (all containing
> data) that appear to have the same problem, though I've only got the
> one big guy. Any ideas what can cause this behavior?
> Philip
>|||In my tests, I dropped and recreated my "new" indexes several times
(scripted in Query Analyzer, not in any GUI tool. I did not specify
that option; is there any way this could have been configured as the
default for this command? Once built, the indexes had statistics. As
soon as I issued UPDATE STATISTICS, they disappeared.
Philip|||There is no default that I am aware of. Are you running any trace flags?
What does DBCC TRACESTATUS(-1) give you? What service pack are you on? If
you are not on the latest I would suggest testing to see if that may help.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147881230.970958.76080@.38g2000cwa.googlegroups.com...
> In my tests, I dropped and recreated my "new" indexes several times
> (scripted in Query Analyzer, not in any GUI tool. I did not specify
> that option; is there any way this could have been configured as the
> default for this command? Once built, the indexes had statistics. As
> soon as I issued UPDATE STATISTICS, they disappeared.
> Philip
>|||We are not intentionally running any trace flags. When I run DBCC
TRACESTATUS (-1), I get back:
Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
which I'd guess means that no trace flags are running.
The Production server where the database was hosted is a two-node
active/passive cluster, running service pack 4; the box I'm doing the
testing on is a single server, sp3, standard edition. (The server is
slated to be rebuilt any day now.)
Philip|||Sorry but not sure what else to tell you other than to open a case with MS
PSS if this continues.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147987042.392899.6900@.j33g2000cwa.googlegroups.com...
> We are not intentionally running any trace flags. When I run DBCC
> TRACESTATUS (-1), I get back:
> Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> which I'd guess means that no trace flags are running.
> The Production server where the database was hosted is a two-node
> active/passive cluster, running service pack 4; the box I'm doing the
> testing on is a single server, sp3, standard edition. (The server is
> slated to be rebuilt any day now.)
> Philip
>
Index Statistics are Missing
SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
database/log/tempdb files distributed across multiple spindles.
I've got a table with 3GB/a bit over 41 million rows. It has two
non-clustered indexes. There are no statistics for these indexes
(sysIndexes.statblob is null). If I drop and recreate the indexes,
statistics are recalculated... but if (when, it's part of a regular
job) I run UPDATE STATISTICS, they disappear (get nulled out again).
Moreover, when I have statistics on one of these indexes, it is not
used by the query compiler ("select col, count(*) group by col" where
col is indexed is processed by a table scan, not an index scan).
Putting in a table hint to force usage gets the index scan, and better
performance.
This just cropped up recently. I've restored and played around with a
copy of the database from last week (only fractionally smaller), and it
does not exhibit this behavior (statistics exist, do not get wiped when
recalced).
Nosing around, I've found that there are several tables (all containing
data) that appear to have the same problem, though I've only got the
one big guy. Any ideas what can cause this behavior?
PhilipMaybe the indexes were created with the STATISTICS_NORECOMPUTE option? I
haven't tested it but it sounds feasible.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147793033.972043.153060@.u72g2000cwu.googlegroups.com...
> SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
> hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
> database/log/tempdb files distributed across multiple spindles.
> I've got a table with 3GB/a bit over 41 million rows. It has two
> non-clustered indexes. There are no statistics for these indexes
> (sysIndexes.statblob is null). If I drop and recreate the indexes,
> statistics are recalculated... but if (when, it's part of a regular
> job) I run UPDATE STATISTICS, they disappear (get nulled out again).
> Moreover, when I have statistics on one of these indexes, it is not
> used by the query compiler ("select col, count(*) group by col" where
> col is indexed is processed by a table scan, not an index scan).
> Putting in a table hint to force usage gets the index scan, and better
> performance.
> This just cropped up recently. I've restored and played around with a
> copy of the database from last week (only fractionally smaller), and it
> does not exhibit this behavior (statistics exist, do not get wiped when
> recalced).
> Nosing around, I've found that there are several tables (all containing
> data) that appear to have the same problem, though I've only got the
> one big guy. Any ideas what can cause this behavior?
> Philip
>|||In my tests, I dropped and recreated my "new" indexes several times
(scripted in Query Analyzer, not in any GUI tool. I did not specify
that option; is there any way this could have been configured as the
default for this command? Once built, the indexes had statistics. As
soon as I issued UPDATE STATISTICS, they disappeared.
Philip|||There is no default that I am aware of. Are you running any trace flags?
What does DBCC TRACESTATUS(-1) give you? What service pack are you on? If
you are not on the latest I would suggest testing to see if that may help.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147881230.970958.76080@.38g2000cwa.googlegroups.com...
> In my tests, I dropped and recreated my "new" indexes several times
> (scripted in Query Analyzer, not in any GUI tool. I did not specify
> that option; is there any way this could have been configured as the
> default for this command? Once built, the indexes had statistics. As
> soon as I issued UPDATE STATISTICS, they disappeared.
> Philip
>|||We are not intentionally running any trace flags. When I run DBCC
TRACESTATUS (-1), I get back:
Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
which I'd guess means that no trace flags are running.
The Production server where the database was hosted is a two-node
active/passive cluster, running service pack 4; the box I'm doing the
testing on is a single server, sp3, standard edition. (The server is
slated to be rebuilt any day now.)
Philip|||Sorry but not sure what else to tell you other than to open a case with MS
PSS if this continues.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147987042.392899.6900@.j33g2000cwa.googlegroups.com...
> We are not intentionally running any trace flags. When I run DBCC
> TRACESTATUS (-1), I get back:
> Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> which I'd guess means that no trace flags are running.
> The Production server where the database was hosted is a two-node
> active/passive cluster, running service pack 4; the box I'm doing the
> testing on is a single server, sp3, standard edition. (The server is
> slated to be rebuilt any day now.)
> Philip
>
hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
database/log/tempdb files distributed across multiple spindles.
I've got a table with 3GB/a bit over 41 million rows. It has two
non-clustered indexes. There are no statistics for these indexes
(sysIndexes.statblob is null). If I drop and recreate the indexes,
statistics are recalculated... but if (when, it's part of a regular
job) I run UPDATE STATISTICS, they disappear (get nulled out again).
Moreover, when I have statistics on one of these indexes, it is not
used by the query compiler ("select col, count(*) group by col" where
col is indexed is processed by a table scan, not an index scan).
Putting in a table hint to force usage gets the index scan, and better
performance.
This just cropped up recently. I've restored and played around with a
copy of the database from last week (only fractionally smaller), and it
does not exhibit this behavior (statistics exist, do not get wiped when
recalced).
Nosing around, I've found that there are several tables (all containing
data) that appear to have the same problem, though I've only got the
one big guy. Any ideas what can cause this behavior?
PhilipMaybe the indexes were created with the STATISTICS_NORECOMPUTE option? I
haven't tested it but it sounds feasible.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147793033.972043.153060@.u72g2000cwu.googlegroups.com...
> SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
> hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
> database/log/tempdb files distributed across multiple spindles.
> I've got a table with 3GB/a bit over 41 million rows. It has two
> non-clustered indexes. There are no statistics for these indexes
> (sysIndexes.statblob is null). If I drop and recreate the indexes,
> statistics are recalculated... but if (when, it's part of a regular
> job) I run UPDATE STATISTICS, they disappear (get nulled out again).
> Moreover, when I have statistics on one of these indexes, it is not
> used by the query compiler ("select col, count(*) group by col" where
> col is indexed is processed by a table scan, not an index scan).
> Putting in a table hint to force usage gets the index scan, and better
> performance.
> This just cropped up recently. I've restored and played around with a
> copy of the database from last week (only fractionally smaller), and it
> does not exhibit this behavior (statistics exist, do not get wiped when
> recalced).
> Nosing around, I've found that there are several tables (all containing
> data) that appear to have the same problem, though I've only got the
> one big guy. Any ideas what can cause this behavior?
> Philip
>|||In my tests, I dropped and recreated my "new" indexes several times
(scripted in Query Analyzer, not in any GUI tool. I did not specify
that option; is there any way this could have been configured as the
default for this command? Once built, the indexes had statistics. As
soon as I issued UPDATE STATISTICS, they disappeared.
Philip|||There is no default that I am aware of. Are you running any trace flags?
What does DBCC TRACESTATUS(-1) give you? What service pack are you on? If
you are not on the latest I would suggest testing to see if that may help.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147881230.970958.76080@.38g2000cwa.googlegroups.com...
> In my tests, I dropped and recreated my "new" indexes several times
> (scripted in Query Analyzer, not in any GUI tool. I did not specify
> that option; is there any way this could have been configured as the
> default for this command? Once built, the indexes had statistics. As
> soon as I issued UPDATE STATISTICS, they disappeared.
> Philip
>|||We are not intentionally running any trace flags. When I run DBCC
TRACESTATUS (-1), I get back:
Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
which I'd guess means that no trace flags are running.
The Production server where the database was hosted is a two-node
active/passive cluster, running service pack 4; the box I'm doing the
testing on is a single server, sp3, standard edition. (The server is
slated to be rebuilt any day now.)
Philip|||Sorry but not sure what else to tell you other than to open a case with MS
PSS if this continues.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147987042.392899.6900@.j33g2000cwa.googlegroups.com...
> We are not intentionally running any trace flags. When I run DBCC
> TRACESTATUS (-1), I get back:
> Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> which I'd guess means that no trace flags are running.
> The Production server where the database was hosted is a two-node
> active/passive cluster, running service pack 4; the box I'm doing the
> testing on is a single server, sp3, standard edition. (The server is
> slated to be rebuilt any day now.)
> Philip
>
index statistics are created when?
If the auto create stats and auto update stats is off (actually the value is null) at the db level - are stats generated when an index is recreated?
Additionaly, running the sp_autostats on the above db shows that the auto update stats are on. Where is the option to set it on for an object rather than the db?
Mike
I just read by Kalen that if the options are false (off) this overrides the table level. Still not clear on what occurs when options are <null>True when you run DBCC DBREINDEX those stats will be updated too.
SP_AUTOSTATS is used to display or change the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Additionaly, running the sp_autostats on the above db shows that the auto update stats are on. Where is the option to set it on for an object rather than the db?
Mike
I just read by Kalen that if the options are false (off) this overrides the table level. Still not clear on what occurs when options are <null>True when you run DBCC DBREINDEX those stats will be updated too.
SP_AUTOSTATS is used to display or change the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Index statistics and a primary key
Hi
I have a question regarding updating statistics for a primary key.
Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.
Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?
Hope someone can help
Thanks
James
If you have not changed anything statistics will be automatically updated by SQL Server after a number of modifications have been made to the table. SQL Server 2005 updates the counter that checks the number of modifications for BULK INSERT too. When autostats kicks in only a sample of the data is used to calculate them.
Doing a full scan requires a lot of I/O so with 80 million rows it might be slow if your disk subsystem is not fast enough..
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
sqlIndex Statistics
I am toying around with the article below to try to get my brain around some
index tuning issues and understand how to apply the knowledge to our
database.
http://www.sql-server-performance.c..._statistics.asp
So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
understand these results:
Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Rows
BASFITSERVICES 664.69312 683.0 0
974.95349
If Range-Rows is how many items mapped to this index key and Eq-Rows
represents how many items are equal to this index key, then how can Eq-Rows
be greater, and why would the Average be even higher?
--
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b{2})" -- William ShakespeareHi Mike
I think you have not quite understood the values being returned!
From BOL:
RANGE_HI_KEY Upper bound value of a histogram step.
RANGE_ROWS Number of rows from the sample that fall within a histogram step,
excluding the upper bound.
EQ_ROWS Number of rows from the sample that are equal in value to the upper
bound of the histogram step.
DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
excluding the upper bound.
AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
DISTINCT_RANGE_ROWS > 0).
You may also want to look at the pages on this in "Inside SQL Server 2000"
by Kalen Delany ISBN 0-7356-0998-5
John
"Mike Labosh" wrote:
> I am toying around with the article below to try to get my brain around so
me
> index tuning issues and understand how to apply the knowledge to our
> database.
> http://www.sql-server-performance.c..._statistics.asp
> So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
> understand these results:
> Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Ro
ws
> BASFITSERVICES 664.69312 683.0 0
> 974.95349
> If Range-Rows is how many items mapped to this index key and Eq-Rows
> represents how many items are equal to this index key, then how can Eq-Row
s
> be greater, and why would the Average be even higher?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "(bb)|(^b{2})" -- William Shakespeare
>
>|||Thanks, John!
Mike, you might also want to look at this whitepaper:
http://msdn.microsoft.com/library/d...r />
query.asp
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
> Hi Mike
> I think you have not quite understood the values being returned!
> From BOL:
> RANGE_HI_KEY Upper bound value of a histogram step.
> RANGE_ROWS Number of rows from the sample that fall within a histogram
> step,
> excluding the upper bound.
> EQ_ROWS Number of rows from the sample that are equal in value to the
> upper
> bound of the histogram step.
> DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
> excluding the upper bound.
> AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
> excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
> DISTINCT_RANGE_ROWS > 0).
> You may also want to look at the pages on this in "Inside SQL Server 2000"
> by Kalen Delany ISBN 0-7356-0998-5
> John
>
>
> "Mike Labosh" wrote:
>|||Of course if you are in the UK on 8th or 10th Feb and wish to hear the
master herself then check out http://www.sqlserverfaq.com/
John
:):)
Kalen Delaney wrote:
> Thanks, John!
> Mike, you might also want to look at this whitepaper:
>
>
http://msdn.microsoft.com/library/d...l/statquery.asp
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
histogram
the
step,
histogram step,
Server 2000"
around
our
http://www.sql-server-performance.c..._statistics.asp
I don't
Eq-Rows
can|||And thanks again...:-)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1106676698.587786.264330@.f14g2000cwb.googlegroups.com...
> Of course if you are in the UK on 8th or 10th Feb and wish to hear the
> master herself then check out http://www.sqlserverfaq.com/
> John
> :):)
>
> Kalen Delaney wrote:
> http://msdn.microsoft.com/library/d.../>
atquery.asp
> histogram
> the
> step,
> histogram step,
> Server 2000"
> around
> our
> http://www.sql-server-performance.c..._statistics.asp
> I don't
> Eq-Rows
> can
>
index tuning issues and understand how to apply the knowledge to our
database.
http://www.sql-server-performance.c..._statistics.asp
So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
understand these results:
Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Rows
BASFITSERVICES 664.69312 683.0 0
974.95349
If Range-Rows is how many items mapped to this index key and Eq-Rows
represents how many items are equal to this index key, then how can Eq-Rows
be greater, and why would the Average be even higher?
--
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b{2})" -- William ShakespeareHi Mike
I think you have not quite understood the values being returned!
From BOL:
RANGE_HI_KEY Upper bound value of a histogram step.
RANGE_ROWS Number of rows from the sample that fall within a histogram step,
excluding the upper bound.
EQ_ROWS Number of rows from the sample that are equal in value to the upper
bound of the histogram step.
DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
excluding the upper bound.
AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
DISTINCT_RANGE_ROWS > 0).
You may also want to look at the pages on this in "Inside SQL Server 2000"
by Kalen Delany ISBN 0-7356-0998-5
John
"Mike Labosh" wrote:
> I am toying around with the article below to try to get my brain around so
me
> index tuning issues and understand how to apply the knowledge to our
> database.
> http://www.sql-server-performance.c..._statistics.asp
> So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
> understand these results:
> Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Ro
ws
> BASFITSERVICES 664.69312 683.0 0
> 974.95349
> If Range-Rows is how many items mapped to this index key and Eq-Rows
> represents how many items are equal to this index key, then how can Eq-Row
s
> be greater, and why would the Average be even higher?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "(bb)|(^b{2})" -- William Shakespeare
>
>|||Thanks, John!
Mike, you might also want to look at this whitepaper:
http://msdn.microsoft.com/library/d...r />
query.asp
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
> Hi Mike
> I think you have not quite understood the values being returned!
> From BOL:
> RANGE_HI_KEY Upper bound value of a histogram step.
> RANGE_ROWS Number of rows from the sample that fall within a histogram
> step,
> excluding the upper bound.
> EQ_ROWS Number of rows from the sample that are equal in value to the
> upper
> bound of the histogram step.
> DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
> excluding the upper bound.
> AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
> excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
> DISTINCT_RANGE_ROWS > 0).
> You may also want to look at the pages on this in "Inside SQL Server 2000"
> by Kalen Delany ISBN 0-7356-0998-5
> John
>
>
> "Mike Labosh" wrote:
>|||Of course if you are in the UK on 8th or 10th Feb and wish to hear the
master herself then check out http://www.sqlserverfaq.com/
John
:):)
Kalen Delaney wrote:
> Thanks, John!
> Mike, you might also want to look at this whitepaper:
>
>
http://msdn.microsoft.com/library/d...l/statquery.asp
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
histogram
the
step,
histogram step,
Server 2000"
around
our
http://www.sql-server-performance.c..._statistics.asp
I don't
Eq-Rows
can|||And thanks again...:-)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1106676698.587786.264330@.f14g2000cwb.googlegroups.com...
> Of course if you are in the UK on 8th or 10th Feb and wish to hear the
> master herself then check out http://www.sqlserverfaq.com/
> John
> :):)
>
> Kalen Delaney wrote:
> http://msdn.microsoft.com/library/d.../>
atquery.asp
> histogram
> the
> step,
> histogram step,
> Server 2000"
> around
> our
> http://www.sql-server-performance.c..._statistics.asp
> I don't
> Eq-Rows
> can
>
Friday, March 23, 2012
Index Physical Statistics
The Index Physical Statistics report is telling me to rebuild or reorganize
a
few indexes on some of my tables, and I did. The report still tells me to
rebuild or reorganize a few indexes. Should I be concerned that the report
still tells me to do what I just did?Hi Curtis
Can you be more specific about what report you are referring to? Is it
giving you any additional information about the indexes that it is telling
you to rebuild?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> The Index Physical Statistics report is telling me to rebuild or
> reorganize a
> few indexes on some of my tables, and I did. The report still tells me to
> rebuild or reorganize a few indexes. Should I be concerned that the report
> still tells me to do what I just did?|||The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>|||If your tables are small, then don't worry about its suggestion to
rebuild/reorg.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>|||Only 32 pages. MS recommends not to worry about fragmentation unless you hav
e at least 1000 pages.
The report should take that into consideration, but apparently doesn't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>|||You should look at the number of pages before deciding to rebuild an index.
For very small tables, not only will fragmentation not hurt you, but it will
be almost impossible to get rid of.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>
a
few indexes on some of my tables, and I did. The report still tells me to
rebuild or reorganize a few indexes. Should I be concerned that the report
still tells me to do what I just did?Hi Curtis
Can you be more specific about what report you are referring to? Is it
giving you any additional information about the indexes that it is telling
you to rebuild?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> The Index Physical Statistics report is telling me to rebuild or
> reorganize a
> few indexes on some of my tables, and I did. The report still tells me to
> rebuild or reorganize a few indexes. Should I be concerned that the report
> still tells me to do what I just did?|||The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>|||If your tables are small, then don't worry about its suggestion to
rebuild/reorg.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>|||Only 32 pages. MS recommends not to worry about fragmentation unless you hav
e at least 1000 pages.
The report should take that into consideration, but apparently doesn't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>|||You should look at the number of pages before deciding to rebuild an index.
For very small tables, not only will fragmentation not hurt you, but it will
be almost impossible to get rid of.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>
Index Physical Statistics
The Index Physical Statistics report is telling me to rebuild or reorganize a
few indexes on some of my tables, and I did. The report still tells me to
rebuild or reorganize a few indexes. Should I be concerned that the report
still tells me to do what I just did?
Hi Curtis
Can you be more specific about what report you are referring to? Is it
giving you any additional information about the indexes that it is telling
you to rebuild?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> The Index Physical Statistics report is telling me to rebuild or
> reorganize a
> few indexes on some of my tables, and I did. The report still tells me to
> rebuild or reorganize a few indexes. Should I be concerned that the report
> still tells me to do what I just did?
|||The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>
|||If your tables are small, then don't worry about its suggestion to
rebuild/reorg.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>
|||Only 32 pages. MS recommends not to worry about fragmentation unless you have at least 1000 pages.
The report should take that into consideration, but apparently doesn't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
|||You should look at the number of pages before deciding to rebuild an index.
For very small tables, not only will fragmentation not hurt you, but it will
be almost impossible to get rid of.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
few indexes on some of my tables, and I did. The report still tells me to
rebuild or reorganize a few indexes. Should I be concerned that the report
still tells me to do what I just did?
Hi Curtis
Can you be more specific about what report you are referring to? Is it
giving you any additional information about the indexes that it is telling
you to rebuild?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> The Index Physical Statistics report is telling me to rebuild or
> reorganize a
> few indexes on some of my tables, and I did. The report still tells me to
> rebuild or reorganize a few indexes. Should I be concerned that the report
> still tells me to do what I just did?
|||The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>
|||If your tables are small, then don't worry about its suggestion to
rebuild/reorg.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>
>
|||Only 32 pages. MS recommends not to worry about fragmentation unless you have at least 1000 pages.
The report should take that into consideration, but apparently doesn't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
|||You should look at the number of pages before deciding to rebuild an index.
For very small tables, not only will fragmentation not hurt you, but it will
be almost impossible to get rid of.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...[vbcol=seagreen]
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
Index Physical Statistics
The Index Physical Statistics report is telling me to rebuild or reorganize a
few indexes on some of my tables, and I did. The report still tells me to
rebuild or reorganize a few indexes. Should I be concerned that the report
still tells me to do what I just did?Hi Curtis
Can you be more specific about what report you are referring to? Is it
giving you any additional information about the indexes that it is telling
you to rebuild?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> The Index Physical Statistics report is telling me to rebuild or
> reorganize a
> few indexes on some of my tables, and I did. The report still tells me to
> rebuild or reorganize a few indexes. Should I be concerned that the report
> still tells me to do what I just did?|||The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> > The Index Physical Statistics report is telling me to rebuild or
> > reorganize a
> > few indexes on some of my tables, and I did. The report still tells me to
> > rebuild or reorganize a few indexes. Should I be concerned that the report
> > still tells me to do what I just did?
>
>|||If your tables are small, then don't worry about its suggestion to
rebuild/reorg.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> > The Index Physical Statistics report is telling me to rebuild or
> > reorganize a
> > few indexes on some of my tables, and I did. The report still tells me
> > to
> > rebuild or reorganize a few indexes. Should I be concerned that the
> > report
> > still tells me to do what I just did?
>
>|||Only 32 pages. MS recommends not to worry about fragmentation unless you have at least 1000 pages.
The report should take that into consideration, but apparently doesn't.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>> Hi Curtis
>> Can you be more specific about what report you are referring to? Is it
>> giving you any additional information about the indexes that it is telling
>> you to rebuild?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>> > The Index Physical Statistics report is telling me to rebuild or
>> > reorganize a
>> > few indexes on some of my tables, and I did. The report still tells me to
>> > rebuild or reorganize a few indexes. Should I be concerned that the report
>> > still tells me to do what I just did?
>>|||You should look at the number of pages before deciding to rebuild an index.
For very small tables, not only will fragmentation not hurt you, but it will
be almost impossible to get rid of.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>> Hi Curtis
>> Can you be more specific about what report you are referring to? Is it
>> giving you any additional information about the indexes that it is
>> telling
>> you to rebuild?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>> > The Index Physical Statistics report is telling me to rebuild or
>> > reorganize a
>> > few indexes on some of my tables, and I did. The report still tells me
>> > to
>> > rebuild or reorganize a few indexes. Should I be concerned that the
>> > report
>> > still tells me to do what I just did?
>>
few indexes on some of my tables, and I did. The report still tells me to
rebuild or reorganize a few indexes. Should I be concerned that the report
still tells me to do what I just did?Hi Curtis
Can you be more specific about what report you are referring to? Is it
giving you any additional information about the indexes that it is telling
you to rebuild?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> The Index Physical Statistics report is telling me to rebuild or
> reorganize a
> few indexes on some of my tables, and I did. The report still tells me to
> rebuild or reorganize a few indexes. Should I be concerned that the report
> still tells me to do what I just did?|||The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> > The Index Physical Statistics report is telling me to rebuild or
> > reorganize a
> > few indexes on some of my tables, and I did. The report still tells me to
> > rebuild or reorganize a few indexes. Should I be concerned that the report
> > still tells me to do what I just did?
>
>|||If your tables are small, then don't worry about its suggestion to
rebuild/reorg.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
The report is part of the management studio.
It also tells me:
Avg Fragmentation (%) 13
# Fragments 8
Avg Pages Per Fragment 4
#Pages 31
"Kalen Delaney" wrote:
> Hi Curtis
> Can you be more specific about what report you are referring to? Is it
> giving you any additional information about the indexes that it is telling
> you to rebuild?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
> > The Index Physical Statistics report is telling me to rebuild or
> > reorganize a
> > few indexes on some of my tables, and I did. The report still tells me
> > to
> > rebuild or reorganize a few indexes. Should I be concerned that the
> > report
> > still tells me to do what I just did?
>
>|||Only 32 pages. MS recommends not to worry about fragmentation unless you have at least 1000 pages.
The report should take that into consideration, but apparently doesn't.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>> Hi Curtis
>> Can you be more specific about what report you are referring to? Is it
>> giving you any additional information about the indexes that it is telling
>> you to rebuild?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>> > The Index Physical Statistics report is telling me to rebuild or
>> > reorganize a
>> > few indexes on some of my tables, and I did. The report still tells me to
>> > rebuild or reorganize a few indexes. Should I be concerned that the report
>> > still tells me to do what I just did?
>>|||You should look at the number of pages before deciding to rebuild an index.
For very small tables, not only will fragmentation not hurt you, but it will
be almost impossible to get rid of.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:81785ED5-988F-4EC3-BB23-7CF16511F592@.microsoft.com...
> The report is part of the management studio.
> It also tells me:
> Avg Fragmentation (%) 13
> # Fragments 8
> Avg Pages Per Fragment 4
> #Pages 31
> "Kalen Delaney" wrote:
>> Hi Curtis
>> Can you be more specific about what report you are referring to? Is it
>> giving you any additional information about the indexes that it is
>> telling
>> you to rebuild?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:376470F8-C429-4327-82B4-BDD2FEAE2ADC@.microsoft.com...
>> > The Index Physical Statistics report is telling me to rebuild or
>> > reorganize a
>> > few indexes on some of my tables, and I did. The report still tells me
>> > to
>> > rebuild or reorganize a few indexes. Should I be concerned that the
>> > report
>> > still tells me to do what I just did?
>>
Monday, March 12, 2012
Index is not faster any more
After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.
Thank you
Raj SankarOriginally posted by raj_sankar
After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.
Thank you
Raj Sankar
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex
Joe|||Originally posted by mkg_1232000
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex
Joe
The index is ok, Some how execution plan changed. Now every this ok after uodating the statistics. how ever the foloowing problem remains.
from query analyser.
select * from table_name where store = '3' --is faster and using index scan.
Declare @.store as int
set @.store = '3'
select * from table_name where store = @.store --is very slow and using table scan.
I don't how to fix this, it was ok before.
Thank you
Raj SankarOriginally posted by raj_sankar
After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.
Thank you
Raj Sankar
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex
Joe|||Originally posted by mkg_1232000
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex
Joe
The index is ok, Some how execution plan changed. Now every this ok after uodating the statistics. how ever the foloowing problem remains.
from query analyser.
select * from table_name where store = '3' --is faster and using index scan.
Declare @.store as int
set @.store = '3'
select * from table_name where store = @.store --is very slow and using table scan.
I don't how to fix this, it was ok before.
Friday, February 24, 2012
Index Corruption
Hi,
Does anyone know of an obvious reason for indexes to get corrupt or have the
statistics get really out of wack' We had a problem today where the
performance just went down the toilet.. It was really obvious that a
particular frequent query was taking 1000 times longer than normal.. The
query plan had a new start "Scan Constants'" I dropped and recreated
the index and the problem went away..
Why would it so suddenly go bad?
Thanks
BillHi
Auto Create and Update Statistics On?
The SP could generate another query plan during the day it it finds it's
earlier estimations were wrong.
I have found that once the statistics get really out of date, queries going
souith are occuring more frequently.
There has been a bit of discussion on this in
microsoft.public.sqlserver.server and
microsoft.public.sqlserver.programming lately on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>|||Yep, Auto Create and Auto Update are on...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ujKN8FkNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi
> Auto Create and Update Statistics On?
> The SP could generate another query plan during the day it it finds it's
> earlier estimations were wrong.
> I have found that once the statistics get really out of date, queries
> going souith are occuring more frequently.
> There has been a bit of discussion on this in
> microsoft.public.sqlserver.server and
> microsoft.public.sqlserver.programming lately on it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Bill" <someone@.somewhere.com> wrote in message
> news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Does anyone know of an obvious reason for indexes to get corrupt or have
>> the statistics get really out of wack' We had a problem today where
>> the performance just went down the toilet.. It was really obvious that
>> a particular frequent query was taking 1000 times longer than normal..
>> The query plan had a new start "Scan Constants'" I dropped and
>> recreated the index and the problem went away..
>> Why would it so suddenly go bad?
>> Thanks
>> Bill
>|||That is not index corruption and by reindexing you simply forced a new plan
to be generated. You could have just recompiled the proc. This happens
occasionally. You can have a value that is atypical and requires a scan
where as all the others would use a seek. If the first time the proc was
compiled or recompiled it happened to get that bad value passed in the query
plan would be right for that one time but wrong for the majority of the
others.
--
Andrew J. Kelly SQL MVP
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>
Does anyone know of an obvious reason for indexes to get corrupt or have the
statistics get really out of wack' We had a problem today where the
performance just went down the toilet.. It was really obvious that a
particular frequent query was taking 1000 times longer than normal.. The
query plan had a new start "Scan Constants'" I dropped and recreated
the index and the problem went away..
Why would it so suddenly go bad?
Thanks
BillHi
Auto Create and Update Statistics On?
The SP could generate another query plan during the day it it finds it's
earlier estimations were wrong.
I have found that once the statistics get really out of date, queries going
souith are occuring more frequently.
There has been a bit of discussion on this in
microsoft.public.sqlserver.server and
microsoft.public.sqlserver.programming lately on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>|||Yep, Auto Create and Auto Update are on...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ujKN8FkNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi
> Auto Create and Update Statistics On?
> The SP could generate another query plan during the day it it finds it's
> earlier estimations were wrong.
> I have found that once the statistics get really out of date, queries
> going souith are occuring more frequently.
> There has been a bit of discussion on this in
> microsoft.public.sqlserver.server and
> microsoft.public.sqlserver.programming lately on it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Bill" <someone@.somewhere.com> wrote in message
> news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Does anyone know of an obvious reason for indexes to get corrupt or have
>> the statistics get really out of wack' We had a problem today where
>> the performance just went down the toilet.. It was really obvious that
>> a particular frequent query was taking 1000 times longer than normal..
>> The query plan had a new start "Scan Constants'" I dropped and
>> recreated the index and the problem went away..
>> Why would it so suddenly go bad?
>> Thanks
>> Bill
>|||That is not index corruption and by reindexing you simply forced a new plan
to be generated. You could have just recompiled the proc. This happens
occasionally. You can have a value that is atypical and requires a scan
where as all the others would use a seek. If the first time the proc was
compiled or recompiled it happened to get that bad value passed in the query
plan would be right for that one time but wrong for the majority of the
others.
--
Andrew J. Kelly SQL MVP
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>
Sunday, February 19, 2012
Index Building
First use, sometimes doing an update statistics will help.
Other things I've done at some sites, with 7.0 is execute
the proc after its built before my users get in. Not the
cleanest way, but it worked. I haven't had similar
problems in 2000, but you never know.
Gary Abbott
MS-SQL Database Architect
>--Original Message--
>Does SQL Server 2000 optimize indexes differently than
SQL 7
>It seems that SQL 2K doesn't (re)build the index until
you execute a
>query that will utilize the index. This is causing the
first time you
>execute the query to be slow. The second time it is
executed it runs
>fast.
>We have maintenance plans to rebuild the indexes each
night. This
>doesn't seem to help. Instead if it seems like the index
is rebuilt
>when our users are executing the query which is
undesirable. Does
>anyone know if this is what SQL Server does? If so, how
can I can this
>behavior?
>Greg
>.
>UPDATE STATISTICS shouldn't be needed after DBCC DBREINDEX because the
distribution data is updated with the index rebuild...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:1213e01c3f5b8$534208d0$a401280a@.phx
.gbl...
> First use, sometimes doing an update statistics will help.
> Other things I've done at some sites, with 7.0 is execute
> the proc after its built before my users get in. Not the
> cleanest way, but it worked. I haven't had similar
> problems in 2000, but you never know.
> Gary Abbott
> MS-SQL Database Architect
>
> SQL 7
> you execute a
> first time you
> executed it runs
> night. This
> is rebuilt
> undesirable. Does
> can I can this
Other things I've done at some sites, with 7.0 is execute
the proc after its built before my users get in. Not the
cleanest way, but it worked. I haven't had similar
problems in 2000, but you never know.
Gary Abbott
MS-SQL Database Architect
>--Original Message--
>Does SQL Server 2000 optimize indexes differently than
SQL 7
>It seems that SQL 2K doesn't (re)build the index until
you execute a
>query that will utilize the index. This is causing the
first time you
>execute the query to be slow. The second time it is
executed it runs
>fast.
>We have maintenance plans to rebuild the indexes each
night. This
>doesn't seem to help. Instead if it seems like the index
is rebuilt
>when our users are executing the query which is
undesirable. Does
>anyone know if this is what SQL Server does? If so, how
can I can this
>behavior?
>Greg
>.
>UPDATE STATISTICS shouldn't be needed after DBCC DBREINDEX because the
distribution data is updated with the index rebuild...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:1213e01c3f5b8$534208d0$a401280a@.phx
.gbl...
> First use, sometimes doing an update statistics will help.
> Other things I've done at some sites, with 7.0 is execute
> the proc after its built before my users get in. Not the
> cleanest way, but it worked. I haven't had similar
> problems in 2000, but you never know.
> Gary Abbott
> MS-SQL Database Architect
>
> SQL 7
> you execute a
> first time you
> executed it runs
> night. This
> is rebuilt
> undesirable. Does
> can I can this
index & statistics
sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnx
SQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?
|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnx
SQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?
|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>
index & statistics
sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you
to create new useful
indexes (through some dynamic management views). 2000 do not have that funct
ionality.
> i've read that server may refuse to use some existing index during query i
f estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and wi
ll use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate tha
t flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a
table scan in the first
place, why scan all data, create the index, if the alternative was to scan a
ll data in the first
place? Having said that, there are strategies that SQL Server can use which
are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so t
hat "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *seve
ral times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we ha
ve more modern methods,
like a "hash join", which at a *very* high level could be considered like cr
eating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance &#
91;respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query i
f estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate tha
t flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>[/vbcol]|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/pr...n/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query i
f
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate tha
t
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic manag
ement views to get this
historical information, and create indexes based on that information. SQL Se
rver 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard
, as suggested by
Jonathan can be a tool to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...[vbc
ol=seagreen]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napi
sao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.
e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly
* improve performances?
>
>[/vbcol]
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you
to create new useful
indexes (through some dynamic management views). 2000 do not have that funct
ionality.
> i've read that server may refuse to use some existing index during query i
f estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and wi
ll use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate tha
t flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a
table scan in the first
place, why scan all data, create the index, if the alternative was to scan a
ll data in the first
place? Having said that, there are strategies that SQL Server can use which
are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so t
hat "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *seve
ral times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we ha
ve more modern methods,
like a "hash join", which at a *very* high level could be considered like cr
eating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance &#
91;respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query i
f estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate tha
t flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>[/vbcol]|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/pr...n/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query i
f
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate tha
t
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic manag
ement views to get this
historical information, and create indexes based on that information. SQL Se
rver 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard
, as suggested by
Jonathan can be a tool to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...[vbc
ol=seagreen]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napi
sao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.
e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly
* improve performances?
>
>[/vbcol]
index & statistics
sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate
>> that flat table scan is time & disk i/o wasting?
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query if
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate that
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate that flat table scan is
>> time & disk i/o wasting?
>> To create an index, it would have to scan all data. IF the alternative is a table scan in the
>> first place, why scan all data, create the index, if the alternative was to scan all data in the
>> first place?
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate
>> that flat table scan is time & disk i/o wasting?
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query if
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate that
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate that flat table scan is
>> time & disk i/o wasting?
>> To create an index, it would have to scan all data. IF the alternative is a table scan in the
>> first place, why scan all data, create the index, if the alternative was to scan all data in the
>> first place?
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>
Subscribe to:
Posts (Atom)