2 servers, same subtree cost, drastic clock time difference

2 servers, same subtree cost, drastic clock time difference

Post by Jeff Bak » Fri, 09 Nov 2001 12:44:32



Any help understanding this appreciated:

Two machines, both Win2K with SQL Server 2000.  Both have copy of the
same database, executing the same query in SQLQA.

Machine 1
Root Subtree cost: 2.50
Clock time to display results in results windows: 8 seconds

Machine 2
Root Subtree cost: 1.70
Clock time to display same results: 130 seconds

Another twist is that machine 2 is a dual-proc server with 512M memory
and Machine 1 is a laptop...

Any thoughts?

Thanks,
 - Jeff

 
 
 

2 servers, same subtree cost, drastic clock time difference

Post by BP Margoli » Fri, 09 Nov 2001 13:19:36


Jeff,

SQL Server can create different query plans depending upon the number of
CPU's available and the amount of RAM available.

As a first step, run the system stored procedure sp_updatestats to make sure
that SQL Server has the most current set of statistics available for the
query optimizer to use.

Also keep in mind that the laptop is probably being used by a single
individual, while the server has to handle multiple users. Depending upon
the queries being issued, blocking might be occurring. You can use the
system stored procedure sp_lock, or Enterprise Manager, to determine if
blocking is occurring.

Additional information on sp_updatestats and sp_lock can be found in the SQL
Server 2000 Books Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Any help understanding this appreciated:

> Two machines, both Win2K with SQL Server 2000.  Both have copy of the
> same database, executing the same query in SQLQA.

> Machine 1
> Root Subtree cost: 2.50
> Clock time to display results in results windows: 8 seconds

> Machine 2
> Root Subtree cost: 1.70
> Clock time to display same results: 130 seconds

> Another twist is that machine 2 is a dual-proc server with 512M memory
> and Machine 1 is a laptop...

> Any thoughts?

> Thanks,
>  - Jeff


 
 
 

2 servers, same subtree cost, drastic clock time difference

Post by Jeff Bak » Fri, 09 Nov 2001 21:32:03


Thanks for the information.  We'll look into the things you suggested.
 Note that given the 'off-hours' testing, both boxes were only being
used by one user.

How can I look at other performance factors (e.g. disk reads, disk
writes, CPU utilization) as they related to the given query?

Thanks,
 - Jeff

 
 
 

2 servers, same subtree cost, drastic clock time difference

Post by BP Margoli » Sat, 10 Nov 2001 13:45:01


Jeff,

Check out the documentation on SQL Server Profiler in the SQL Server Books
Online. You might also check the SQL Server magazine (www.sqlmag.com)
archives for articles on SQL Server Profiler.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Thanks for the information.  We'll look into the things you suggested.
>  Note that given the 'off-hours' testing, both boxes were only being
> used by one user.

> How can I look at other performance factors (e.g. disk reads, disk
> writes, CPU utilization) as they related to the given query?

> Thanks,
>  - Jeff

 
 
 

1. Subtree Cost and Duration Time -QA

Hello all,

I have a table called APL_transaction

Create table APL_Transaction (snam varchar(7),Amount
numeric (18,2) , AsOn Datetime)
Other columns are not relevant for discussion

SNAM is having clustered index and Ason is nonclustered
index

Total No of rows = 234569

I am bit confused with the following batch query

-- Batch 1  Start


select snam,sum(amount)  snamamt from apl_transaction

group by snam  
go

-- Batch 1 End

--  Batch2 Start
select snam,sum(amount)  snamamt from apl_transaction
where ason=(select max(ason) from apl_transaction)
group by snam  
go

-- Batch 2 End

Questions

1. Is it better to calculate maximum of ason column  first
and introduce it into the where clause
   or  as in Batch 2  feed it to the where clause

2. The results in QA

Batch 1
------------    

Cost 0.0641

     select snam,sum(amount)  snamamt from apl_transaction

         group by snam       -- Subtree Cost  4.82

         Total Duration of execution  for both statements -
 1 sec

Batch 2
-----------

select snam,sum(amount)  snamamt from apl_transaction
where ason=(select max(ason) from apl_transaction)
group by snam    --  Subtree Cost 0.935

 Duration of execution as provided by QA - 13 secs

Why Batch 2 is taking more time to execute and display
eventhough subtree cost is
less  . How the Subtree Cost and Duration of execution  
related ?

Thanks

M A Srinivas

2. retrieving the number or rows effected

3. Subtree cost in Execution plan

4. WA-SEATTLE-88793--ORACLE Forms-Developer 2000-SQL*Plus-Triggers-MS ACCESS-Visual

5. subtree cost in Query Analyser?

6. US-NC-FINANCIALS HR-PAYROLL POSITION IN CHARLOTTE

7. Estimated subtree cost

8. Query analyzer newbe question about Subtree cost

9. Estimated I/O, CPU, and SubTree Costs

10. Server Clock to set Update Time

11. Remote Time for Time-clock database

12. Query Analyzer - I/O Cost, CPU Cost, and Cost