Results 1 to 7 of 7

Thread: Indexing for dummies? Please help.

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2006
    Location
    USA
    Posts
    476

    Indexing for dummies? Please help.

    Indexing isn't really my strong suit. I've been re-writing some queries on an SQL 2005 database and examining the execution plans. I'm seeing a lot of index scans instead of index seeks, which is what I assume I would rather be seeing. The DBAs keep jumping all over me when they run my code and see 'clustered index scan' and 'index scan' all over the place.

    On some tables I'm getting clustered index scans even though there is only one row in the table and the overall cost is 0%. I'm thinking I shouldn't really be worried about these, but the DBAs keep jumping on me for those.

    I keep trying to tweak existing indexes and add new ones to try to get the queries to perform better, but nothing works. I've read and re-read the BOL, a book called 'Beginning SQL Server 2005 Programming' by Robert Vieira, and various assorted websites, some of which have been helpful...but I keep wondering if there is something I'm missing. I'm not even sure where to begin.

    P.S. I've never tried full text indexing, but it is disabled on our databases so it's not an available solution.

  2. #2
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Indexing for dummies? Please help.

    First of all, full text indexes are not what you need. They are only used to do advanced searches in columns containing text.

    About the indexes:
    It is not easy to give any exact answers without seeing the table and index structures and what queries you are executing. In order to analyze why sql server uses an index scan instead of a seek you would also need to analyze the statistics for distribution of the different values in the index.
    SQL Server uses the statistics to determine how the execution plan will be.

    Lets say you index a column in a table with 1000 rows. 10% of the rows contains the value 123 in this column. Such a high percentage of the value 123 will probably cause sql server to perform a index scan insted of a seek because it is probably faster to just scan through the index and retrieve the high number of records instead of moving through the index tree.
    That is why sql server uses a scan instead of a seek on the one row table.
    As you understamd analyzing performance and indexes is a complicated topic since there are som many different factors that will affect a execution plan.

    If you feel that the execution plan is using indexes incorrecly then perhaps "auto update statistics" is off? Make sure that it is on.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2006
    Location
    USA
    Posts
    476

    Re: Indexing for dummies? Please help.

    Quote Originally Posted by kaffenils
    First of all, full text indexes are not what you need. They are only used to do advanced searches in columns containing text.

    About the indexes:
    It is not easy to give any exact answers without seeing the table and index structures and what queries you are executing. In order to analyze why sql server uses an index scan instead of a seek you would also need to analyze the statistics for distribution of the different values in the index.
    SQL Server uses the statistics to determine how the execution plan will be.

    Lets say you index a column in a table with 1000 rows. 10% of the rows contains the value 123 in this column. Such a high percentage of the value 123 will probably cause sql server to perform a index scan insted of a seek because it is probably faster to just scan through the index and retrieve the high number of records instead of moving through the index tree.
    That is why sql server uses a scan instead of a seek on the one row table.
    As you understamd analyzing performance and indexes is a complicated topic since there are som many different factors that will affect a execution plan.

    If you feel that the execution plan is using indexes incorrecly then perhaps "auto update statistics" is off? Make sure that it is on.
    I would assume that it IS using indexes correctly and I'm just bad at writing them. I can look at index statistics until the cows come home but they don't actually mean very much to me.

    In a nutshell, I have a table that has about 125,000 rows. It has 41 columns, a primary key (clustered) using unique policy numbers, with fill factors of between 40-85% on each of the fifteen indexes attached to it. Six of the indexes have densities of 0. Four have densities of 1. The other indexes have densities between 0.01766004 and 0.1782839. This means next to nothing to me. I'm still reading up on the DBCC SHOW_STATISTICS command and I'm not finding the answers to my questions.

    Some of the queries using this main table take up to a minute to execute. The database I'm referring to is a radical redesign of the existing application because the first design wasn't good enough. I was instructed to place pairs of transaction-related dates (such as acceptance starting and ending, registration starting and ending, etc) into a separate table and create a one-to-many relationship with the original table. This means when I want to bring in one or both members of a transaction date pair, I have to do a left outer join which is supposed to be an expensive join.

    However, most of the queries run faster in the test redesign database than they did in the original; the average TTE in the original is 17 seconds; in the new one, it's 12. It's just that the ones that run more slowly run quite a BIT more slowly, sometimes taking twice as long, and I'm shooting in the dark here trying to figure out what to do about it.

  4. #4
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Indexing for dummies? Please help.

    BOL doesn't explain the output of SHOW_STATISTICS very well, but I found an article that explains step by step with good examples how sql server uses statistics to determine how to use indexes.

    http://www.sql-server-performance.co...statistics.asp

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2006
    Location
    USA
    Posts
    476

    Re: Indexing for dummies? Please help.

    Quote Originally Posted by kaffenils
    BOL doesn't explain the output of SHOW_STATISTICS very well, but I found an article that explains step by step with good examples how sql server uses statistics to determine how to use indexes.

    http://www.sql-server-performance.co...statistics.asp
    OK...not much of that made sense, and it assumes that I know more about indexing than I actually do.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Indexing for dummies? Please help.

    Get your hands on a copy "Inside MS SQL Server 2000" - by Kalen Delaney. Not sure if there is a 2005 version yet - but I'm sure the concepts have not changed that much...

    Or google around for her name...

    This book is a bible for understanding the architecture of MS SQL Server...

    Once you read chapter 8 - and understand index structure...

    You get into chapter 15 and 16 and start to understand the QUERY PROCESSOR - where the index choices are actually made.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2006
    Location
    USA
    Posts
    476

    Re: Indexing for dummies? Please help.

    Quote Originally Posted by szlamany
    Get your hands on a copy "Inside MS SQL Server 2000" - by Kalen Delaney. Not sure if there is a 2005 version yet - but I'm sure the concepts have not changed that much...

    Or google around for her name...

    This book is a bible for understanding the architecture of MS SQL Server...

    Once you read chapter 8 - and understand index structure...

    You get into chapter 15 and 16 and start to understand the QUERY PROCESSOR - where the index choices are actually made.
    Thanks szlamany...there's not a 2005 version and when running the same queries on the same structures in 2000 and 2005 I'm noticing a lot of differences so I'm not sure how helpful the 2000 book will be. I was having a look around on Amazon.co.uk and thought 'Inside Microsoft SQL Server 2005: T-SQL Querying' might be useful...have you heard anything about that one?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width