how to tell if indexes are helping 4

[seminewbee2005]

I was wondering if some one could give me a god example for me to contstruct to see it work. like a basic table with no indexes, query it and look in QA the execution plan cost, then set index or indexes on field that the query uses in where clause to see if cost is less. I did this and saw no difference.

Will I need to have several hundred records to test this ot would 20 sufice. Also are you indexes actually used or are just hints to sql. If just hints what in the world is the point indexing? does it even help.

thanks

 

[stevexff]

You will probably need quite a lot of records to notice a difference. Imagine your phone book (the real printed one). It's probably got about a hundred thousand entries, call it 128K to make the maths simpler. That's 2¹⁷. So by using an index we can find any entry in the book by looking at 17 records (or less if we are lucky). The alternative, unindexed way would be to read all 128K records. Assuming we are averagely lucky, then for any given name we could expect to find it by examining *only* 64,000 records.

Because modern database servers keep quite a lot of data in memory buffers, you probably won't notice much difference unless you have several thousands of records. You could always write a script to generate them...

 

[DotNetGnat]

Let me give you the real situation i encountered recently...

I have this table with 6 million record....This table contains all the Review Note details entered by our customer service representatives in our company...when ever a customer calls, the representative make a note of something...

So the table contains columns like...

CustRepName, CustAccNum, NoteDate, Note1, Note2

and so on 10 more columns...

And i had this situation where i need to create a small form where the customer service rep can see what all notes she/he made on so and so date or during particular dates...

First i did this without indexes and i used to get a time out error.
Then i put a index on the CustRepName on which our search criteria is based...and i guess we have 200 Customer Service reps...

so now the same query brings in results in 1 sec...its like the whole 6 million record table has been reduced to 200 records..

-DNG

 

[vongrunt]

Some situations when indexes are not used:

- small tables that occupy only few 8kB physical pages. That's because additional cost of bookmarking overweights otherwise slow table/clustered scan.

- "broad" WHERE clause, for example when query returns 20% or more rows from table.

- function used in WHERE clause

- poor data selectivity (very few distinct values in indexed column).

Server may or may not use indexes. The point behind is to let developers concentrate on query logic and leave best "physical" course of action (fastest exec plan) to server.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[seminewbee2005]

thank for all your answers!!!!! very useful. does sql have bitmap indexing like oracle?

 

[vongrunt]

Nope. Only B-tree based indexes.

There is bitmap operator internally used for optimization of large joins - but it goes bye-bye after query is done.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

First. Everything said above is good.

This addressses the question of
"how to tell if indexes are helping"

If you have built some indexes and are wondering if they are usefull, there are a couple of nice commands to be aware of..

I like to use

Set Statistics IO on 
Set Statistics Time on

What statistics IO does is tell you how many "pages" were aquired and read to solve your query.
A page is an 8k grouping of rows.

The smaller the io, generally speeking the faster the query. But another very good thing to realize is that it can remove alot of locking issues, so remove overhead and issues in situations where you have more than one user.

If you run a query on a 3 gig table and don't have a good index or your criteria doesn't limit the number of rows that the query needs to examing (like a "where colX <> 1") then you will see approx 393216 pages of IO to solve the query.

Add a good index and create a query with a valid search argument and it might be resloved in as few a 3 or 5 pages of I/O.

Add a bad index and it will either not be used or you can see IO go up. THis is one reason that you should be very carefull about using "optimizer hints" to force an index use.


The Stats TIme is a little harder to read, but can be usefull in working out if the index strategy is good. It tells you info like how long did sql think about how to build the query and then execute it.. I have seen some strategys use very little IO but run slow due to the way they query executed...

Another excellent tool it the "show query Plan" option in Query Analyser..

To access this option, you would right click the query window and select "Show Execution Plan" or do a "Ctrl-K".

THis will tell you alot about the acutall indexes used and what part of the query took time. or caused sql to work harder.


Also Index type is "critical" in getting the best performance on many types of querys. Non-Clustered vrs Clustered should take some very carefull analysis (of your querys and the priority of each query - if you are low on the ground floor in index knowledge - take a good look at the "Index tuning wizard" (available via Enterprise Manager)

I tend to stay away from putting Clustered indexes on PRimary key cols as a non-clustered index is as good at finding a single record..(we don't useually do a search on a range of PK's, but due to the way you use foreign keys these are usually very good candidates for clusteed indexes)

Another goood rule is be VERY Carefull in ceating Multi COlumn Clutered inexees. The reason being the every nonclusered index also contains all the columns that exist in your clusteed index. THis can cause your databases to become HUGE##$ very fast. And actuall slow things down.

HTH


Rob