以下简单的select语句花费太多时间执行:select * from sys_letter_intidsys_letter_intid表结构:CREATE TABLE [dbo].[sys_letter_intid]([intid] [int] NULL,[id] [int] IDENTITY(1,1) NOT NULL,[misc_text] [nvarchar](50) NULL,[misc_Date] [datetime] NULL,[misc_amount] [money] NULL) ON [PRIMARY]不能确定行数,因为即使通过int desc查询从sys_letter_intid中选择COUNT(*)并从sys_letter_intid中选择前1 *也要花费太多时间来执行。索引空间-0.023 MB行数-1数据空间-0.008 MBsp_lock提供以下输出:spid dbid ObjId IndId Type Resource Mode Status------ ------ ----------- ------ ---- -------------------------------- -------- ------52 8 0 0 DB S GRANT54 8 1185439297 0 TAB IX GRANT54 8 0 0 DB S GRANT55 5 0 0 DB S GRANT56 8 0 0 DB S GRANT56 8 2049442375 1 KEY (716bd5e0da25) X GRANT56 8 2049442375 0 TAB IX GRANT56 8 2049442375 1 PAG 1:29168 IX GRANT58 8 0 0 DB S GRANT60 1 2107154552 0 TAB IS GRANT60 8 0 0 DB S GRANT61 8 0 0 DB S GRANT61 8 2049442375 1 PAG 1:29168 IS GRANT61 8 2049442375 1 KEY (716bd5e0da25) S WAIT61 8 2049442375 0 TAB IS GRANT62 8 0 0 DB S GRANT63 5 0 0 DB S GRANT64 8 0 0 DB S GRANT66 8 0 0 DB S GRANTSP_Who2提供以下输出:SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID----- ------------------------------ ------------------------------------------------------------------------------ --------------- ----- ---------------------------- ---------------- ------- ------ -------------- ------------------------------------------------------------------ ----- ---------1 BACKGROUND sa . . NULL LOG WRITER 234 0 02/27 03:22:02 1 02 BACKGROUND sa . . NULL LAZY WRITER 608 0 02/27 03:22:02 2 03 BACKGROUND sa . . NULL RECOVERY WRITER 93 0 02/27 03:22:02 3 04 BACKGROUND sa . . NULL LOCK MONITOR 0 0 02/27 03:22:02 4 05 BACKGROUND sa . . master SIGNAL HANDLER 0 0 02/27 03:22:02 5 06 BACKGROUND sa . . NULL XE DISPATCHER 62 0 02/27 03:22:02 6 07 BACKGROUND sa . . NULL RESOURCE MONITOR 780 0 02/27 03:22:02 7 08 BACKGROUND sa . . NULL XE TIMER 0 0 02/27 03:22:02 8 09 BACKGROUND sa . . master BRKR TASK 0 0 02/27 03:22:40 9 010 sleeping sa . . master TASK MANAGER 0 0 02/27 03:22:03 10 011 BACKGROUND sa . . master TRACE QUEUE TASK 0 0 02/27 03:22:03 11 012 BACKGROUND sa . . NULL SYSTEM_HEALTH_MO 0 0 02/27 03:22:04 12 013 BACKGROUND sa . . NULL RECEIVE 312 2 02/27 03:22:04 13 014 sleeping sa . . master TASK MANAGER 0 159 03/12 22:46:23 14 015 sleeping sa . . master TASK MANAGER 0 1 03/12 22:46:23 15 016 BACKGROUND sa . . master CHECKPOINT 109 159 02/27 03:22:34 16 017 BACKGROUND sa . . master TASK MANAGER 0 0 02/27 03:22:34 17 018 BACKGROUND sa . . NULL UNKNOWN TOKEN 0 0 02/27 03:23:35 18 019 sleeping sa . . master TASK MANAGER 0 4 03/12 22:36:22 19 020 sleeping sa . . master TASK MANAGER 0 0 03/12 22:46:33 20 021 sleeping sa . . master TASK MANAGER 0 0 03/12 22:33:17 21 022 sleeping sa . . master TASK MANAGER 0 0 03/12 22:46:33 22 023 sleeping sa . . master TASK MANAGER 0 0 03/12 22:50:39 23 024 sleeping sa . . master TASK MANAGER 0 0 03/12 22:43:18 24 025 BACKGROUND sa . . master BRKR EVENT HNDLR 0 41 02/27 03:22:40 25 026 BACKGROUND sa . . master BRKR TASK 218 0 02/27 03:22:40 26 027 BACKGROUND sa . . master BRKR TASK 31 0 02/27 03:22:40 27 028 BACKGROUND sa . . master BRKR TASK 0 0 02/27 03:22:40 28 029 sleeping sa . . master TASK MANAGER 0 7 03/12 22:50:39 29 030 sleeping sa . . master TASK MANAGER 0 0 03/12 22:50:39 30 051 sleeping admin CGVAK-328 . master AWAITING COMMAND 157 283 03/13 01:15:51 Microsoft SQL Server Management Studio 51 052 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 16 1 03/13 01:33:08 Microsoft SQL Server Management Studio - Query 52 053 sleeping NT SERVICE\ReportServer$MSSQLSERVER2012 WIN-07VQ7EIB4L1 . ReportServer$MSSQLSERVER2012 AWAITING COMMAND 0 0 03/13 01:40:09 Report Server 53 054 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 218 0 03/13 01:28:50 jTDS 54 055 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 0 0 03/13 01:29:34 jTDS 55 056 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 376 5 03/13 01:31:11 jTDS 56 057 sleeping admin CGVAK-328 . master AWAITING COMMAND 16 38 03/13 01:40:22 Database Engine Tuning Advisor 57 058 sleeping NT SERVICE\ReportServer$MSSQLSERVER2012 WIN-07VQ7EIB4L1 . ReportServer$MSSQLSERVER2012 AWAITING COMMAND 0 0 03/13 01:41:14 Report Server 58 059 sleeping admin CGVAK-328 . master AWAITING COMMAND 0 0 03/13 00:50:47 Database Engine Tuning Advisor 59 060 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 125 7 03/13 01:17:27 Microsoft SQL Server Management Studio - Query 60 062 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 202 0 03/13 01:41:20 jTDS 62 063 sleeping admin CGVAK-328 . PUC AWAITING COMMAND 718 8 03/13 01:29:35 jTDS 63 064 SUSPENDED admin CGVAK-328 55 PUC DELETE 0 0 03/13 01:31:11 jTDS 64 065 sleeping admin DEVARAJ-PC . master AWAITING COMMAND 374 0 03/13 01:15:05 Microsoft SQL Server Management Studio 65 066 sleeping admin DEVARAJ-PC . PUC AWAITING COMMAND 0 0 03/13 01:31:50 Microsoft SQL Server Management Studio - Query 66 067 sleeping admin DEVARAJ-PC . master AWAITING COMMAND 0 0 03/13 01:31:46 Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 67 068 sleeping admin DEVARAJ-PC . master AWAITING COMMAND 0 0 03/13 01:33:19 Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 68 069 RUNNABLE admin CGVAK-328 . PUC SELECT INTO 31 8 03/13 01:33:29 Microsoft SQL Server Management Studio - Query 69 0(48 row(s) affected)以下是SELECT * FROM sys.dm_exec_requests的输出,其中blocking_session_id 0session_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---------- ----------- ----------------------- ------------------------------ -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------------------------------------ ------------------- ------------------------------------------------------------ ----------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ------------------------- ----------- ------------------ ------------ ------------------ -------------------- -------------------- -------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ----------------- ---------- ----------------- ------------- ------------- ------------ ---------- ----------------------- --------------------------- ------------ ----------------- -------------------- ----------- ----------- -------------------- ---------------------- ----------- ------------------ ------------------64 0 2014-03-13 01:31:11.927 suspended DELETE 0x0200000095E2BC33A9AC347266EB7BD07B54F63FA636143300000000000000000000000000000000 0 -1 0x0600080095E2BC3330BCFB1501000000000000000000000000000000000000000000000000000000 8 1 07E2265C-3159-4099-B6DF-8B2A8FCA4BB9 55 LCK_M_U 137240 LCK_M_U KEY: 8:72057596467675136 (915ffb6f1e99) 2 1 6920013 0x 0 0 0 137240 1 0x638702F8 0 0 2 2147483647 us_english mdy 7 1 0 1 0 1 1 1 1 2 -1 0 0 0 1 0 0 1 0x83FE4907FA5F0EF2 0x873EF4A76D497C0C(1 row(s) affected)我正在使用SQL Server 2012,以下是执行计划。<?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.3128.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032831" StatementText="select * from sys_letter_intid" StatementType="SELECT" QueryHash="0x806EE30ADB72F191" QueryPlanHash="0x47B522D12A52B90C" 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 CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="56"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104702" EstimatedPagesCached="9938" EstimatedAvailableDegreeOfParallelism="2" /> <RelOp AvgRowSize="85" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1"> <OutputList> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="intid" /> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="id" /> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="misc_text" /> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="misc_Date" /> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="misc_amount" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="intid" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="id" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="misc_text" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="misc_Date" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Column="misc_amount" /> </DefinedValue> </DefinedValues> <Object Database="[PUC]" Schema="[dbo]" Table="[sys_letter_intid]" Index="[IDX_sys_letter_intid]" IndexKind="Clustered" /> </IndexScan> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence></ShowPlanXML> 最佳答案 为了解决这个问题,要感谢迈克尔·托德(Michael Todd),RBarryYoung和菲尔·桑德勒(Phil Sandler)的评论,SELECT花费了很长时间,因为它被另一个进程阻止了。在WITH (NOLOCK)语句上使用SELECT绕过了更新锁(LCK_M_U),从而允许该语句继续进行。另请参见theChunKk的评论,指出对事务使用不同的隔离级别(例如set transaction isolation level read uncommitted)是对NOLOCK语句使用SELECT的替代方法。关于sql-server - 一个简单的选择语句会花费太多时间,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22361919/