Post reply

performance vs indexes

  • June 6, 2016 at 4:24 pm

    #308169

    I was looking at some statement written by a colleague of mine that started to perform bad as the database size increased. The culprit was a LEFT JOIN statement similar to:

    LEFT JOIN MyTable T ON T.Field1 = M.Field1 AND T.Field2 = M.Field2

    MyTable had a non-clustered index made of Field1, Field2 and couple of other fields. Adding an extra non-clustered index solely made of Field1 and Field2 solved the problem.

    Does someone know an easy way to prevent this from happening. Can SQL Server itself or a third party tool produce a list of indexes that should be created (and indexes that could be dropped) ?

  • June 6, 2016 at 4:46 pm

    #1883486

    To identify "missing undexes" and unused indexes have a look at:

    https://www.red-gate.com/library/performance-tuning-with-sql-server-dynamic-management-views

    It will give you some tools and ideas for proactive index monitoring.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

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