May 30, 2006 at 11:25 am
Does any one have an idea why the same query runs on identical databases on different servers have different subtree costs?
Server A
SQL Server 2000 standard SP3a windows 2000 no raid 1G memory lower subtree cost returns almost all long queries faster than
server B
Sql 2000 Enterprise sp3a windows 2003 enterprise Raid5 4g memory higher subtree cost double return times .
Not sure where else to look
Thanks
Mike
May 30, 2006 at 12:27 pm
Is the data the exact same on both servers. Have statistics been updated on both servers. No raid will run faster than raid5. What about processor speed.
Tom
May 30, 2006 at 12:47 pm
Tom
Thanks for your time .
The data is exactly the same on both servers .
Auto create and auto update stats is enabled.
Statistics for all tables involved in the query were rerun just in case ....
I will be glad to supply any info you may need to help with this
The slower server is a poweredge6550 xeon processors and the other one is a dell2400(workstation)
Mike
May 30, 2006 at 12:54 pm
What about the processor?
Run this on both machines and post the output from the query analyzer messages tab.
set statistics io on
go
set statistics time on
go
--your code here
Try that, I'll take a look at the output.
Tom
May 30, 2006 at 1:11 pm
FASTER less beefed up workstation
*************************
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(2 row(s) affected)
Table 'spd'. Scan count 196, logical reads 53664, physical reads 0, read-ahead reads 0.
Table 's_s_sph'. Scan count 480, logical reads 480, physical reads 0, read-ahead reads 0.
Table 'sph'. Scan count 304, logical reads 612, physical reads 0, read-ahead reads 0.
Table 'psb'. Scan count 12, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'spg'. Scan count 12, logical reads 36, physical reads 0, read-ahead reads 0.
Table 'smn'. Scan count 6, logical reads 14, physical reads 0, read-ahead reads 0.
Table 'bra'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0.
Table 'ppd'. Scan count 36, logical reads 144, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 12610 ms, elapsed time = 12655 ms.
slower beefed up server
************************************
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(2 row(s) affected)
Table 'spd'. Scan count 196, logical reads 53664, physical reads 0, read-ahead reads 0.
Table 's_s_sph'. Scan count 480, logical reads 480, physical reads 0, read-ahead reads 0.
Table 'sph'. Scan count 304, logical reads 612, physical reads 0, read-ahead reads 0.
Table 'psb'. Scan count 12, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'spg'. Scan count 12, logical reads 36, physical reads 0, read-ahead reads 0.
Table 'smn'. Scan count 6, logical reads 14, physical reads 0, read-ahead reads 0.
Table 'bra'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0.
Table 'ppd'. Scan count 36, logical reads 144, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 23625 ms, elapsed time = 24046 ms.
Thanks again Mike
May 30, 2006 at 3:00 pm
Mike,
How many processors are on each server??Try this hint on the slower, beefier machine. Run it with the stats set.
At the end of the select add: option maxdop 1
Thanks
Tom
May 31, 2006 at 7:45 am
Tom this is the information you requested
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 10656 ms, elapsed time = 10676 ms.
(1 row(s) affected)
Table 'spd'. Scan count 200, logical reads 54848, physical reads 0, read-ahead reads 0.
Table 's_s_sph'. Scan count 496, logical reads 496, physical reads 0, read-ahead reads 0.
Table 'sph'. Scan count 316, logical reads 648, physical reads 0, read-ahead reads 0.
Table 'psb'. Scan count 12, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'spg'. Scan count 12, logical reads 36, physical reads 0, read-ahead reads 0.
Table 'smn'. Scan count 6, logical reads 14, physical reads 0, read-ahead reads 0.
Table 'bra'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0.
Table 'ppd'. Scan count 39, logical reads 153, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 24172 ms, elapsed time = 24209 ms.
Thanks a lot for your time
Mike
May 31, 2006 at 8:32 am
Mike,
When was the last time you rebuilt the indexes on the table. Run dbcc showcontig on the tables involved on each system and post that. It will show the index fragmentation. Have you displayed the execution plans. Are they the same? cntrl-k in query analyzer will show execution plan when the code is executed.
Try that.
Tom
May 31, 2006 at 8:48 am
Not sure my indexes need to be rebuilt .This is showcontig .The explain plans are the same as explained at the start but the subtree costs are higher in the "beefed up" system with the xeon processors
DBCC SHOWCONTIG scanning 'smn' table...
Table: 'smn' (409768517); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 1672.0
- Avg. Page Density (full).....................: 79.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'ppd' table...
Table: 'ppd' (1236199454); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 2566
- Extents Scanned..............................: 323
- Extent Switches..............................: 322
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.38% [321:323]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 8.36%
- Avg. Bytes Free per Page.....................: 118.6
- Avg. Page Density (full).....................: 98.54%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'spd' table...
Table: 'spd' (384720423); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 40188
- Extents Scanned..............................: 5091
- Extent Switches..............................: 5090
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.68% [5024:5091]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 92.0
- Avg. Page Density (full).....................: 98.86%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Thank you
May 31, 2006 at 9:05 am
You say that your subtree cost is higher on your new server so you must have already looked at the exection plans. Can you post the text version of your execution plans for both servers? When you cut over to the server with the disk array, did you keep your database filegroup structure the same?
May 31, 2006 at 12:43 pm
Mike,
Try running. sp_updatestats. I also will run sp_createstats on some tables to give the optimizer more options.
Tom
June 1, 2006 at 1:39 am
Is the 'beefed up' server multi-cpu and the workstation single? Could be a problem if you are getting a parallel execution plan on the 'beefed up' server.
June 1, 2006 at 5:12 am
Additional information:
Discovered that all the long queries are related to views but all the table queries run as expected.Not sure wether that gives any more clues or not.
The explain plan are 35pages long each do you still want them.
Thanks a million for your time
Mike
June 1, 2006 at 6:48 am
Thanks everyone for your time especially Tom and Steward
Mike
June 2, 2006 at 2:05 pm
Forgot to mention our server was patched to sp4 and the problems disappeared
Mike
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply