Sql server indexes tips

Question

Hi,

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

Answer 1

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:

--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'
                                )

or this one:

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

Answer 2

One of the biggest and most important tools to be used for analyzing SQL Server performance is SQL Profiler. It's not exactly easy to understand and use, so this Master SQL Server Profiler video series might help you gain an understanding on how to use SQL Profiler to help you with SQL Server performance tuning.

Answer 3

The most critical things to look at indexing first are: Foreign keys (these are not indexed automatically and are generally used in joins and thus should usually be indexed unless the table size is small and will remain small.) Much more often than not FKs need indexes.

Things in where clauses that have sufficient variablity to make an index useful. For instance last_name used in where clauses probably needs an index but a field with 'Y' or 'N' text only would not benefit from one. Things you will order by frequently.

If there is only one query that uses that particular field in the where or order by, don't index unless the query is taking way too long.

Things not to index: Large fields that are text or varchar(Max) or things usually searched by like '%text%'. These may benefit more from a full-text index than a regular index.

Bit fields. There is no point indexing a bit field at all (and not all databases may even allow an index) as it doesn't have enough variablity to make good use of an index.

Remember every index adds to the time to insert, update or delete. While I've noticed users will generally tolerate more time to do these things than to return results from a select, it must be kept in mind.

Answer 4

Some favorites:

1. Performance tuning articles and tutorials from from SQLServerPedia
2. An introduction to indexes from SQL Server MVP Gail Shaw
3. SQL Server Execution Plans by Grant Fritchey. Understanding execution plans will be a critical part of optimizing your code. This is one of the best SQL Server books I've read and it's free.