Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,1064,1074,1084,1094,110»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 9:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 7,814, Visits: 9,562
GilaMonster (8/26/2013)
So the current list is:

No need to index small tables
No need to index tables that fit into memory
Indexes enforce physical order of data
Seeks are better than scans
Clustered index seek/scan is better than a nonclustered index seek/scan
Indexes are good. More indexes are better
You only need one (clustered) index

Here are two I used to hear a lot:
If you have an index on column A and an index on column B and an index on (A,B) the optimiser will never choose the index on (A,B)
If you have an index on (A,B) there's no point in having an index on B.

And don't forget the good old
The optimiser never uses an index on a table variable
or its "fundamentalist" variant
You can't have indexes on table variables


Tom
Post #1488431
Posted Monday, August 26, 2013 9:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
Both of those covered in the 'considerations for nonclustered indexes' section.

One more added - Indexes with a leading bit column are useless
Edit: and another - Optimiser's choice of seek/scan is affected by fragmentation



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488437
Posted Monday, August 26, 2013 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 10,295, Visits: 13,278
GilaMonster (8/26/2013)

One more added - Indexes with a leading bit column are useless


I'll probably attend just for this one. I would currently argue that you'd be better off leading with the other key columns in order to see the index get used more often and adding the bit column later in the index, or if your most common queries where bitColumn = 1 having a filtered index with the other key columns and a filter on the bit column. As always I'm sure I'll be proved incorrect in your session, but I'm definitely interested in learning the why.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1488442
Posted Monday, August 26, 2013 10:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
I didn't say they're the best thing out there. Just that they're not useless (which many say they are)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488448
Posted Monday, August 26, 2013 10:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 20,744, Visits: 32,557
Jack Corbett (8/26/2013)
GilaMonster (8/26/2013)

One more added - Indexes with a leading bit column are useless


I'll probably attend just for this one. I would currently argue that you'd be better off leading with the other key columns in order to see the index get used more often and adding the bit column later in the index, or if your most common queries where bitColumn = 1 having a filtered index with the other key columns and a filter on the bit column. As always I'm sure I'll be proved incorrect in your session, but I'm definitely interested in learning the why.



Had an index like that on several tables at a previous employer. The bit flag indicated if a row was historical or current. Most queries against this table were for current data which was less than 10% of the total data. Was a very useful index to have, especially on a SQL Server 2000 system.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1488451
Posted Monday, August 26, 2013 12:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
33 slides for 3 hours. Should be enough.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488477
Posted Monday, August 26, 2013 1:11 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 17,843, Visits: 15,791
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.


With that many slides you could easily stretch it into an 8hr preso




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1488481
Posted Monday, August 26, 2013 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:12 AM
Points: 31,210, Visits: 15,651
SQLRNNR (8/26/2013)
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.


With that many slides you could easily stretch it into an 8hr preso


33 slides is about 16 minutes for me







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1488516
Posted Monday, August 26, 2013 6:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 7,814, Visits: 9,562
Steve Jones - SSC Editor (8/26/2013)
SQLRNNR (8/26/2013)
GilaMonster (8/26/2013)
33 slides for 3 hours. Should be enough.


With that many slides you could easily stretch it into an 8hr preso


33 slides is about 16 minutes for me

I guess Gail gets more audience interaction than you do, then. She must be a more interesting speaker.


Tom
Post #1488518
Posted Monday, August 26, 2013 7:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 9:36 PM
Points: 9,927, Visits: 11,189
Jack Corbett (8/26/2013)
I would currently argue that you'd be better off leading with the other key columns in order to see the index get used more often and adding the bit column later in the index, or if your most common queries where bitColumn = 1 having a filtered index with the other key columns and a filter on the bit column.

Having an index with a leading bit column is certainly pretty "useless" from a cardinality estimation point of view. Data-distribution information (the statistics histogram) is only created for the first column in the index key. Placing the bit column later in the index key, but before any columns used in inequality tests is a reasonable fix for that, and might be more generally useful, as you say..

A filtered index is also an option, though they can be a bit of a double-edged sword. Having the filtering columns in the keys means Halloween Protection might be needed more often; having a filter on a column that is not in the keys can cause incorrect results and/or suboptimal query plans. There again, just using a filtered index at all can also result in suboptimal plans, so the answer isn't simple (how strange! ha ha).




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1488519
« Prev Topic | Next Topic »

Add to briefcase «««4,1064,1074,1084,1094,110»»»

Permissions Expand / Collapse