counters for index-tune in profile

what counters should be checked in profilers, so that you can feed into index tunin wizard?
LVL 5
25112Asked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
I don't know if there are necessarily counters to check.  My approach has always been to use the duration template in profiler and filter by however many milliseconds are considered too slow.  The procedures that take the longest generally are the ones that you should concentrate on.

Greg

0
 
25112Author Commented:
do you not feed the trace into the tuning wizard?
0
 
JestersGrindCommented:
You can, but I usually address each issue that I find individually.

Greg

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
25112Author Commented:
OK-

but in the case of having MS give high level index reco from a trace, what counters would the wizard expect to make a good reco?
0
 
JestersGrindCommented:
I would stick with the templates.  TSQL_Duration is probably the best one to identify long running queries.  The counters that you choose aren't that important.  You want to use profiler just to identify the queries that need the most attention.  Then you use that as the source for the index tuning wizard and it will analyze the workload.  This Microsoft article probably explains it better than I am.

http://msdn.microsoft.com/en-us/library/ff650692.aspx

Greg

0
 
25112Author Commented:
I am following you. But you would need most of tsql and stored proc counters, right? the other ones like locks, sessions, database counters won't be relevant to indexes?
0
 
JestersGrindCommented:
That's true.  I believe the SQL text data is what the index tuner uses as the workload to analyze.

Greg

0
 
25112Author Commented:
>>That's true.  I believe the SQL text data is what the index tuner uses as the workload to analyze.

will it de-phrase each statement and make its own version of statements to test and suggest index?

for example, some textdata will start with 'SELECT' some others will have 'exec sp_execute' and some will have HTML tags like <SHOWPLANXL....some will have 'declare ....." etc
0
 
JestersGrindConnect With a Mentor Commented:
I believe it literally uses the statements that it captures.  

Greg

0
 
25112Author Commented:
OK- thx.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.