Sql server 索引提示

Question

过去两天我一直在阅读有关索引和如何优化数据库的内容。

尽管我对索引有一个清晰的想法，但我还不知道如何实际优化我的数据库。

您能推荐任何优化数据库的教程或技术吗？

请具体一点，因为我已经阅读了很多理论，但到目前为止还没有具体说明

谢谢

Answer 1

如果它“没有”损坏，请不要修复它。查找应用程序的慢速区域，找到有问题的查询并分析这些查询计划并解决索引问题(如果有)。不要因为刚刚学到一些东西就开始对系统进行更改!

进一步阅读:http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

试试这个查询来查找缺失的索引:

--based on http://stackoverflow.com/questions/1540192/searching-for-table-index-scans
--this query will show cahced query plans that "SCAN", change comments for other things

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, CachedPlans AS
(SELECT
     RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID
         ,RelOp.op.value(N'@NodeId', N'int') AS OperationID
         ,RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator
         ,RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator
         ,RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost
         ,RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO
         ,RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU
         ,RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows
         ,cp.plan_handle AS PlanHandle
         ,qp.query_plan AS QueryPlan
         ,st.TEXT AS QueryText
         ,cp.cacheobjtype AS CacheObjectType
         ,cp.objtype AS ObjectType
         ,cp.usecounts AS UseCounts
     FROM sys.dm_exec_cached_plans                            cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)     st
         CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)   qp
         CROSS APPLY qp.query_plan.nodes(N'//RelOp')          RelOp (op)
)
SELECT
    PlanHandle
        ,ParentOperationID
        ,OperationID
        ,PhysicalOperator
        ,LogicalOperator
        ,UseCounts
        ,CacheObjectType
        ,ObjectType
        ,EstimatedCost
        ,EstimatedIO
        ,EstimatedCPU
        ,EstimatedRows
        ,QueryText
    FROM CachedPlans
    WHERE CacheObjectType = N'Compiled Plan'
        AND PhysicalOperator IN ('nothing will ever match this one!'
                                --,'Assert'                             
                                --,'Bitmap'
                                --,'Clustered Index Delete'
                                --,'Clustered Index Insert'
                                ,'Clustered Index Scan'
                                --,'Clustered Index Seek'
                                --,'Clustered Index Update'
                                --,'Compute Scalar'
                                --,'Concatenation'
                                --,'Constant Scan'
                                ,'Deleted Scan'
                                --,'Filter'
                                --,'Hash Match'
                                ,'Index Scan'
                                --,'Index Seek'
                                --,'Index Spool'
                                ,'Inserted Scan'
                                --,'Merge Join'
                                --,'Nested Loops'
                                --,'Parallelism'
                                ,'Parameter Table Scan'
                                --,'RID Lookup'
                                --,'Segment'
                                --,'Sequence Project'
                                --,'Sort'
                                --,'Stream Aggregate'
                                --,'Table Delete'
                                --,'Table Insert'
                                ,'Table Scan'
                                --,'Table Spool'
                                --,'Table Update'
                                --,'Table-valued function'
                                --,'Top'
                                )

或者这个:

SELECT TOP 50
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 
ORDER BY 3 DESC