SQL Server Performance Forum – Threads Archive

Some hardware Advice to Process million of records

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.

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

Hi waqar, Partioning tables spanning multiple disks having appropriate RAID and decent cotroller from PERC can also speed up things Cheers
Sat

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by techbabu303 Hi waqar, Partioning tables spanning multiple disks having appropriate RAID and decent cotroller from PERC can also speed up things Cheers Sat <hr height="1" noshade id="quote"></blockquote id="quote"> @JOE, I put log at separate RAID, will test this weekend for speed. @babu, 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. Thank you guys. ________________________________________________ Be great in act, as you have been in thought.

]]>