| Atenza 2006-10-25, 6:00 am |
| thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
>
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
>
> As for your questions:
>
>
> There's no "magic button" for this. See my above elaboration.
>
>
>
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
>
> But not for:
> B = 45
>
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>
>
> See above.
>
>
>
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
>
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
>
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Atenza" <Atenza@mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@TK2MSFTNGP05.phx.gbl...
>
|