Добрый день,
У меня в продакшене есть такое странное поведение. Одна и таже процедура может виполнятся менше секунды а может около минуты в одно и тоже время с одинаковими параметрами. С помощю такого селекта
SELECT d.name,
er.session_id, blocking_session_id,--st.text,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text,
er.percent_complete
FROM
sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
inner join sys.databases d on er.database_id = d.database_id
WHERE er.Session_id <> @@spid
я вичислил что зависает на следуещем запросе
SELECT TOP 1 @inv_id = INV_ID
FROM dbo.T_DW_OPER dwo
INNER JOIN dbo.T_DW_CHECK dwc ON dwo.DWC_ID = dwc.DWC_ID
WHERE dwc.DWS_ID = @dws_id
ORDER by dwo.DWO_ID desc
В тоже врема профайлер показывает что процедура делает очень много чтений
CPU - 45875
Read - 5078540
Write - 0
Duration - 46032
ниже навожу єкзекюшен план єтого селекта
| Rows | Executes | StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions | | 1 | 1 | SELECT TOP 1 @inv_id = INV_ID FROM dbo.T_DW_OPER dwo INNER JOIN dbo.T_DW_CHECK dwc ON dwo.DWC_ID = dwc.DWC_ID WHERE dwc.DWS_ID = @dws_id ORDER by dwo.DWO_ID desc | 1 | 1 | 0 | NULL | NULL | NULL | NULL | 1 | NULL | NULL | NULL | 0.5330461 | NULL | NULL | SELECT | 0 | NULL | | 1 | 1 | |--Top(TOP EXPRESSION:((1))) | 1 | 2 | 1 | Top | Top | TOP EXPRESSION:((1)) | NULL | 1 | 0 | 1E-07 | 11 | 0.5330461 | [Expr1004] | NULL | PLAN_ROW | 0 | 1 | | 1 | 1 | |--Nested Loops(Inner Join; WHERE:([CMT].[dbo].[T_DW_CHECK].[DWC_ID] as [dwc].[DWC_ID]=[CMT].[dbo].[T_DW_OPER].[DWC_ID] as [dwo].[DWC_ID])) | 1 | 3 | 2 | Nested Loops | Inner Join | WHERE:([CMT].[dbo].[T_DW_CHECK].[DWC_ID] as [dwc].[DWC_ID]=[CMT].[dbo].[T_DW_OPER].[DWC_ID] as [dwo].[DWC_ID]) | NULL | 1 | 0 | 411.6985 | 20 | 0.5330459 | [dwo].[DWO_ID]; [Expr1004] | NULL | PLAN_ROW | 0 | 1 | | 0 | 0 | |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int;[CMT].[dbo].[T_DW_OPER].[INV_ID] as [dwo].[INV_ID];0))) | 1 | 4 | 3 | Compute Scalar | Compute Scalar | DEFINE:([Expr1004]=CONVERT_IMPLICIT(int;[CMT].[dbo].[T_DW_OPER].[INV_ID] as [dwo].[INV_ID];0)) | [Expr1004]=CONVERT_IMPLICIT(int;[CMT].[dbo].[T_DW_OPER].[INV_ID] as [dwo].[INV_ID];0) | 3933.556 | 0 | 0.2527148 | 29 | 0.03633642 | [dwo].[DWO_ID]; [dwo].[DWC_ID]; [Expr1004] | NULL | PLAN_ROW | 0 | 1 | | 1 | 1 | | |--Clustered Index Scan(OBJECT:([CMT].[dbo].[T_DW_OPER].[PK_DW_OPER__DWO_ID] AS [dwo]); ORDERED BACKWARD) | 1 | 5 | 4 | Clustered Index Scan | Clustered Index Scan | OBJECT:([CMT].[dbo].[T_DW_OPER].[PK_DW_OPER__DWO_ID] AS [dwo]); ORDERED BACKWARD | [dwo].[DWO_ID]; [dwo].[DWC_ID]; [dwo].[INV_ID] | 3933.556 | 18.21127 | 2.78002 | 34 | 0.03594306 | [dwo].[DWO_ID]; [dwo].[DWC_ID]; [dwo].[INV_ID] | NULL | PLAN_ROW | 0 | 1 | | 28 | 1 | |--Table Spool | 1 | 9 | 3 | Table Spool | Lazy Spool | NULL | NULL | 38.97377 | 0.01 | 0.0001071153 | 16 | 0.4343231 | [dwc].[DWC_ID] | NULL | PLAN_ROW | 0 | 3934 | | 28 | 1 | |--Index Seek(OBJECT:([CMT].[dbo].[T_DW_CHECK].[IDX_DW_CHECK_DWS_ID] AS [dwc]); SEEK:([dwc].[DWS_ID]=CONVERT_IMPLICIT(numeric(10;0);[@dws_id];0)) ORDERED FORWARD) | 1 | 10 | 9 | Index Seek | Index Seek | OBJECT:([CMT].[dbo].[T_DW_CHECK].[IDX_DW_CHECK_DWS_ID] AS [dwc]); SEEK:([dwc].[DWS_ID]=CONVERT_IMPLICIT(numeric(10;0);[@dws_id];0)) ORDERED FORWARD | [dwc].[DWC_ID] | 38.97377 | 0.003125 | 0.0001998711 | 16 | 0.003324871 | [dwc].[DWC_ID] | NULL | PLAN_ROW | 0 | 1 |
|
в этих таблицах около 2-3 милионов записей
dbo.T_DW_OPER
index DWC_ID
index INV_ID
Primary Key DWO_ID
dbo.T_DW_CHECK
Index DWS_ID
Primary Key DWO_ID
MSSQL 2005 Enterprice Edition
Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
может у когото есть идеи?