1

I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table. Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and SQL:BatchCompleted but filtering only on that table?

The idea being I can focus my indexing maintenance on that table and work out what the best indexes will be?

  • 2
    Have you looked at the execution plan for the slow query? That is going to give you a lot more concrete information than Profiler. – Aaron Bertrand May 1 '14 at 13:14
  • Yes I have and I think I know how to improve the queries performance by creating a covering index. However I always worry about what impact adding this index will have on other queries (inserts\updates) using this table, and I have never found a good way of working this out. – Tom May 1 '14 at 13:40
  • 2
    There's no magic - the only way to really gauge the impact of a new index on your workload is to create the index and measure your workload. Preferably in a reasonably accurate test or staging environment. This might be useful reading. – Aaron Bertrand May 1 '14 at 13:53
  • Thanks, do you have any good articles on capturing a workload? Also some of the queries from our application are wrapped in FETCHAPI_CURSOR meaning I can't actually see easily what is going on, do you have any advice on capturing workloads in this situation? – Tom May 1 '14 at 14:56
  • @Tom in my experience with the FETCHAPI_CURSOR wait, sql server has been waiting on an application that's doing batch processing (pull n rows, process them, leave sql server hanging, pull another n rows ... repeat). you may want to look at client config to see if you change that behavior. – swasheck May 1 '14 at 16:06
2

Joe Changs ExecStats provides a nice GUI for analysing which queries touch each index.

It can be downloaded from: http://www.qdpma.com/JoeChang.html

Or write Joe directly for the latest version. The tool uses sys.dm_exec_query_stats so it is very lightweight.

  • This sounds like a nice tool, but in this case the OP is trying to determine which indexes to create. – Mark Wilkinson May 2 '14 at 0:34
  • 1
    The tool does that. Since the OP is also looking for the cost of index maintenance - he needs to look at that too. – Thomas Kejser May 2 '14 at 7:32
1

from the comments I see you mad it clear that you just want to finds out what are the queries are being ran against specific tables. so later you can analyze the new index impact on those queries.

one way to find this out w/o putting the SQL Profiler load on server is using the sys.syscomments.

I have used the 'ESCAPE' just to indicate that you can use that to narrow down your search to the precise list of objects. you can also add more where filters if you have only specific objects type request.

    SELECT so.name,so.type
    FROM sys.syscomments scmt
    JOIN sys.sysobjects so
            ON so.id=scmt.id
    WHERE scmt.text LIKE '%gc/_%' ESCAPE '/'
    ORDER BY so.type

this should give your the obejct names that uses that table and now you can see the object definition using sp_helptext or directly in SSMS and see what are the queries.

using profiler you will have to find all iser workflow that can trigger execution of all those objects that uses the table. unless you are looking for improving only specific scenario.

1

Tom, your best bet (as Aaron said) is to check out the execution plan for the slow query in question.

Instead of running profiler for a day to see what is hitting the table I would recommend querying the plan cache for plans that hit the table.

Looking at these plans may also help guide you in your index creation.

Here is a script that could help you out:

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    cp.*,
    st.text As sql_text,
    qp.query_plan
FROM
    sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE
    qp.query_plan.exist(N'//RelOp/OutputList/ColumnReference[@Table = "[TABLENAME]"]') = 1

Just replace TABLENAME (leave the square braces) with the name of the table in question. This will show you the query that was executed, and the plan, but only for those plans related to the table in question.

Granted, this is only an option if you have not recently cleared the cache, either explicitly so, or by restarting the server or SQL Server service.

-1

When it comes to SQL Server index maintenance you cant beat this maintenance script. http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I wouldn't use the profiler to decide anything like this. If you want to write your own simple maintenance plan you need to look at the index DMVs.

  • Sorry I should have been clearer I am actually wanting to work out what queries are being executed against a particular table to work out what indexes I should have on that table. – Tom May 1 '14 at 11:19
  • Ah OK. Have you looked at these DMVs?: sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details. Don't blindly add what you see here though as they don't take into account the indexes you currently have in place. Also each suggested index doesn't know about the other suggestions so if you added all of them you would have very similar indexes with only a few of them actually being used – James Anderson May 1 '14 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.