Sql-server – Is Index causing timeouts

indexsql serversql-server-2005

Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often.

I am using the following query to keep tab on index stats

SELECT DISTINCT Object_name(sis.OBJECT_ID) TableName,
                si.name                    AS IndexName,
                sc.Name                    AS ColumnName,
                sic.Index_ID,
                sis.user_seeks,
                sis.user_scans,
                sis.user_lookups,
                sis.user_updates
FROM   sys.dm_db_index_usage_stats sis
       INNER JOIN sys.indexes si
         ON sis.OBJECT_ID = si.OBJECT_ID
            AND sis.Index_ID = si.Index_ID
       INNER JOIN sys.index_columns sic
         ON sis.OBJECT_ID = sic.OBJECT_ID
            AND sic.Index_ID = si.Index_ID
       INNER JOIN sys.columns sc
         ON sis.OBJECT_ID = sc.OBJECT_ID
            AND sic.Column_ID = sc.Column_ID
WHERE  sis.Database_ID = Db_id('mydb')

Earlier, there was hardly any user_seeks and most of the queries did a user_scan. As I changed the indexes, the user scans has gone down to zero and all the queries are using user_seek. Now I have read many places that index seeks are desired over index scans, so with that thought my db perf should be better.

But I am getting a lot of SQL Timeouts on the Asp.NET web app. Given that the index I defined contains 11 columns and a larger number of included columns, I was wondering whether maintaining the index itself has affected the perf?

After creating the new index, the user seeks to user updates ratio is 4489587/111656.

Best Answer

You must get execution plan of each slowly query first, and survay required Index.

for change timeout time use below address in SSMS:

Tools\Options\Designers\Trasnaction time-out after

Default value for timeout is 30s and you change this value to about 6000s and then create own index. The above path is in SQL Server Management Studio 2008 R2

Related Solutions

Sql-server – Determining that Indexes on a table are unused

Index maintenance (rebuild/reorganize) and DBCC CHECKDB activity most likely, possibly statistics updates. Any scheduled maintenance configured?

If there is no user access, bin them. Just be mindful of the time frame over which you decide they are no longer used. Are there any weekly or monthly reporting tasks for instance?

While you're looking, dig around for duplicate indexes as well.

Edit: regarding SSC link

From a quick scan through the thread, looks like the SSC folk had similar thoughts. They are however taking a more cautious stance on the possible "occasional" use of these indexes, taking the position that someone put them there for a reason, a perfectly reasonable argument. The counter argument is that all too often it's the exact opposite, someone put them there because they thought it was the right thing to do but through a lack of understanding or lack of testing, it wasn't.

I've brought a couple of systems back from the brink by doing nothing other than dropping unused and duplicated indexes. Over indexing can cause chaos.

It's your system, you need to understand and weigh the risks of leaving these indexes in place or dropping them. If you decide to go ahead with the drop, document what you do, why you're doing it, script the indexes and publish to all interested parties.

Sql-server – How to Optimise Query

The optimizer can consider many plan alternatives (including ones with multiple seeks) but for disjunctions (OR predicates) it does not consider plans involving index intersections by default. Given the indexes:

CREATE CLUSTERED INDEX cx 
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1 
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

We can force index seeks (assuming SQL Server 2008 or later):

SELECT * 
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON 
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

FORCESEEK plan

Using your sample data, the seek plan costs at 0.0332551 units compared with 0.0068057 for the scan plan:

Scan plan

There are all sorts of possible query rewrites and hints we can try. One example of a rewrite to promote an option the optimizer does not consider for the original plan is:

SELECT * 
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

This execution plan does not seek the second index if it finds a match on the first:

APPLY TOP Plan

This may perform very slightly better than the default FORCESEEK plan.

Without adding any new indexes, we can also force a seek into the Dispatch table:

SELECT * 
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

Seek 2

This may be better or worse than the first example depending on things like how many rows are in each of the tables. The APPLY + TOP improvement is still possible:

SELECT * 
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

Best Answer

Related Solutions

Sql-server – Determining that Indexes on a table are unused

Sql-server – How to Optimise Query

Related Question