September 17, 2004 at 3:29 am
Howzit Guys
I recently got involved in our telephone system .
There is a pKey (Composite key) made out of 3 columns ...then there is a unique non-clustered index that is made out of the same three columns (why I do not know)...when I query the table and look at the execution plan it searches the non-clustered index ..why ? ..shouldn't it query the Pkey ?
Here is some details from sp_helpindex
PK_smdrclustered, unique, primary key located on PRIMARYProcessID, ProcessTime, RecNum
ixPrimarynonclustered, unique located on PRIMARYProcessID, ProcessTime, RecNum
September 17, 2004 at 5:39 am
I would research and ensure that the NON-CLUSTERED index is not referenced in any code and then REMOVE it.
This way the system will start using the PK again. OR you could FORCE the issue by using WITH(INDEX(PK_smdr) after the table name.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 17, 2004 at 2:25 pm
Hi,
Use the UPDATE STATISTICS (table_name) to update the statistics. Cause the query plan is based on these statistics. In the result set try to use the same fields (in the same order) from which is based the PK.
Regards,
Jorge
September 17, 2004 at 2:44 pm
As they are exactly the same unless someone has forced a hint to use the ixPrimary index drop it.
September 18, 2004 at 9:55 am
Don't drop the index!
If SQl server is ising it then it must be of use.
The most likly reason is that the query only requires these three columns form the table. In this case, it is much more efficient to read the non clustered index. This is because the leaf level (i.e. detail level) of the clustered index is the actual data page and contains every column in the table.
As an example:
Say your table has 20 culumns of say 10 bytes, total length 200 bytes per row, and there are 1000 rows in the table, then to read the whole table via the clustered index would require 20 * 10 * 1000 bytes to be read from disk.
This will be true even if you olny want to use 3 of the columns.
Now, if you have a 'covering index', which is what the non clusted index in this case is called, then you only have to read 3 * 10 * 1000 to get the same result. This can amount to a big saving.
So - in your case, leave the index in place as SQL is using it. If you drop the index, the query will take longer to run.
Oh, congratuations to the original developer for spotting that a covering index would be useful in this case.
September 18, 2004 at 12:18 pm
If you query the table only using the 3 columns why would sql query all columns ?? doesn't make any sence what you are saying ..the query should perform fine with just the clustered index in place ....I have tested it in QA and SQL uses the PK better then the n-clustered index - Query execution plan stats ...the cost of the query is less when using the PK ....and is much much faster
September 20, 2004 at 1:37 pm
Douglas,
What you are referring to means a table scan. If you defined a non-clustered index on top of a clustered index and if the optimizer uses the non-clustered index, it will do a index seek/scan and then a lookup in the clustered index. Refer to Steve's article below.
http://www.sqlservercentral.com/columnists/sjones/sqlserver2000indexing.asp
Also refer to the links to microsoft's website
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply