Sql服务器索引提示(Sql server indexes tips)

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

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

你能建议任何教程或技术来优化数据库。

请具体，因为我一直在阅读大量的理论，但没有具体的指示

谢谢

I have been reading for the last two days about indexes and how to optimize a database.

Despite I have a clear idea on Indexes i don't know yet how to practically optimize my database.

Could you suggest any tutorial or technique to optimize a database.

Please be specific as i have been reading lots of theory but no specific instruction up to now

thanks

最满意答案

如果它“破”不解决问题。查找应用程序的缓慢区域，查找有问题的查询并分析这些查询计划并解决索引问题（如果有）。不要只是开始对系统进行修改，因为你刚刚学到了一些东西！

进一步阅读： 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

If it "aint" broken don't fix it. Look for the slow areas of your application, find the offending queries and analyze those query plans and address the index issues if any. Don't just start making changes in your system because you just learned something!

further reading: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

try this query to find missing indexes:

or this one:

更多推荐