SQL view timing out

Question

I have a sql view which is running very slow. Can you please through some pointers on how i can check what part of the query is taking most time.

Query below

CREATE view ConsolidationReportView       
as      

SELECT  distinct TOP (100) PERCENT       
Client.OH_Code AS ClientCode,      
client.OH_FullName AS Client,      
consignee.OH_Code AS ConsigneeCode,      
CASE WHEN consignee.oh_pk IS NULL THEN consigneedocaddress.e2_companyname ELSE consignee.oh_fullname END AS Consignee,      
RefCountry_1.RN_Desc AS CGNEE_COUNTRY,      
dbo.Client_ReturnWK(pallet.MB_PR_CLOSED_DT) AS WEEKNUM,      
part.OP_Desc AS ProductDescription,      
part.OP_CustomAttrib1 AS ISSUE_DESC,      
part.OP_Weight AS x,      
ROUND(part.OP_Weight * packing.MB_PD_Units + .5, 0) AS Weight,       
packing.MB_PD_Units AS Units,      
CASE WHEN RS_Description = 'AIR' OR RS_Description = 'SEA' THEN 0 ELSE .03 END AS Rate,       
CASE WHEN RS_Description = 'AIR' OR RS_Description = 'SEA' THEN 0 ELSE .03 END *       
ROUND(part.OP_Weight * packing.MB_PD_Units + .5, 0) AS Charge,      
docket.WD_DocketID,      
CASE WHEN RS_Description = 'Warehouse Handling' THEN 'HAN' ELSE RS_description END AS Service,       
'NO AUTO/R.' AS CHARGE_SELL_AMMOUNT,         
docket.WD_DocketStatus AS DOCKET_STATUS,       
OrgMiscServ.OM_CustomFlag2 AS SHIP_ACCOUNT,         
'' AS INVOICE_NUM,       
part.OP_Desc AS TITLE,      
CASE WHEN OU_LocalPartNumber IS NULL THEN CASE WHEN LEN(SUBSTRING(part.OP_PartNum, 1, CASE WHEN CHARINDEX('-',         
 part.OP_PartNum) > 0 THEN CHARINDEX('-', part.OP_PartNum) - 1 ELSE 0 END)) = 0 THEN OU_LocalPartNumber ELSE SUBSTRING(part.OP_PartNum, 1,       
CASE WHEN CHARINDEX('-', part.OP_PartNum) > 0 THEN CHARINDEX('-', part.OP_PartNum) - 1 ELSE 0 END) END ELSE OU_LocalPartNumber END AS MAG_CODE,       
client.OH_PK AS ClientPK,      
warehouse.WW_PK AS WarehousePK,      
docketline.WE_OP AS ProductPK,      
pallet.MB_PR_CLOSED_DT,      
part.OP_Weight AS COPY_KG,      
CASE WHEN RS_Description = 'AIR' OR RS_Description = 'SEA' THEN 0 ELSE dbo.Client_ReturnTariffCharge(client.OH_PK,         
      pallet.MB_PR_CLOSED_DT, 'WHSHAN', part.OP_Weight, packing.MB_PD_Units) END AS TARIFF_CHRG,       
CASE WHEN RS_Description = 'AIR' OR RS_Description = 'SEA' THEN 0 ELSE dbo.Client_ReturnTariffRate(client.OH_PK, pallet.MB_PR_CLOSED_DT, 'WHSHAN') END AS TARIFF_RATE,      
DATEPART(YEAR, CONVERT(varchar(8), pallet.MB_PR_CLOSED_DT, 112)) AS YEAR,             
CASE WHEN RS_Description = 'AIR' OR RS_Description = 'SEA' THEN 0 ELSE dbo.Client_ReturnTariffCharge(client.OH_PK,         
                      pallet.MB_PR_CLOSED_DT, 'WHSHAN', part.OP_Weight + .009, packing.MB_PD_Units) END AS TARIFF_CHRG_UPLIFT,       
(part.OP_Weight + .009) * packing.MB_PD_Units AS Weight_UPLIFT,         
part.OP_Weight + .009 AS COPY_KG_UPLIFT      


FROM       
 Client_whspalletpacking packing  WITH (NOLOCK)     
inner join whsDocketLine docketLine  WITH (NOLOCK) on  packing.we_fk  =docketline.we_pk      
inner join whsDocket  docket WITH (NOLOCK) on docketline.we_wd =docket.wd_pk      
inner join client_whspallet pallet WITH (NOLOCK) on packing.MB_PD_PR = pallet.MB_PR_PK      
inner join orgheader Client WITH (NOLOCK) on docket.wd_oh_client= Client.oh_pk      
inner join orgheader consignee  WITH (NOLOCK) on packing.MB_PR_OH=consignee.oh_pk      
INNER JOIN OrgSupplierPart part WITH (NOLOCK) ON docketline.WE_OP = part.OP_PK      
left outer join OrgPartRelation WITH (NOLOCK) on part.op_pk=OrgPartRelation.OU_OP      
inner join OrgMiscServ WITH (NOLOCK) on consignee.OH_PK =OrgMiscServ.OM_OH      
inner join JobDocAddress AS consigneedocaddress WITH (NOLOCK) on  docket.WD_PK=consigneedocaddress.E2_ParentID       
INNER JOIN RefUNLOCO AS RefUNLOCO_1 WITH (NOLOCK) ON consignee.OH_RL_NKClosestPort = RefUNLOCO_1.RL_Code       
INNER JOIN RefCountry AS RefCountry_1 WITH (NOLOCK) ON RefUNLOCO_1.RL_RN = RefCountry_1.RN_PK       
INNER JOIN WhsWarehouse AS warehouse WITH (NOLOCK) ON warehouse.WW_PK = docket.WD_WW_Whs       
inner join RefServiceLevel WITH (NOLOCK) on docket.WD_RS_ServiceLevel = RefServiceLevel.RS_PK       

ORDER BY       
part.OP_Desc ,       
part.OP_CustomAttrib1,       
CASE WHEN consignee.oh_pk IS NULL THEN consigneedocaddress.e2_companyname ELSE consignee.oh_fullname END,      
RefCountry_1.RN_Desc 

Thanks

Answer 1

1. Try looking at its Query Execution Plan.

2. Using DMVs

3. Find missing indexes.

4. Run the sql profiler see if the numbers of locks being acquired are too much.

5. Check your server health while running your query.

6. you can clean your query results buffers by

   DBCC DROPCLEANBUFFERS gO

7. Run your view like this and got to message tab

set statistics time on set statistics io on Go

select * from youview

set statistics io off Go set statistics time off Go

DBCC DROPCLEANBUFFERS gO

See if your query comes under the result set of either one or both.

-- Get Top 200 executed SP's ordered by logical reads (memory pressure)

SELECT TOP 200 qt.text AS 'SP Name', total_logical_reads, 
qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY total_logical_reads DESC

-- Get Top 20 executed SP's ordered by total worker time (CPU pressure)

SELECT TOP 200 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'Execution Count', 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
qs.max_logical_reads, qs.max_logical_writes, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.total_worker_time DESC

Answer 2

And are any of those joins referencing other views by chance? Never reference a view in a view if you want performance.

There is also a good chance this is the culprit dbo.Client_ReturnWK(pallet.MB_PR_CLOSED_DT)

functions used that way often force row-by-row and not set-based processing and slow down queries tremendously.

Answer 3

As others have said, you need to look at the execution plan. If this is MS SQL and you are unfamiliar with this, you can find more details here. There is also a link so you can download the full e-book for free.

Answer 4

Depends on the query.

Try the explain plan for a start.