22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId) AS procName,
DB_NAME(qt.dbId) AS [db_name],
qt.text AS SQL_Full,
SUBSTRING(
qt.text,
(qs.statement_start_offset / 2) + 1,
(
(
CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
) / 2
) + 1
) AS SQL_Part --统计对应的部分语句
,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.last_elapsed_time / 1000000 AS lastElapsedSeconds,
qs.last_worker_time / 1000000 AS lastCpuSeconds,
CAST(
qs.total_elapsed_time / 1000000.0 / (
CASE
WHEN qs.execution_count = 0 THEN -1
ELSE qs.execution_count
END
) AS DECIMAL(28, 2)
) AS avgDurationSeconds,
CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS
lastLogicReadsMB,
qs.last_logical_reads,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00' --今天8点之后的慢SQL
AND qs.last_elapsed_time >= 3 * 1000 * 1000 --只取执行时间大于 3 秒的记录
AND qt.[text] NOT LIKE '%Proc_DBA%'
ORDER BY
qs.last_worker_time DESC
先查一下慢SQL, 时间你自己改一下。
另外, CPU, 内存, 磁盘IO的情况也贴出来看看。
慢的时候, 再看下有没有堵塞?
SELECT * FROM sys.sysprocesses AS s WHERE s.spid>50 AND s.blocked>0
--查到上面的spid,然后查下 spid 对应的语句
DBCC INPUTBUFFER( ? )
--先创建索引
CREATE INDEX ix_TBL_PA_VAT_DOCUMENT_DOCUMENT_ID_DOCUMENT_NR ON TBL_PA_VAT_DOCUMENT(DOCUMENT_ID,DOCUMENT_NR)
CREATE INDEX ix_TBL_PA_VAT_DOCUMENT_ITEM_DOCUMENT_ID ON TBL_PA_VAT_DOCUMENT_ITEM(DOCUMENT_ID)
--再执行
SELECT this_.DOCUMENT_ID AS DOCUMENT1_1_1_,
this_.DOCUMENT_NR AS DOCUMENT2_1_1_,
this_.INVOICE_TYPE AS INVOICE3_1_1_,
this_.DOCUMENT_DATE AS DOCUMENT4_1_1_,
this_.DOCUMENT_CREATE_DATE AS DOCUMENT5_1_1_,
this_.CUSTOMER_NR AS CUSTOMER6_1_1_,
this_.CUSTOMER_NAME AS CUSTOMER7_1_1_,
this_.CUSTOMER_TAX_NR AS CUSTOMER8_1_1_,
this_.CUSTOMER_ADDRESS AS CUSTOMER9_1_1_,
this_.CUSTOMER_BANKACCOUNT AS CUSTOMER10_1_1_,
this_.MEMO AS MEMO1_1_,
this_.ISSUED AS ISSUED1_1_,
this_.SPLIT_MERGE_STATE AS SPLIT13_1_1_,
this_.SPLIT_MERGE_REF_NR AS SPLIT14_1_1_,
this_.ISSUER AS ISSUER1_1_,
this_.CHECKER AS CHECKER1_1_,
this_.PAYEE AS PAYEE1_1_,
this_.SELLER_TAX_NR AS SELLER18_1_1_,
this_.SELLER_ADDRESS_TEL AS SELLER19_1_1_,
this_.SELLER_BANK_ACCOUNT AS SELLER20_1_1_,
this_.LIST_NAME AS LIST21_1_1_,
this_.MACHINE_NR AS MACHINE22_1_1_,
this_.DISCOUNT_VALUE AS DISCOUNT23_1_1_,
this_.DISCOUNT_TAX AS DISCOUNT24_1_1_,
this_.DISCOUNT_RATE AS DISCOUNT25_1_1_,
this_.INCLUDETAX AS INCLUDETAX1_1_,
this_.UPLOAD AS UPLOAD1_1_,
this_.AUTO_MERGE AS AUTO28_1_1_,
this_.ALLOW_ISSUE AS ALLOW29_1_1_,
this_.IS_MANUAL AS IS30_1_1_,
this_.REVERSED_NOTICE_NR AS REVERSED31_1_1_,
this_.REFINVOICECODE AS REFINVO32_1_1_,
this_.REFINVOICENR AS REFINVO33_1_1_,
this_.STR1 AS STR34_1_1_,
this_.STR2 AS STR35_1_1_,
this_.STR3 AS STR36_1_1_,
this_.STR4 AS STR37_1_1_,
this_.STR5 AS STR38_1_1_,
this_.BOOL1 AS BOOL39_1_1_,
this_.BOOL2 AS BOOL40_1_1_,
this_.BOOL3 AS BOOL41_1_1_,
this_.BOOL4 AS BOOL42_1_1_,
this_.BOOL5 AS BOOL43_1_1_,
this_.CONSIGNER_NAME AS CONSIGNER44_1_1_,
this_.CONSIGNER_TAX_NR AS CONSIGNER45_1_1_,
this_.SHIPPER_NAME AS SHIPPER46_1_1_,
this_.SHIPPER_TAX_NR AS SHIPPER47_1_1_,
this_.ORIGIN_VIA_ARRIVAL_PLACE AS ORIGIN48_1_1_,
this_.VEHICLE_KIND_NO AS VEHICLE49_1_1_,
this_.VEHICLE_TONNAGE AS VEHICLE50_1_1_,
this_.FREIGHT_MEMO AS FREIGHT51_1_1_,
items2_.DOCUMENT_ID AS DOCUMENT37_3_,
items2_.DOCUMENT_ITEM_ID AS DOCUMENT1_3_,
items2_.ITEM_INDEX AS ITEM38_3_,
items2_.DOCUMENT_ITEM_ID AS DOCUMENT1_2_0_,
items2_.SEQUENCE_NR AS SEQUENCE2_2_0_,
items2_.PRODUCT_NR AS PRODUCT3_2_0_,
items2_.PRODUCT_NAME AS PRODUCT4_2_0_,
items2_.SPEC AS SPEC2_0_,
items2_.UNIT AS UNIT2_0_,
items2_.QUANTITY AS QUANTITY2_0_,
items2_.PRICE AS PRICE2_0_,
items2_.VALUE AS VALUE2_0_,
items2_.TAX_RATE AS TAX10_2_0_,
items2_.INCLUDE_TAX AS INCLUDE11_2_0_,
items2_.TAX AS TAX2_0_,
items2_.TAX_ITEM AS TAX13_2_0_,
items2_.DISCOUNT_RATE AS DISCOUNT14_2_0_,
items2_.DISCOUNT_TAX AS DISCOUNT15_2_0_,
items2_.DISCOUNT_VALUE AS DISCOUNT16_2_0_,
items2_.STR1 AS STR17_2_0_,
items2_.STR2 AS STR18_2_0_,
items2_.STR3 AS STR19_2_0_,
items2_.STR4 AS STR20_2_0_,
items2_.STR5 AS STR21_2_0_,
items2_.DBL1 AS DBL22_2_0_,
items2_.DBL2 AS DBL23_2_0_,
items2_.DBL3 AS DBL24_2_0_,
items2_.DBL4 AS DBL25_2_0_,
items2_.DBL5 AS DBL26_2_0_,
items2_.bool1 AS bool27_2_0_,
items2_.bool2 AS bool28_2_0_,
items2_.bool3 AS bool29_2_0_,
items2_.bool4 AS bool30_2_0_,
items2_.bool5 AS bool31_2_0_,
items2_.GOODS_NO_VER AS GOODS32_2_0_,
items2_.GOODS_TAX_NO AS GOODS33_2_0_,
items2_.TAX_PRE AS TAX34_2_0_,
items2_.TAX_PRE_CON AS TAX35_2_0_,
items2_.ZERO_TAX AS ZERO36_2_0_
FROM TBL_PA_VAT_DOCUMENT this_
LEFT OUTER JOIN TBL_PA_VAT_DOCUMENT_ITEM items2_
ON this_.DOCUMENT_ID = items2_.DOCUMENT_ID
WHERE this_.DOCUMENT_NR = @P0;
另外, 这个SQL 执行出来的结果一般有多少行?