我之前的索引: create index xxbm_zybm_kcbm_idx on td_skkb(xx_bm,kc_bm,zy_bm) go create nonclustered index xxbm_xsbm_zybm_xsxm_idx on t_gdxs(xx_bm,zy_bm)include (xs_bm,xs_xm) go
没创建这两个索引之前耗时16s，现在耗时8s这任然是不能接受的 [最优解释] 你们这些高手啊，没事干了是不？ [其他解释] 围观一下 [其他解释] select g.xs_bm,g.xs_xm,g.xx_bm,s.zy_bm,s.kc_bm from td_skkb s inner join (select * from t_gdxs where xx_bm='0101') g on g.xx_bm=s.xx_bm and g.zy_bm=s.zy_bm [其他解释]
没用 早试过了 [其他解释] 为什么不在查询条件上建立聚集索引 CREATE INDEX index_name ON t_gdxs (zy_bm,zy_bm) CREATE CLUSTERED INDEX INDEX_name ON t_gdxs(xx_bm)
想快就加大cpu的性能吧 [其他解释] The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed.
[其他解释] hj hjgjgyu [其他解释] 59W行的数，8秒还不满足么？这种情况下，合并连接而且没有排序操作，应该已经是最快的计划了。 如果非要优化，只能考虑减少结果集，如果是最后要输出的结果，我认为如此大的结果集意义不大，如果是计算的中间集，从结果出发，考虑如何减少输出 [其他解释] create index xxbm_zybm_kcbm_idx on td_skkb(xx_bm,kc_bm,zy_bm) 改为： create index xxbm_zybm_kcbm_idx on td_skkb(xx_bm,zy_bm,kc_bm)
create nonclustered index xxbm_xsbm_zybm_xsxm_idx on t_gdxs(xx_bm,zy_bm)include (xs_bm,xs_xm)
[其他解释] 围观 [其他解释] 未使用的索引：
-- Unused indexes SELECT SCH.name + '.' + OBJ.name AS ObjectName ,OBJ.type_desc AS ObjectType ,IDX.name AS IndexName ,IDX.type_desc AS IndexType FROM sys.indexes AS IDX LEFT JOIN sys.dm_db_index_usage_stats AS IUS
ON IUS.index_id = IDX.index_id AND IUS.object_id = IDX.object_id INNER JOIN sys.objects AS OBJ ON IDX.object_id = OBJ.object_id INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id WHERE OBJ.is_ms_shipped = 0 -- Exclude MS objects AND OBJ.type IN ('U', 'V') -- Only user defined tables & views AND IDX.type > 0 -- Ignore heaps AND IDX.is_disabled = 0 -- Disabled indexes aren't used anyway AND IDX.is_primary_key = 0 -- Exclude PK => FK constraints / part of business logic AND IDX.is_unique = 0 -- Exclude unique indexes => part of business logic AND IUS.object_id IS NULL ORDER BY ObjectName ,IndexName
执行计划中丢失索引的查询语句:
-- Get all SQL Statements with missing indexes and their cached query plans ;WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan) SELECT ECP.[usecounts] AS [UsageCounts] ,ECP.[refcounts] AS [RefencedCounts] ,ECP.[objtype] AS [ObjectType] ,ECP.[cacheobjtype] AS [CacheObjectType] ,EST.[dbid] AS [DatabaseID] ,EST.[objectid] AS [ObjectID] ,EST.[text] AS [Statement] ,EQP.[query_plan] AS [QueryPlan] FROM sys.dm_exec_cached_plans AS ECP CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries) AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY ECP.[usecounts] DESC
[其他解释] create nonclustered index xxbm_xsbm_zybm_xsxm_idx on t_gdxs(xx_bm,zy_bm)include (xs_bm,xs_xm) 这个索引不给力 select * from t_gdxs where xx_bm='0101' 这条单独执行 要多久? [其他解释]
毫秒级 [其他解释]
[其他解释] inner join 指定为 nested loop 试试 [其他解释]
declare @dt datetime set @dt=getdate() select g.xs_bm, g.xs_xm, g.xx_bm from t_gdxs g where g.xx_bm='0101' select datediff(ms,@dt,getdate()) /* 590 */
[其他解释] 尝试了结果集inner join了么 [其他解释] create index xxbm_zybm_kcbm_idx on td_skkb(xx_bm,zy_bm)include(kc_bm) gocreate nonclustered index xxbm_xsbm_zybm_xsxm_idx on t_gdxs(xx_bm,zy_bm)include (xs_bm,xs_xm) go
