Case study 18: Check index selectivity with dbcc traceon (302)
Platform: HP-UNIX 11, Tru64 , Windows
Sybase ASE version: 12.5.x
Background story: We have a problem with query performances and I've wanted to see is it posible to introduce some new indexes which would improve query performances. To find it out, I have to know is it one index better than another. I compare them with index selectivity.
Task: Find out how query optimizer choose between more indexes and why.
Solution: To see query optimizer output, dbcc traceon(3604, 302) command has to be issued. It prints trace information for index selection.
How this work?
I'll explain this on one simple example. This is not real example but it will point out key things.
Let's say that we have one table which creation script is given below:
create table TAB1 (
ID int not null,
NAME varchar(100) null,
AUTOINC numeric(20,0) identity,
CONSTRAINT TAB1_AUTOINC_PK PRIMARY KEY CLUSTERED (AUTOINC)
)
go
This script will create index on PK field, AUTOINC, and that will be the only index for now.
This table can be filled with data, and if any wants to do it, this script can be used.
Now, we are going to enable index selectivity trace. Following command has to be issued:
dbcc traceon(3604,302)
It will print trace information to the SQL client.
After that, we are going to see how Sybase see existing index. To see it, we will issue next query:
select * from TAB1 where ID = 500
Full trace can be seen here , and importan part is given below:
The best qualifying access is a table scan, costing 161 pages...
As it can bee seen, optimizer chose full table scan, and it cost 161
page.
Let's add an usefull index for example query:
create nonclustered index TAB1_ID_INDX on TAB1 (ID)
go
Now we are going to execute the same query as a few moments ago:
select * from TAB1 where ID = 500
Full trace can be seen here , and importan part is given below:
The best qualifying index is 'TAB1_ID_INDX' (indid 2)
costing 3 pages...
Comparing these two traces it can be seen that index on ID costs significantly less then in situation where index is on column which is not used in query. So, optimizer choose second index because it costs less.
This is extreme example, but it shows how index cost and selectivity can be seen, and compared.
Remark: more about index selectivity and dbcc traceon(302) can be seen in Performance and Tuning Guide: Tools for Monitoring and
Analyzing Performance, chapter Tuning with dbcc traceon(302)
