INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Indexing my table - advice please.

Indexing my table - advice please.

(OP)
Hi All,

I have a largish table in SQL 2005 containing monthly 'buckets' of data.

When a new month of data becomes available I import this (via a staging table) and before the import I disable all indexes. After the inmport, I then rebuild all indexes on the table.

So I guess I have two questions:
1. Is this the correct approach in the first place?
2. Should it really take two hours and fifty minutes to rebuild the indices?
3. Have I built the correct indicies in the first place?

So, my table has 28 months worth of data averaging 1,000,000 rows each month.

I've indexed the 'month' field plus the things likely to be in the 'where' clauses. All indexes are non-unique and non-clustered.

I'd love any advice that anyone can give me.

Thanks all,

Fee

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

RE: Indexing my table - advice please.

You haven't really given enough information for us to give you advice.

Can you run the following queries and post the output here?

CODE

sp_spaceused 'YourTableNameHere' 

CODE

sp_helpindex 'YourTableNameHere' 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Indexing my table - advice please.

(OP)
sp_spacedused:

name rows reserved data index_size unused
PresLevelData 281409186 71942080 KB 33113520 KB 38827944 KB 616 KB

sp_helpindex

index_name index_description index_keys
idxBNFData nonclustered located on PRIMARY pdBNF
idxChemicalData nonclustered located on PRIMARY pdChemicalCode
idxPCTData nonclustered located on PRIMARY pdPCT
idxPCTPracticeData nonclustered located on PRIMARY pdPeriod, pdPractice, pdPCT
idxPeriodData nonclustered located on PRIMARY pdPeriod
idxPracticeData nonclustered located on PRIMARY pdPractice
idxSHAData nonclustered located on PRIMARY pdSHA

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

RE: Indexing my table - advice please.

Looks like you have 38 gigabytes of index space. This is a little larger than the actual data (33 GB).

I only see 1 possible redundant index. You have an index on:

CODE

idxPCTPracticeData	pdPeriod, pdPractice, pdPCT
idxPeriodData		pdPeriod 

I say that this is *possibly* a redundant index. If you run a query that uses the idxPeriodData index, it could just as easily use the idxPCTPracticeData index. On some occasions, this set up is appropriate, especially if the extra columns are "wide" columns like varchars and/or varbinaries. If pdPractice and pdPCT are small data types, like integers, you could probably drop the idxPeriodData index without any adverse effects.

If you decide to drop this index, I would encourage you to script it to a file first. This way... if any queries are noticeably affected by droping the index, you can always add it back in.

I encourage you to run this query:

CODE

SELECT OBJECT_NAME(i.object_id) as Table_Name,
       ii.name,
       *
FROM   sys.dm_db_index_usage_stats AS i
       Inner Join sys.Indexes ii
          On I.Index_Id = ii.Index_Id
          And i.object_id = ii.object_id
WHERE  i.database_id = db_id()
       And i.object_id = object_id('YourTableNameHere')
Order By ii.name 

This will give you information regarding the index usages for this table. Take a look for anything weird, like nulls or really old dates in the last_user columns.

Also take a look at the fill factors. I get the impression that data is loaded in to this table once per month, and is never updated or deleted. If this is the case, the fill factor should be 0 (which means 100 percent) or something really high like 95+ percent.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Indexing my table - advice please.

(OP)
Hmm. I don't have rights to run that query.

I guess I'll just keep going then!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

RE: Indexing my table - advice please.

(OP)
Am I correct to disable the indices and then rebuild, rather than dropping and re-creating?

(I wish I knew much more about this, but I'm all we've got so have to do the best I can!)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

RE: Indexing my table - advice please.

It is better to disable then rebuild.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Indexing my table - advice please.

If this is the only way data gets into the table then i would also set fillfactor on the indexes to 100.

That way your indexes will be full pages - so smaller in size (slightly) and as its more compact - a quicker index.

Dan

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

RE: Indexing my table - advice please.

(OP)
OK - I'll make that change. Presumably the best time is after diabling next import and before rebuilding?

Thansk for all help chaps - I really appreciate it.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close