Unindexed column used by Query Optimiser

Posted on 2006-04-06
Last Modified: 2012-06-27
Can someone please tell me how I can extract Unindexed columns used by Query Optimiser. My understanding is that the query optimiser creates a temporary index for colums which ideally would have been indexed. I would like to gather info on these indexes and possibly create permanent indexes for some of temporarily created indexes. Thanx
Question by:MSSexpert
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    The query optimizer creates some lookup tables for those columns referenced in the where clause, using any available indexes in the table. You can see this in the Query execution plan as 'Bookmark Lookup'
    LVL 28

    Expert Comment

    Why wont you use Index tuning wizard to create indexes, statistics and optimize the quries.
    LVL 68

    Accepted Solution

    >> My understanding is that the query optimiser creates a temporary index for colums which ideally would have been indexed. <<

    Yes, SQL will create its own statistics, which appear as an index in system tables.

    The "index" name begins with "_" (underscore), so to find them all, assuming you don't name your own indexes beginning with an _:

    select left(object_name(id), 50) AS [Table Name],
        parsename(replace(substring(name, 2, 200), '_', '.'), 2) AS [Column Name With Stats]
    from sysindexes
    where name like '~_%' ESCAPE '~'
    order by 1, 2

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now