Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: table with 14 FK-ID's, does that mean I need 14 1-column indexes?

    If I have indexes that cover these columns, do I still need (is it recommended?) to create individual 1-column indexes on these columns that are Foreign Key's to other tables?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the answer is: it depends

    it's pretty difficult to comment without seeing the table and its columns, and, more importantly, how they're used

    14 foreign keys sounds like a lot, though, and i cannot imagine how a single index that covers all of them will be of any use whatsoever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Taking denormalisation a bit far?

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    This table accounts for 85% of the total DB size (11GB data, 46.8GB index, 63.8 MM rows). No stored procedures are used, all queries are dynamic SQL.

    I dropped all of the single column indexes on my test system, and created 6 indexes that covers most of the individual column indexes. I think I will just add indexes for any singular columns that are not currently covering FK's.

    I analyzed a week's worth of profiler trace data (ran 2 hours a day, anything > 100ms) and used DTA on any queries that referenced this table and took longer than half a second to run to identify these indexes. Index size dropped 1/3 and performance went from about 8 minutes duration for the workload to 4 minutes.

    I am going to use table partitioning to hopefully alleviate a lot of their db maintenance problems this week. They are doing a purge every 30 minutes deleting any data older than 7 days, then they have a DB reindex job running daily and a t-log backup every 15 minutes which is getting some large file sizes due to the purge and reindex.
    Last edited by Gagnon; 05-17-11 at 14:05.

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    Here is the index coverage of any columns that have an FK on it:

    Code:
    		Rows	idx1	idx2	idx3	idx4	idx5	idx6
    Agent_ID	155		x	x		x	
    Date_ID		14000						
    Device_ID	48						
    f_Success_ID	960000	x	x	x	x	x	x
    Location_ID	137						
    MetaTagMap_ID	238000		x				
    Metric_ID	19000	x	x		x	x	x
    Plan_ID		68					x	
    PlanRun_ID	2						
    Test_ID		1000	x		x	x	x	
    TestCampaign_ID	37						
    TestSequence_ID	315						
    Time_ID		86000						
    Transaction_ID	1500	x			x
    Rows refers to the number of rows in the FK table.
    Last edited by Gagnon; 05-17-11 at 14:48.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    So these are the columns's with FK's that have no index coverage.

    sorted by selectivity
    Code:
    Time_ID		86000	
    Date_ID		14000						
    
    TestSequence_ID	315						
    Location_ID	137
    Device_ID	48	
    TestCampaign_ID	37						
    PlanRun_ID	2
    I am thinking of adding one more index on:
    (Time_ID, Date_ID)

    Make sense?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •