August 17, 2009 at 1:40 pm
I have a table that I have created a non-clustered index with a filter. If I run
Select * from table with WITH (INDEX = 1) where field = '123456'
the statement runs between 10-12 seconds. I am running 100 in a row otherwise it would just be 1 second.
I run
Select * from table with WITH (INDEX = 20) where field = '123456'
and the statement runs in 6-8 seconds using the same 100 runs.
I created the new non-clustered index with filter on the field for all values = '123456'. This index is index 20 and the clustered index on the table is index 1.
So when I run the statement with index = 20 I get a 20%+ performance increase.
When I run the statement with out the use of a with (INDEX = 20) it always takes 10-12 seconds.
How do I determine which index is being used? (Based off of times I thing index 1 is being used)
and how do I get SQL to use the better tuned index?
I would like to create more filtered indexes if SQL would use them.
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
August 17, 2009 at 1:50 pm
John Burris (8/17/2009)
How do I determine which index is being used?
Look at the execution plan. On the management studio toolbar there's a button 'Include actual execution plan'
and how do I get SQL to use the better tuned index?
Depends why it's not using the index. Without seeing index, query and exec plans (default and one with hint) it's very hard to say for sure. Usually (as in the vast majority of cases) if SQL chooses not to use a particular index, there's a good reason.
This may help: http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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 17, 2009 at 1:52 pm
Keep in mind that testing with SELECT * guarantees a bookmark lookup. Are you sure you're not picking up the slowness of those bookmarks? If it's not selective enough (like Gail was alluding to), SQL Server may choose NOT to use the index, just becuase of the additional cost of the lookups.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 17, 2009 at 2:00 pm
Also check the output of STATISTICS IO. SQL prefers query plans with lower IOs. If there's bookmark (key/RID lookups) the IOs may be very high
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 17, 2009 at 2:05 pm
It is using index 2 when running with out hints.
selecte.enrollid,
s.studentid,
s.firstname,
s.lastname,
s.dob,
s.stamp
from dbo.tblstudent s
inner join dbo.tblenrollment e on s.studentid = e.studentid
where e.campusid = '212905001' and s.firstname like 'john'
order bys.lastname,
s.firstname
Here are the three indexes on the table.
INDEX 1
ALTER TABLE [dbo].[tblEnrollment] ADD CONSTRAINT [PK_tblEnrollment] PRIMARY KEY CLUSTERED
(
[enrollid] ASC
)
INDEX 2
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblEnrollment] ON [dbo].[tblEnrollment]
(
[studentid] ASC,
[campusid] ASC
)
INDEX 20
CREATE NONCLUSTERED INDEX [ncl_tblEnrollment_212905001] ON [dbo].[tblEnrollment]
(
([campusid]='212905001') ASC
)
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
August 17, 2009 at 2:13 pm
The Estimated I/O cost for the filtered index 20 is .003125
The Estimated I/O cost for the non-filtered index 2 is 1.83127
so the I/O is lower for the filtered index then the non-filtered index.
I am looking at the Estimated I/O cost from the Actual Execution Plan for the Index Scan for that table.
I am a great administrator, but I am just getting into performance tuning of t-sql. Any help or direction is greatly appreciated.
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
August 17, 2009 at 2:17 pm
Execution plans please? (see this article on saving and posting - http://www.sqlservercentral.com/articles/SQLServerCentral/66909/)
Why are you doing a LIKE with no wildcard?
Looking at that, the most optimal index I think will be this one
CREATE UNIQUE INDEX idx_Enrollment_CampusStudent
ON [dbo].[tblEnrollment] ([campusid], [studentid])
INCLUDE (enrollid)
This is covering, so no lookups, and the order of the columns allows the filter first and the join straight after.
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 17, 2009 at 2:46 pm
I have uploaded the two execution plans.
As for the Like with no wildcard, I am focusing on the index for the table tblenrollment and grabbed for testing purposes the part of the code from the SP that had the where clause for the other table. I just put a fixed name in for testing.
After reading the article from SQLinthewild, I now see the order of columns play into the mix.
If sql only has to look though an index of 2486 records instead of 604449 records I would think it would be faster.
PK_tblEnrollment1604449
IX_tblEnrollment2604449
IX_tblEnrollment_13604449
IX_tblEnrollment_24604449
ncl_tblEnrollment_212905001202486
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
August 17, 2009 at 2:55 pm
After looking at the order, add your comments, add a good article on the order of columns, give brain a tuneup and then look at problem upside down. I think by adding the studentid into index 20 the non-hint version now runs index 20 and the cost for the query is 0% compared to the cost of the Index scan of the other table which is now 94%. It was about 25% and 69%.
This is a big difference and big help.
Thanks for the help I think I learned a little bit today.
If you learn one thing a day you will never die.
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
August 17, 2009 at 3:08 pm
If the CampusID filter is hard-coded and never changes, this filtered index should probably work better.
CREATE UNIQUE INDEX idx_Enrollment_CampusStudent
ON [dbo].[tblEnrollment] ([studentid])
INCLUDE (enrollid)
WHERE [campusid]='212905001'
Edit: After reading again, I see that's exactly what you did.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply