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
