Post reply

Indexes

  • May 24, 2014 at 4:57 pm

    #285861

    Hi,

    I am working with SP tuning.

    I have some doubts about the indexes.

    I am thinking that

    1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.

    2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.

    Please correct me if I am wrong.

  • May 24, 2014 at 4:58 pm

    #1716343

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 24, 2014 at 5:17 pm

    #1716344

    Thanks Gail Shaw.

  • May 25, 2014 at 4:02 am

    #1716370

    On the 50 ms of savings before you apply an index, Let's say we have an index that runs in 30ms. Does it need an index if we can shave 15ms off th execution time? If it's called once a day? Heck no. If it's called 100 times in a minute? Yes, let's put an index on it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 25, 2014 at 11:58 am

    #1716400

    Thanks Grant.

    One more doubt. Suppose if I have 2 indexes on a table with almost with similar columns. How can we find which index needs to drop and which index we have to keep.

    Is there any way to find this index is better than other? This index is used by this application this no of times ...

    Thanks

  • May 25, 2014 at 1:32 pm

    #1716401

    ramana3327 (5/25/2014)

    Thanks Grant.

    One more doubt. Suppose if I have 2 indexes on a table with almost with similar columns. How can we find which index needs to drop and which index we have to keep.

    Is there any way to find this index is better than other? This index is used by this application this no of times ...

    Thanks

    There's no flawless way to find that out, no. You can check sys.dm_db_index_usage_stats. That will show which indexes are being accessed and how they're being accessed. But it doesn't keep records from the dawn of time. The data can be reset, so it's only covering since the last reboot, restore, attach, whatever. That makes it a somewhat sketchy thing to rely on.

    But, this would only be a concern if by "similar" you mean starting with the same column. That first column, frequently referred to as the leading edge, is what is used to make the histogram in statistics. If that column is the same for two indexes, then you may find that one of them is not used much, if at all. But, if you have an index that is on ColumnA and ColumnB and another index on ColumnB and ColumnA, these are effectively two completely different indexes.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 26, 2014 at 3:57 pm

    #1716548

    Hi Grant,

    Thanks for your reply. It looks clear but some confusion is left. For a table, I have several indexes

    but two Indexes with similar columns

    EX:

    Index1

    with column1(State), col2(purchaseStatus), col3 (storeStatus),col4(storeID) as key columns

    and col5 (purchaseId), col6 (customerId), col7 (purchaseno), col8 (purchasedate)

    Index2

    with column1(State), col2(purchaseStatus) as key columns

    and col5 (PurchaseId), col6 (CustomerID), col7 (Purchaseno), col8 (Purchasedate), col 9 (CustomerName).

    Can you explain me clearly start the process which Index I have to eliminate based on what?

    I use the query SELECT object_name(a.object_id) AS table_name, COALESCE(name, 'table with no clustered index') AS index_name, type_desc AS index_type, user_seeks, user_scans, user_lookups, user_updates FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b ON a.index_id = b.index_id AND a.object_id = b.object_id WHERE database_id = DB_ID('AdventureWorksdw')

    I found that both Indexes are non-clustered. Index 1 has has 0 user seeks, 12 user scans, 0 user lookups, 4000 user updats

    Index2 has 5500 user seeks, 0 user scans, 0 user lookups, 3300 userupdates.

  • May 26, 2014 at 10:05 pm

    #1716574

    According to what you're showing, both indexes have been used. One has been used in seeks and one has been used in scans. Don't worry about updates. Any update to the table has to update the indexes. Which one can you eliminate based on what you've shown? It doesn't sound like either one is a candidate. But, the one that is just supporting scans, I'd be curious which queries are accessing it in that manner. For that, you'd have to either just know the queries used against the tables, or, you'd have to query the plans in cache to determine if you can see the queries there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 26, 2014 at 10:50 pm

    #1716576

    Hi,

    Please let me know how to find that queries.

  • May 26, 2014 at 11:04 pm

    #1716578

    To query the plans in cache you need to use the dynamic management views. Something along these lines could work:

    SELECT deqs.execution_count,

    deqs.total_elapsed_time,

    deqs.total_logical_reads,

    deqs.total_worker_time,

    detqp.query_plan,

    dest.text

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,

    deqs.statement_start_offset,

    deqs.statement_end_offset) AS detqp

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    WHERE detqp.query_plan LIKE '%YourIndexName%'

    That's put together on the fly, so it might need adjustment, but it'll put you in the right direction.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 27, 2014 at 12:17 am

    #1716583

    Hi Grant,

    Thanks for the reply. It is showing the text like create procedure, create trigger and update with where clause

  • May 27, 2014 at 2:14 am

    #1716608

    Yup, that sounds about right.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2014 at 10:14 am

    #1716753

    Thank you.

    In that case, shall we drop the index? If we drop that Index, what will happen?

    Those operations automatically use the second Index

  • May 27, 2014 at 10:41 am

    #1716766

    ramana3327 (5/27/2014)

    In that case, shall we drop the index?

    Why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2014 at 11:59 am

    #1716788

    They ask me to find the exact duplicate and partially duplicate Indexes. Also I need to drop the indexes that are duplicated either exactly or partially. So I have to find a strong supporting point why I choose to drop that indexes over the other..

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply