Indexing my table - advice please.

[Welshbird]

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

 

[gmmastros]

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

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

sp_spaceused 'YourTableNameHere'

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

 

[Welshbird]

sp_spacedused:
[tt]
name rows reserved data index_size unused
PresLevelData 281409186 71942080 KB 33113520 KB 38827944 KB 616 KB
[/tt]
sp_helpindex
[tt]
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
[/tt]

Fee

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

 

[gmmastros]

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:

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:

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

 

[Welshbird]

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

 

[Welshbird]

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

 

[gmmastros]

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

 

[SQLScholar]

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

----------------------------------------

http://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
----------------------------------------

 

[Welshbird]

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