SQL Server Performance

Some hardware Advice to Process million of records

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by waqar, Jun 20, 2007.

  1. waqar Member

    Hi,

    I am currently running follow server for my production server

    PowerEdge 2850
    2x73Gx10K RPM (OS/SQL/TEMP DB) - RAID 1

    PoweValut220
    10x146Gx10K RPM (Database files/Log Files) - RAID 5

    I have 5~6 table contain 40~50 million records each.

    Now i want to process 6 million records against one table (have about 50 million records) to check if 3 fields match against 6 million records. If yes replace them with new records (replace= delete and add new records).

    This whole process is taking ages to complete. Structure of table is as below


    CREATE TABLE [dbo].[TBL1](
    [c1] [varchar](13) NULL,
    [c2] [varchar](13) NULL,
    [c3] [varchar](16) NULL,
    [c4] [varchar](5) NULL,
    [c5] [datetime] NULL,
    [c6] [int] NULL,
    [c7] [money] NULL,
    [c8] [datetime] NULL,
    [c9] [varchar](4) NULL
    ) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_tbl1_7_1017770683__K2_K5_K4_6_7] ON [dbo].[ProHistory]
    (
    [c2] ASC,
    [c5] ASC,
    [c4] ASC
    )
    INCLUDE ( [c6],[c7]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_tbl1_7_1785773419__K2_K4_K5_K1_3_6_7_9] ON [dbo].[tbl1]
    (
    [c2] ASC,
    [c4] ASC,
    [c5] ASC,
    [c1] ASC
    )
    INCLUDE ( [c3],[c6],[c7],[c9]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_tbl1_8_1017770683__K1] ON [dbo].[tbl1]
    (
    [c1] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_tbl1_8_1017770683__K2] ON [dbo].[tbl1]
    (
    [c2] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_tbl1_8_1017770683__K2_K5_6_7] ON [dbo].[tbl1]
    (
    [c2] ASC,
    [c5] ASC
    )
    INCLUDE ( [c6],[c7]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_tbl1_8_1017770683__K4] ON [dbo].[tbl1]
    (
    [c4] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE CLUSTERED INDEX [_dta_index_tbl1_8_1017770683__K5] ON [dbo].[tbl1]
    (
    [c5] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    .

    Beside this, i was also thinking to buy a storage to only process such huge tables.
    Any advice what should i buy for data processing only.

    ________________________________________________
    Be great in act, as you have been in thought.
  2. joechang New Member

    my thinking is: buy 4 more drives for the empty bays in the PE2850 ( 6 bays right?)

    move tempdb data to the same arrays as data on the PV220
    put the main db log on the 4 new drives in the PE2850

    need to actually see perf data to be sure
  3. techbabu303 New Member

    Hi waqar,

    Partioning tables spanning multiple disks having appropriate RAID and decent cotroller from PERC can also speed up things

    Cheers
    Sat
  4. waqar Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by techbabu303</i><br /><br />Hi waqar,<br /><br />Partioning tables spanning multiple disks having appropriate RAID and decent cotroller from PERC can also speed up things<br /><br />Cheers<br />Sat<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />@JOE,<br />I put log at separate RAID, will test this weekend for speed.<br /><br />@babu,<br />Currently i have constraint of disks <img src='/community/emoticons/emotion-1.gif' alt=':)' />. I am planning to purchase 4 external cages of 15K SAS drive (14 drives each). Then i will be able to strategies placement.<br /><br />Thank you guys.<br /><br />________________________________________________<br />Be great in act, as you have been in thought.

Share This Page