Board index MS SQL Server

Different SQL Server's Approaching View in different ways

Different SQL Server's Approaching View in different ways

Post by Brian Etheridg » Fri, 10 Dec 1999 04:00:00



I have a SQL database that we are moving to a machine with a faster
processor and SDRam to try to speed things up.  I've run into a problem that
I can't seem to fix.  The new server is actually slower.  Below are the
technical differences:

OLD SERVER:    12GB Hard Drive, Windows NT Server, SQL 7.0, 450mghz Pentium
II, 128MB RAM
NEW SERVER:   18GB Hard Drive, Windows 2000, SQL 7.0, 500mghz Pentium III,
256MB SDRAM

To move the database, instead of importing tables and views, etc., I copied
the mdf and ldf files from the MSSQL7\Data folder on the old server and
pasted them into the same folder on the new server.  This maintained all of
my indexes.  I then made sure that the server properties were set the same
way on both machines and did the same for the database settings.

I have run query optimizer on both the old and new servers for a few of the
queries and what I have found is that on the old server, 92% of the
processing time is associated with a sort.  The old server does an index
SEEK and a clustered index SEEK and then does a NET LOOP/INNER JOIN, which
comprise the remaining 8% of the processing time.

The new server does an index SEEK and a clustered index SCAN and then does a
HASH MATCH/INNER JOIN.  The clustered index scan takes 20%, and the HASH
MATCH takes 74%.  The old server runs the process in about 5 minutes.  The
new server takes about 7 hours.

Any ideas on this one?

--
Brian M. Etheridge
Project Manager, MIS

 
 
 
Top

Different SQL Server's Approaching View in different ways

Post by Brian Etheridg » Fri, 10 Dec 1999 04:00:00


Ok, figured it out.... It's the service pack.  Forgot to load it.  Wow, what
a difference!

Quote:> I have a SQL database that we are moving to a machine with a faster
> processor and SDRam to try to speed things up.  I've run into a problem
that
> I can't seem to fix.  The new server is actually slower.  Below are the
> technical differences:

> OLD SERVER:    12GB Hard Drive, Windows NT Server, SQL 7.0, 450mghz
Pentium
> II, 128MB RAM
> NEW SERVER:   18GB Hard Drive, Windows 2000, SQL 7.0, 500mghz Pentium III,
> 256MB SDRAM

> To move the database, instead of importing tables and views, etc., I
copied
> the mdf and ldf files from the MSSQL7\Data folder on the old server and
> pasted them into the same folder on the new server.  This maintained all
of
> my indexes.  I then made sure that the server properties were set the same
> way on both machines and did the same for the database settings.

> I have run query optimizer on both the old and new servers for a few of
the
> queries and what I have found is that on the old server, 92% of the
> processing time is associated with a sort.  The old server does an index
> SEEK and a clustered index SEEK and then does a NET LOOP/INNER JOIN, which
> comprise the remaining 8% of the processing time.

> The new server does an index SEEK and a clustered index SCAN and then does
a
> HASH MATCH/INNER JOIN.  The clustered index scan takes 20%, and the HASH
> MATCH takes 74%.  The old server runs the process in about 5 minutes.  The
> new server takes about 7 hours.

> Any ideas on this one?

> --
> Brian M. Etheridge
> Project Manager, MIS


 
 
 
Top

1. I'm failing three different ways....

I'm trying to connect to my local server and db and query/insert into an offsite
server that the user apit has only rights to one db 'apit'.

1. All of these simple queries to an offsite server fail when I add a registered
server...
select * from [206.71.25.126].apit.apit.order_apit
select * from [206.71.25.126].apit..order_apit
select * from [206.71.25.126].apit.order_apit
select * from [206.71.25.126]..order_apit
select * from [206.71.25.126].order_apit

2. Adding a linked server...I can query the table fine, this works...

SELECT * FROM OPENQUERY([216.70.254.116], 'SELECT * FROM order_apit')

Doing a straight insert fails to a linked server that is linked by IP to the
apit db, order_apit table. I get the msg, Invalid object name
'206.71.25.126.order_apit'.

I can do an insert using vbs fine, but I don't know how get an insert to work
w/openquery.

insert into [206.71.25.126].order_apit (apit_accountno ,order_takenby
,[order_date] ,[order_agencyname] ,[order_policyno] ,[order_amountpolicy]
,[app_lastname] ,[app_firstname] ,[app_ssn] ,[app_dob] ,[app_gender]
,[app_address] ,[app_suite] ,[app_city] ,[app_state] ,[app_zip] ,[app_phone]
,[app_wphone] ,[app_ext] ,[order_mailoriginalto] ,[order_mailcopyto]
,[order_faxto] ,[order_reference] ,[order_testenable] ) values('MYACCNO0101A'
,'JEFFP' ,getdate() ,'American Physicians Insurance Trust' ,null ,100000
,'Proctor' ,'Jeff' ,'555-12-1212' ,'01/01/1962' ,'M' ,'1234 Any Street' ,null
,'Town' ,'CA' ,'90000' ,'(818)999-1234' ,'(818)688-8888' ,null ,null ,null ,null
,null ,0 )

3. I'm crying, this isn't supposed to be that hard.  My rule is that if it is
then I've passed the solution and I'm going in circles....

TIA

JeffP....

2. US-ALL AREAS-ORACLE FINANCIALS, ORACLE MANUFACTURING, ORACLE HUMAN RESOURCES TECHNICAL & FUNCTIONAL IMPLMEMENTORS WANTED

3. Different views with same name for different users

4. sequence #'s and triggers

5. Different SQL Servers produce different response time for Same Query

6. Age calculation

7. Binding different IP addresses to different SQL Server instances

8. Looking for Pick Programmers

9. Tables in queries from different databases which reside on different SQL 6.5 Servers

10. Oracle Enterprise Manager to manage different instances with different versions on different machines

11. Please give opinions on different ways to setup disks for best performance under SQL

12. ADO on different PC's with different operating system/MS Office

13. 2 SQL SERVER's on two different IP's


All times are UTC
Board index