[Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-03
Medium Priority
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
  • 3
  • 2
LVL 51

Accepted Solution

Ted Bouskill earned 2000 total points
ID: 20063219
Go to http://www.sql-server-performance.com 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

ID: 20086141
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

by:Ted Bouskill
ID: 20086713
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

ID: 20155590
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

by:Ted Bouskill
ID: 20160479
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

868 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