SQL Server Performance

How to trace all queries to a particular table

Discussion in 'Performance Tuning for DBAs' started by karavyu, Feb 21, 2003.

  1. karavyu New Member

    Hi all,

    I am going to redesign all indexes to a very important and big table, which is heavely used. To do that, I need to know all queries which are done to this table, how often, reads and so on. In order if I create or delete index for one query not to impact another important one. I trace with Profiler, specify filter DB ID, as well as Duration <>0, reads<>0. Than I load all data into SQL table. How can I find queries to a particular table? In TSQL Event Classes or Stored Procedures Event Category there is no Object ID, in Scan Event Category there is no SQL statement. The only way I see is to query table with where clause textdata like '%name of table%'. Is there any other approach to achieve that?
  2. satya Moderator

    Once you capture that trace submit to the INDEX TUNING WIZARD for recommendations.
    Also make sure to have regular DBCC checks and maintenance tasks against the database to keepup the health.

    HTH

    Satya SKJ
  3. bradmcgehee New Member

    If you load all the trace data into a SQL table, you can run a SELECT statement against the table name from the TextData data column. This will produce a list of all the queries in this particular trace that hits the specific table.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. thomas New Member

    You can also filter by object_id or object_name when you create the SQL Profile. However, I have found this to be unreliable myself in the past, so capturing it all then running select statements where textdata like '%tablename%' may indeed be a better option.

    Tom Pullen
    DBA, Oxfam GB
  5. karavyu New Member

    Once you capture that trace submit to the INDEX TUNING WIZARD for recommendations.


    Yes, I use it. It suggests to remove big indexes and I believe I need to do that. But I want to be sure that I will do right things. I'd like to take each important query and look at it's execution plan and read statistics in QA.

    If you load all the trace data into a SQL table, you can run a SELECT statement against the table name from the TextData data column. This will produce a list of all the queries in this particular trace that hits the specific table.


    Yes, but table name in textdata looks like "dbo"."Expense". As a word Expense is used very often in comments, in sp name and so on, I get a lot of unuseful information. Now I think to use DTS to remove doublequotes in textdata and run query with condition like'%dbo.Expense%', than I probably will get what i need.

    Thanks

  6. Chappy New Member

    As long as your profile trace contains a representative amount of different queries which occur on that table, then index tuning wizard should generally provide optimum indices. You can always fine tune each query after this to ensure it has not missed any, by checking for table scans etc.

    For querying the trace table, why not just use LIKE '%"dbo"."Expense"' ?
  7. satya Moderator

    Also you can use QUERY EXECUTION PLAN for information apart from PROFILER.

    HTH

    Satya SKJ

Share This Page