Is this a bad indexing strategy for a table?

Posted by llamaoo7 on Stack Overflow See other posts from Stack Overflow or by llamaoo7
Published on 2009-02-15T19:40:49Z Indexed on 2010/06/06 20:12 UTC
Read the original article Hit count: 148

The table in question is part of a database that a vendor's software uses on our network. The table contains metadata about files. The schema of the table is as follows

Metadata 
ResultID (PK, int, not null) 
MappedFieldname (char(50), not null) 
Fieldname (PK, char(50), not null) 
Fieldvalue (text, null)

There is a clustered index on ResultID and Fieldname. This table typically contains millions of rows (in one case, it contains 500 million). The table is populated by 24 workers running 4 threads each when data is being "processed". This results in many non-sequential inserts. Later after processing, more data is inserted into this table by some of our in-house software. The fragmentation for a given table is at least 50%. In the case of the largest table, it is at 90%. We do not have a DBA. I am aware we desperately need a DB maintenance strategy. As far as my background, I'm a college student working part time at this company.

My question is this, is a clustered index the best way to go about this? Should another index be considered? Are there any good references for this type and similar ad-hoc DBA tasks?

© Stack Overflow or respective owner

Related posts about database

Related posts about sql-server-2005