June 9, 2008 at 8:00 am
I have 2 identical Hardware servers. They were bot purchased and set up the same exact way. They are HP DL 580 G5, 64-bit EM64t/x64 with 16 CPU's running at 2.9GHz with 64 GB RAM. Both have SQL Server 2005 installed. SQL Server has a max memory setting of 52GB. Both servers have a database called OLAP which were created identical and have been loaded with the same backup copy of a SQL Server 2000 Database.
I am executing a not so well written query on each server. I am not looking at tuning this query, it is software generated. But on one server the query returns in 1 second. On the second server it return in 10 minutes and 30 seconds. The execution plan is different. I am at a total loss here.
This is the Query
SELECT F.DATASOURCE_NUM_ID, F.INTEGRATION_ID, F.ROW_WIDFROM WC_EPCR_BOOKING_DETAIL_F F (NOLOCK) JOIN WC_EPCR_PARAM_G P (NOLOCK) ON 1=1 WHERE F.ORDER_DATE_WID >= P.THIRTY_DAYS_AGO_START_WID AND NOT EXISTS ( SELECT FS.CTRLNUM FROM WC_EPCR_BOOKING_DETAIL_SEATS_FS FS (NOLOCK) WHERE F.INTEGRATION_ID = FS.SOURCE+UPPER(FS.CTRLNUM)+CONVERT(VARCHAR(5), FS.LINE_NO))
The server that executes this in one second has an execution plan of:
Select ----- Nested Loops ------ Nested Loops ------------ Nested Loops ------------ Table Scan 23%
| | |
| | Nested Loops ---------- Compute Scalar ------- Constant Scan
| RID Lookup 23% |
| Index Seek 23%
Top ------- Table Scan 32%
The server that executes this in 10 min 30 seconda has an execution plan of:
Select ------------ Nested Loops 11% ---------------------- Nested Loops 11% ------------------ Table Scan
Left Anti Semi Join Inner Join
| |
| Nested Loops 0% ------------- Compute Scaler 0% --------------- Constant Scan 0%
| |
| Index Seek 19%
|
Top 21% ----- Table Scan 39%
The three tables have:
WC_EPCR_BOOKING_DETAIL_F - 2,108,463 rows
WC_BOOKING_DETAIL_SEATS_FS - 37,008 rows
WC_EPCR_PARAM_G - 1 row (Yes, that is ONE row)
I realize there is probably not enough infor here to give me a 100% reason why. But if someone can tell me what to look for, what to lookat, etc. to start figuring out there is such a major difference, I would appreciate it.
All the SQL Server configuration parameters are EXACTLY the same.
I did notice that the one thing different is the Statistics are very differetn on each of the 3 tables. I have run Update statistic severla times with FULL SCAN on both servers.
Disk drives are all in a SAN and have been configured exactly the same.
Separate LUNS for TEMP Database, Data Files, Log Files, Operating system, SQL Server Installation and binaries, pagefile, backups
C: Operating system RAID 1
D: SQL Server Install - RAID 1
M: TEMP Database - RAID 10
L: Logs - RAID 1
S: Data - RAID 10
Z: Backups - RAID 5
P: and F: Pagefile - RAID 0
Database is roughly 300GB
June 9, 2008 at 8:48 am
This appears to be a duplicate question. I replied to the other copy (same forum).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply