May 30, 2002 at 3:21 am
Hi all
Had a very strange one today. The nasty query below generates a very different plan on my TEST box to PROD and DEV. A small move in code, namely swapping the following:
LEFT OUTER JOIN qselTRSORGTradeName
ON tc.tc_nac_org_id = qselTRSORGTradeName.org_id
LEFT OUTER JOIN qselTRSRTOTradeName
INNER JOIN enrolment
ON qselTRSRTOTradeName.rto_org_id = enrolment.enrol_rto_org_id
ON tc.tc_id = enrolment.enrol_tc_id
generates a good plan on PROD and DEV (test is slightly faster), going from 1min 3sec down to 2secs. I have compared the code between all servers, and config settings, only differce is the SQL Server memory!
This is very strange indeed and no matter what I do on DEV and PROD, I can not get it to generate a plan anywhere near simple to the plan generated on TEST. The raw query below for example will do a index scan over one particular table and the exution plan tells me it return 28million rows on DEV and PROD, whereas on test its only 47k.
Anyone seen this sort of strange going's on before with the optimizer???
DBCC FREEPROCCACHE
SELECT DISTINCT TOP 3000 tc.tc_id, tc.tc_commenced, tc.tc_registered, tc.tc_completed, tc.tc_trade_cert_issued_date, tc.tc_project_id, Person.person_id, Person.person_surname, Person.person_given_names, Person.person_dob, qselTRSPersonResidentialAddress.person_residential_address_line1, qselTRSPersonResidentialAddress.person_residential_address_line2, qselTRSPersonResidentialAddress.person_residential_address_postcode, qselTRSPersonResidentialAddress.person_residential_address_suburb, Person.person_phone_home, Person.person_guard_phone_home, Training_Product.registered_id, Training_Product.registered_name, tc.tc_nac_org_id, qselTRSORGTradeName.org_name As nac_org_name, Admin_Division.admindiv_id, Admin_Division.admindiv_desc, Organisation_Emp.org_emp_id, qselTRSEMPTradeName.emp_trade_org_name, qselTRSEMPLegalName.emp_legal_org_name, qselTRSContactPerson.conperson_first_name, qselTRSContactPerson.conperson_surname, qselTRSContactPerson.conperson_phone_std, qselTRSContactPerson.conperson_phone_no,
tc_status.tcstatus_description, tc_sub_status.tcsubstat_description, Training_Product_QUAL.registered_name AS registered_name_qual, qselTRSORGBusinessAddress.org_business_address_line1, qselTRSORGBusinessAddress.org_business_address_line2, qselTRSORGBusinessAddress.org_business_address_postcode, qselTRSORGBusinessAddress.org_business_address_suburb, qselTRSORGPostalAddress.org_postal_address_line1, qselTRSORGPostalAddress.org_postal_address_line2, qselTRSORGPostalAddress.org_postal_address_postcode , qselTRSORGPostalAddress.org_postal_address_suburb , qselTRSRTOTradeName.rto_org_id, qselTRSRTOTradeName.rto_org_name
FROM trade
RIGHT OUTER JOIN Apprenticeship
ON trade.trade_id = Apprenticeship.appr_trade_id
RIGHT OUTER JOIN Traineeship
RIGHT OUTER JOIN Training_Product
ON Traineeship.trainee_traineeship_id = Training_Product.training_product_id
ON Apprenticeship.appr_apprenticeship_id = Training_Product.training_product_id
LEFT OUTER JOIN trade_trade_party
ON Training_Product.training_product_id = trade_trade_party.ttparty_training_product_id
RIGHT OUTER JOIN Person
INNER JOIN Organisation Organisation_Emp
INNER JOIN tc
INNER JOIN qselTRSEMPTradeName
ON tc.tc_empsite_parentorg_id = qselTRSEMPTradeName.emp_org_id
INNER JOIN qselTRSEMPLegalName
ON tc.tc_empsite_parentorg_id = qselTRSEMPLegalName.emp_org_id
ON Organisation_Emp.org_id = tc.tc_empsite_parentorg_id
ON Person.person_id = tc.tc_person_id
ON Training_Product.training_product_id = tc.tc_trade_training_product_id
LEFT OUTER JOIN Training_Product Training_Product_QUAL
ON tc.tc_qual_training_product_id = Training_Product_QUAL.training_product_id
LEFT OUTER JOIN tc_sub_status
ON tc.tc_tcsubstat_id = tc_sub_status.tcsubstat_id AND tc.tc_tcsubstat_id = tc_sub_status.tcsubstat_id AND tc.tc_tcsubstat_id = tc_sub_status.tcsubstat_id AND tc.tc_tcsubstat_id = tc_sub_status.tcsubstat_id
LEFT OUTER JOIN Admin_Division
ON tc.tc_admindiv_id = Admin_Division.admindiv_id
LEFT OUTER JOIN tc_status
ON tc.tc_tcstatus_id = tc_status.tcstatus_id AND tc.tc_tcstatus_id = tc_status.tcstatus_id AND tc.tc_tcstatus_id = tc_status.tcstatus_id AND tc.tc_tcstatus_id = tc_status.tcstatus_id
LEFT OUTER JOIN qselTRSORGPostalAddress
ON tc.tc_empsite_org_id = qselTRSORGPostalAddress.address_org_id
LEFT OUTER JOIN qselTRSORGBusinessAddress
ON tc.tc_empsite_org_id = qselTRSORGBusinessAddress.address_org_id
LEFT OUTER JOIN qselTRSPersonResidentialAddress
ON tc.tc_person_id = qselTRSPersonResidentialAddress.address_person_id
LEFT OUTER JOIN qselTRSContactPerson
ON tc.tc_empsite_org_id = qselTRSContactPerson.conperson_org_id
LEFT OUTER JOIN qselTRSRTOTradeName
INNER JOIN enrolment
ON qselTRSRTOTradeName.rto_org_id = enrolment.enrol_rto_org_id
ON tc.tc_id = enrolment.enrol_tc_id
LEFT OUTER JOIN qselTRSORGTradeName
ON tc.tc_nac_org_id = qselTRSORGTradeName.org_id
WHERE
(trade_trade_party.ttparty_tradeparty_org_id = 1030914) AND (enrolment.enrol_ceased IS NULL) AND (tc.tc_commenced BETWEEN '01/01/1980' AND '18/01/2038') AND (tc.tc_completed BETWEEN '01/01/1980' AND '18/01/2038') AND
((tc.tc_project_id = 'TDRSPEC') ) ORDER BY tc_id
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 30, 2002 at 3:34 am
Internal and external fragmentation, free disk space, statistics option on or off, different index on tables, Memory, CPU all this affects how sql generate plan to run a query.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 30, 2002 at 4:40 am
There are numerous factors that can cause this. Another possiblity is the amount of work being done in the DEV and PROD environments as opposed to the TEST environment which is majorly overlooked when you see these issues. You should generate a test load from PROD or DEV using profiler and run against TEST if you want to get a better idea of the process as on those systems. Memory and CPU are also major factors, but so can the NIC, network segement traffic, and so on. You might also try doing SET ROWCOUNT 3000 then use SELECT DISTINCT to see if that runs any faster. Also the major difference in rows will be your biggest factor. And just a bit of advice, it is never advised even for testing to run DBCC FREEPROCCACHE on your production server, especially if you have other databases on it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 7:08 am
Hi all
Thanks for the pointers, ive considered all of these, the dbcc command was my last vain attempt and forcing a recompilation but will remember the comment re freeproccache on prod. The rows counts are only 200 rows less in test, what confused me is the 28million row index full scan over a table tha, from what I can gather, is impossible to deduce from the SQL statement... very strange indeed.
I am restoring prod on the test box tomorrow and (with no change) will test to determine if its a global setting ive missed somehow.
Sorry, but my years under Oracle ive never seen this sort of dramatic blowout (nothing a stats recollection didnt fix anyhow).
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 30, 2002 at 7:44 am
If you want to run
SET SHOWPLAN_TEXT ON
GO
YOURQUERY
GO
And post the results from each box. May be able see something specific in it that I can point out to you. Also may try running sp_updatestats to resample the statistics for the Prod and dev boxes. Have noted in the past a specific percentage of records have to change for auto update stats to kick in and with a large set it may take a while.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply