About indexes

[Puiu]

Hello,

 

I have a database with multiple tables that reference each other using foreign key constraints. On each of these columns i set a different nonclustered index.

However it seems that not everywhere i have a join on those foreign keys the index is used.

 

My question: Is there a chance that a query uses a nonclustered index and the execution plan doesn't show it?

 

I've red somewhere that the nonclustered indexes use the clusterd index to retrieve their data and i was asking myself if this wouldn't hide the use of the nonclustered index in the execution plan.

 

thank you!

[Nerseus]

Althought it's hard to read, the output of the statistics may help. See this thread for the code to use in Query Analyzer. I did a google search and came up with the following link, which looks pretty useful for explaining how to read the output.:

http://blogs.msdn.com/queryoptteam/archive/2006/08/29/730521.aspx

 

I googled for |sql server "statistics profile"| (between the pipes).

 

In answer to your question - as far as I know it, if the optimizer picks your nonclustered index, it will show up in the query plan even if that index relies on the clustered index. I believe the clustered index may be used to return data, if the columns used by your query are in the clusterd index. That's why, in some cases, it's good to have multiple columns in a clustered index. Then if a query matches a row and all columns come from the clustered index, the read is that much faster (it's a direct read instead of a read of a pointer which then reads the row).

 

I've found that digging into the optimizer for day to day stuff is too overwhelming, unless you have a DBA. If you have certain queries that just look right but don't seem to perform well, that's when I dig out these tools.

 

If you need more help, you may be able to create a script of the relevent tables and index and attach them for us to try. Indexes are funny, though. The optimizer will make decisions based on what it thinks the data looks like. Make sure you run DBCC commands to refresh the statistics and indexes. If the statistics on your DB are not up to date, the optimizer may pick the wrong index.

 

-ner

[Puiu]

Thank you for your answer Nerseus. I also found this page that has a lot of interesting things on it: http://www.sql-server-performance.com/

 

At the bottom of the page there are some interesting links.

 

This link: http://blogs.msdn.com/sqlqueryprocessing/default.aspx is actually the new address of the link you gave me.

 

I'll dig into these and i'll write here if I will find anything interesting.

 

Thank you