Database engine tuning advisor I want to choose best index for my table

Posted on 2007-10-03
Last Modified: 2008-01-09
I would like to do some tuning with my database which is having 50GB db data file size. And I would like also to use Database engine tuning advisor as the primary tool that can be use to analyze the best index for my table and I heard that I need a sql profiler for the best recommendations and if the answer is yes for sql profiler,
what kind of event should I pick to use the database engine tuning advisor to look for the best index recommendations?
Question by:motioneye
    LVL 51

    Accepted Solution

    Go to to learn more about index tuning.

    Index tuning is a black art and to be honest I'm not that happy with the tuning advisor.  Generally indexing helps with joins and predicates (the conditions in the WHERE clause)

    What is key to understand is that indexing can be contradictory.  IE: Let's say you have two stored procedures that access the same tables but use different queries and WHERE clauses.  Indexing tuned for one query could actually slow another query down!

    Therefore good indexing analysis involves knowing what queries are run the most often and to determine that hopefully you are using stored procedures because they are cached (a key peformance benefit) and you can then analyze the cache.

    I inspect the cache and look for the most used stored procedures.  I then monitor the performance of the most used ones and fix the slowest.  It seems obvious but many people miss it.

    Here is a query that will return the cache statistics:

          cacheobjtype         CacheObjectType,
          objtype              ObjectType,
          objid                ObjectID,
          (SELECT [name] FROM sysusers WHERE CachedObjects.uid = sysusers.uid) Owner,
          sysobjects.[name]    Object,
          CachedObjects.uid    UserSubmittedID,
          refcounts            [References],
          usecounts            [Uses],
          CachedObjects.status IsCursorPlan
          master..syscacheobjects CachedObjects LEFT JOIN sysobjects ON sysobjects.[id] = CachedObjects.objid
          CachedObjects.dbid IN (SELECT dbid FROM sysfiles INNER JOIN master..sysdatabases Databases ON sysfiles.[filename] = Databases.[filename])

    Author Comment

    Hi tedbilly
    Can u explain me a little bit how do i use a query above to and wht you query a sysusers? and it let says in sql server 2005 does this statement work?
    LVL 51

    Expert Comment

    I just tried it on one of my SQL 2005 databases using the SQL Server Management Studio and it worked.

    The reason for the sysusers JOIN is to get the object owner like 'dbo'.  SQL allows you to create two objects with the same name but different owners.  Personally I don't like using that technique but you have to protect against.

    IE: If you have a SQL user named Fubar who is a database owner the following objects could be created.

    My query will show both.

    Author Comment

    Hmm I'mtrying with the above statement, but the object_id return was not exists in my database

    select object_name(object_id_here) but does not return me any objects
    LVL 51

    Expert Comment

    The OBJECT_ID function requires a string IE: OBJECT_ID('Fubar')  That scalar function as nothing to do with my answer to your question.  I'm not sure why you are using it.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 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

    24 Experts available now in Live!

    Get 1:1 Help Now