Server Performance Problem?

16 Sep 2005 1:22 AM

Greg C

Here are the server's specs:
HP Proliant DL580G2
4 x 3.0ghz Processors
3.6gb RAM
2 x 146gb Local Hard Disks
4 x 250gb 2gbps FiberChannel SAN Disks
2 x Power Supplies
2 x 1000mbs Network Interfaces

Here's the SQL:
BEGIN TRAN
      CREATE TABLE #tmpClmIds   ( EncKeyC INT NOT NULL PRIMARY KEY,
                                    MbrId_Orig VARCHAR(16) NOT NULL,
                                    MbrId_Unique VARCHAR(16)   NULL)
COMMIT
BEGIN TRAN
      INSERT INTO #tmpClmIds
                  ( EncKeyC, MbrId_Orig )
      SELECT EncKeyC, E_Mbr FROM TableA
COMMIT

TableA has 8 million records, many fields, the primary key is EncKeyC, and
it's database and log files are located on the SAN.

It takes the server 33 minutes to load #tmpClmIds.

Doesn't that seem waaay too slow? If so, where should I investigate?

I execute this over a VPN on a client's server and I cannot connect perf
mon. Their IT thinks that there is no problem with the server.

Thanks!

Greg C

16 Sep 2005 2:50 AM

carion1

Where does tempdb reside?

--

Derek Davis
ddavi***@gmail.com

Show quote

"Greg C" <GregC@NoSpam.com> wrote in message
news:m7pWe.10351$w46.4927@tornado.texas.rr.com...
> Here are the server's specs:
> HP Proliant DL580G2
> 4 x 3.0ghz Processors
> 3.6gb RAM
> 2 x 146gb Local Hard Disks
> 4 x 250gb 2gbps FiberChannel SAN Disks
> 2 x Power Supplies
> 2 x 1000mbs Network Interfaces
>
>
> Here's the SQL:
> BEGIN TRAN
>      CREATE TABLE #tmpClmIds   ( EncKeyC INT NOT NULL PRIMARY KEY,
>                                    MbrId_Orig VARCHAR(16) NOT NULL,
>                                    MbrId_Unique VARCHAR(16)   NULL)
> COMMIT
> BEGIN TRAN
>      INSERT INTO #tmpClmIds
>                  ( EncKeyC, MbrId_Orig )
>      SELECT EncKeyC, E_Mbr FROM TableA
> COMMIT
>
>
> TableA has 8 million records, many fields, the primary key is EncKeyC, and
> it's database and log files are located on the SAN.
>
> It takes the server 33 minutes to load #tmpClmIds.
>
> Doesn't that seem waaay too slow? If so, where should I investigate?
>
> I execute this over a VPN on a client's server and I cannot connect perf
> mon. Their IT thinks that there is no problem with the server.
>
> Thanks!
>
> Greg C
>

16 Sep 2005 3:02 AM

Greg C

It's on the SAN also.

Greg C

Show quote

"carion1" <ddavi***@gmail.com> wrote in message
news:O$FwtnmuFHA.1252@TK2MSFTNGP09.phx.gbl...
> Where does tempdb reside?
>
> --
>
> Derek Davis
> ddavi***@gmail.com
>
> "Greg C" <GregC@NoSpam.com> wrote in message
> news:m7pWe.10351$w46.4927@tornado.texas.rr.com...
>> Here are the server's specs:
>> HP Proliant DL580G2
>> 4 x 3.0ghz Processors
>> 3.6gb RAM
>> 2 x 146gb Local Hard Disks
>> 4 x 250gb 2gbps FiberChannel SAN Disks
>> 2 x Power Supplies
>> 2 x 1000mbs Network Interfaces
>>
>>
>> Here's the SQL:
>> BEGIN TRAN
>>      CREATE TABLE #tmpClmIds   ( EncKeyC INT NOT NULL PRIMARY KEY,
>>                                    MbrId_Orig VARCHAR(16) NOT NULL,
>>                                    MbrId_Unique VARCHAR(16)   NULL)
>> COMMIT
>> BEGIN TRAN
>>      INSERT INTO #tmpClmIds
>>                  ( EncKeyC, MbrId_Orig )
>>      SELECT EncKeyC, E_Mbr FROM TableA
>> COMMIT
>>
>>
>> TableA has 8 million records, many fields, the primary key is EncKeyC,
>> and it's database and log files are located on the SAN.
>>
>> It takes the server 33 minutes to load #tmpClmIds.
>>
>> Doesn't that seem waaay too slow? If so, where should I investigate?
>>
>> I execute this over a VPN on a client's server and I cannot connect perf
>> mon. Their IT thinks that there is no problem with the server.
>>
>> Thanks!
>>
>> Greg C
>>
>
>

16 Sep 2005 2:51 AM

Stu

Since you're selecting all records, then you're doing essentially a
tabe scan. There is no way to boost the performance of the SELECT
statement that you are using; however, you may see a slight gain in
performance on the INSERT if you use:

a: a real table (as opposed to a temp table) in a database that has a
lot of white space so it doesn't have to grow

OR

b: a temp table on a seperate physical disk from your original data
that also does not have to grow to accomodate the data

OR (best option)
c: use DTS to move data from one table to the next.

The biggest slowdown you're probably facing is the growth of the target
file; I'd check there first.

HTH,
Stu

16 Sep 2005 3:11 AM

Greg C

Thanks Stu, all good suggestions.

Why would DTS help?

--
Greg

Show quote

"Stu" <stuart.ainswo***@gmail.com> wrote in message
news:1126839072.952422.68100@z14g2000cwz.googlegroups.com...
> Since you're selecting all records, then you're doing essentially a
> tabe scan. There is no way to boost the performance of the SELECT
> statement that you are using; however, you may see a slight gain in
> performance on the INSERT if you use:
>
> a: a real table (as opposed to a temp table) in a database that has a
> lot of white space so it doesn't have to grow
>
> OR
>
> b: a temp table on a seperate physical disk from your original data
> that also does not have to grow to accomodate the data
>
> OR (best option)
> c: use DTS to move data from one table to the next.
>
>
> The biggest slowdown you're probably facing is the growth of the target
> file; I'd check there first.
>
> HTH,
> Stu
>

16 Sep 2005 3:01 PM

Alexander Kuznetsov

Have you tried a covering index: (EncKeyC, E_Mbr)?