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]