Indexing Strategy

  • Hello,

    I wonder if anyone could advise me on an indexing strategy.

    Here's the deal:

    I have a database which is an Audit of another db, and so is constantly inserted into. I have a clustered index on customer id.

    I also have a copy of this db, which is trickle fed from my Audit DB.

    At the end of the month the databases are closed off. The second db is for MIS purposes. The thing is, the MIS must be available in the current month, but must also be able to handle 1 million inserts per day.

    Is there an ideal indexing strategy for this scenario? The MIS system would ideally be clustered on a date field, but with the high value of inserts, would I be at risk from blocking?

    Any suggestions?

    Thanks

  • SQL 2000?

    You should be ok on the inserts. Is this 1M in a time frame? Per Day isn't too bad unless it's 500k one hour and 500k over 11 hours. Know the guesstimate per minute or second to get an idea. You can always drop this on it's own RAID array, which would speed it up.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • Preferable if you want to get maximum performance for writes then use a RAID 10 array (more costly but well worth it).

    Also having each DB on it's own array or server can bennifit you.

    As for indexes depending on how your clustered index is defined then datefield is gretam, datetime is better. Especially since these should be chronological in nature and thus inserts are at end and not way back in dataset.

    Also if the MIS database is used a lot for reporting and does not have to reflect immediate changes then you might want to try this.

    Create an Stored Procedure to do your inserts into a target table. Now for MIS the target table will be one of two. Of which neither have an index (tblAudit1 and tblAudit2) but are the same strucutre as your main table (tblAuditMain). Now you create a control table to control flow between tables with a bit field (tblControl). Then finally you create a job with 4 steps minimum.

    Step 1 check tblControl, if bit = 1 then raise an error

    Ex.

    DECLARE @Tbl INT

    SET @Tbl = (SELECT fldBit FROM tblControl)

    IF @Tbl != 1

    BEGIN

    RAISERROR('Not tblAudit1',11,-1)

    RETURN

    END

    WHen no error goto next step, on error goto step 5

    Step 2 run Alter Proc statment. Use a copy of the CREATE PROCEDURE statement you used for your proc

    but change to ALTER and set name of appropriate table in procedure. When runs goto step 3

    Step 3 run INSERT INTO from table referenced in Step 2 procedure to tblAuditMain. Goto step 4.

    Step 4 run truncate against table referenced in Step 2. Goto step 8.

    Step 5 same as step two but ALTER references other table. When runs goto step 6

    Step 6 run INSERT INTO from table referenced in Step 5 procedure to tblAuditMain. Goto step 7.

    Step 7 run truncate against table referenced in Step 5. Goto step 8.

    Step 8 set fldBit to opposite value. For next control cycle.

    Ex.

    DECLARE @Tbl INT

    SET @Tbl = (SELECT fldBit FROM tblControl)

    IF @Tbl != 1

    BEGIN

    UPDATE tblControl SET fldBit = 1

    END

    ELSE

    BEGIN

    UPDATE tblControl SET fldBit = 0

    END

    Goto next step.

    Now you can set this to run everyday at a particular time or even every so many minutes or hours. Thus allowing you to insert into the Main table less often and less worry with blocking.

    In addition you can set tblAuditMain (w/tblControl), tblAudit1, and tblAudit2 on seperate filegroups on seperate arrays giving you more IO.

    The only downside is you have to get MIS to agree to a time delay that is ok. You can even use this on the other audit system.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Hi,

    Thanks for you replies. Would locking hints help to prevent blocking?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply