My customer is experiencing performance problems. I noticed via SQL Profiler that a lot ot the SQL is using temp tables. I had them modify one stored procedure to not use temp tables as a test. But now, they are still using select distinct. I am not sure but it appears that the majority of the cost in the procedure is spent on the sort - see showplan for . I definitely need to tune tempdb some - any suggestions there? Will adding another temp data and/or log file help?
Any other suggestions.
c.cust_num as RETAILER_NUM,
c.cust_1_name as RETAILER_NAME,
c.cust_street_addr_text as RETAILER_ADDRESS,
c.cust_city_name as RETAILER_CITY,
c.cust_state_us_postal_code as RETAILER_STATE,
c.cust_zip_code as RETAILER_ZIPCODE,
isnull(c.cust_phone_num,' ') as CUST_PHONE_NUM,
' ' as CONTACT_NAME,
ct.SHIP_TO_TERR_NUM as SHIP_TO_TERR_NUM,
E.EMPL_NAME as SALES_REP_NAME
ct.cust_num = c.cust_num
ct.SHIP_TO_TERR_NUM = T.TERRITORY_NUM
AND T.YEAR_NUM = YEAR(GETDATE())
AND T.TERRITORY_NAME is not null
T.EMPLID = E.EMPLID
ct.SALES_ORG_NAME IN ('CROP','POST')
AND ct.SHIP_TO_TERR_NUM LIKE '<@some number' + '%'
AND ISNULL(c.CUST_DELETE_FLAG, 'N') != 'Y'