我有一个包含711条记录的表格。
这是该表格：
USE [wynparts_aspdnsf]
GO
/****** Object: Table [dbo].[WR_SPL_PreCompiledLists] Script Date: 3/10/2014 5:06:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WR_SPL_PreCompiledLists](
[Penta_Nbr] [numeric](15, 0) NOT NULL,
[ListType] [nvarchar](50) NOT NULL,
[ListData] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
一些
ListData 可能会非常庞大。我尝试运行一个像这样的
DELETE 语句：
DELETE FROM WR_SPL_PreCompiledLists WHERE Penta_Nbr = 48855
在处理更大的记录时，这个操作需要4分钟或更长时间。我的查询计划非常简单，只有4个步骤。使用此命令仅删除了3条记录。没有触发器或其他任何东西。执行计划：
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3373.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="0" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0332862" StatementText="DELETE [WR_SPL_PreCompiledLists] WHERE [Penta_Nbr]=@1" StatementType="DELETE" QueryHash="0x418A7DF29AF7A9A0" QueryPlanHash="0x9230291B34359B04" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="192">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="183477" EstimatedPagesCached="14775" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="9" EstimateCPU="3E-06" EstimateIO="0.03" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Delete" NodeId="0" Parallel="false" PhysicalOp="Table Delete" EstimatedTotalSubtreeCost="0.0332862">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[wynparts_aspdnsf]" Schema="[dbo]" Table="[WR_SPL_PreCompiledLists]" IndexKind="Heap" />
<Object Database="[wynparts_aspdnsf]" Schema="[dbo]" Table="[WR_SPL_PreCompiledLists]" Index="[IX_WR_SPL_PreCompiledLists]" IndexKind="NonClustered" />
<Object Database="[wynparts_aspdnsf]" Schema="[dbo]" Table="[WR_SPL_PreCompiledLists]" Index="[IX_WR_SPL_PreCompiledLists_Penta_Nbr]" IndexKind="NonClustered" />
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Column="Bmk1000" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="711">
<OutputList>
<ColumnReference Column="Bmk1000" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
</DefinedValues>
<Object Database="[wynparts_aspdnsf]" Schema="[dbo]" Table="[WR_SPL_PreCompiledLists]" Index="[IX_WR_SPL_PreCompiledLists_Penta_Nbr]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[wynparts_aspdnsf]" Schema="[dbo]" Table="[WR_SPL_PreCompiledLists]" Column="Penta_Nbr" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(numeric(15,0),[@1],0)">
<Identifier>
<ColumnReference Column="ConstExpr1008">
<ScalarOperator>
<Convert DataType="numeric" Precision="15" Scale="0" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@1" ParameterCompiledValue="(48855)" ParameterRuntimeValue="(48855)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
为什么这个操作不止一秒钟？我应该怎样做来提高速度？
更新
我按照下面的答案检查了锁，得到了下面的结果，但我并不是很理解。
TEXT session_id request_id start_time status command sql_handle statement_start_offset statement_end_offset plan_handle database_id user_id connection_id blocking_session_id wait_type wait_time last_wait_type wait_resource open_transaction_count open_resultset_count transaction_id context_info percent_complete estimated_completion_time cpu_time total_elapsed_time scheduler_id task_address reads writes logical_reads text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count prev_error nest_level granted_query_memory executing_managed_code group_id query_hash query_plan_hash
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------- ----------------------- ------------------------------ -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------------------------------------ ------------------- ------------------------------------------------------------ ----------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ------------------------- ----------- ------------------ ------------ ------------------ -------------------- -------------------- -------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ----------------- ---------- ----------------- ------------- ------------- ------------ ---------- ----------------------- --------------------------- ------------ ----------------- -------------------- ----------- ----------- -------------------- ---------------------- ----------- ------------------ ------------------
SELECT sqltext.TEXT,
req.*
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 59 0 2014-03-11 08:02:36.890 running SELECT 0x02000000BD1AA61696485297D5100F2547B979F25F10C2830000000000000000000000000000000000000000 0 -1 0x06000500BD1AA616F09E44FE0000000001000000000000000000000000000000000000000000000000000000 5 1 BAD34569-6E35-4945-8F9F-E1C46F3CF791 0 NULL 0 MISCELLANEOUS 0 1 19335541 0x 0 0 0 0 1 0x00000000C20ED498 0 0 0 2147483647 us_english mdy 7 1 1 1 0 1 1 1 1 2 -1 0 2 0 0 0 0 1 0x4AC3E94A42400E47 0xB94AAAF5F1DB9778
(@1 int)DELETE [WR_SPL_PreCompiledLists] WHERE [Penta_Nbr]=@1 62 0 2014-03-11 08:02:12.820 suspended DELETE 0x0200000015ABE50126BD79096518C75D9209AA1B9DC3DBF60000000000000000000000000000000000000000 16 -1 0x0600050015ABE501409D44FE0000000001000000000000000000000000000000000000000000000000000000 5 1 70353187-2595-4D08-9F9C-E608E6FACEA6 0 PAGEIOLATCH_EX 13 PAGEIOLATCH_EX 5:1:1047189 2 1 19335431 0x 0 0 156 24064 1 0x00000000C20ED0C8 4863 14 20863 2147483647 us_english mdy 7 1 1 1 0 1 1 1 1 2 -1 0 0 0 0 0 0 1 0x418A7DF29AF7A9A0 0x9230291B34359B04
(2 row(s) affected)
更新2
我使用了以下命令，并确定没有其他表参照此表的外键：SELECT OBJECT_NAME(R.CONSTID) FROM SYSREFERENCES R WHERE R.RKEYID = OBJECT_ID('WR_SPL_PreCompiledLists')
解决方案
我的Penta_Nbr和ListType的主键上有一个索引（这是一个多字段主键）。结果发现，这个索引对于仅按Penta_Nbr删除记录并不有用。在Penta_Nbr上创建另一个索引解决了我的性能问题。现在这个删除操作只需不到1秒钟。
SELECT OBJECT_NAME(R.CONSTID) FROM SYSREFERENCES R WHERE R.RKEYID = OBJECT_ID('WR_SPL_PreCompiledLists')- thepirat000