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?
>
>