November 27, 2009 at 11:05 pm
Really great series, Gail!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2009 at 8:12 pm
How about if I have a boolean column where its value is always 0 or 1. Is that a good candidate for NonClustered index? I use it in my where clause. It is not unique and its selectivity in some queries is high but others not.
December 10, 2009 at 11:27 pm
Maybe. 😉
Try it and see. If queries use it, then it's a useful index. If they don't then drop it. I'd say if it's very selective for some queries (<1% of the table) or covering it may be useful.
Is that boolean column used alone in the where clause or with other predicates. If there are other predicates, a composite index may be very useful.
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
December 11, 2009 at 10:02 am
Hi Gail,
Thanks for you prompt reply, the boolean is alone and there are no other ones. What is your final thought on it.
January 22, 2010 at 1:46 pm
Nice article, Gail, and a nice end to a nice series. The more I read the more I want to read more, because in articles like yours there is always some new nugget of information that I really ought to have known but somehow didn't. Thanks very much for this series.
Tom
Tom
January 23, 2010 at 1:14 am
Glad you liked them
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
April 15, 2010 at 3:57 am
Gail i read every articles,comments of urz thats wat gives us more exp and add more knowlege ......
Gail i have 3 questions :-
1 wat if there is a clustered index on a table is disable will be use of nonclustered index by reading ur article my answer is no.
2 wat if there is no clustered index on a table how does noncluster index will work.
3 we use fillfactor and pad index with the indexes if a record is deleted or updated how does the fill factor works we have kept it to 80 % that meens a 20 % of the page will be free
April 15, 2010 at 4:15 am
1) If the clustered index is disabled, the table is inaccessible and cannot be queried at all.
2) It's mentioned in the article, under 'What is a nonclustered index'
3) Fill factor only applies when an index is created or rebuilt. The point of fill factor is to leave space for future inserts or updates. It makes no sense for the fill factor to be maintained when inserts are done, as that would mean that part of a page is always empty (wasting space, degrading performance) and it would defeat the whole point of a smaller fill factor.
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
April 20, 2010 at 10:46 pm
Thankz gail for the info.
Gail do u mean this..
So, what exactly are the considerations with fillfactor? Higher fillfactor settings should result in less index pages which in turn should result in fewer pages read during scan operations. As been mentioned many times already, less I/O generally equates to better performance.
If, however, there are tables present with high rates of INSERT, UPDATE and DELETE activity, lower fillfactor settings of 50-60% should be used. This will need to be coupled with periodic index rebuilds that will re-establish the fillfactor setting to keep DML running smooth through the indexes.
April 21, 2010 at 1:35 am
Pretty much, yes.
A lower fill factor helps reduce/prevent page splits from insert or update operations (not deletes), but the downside is that the index takes more pages, hence more storage space.
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
June 17, 2010 at 5:56 am
Typo:
Were as, if there was a single index defined with all three columns, the matching rows could be located as a single seek operation.
should be
Whereas, if there was a single index defined with all three columns, the matching rows could be located as a single seek operation.
Thanks again Gail.
December 7, 2010 at 10:20 am
This was a very useful and great article. Unfortunately, I marked the wrong rating, it should have been a five star rating rather than a one star rating. Please have someone at the top change it. Thanks for a very well written article.
January 7, 2011 at 10:28 am
Great article series. I'm trying to determine what is meant by "left-based subset".
January 7, 2011 at 10:30 am
Thank you for a well written series of articles, Gail, though I have an outstanding question:
"Column order
... and whether the queries will filter with equality or inequality matches."
I didn't see any explanation of how, when, and why one would consider equality vs inequality of matches, and that's one of the areas I definitely need to learn more about.
January 7, 2011 at 2:29 pm
Kevin Wood-419472 (1/7/2011)
Great article series. I'm trying to determine what is meant by "left-based subset".
A subset of the index key, reading from the left.
eg an index on (Col1, Col2, Col3, Col4) is fully seekable for the following
where Col1 = @1 and Col2=@2 and Col3 = @3 and Col4 = @4
where Col1 = @1 and Col2=@2 and Col3 = @3
where Col1 = @1 and Col2=@2
where Col1 = @1
Hence 'left-based subset'
The index is not seekable if a query filters only on Col3 (for eg)
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 15 posts - 31 through 45 (of 92 total)
You must be logged in to reply to this topic. Login to reply