August 28, 2012 at 6:55 am
Sorry all, just got a total mind block all of a sudden.
The below table has PK as defined in the script below.
CREATE TABLE Table1
(
Col1 VARCHAR(64),
Col2 INT,
...,
...,
...,
PRIMARY KEY CLUSTERED (Col1, Col2)
)
Majority of the queries run off Col2 with a few which use Col2 and Col1, SQL is recomending adding an index to Col2, but as SQL knows the density of Col1, Col1 and Col2, I am thinking just reverse the order of the columns in the key so that the density shifts for Col2, Col2 and Col1. Which should remove the index scan and change it to an index seek if I am not mistaken.
August 28, 2012 at 8:07 am
anthony.green (8/28/2012)
Sorry all, just got a total mind block all of a sudden.The below table has PK as defined in the script below.
CREATE TABLE Table1
(
Col1 VARCHAR(64),
Col2 INT,
...,
...,
...,
PRIMARY KEY CLUSTERED (Col1, Col2)
)
Majority of the queries run off Col2 with a few which use Col2 and Col1, SQL is recomending adding an index to Col2, but as SQL knows the density of Col1, Col1 and Col2, I am thinking just reverse the order of the columns in the key so that the density shifts for Col2, Col2 and Col1. Which should remove the index scan and change it to an index seek if I am not mistaken.
It really depends on the selectivity of Col1 and Col2 when both are used. I would recommend that you test all your queries against the change in index from Col1, Col2 to Col2, Col1 and see what happens. Hopefully you have a test environment to do this.
August 28, 2012 at 8:34 am
Thanks Lynn, have tested in Dev and Test for the column change order and it does indeed now seek on the index instead of scan, for the particular query.
From the tech specs based around this table, every query should use Col2 and only if searching for a specific item Col1 is used along with Col2.
So with this in mind and that Col2 should always be used in all queries, I would say pretty safe to change the column order.
August 28, 2012 at 8:37 am
Yup, it'll do exactly that. If nothing seeks on Col1 alone reversing the order of the columns will give you seeks.
Just bear in mind you'll need to drop the pk to do that, which means dropping all foreign keys too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2012 at 8:46 am
And also remember to change the order of the columns in the foreign key to match the new primary key order. (Just in case anyone finds this in the future).
Thanks both much appreciated as always.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply