"RL" wrote:

> I'm looking for a counter that will help me identify which indexes are used
> and how frequently they are used.  My goal is to remove indexes that are not
> used.
>
> The number of transactions on our database makes it impossible to store
> profiler information / Index Tuning Wizard for an extended period of time.

"RL" wrote:

> I think all of the information I need can be captured by profiler using the
> ExecutionPlan or Show Plan All events, but again, it's not feasible to run
> for an extended period of time.
>
> Show plan all captures some interesting data that gets presented, though I
> have not found a way to extract this data from a trace file or trace table --
> only view it  through profiler.
> PhysicalOp,  LogicalOp, Argument  are pieces of data I would like to see in
> columns so that I could filter and query on them.
>
> "RL" wrote:
>
> > I'm looking for a counter that will help me identify which indexes are used
> > and how frequently they are used.  My goal is to remove indexes that are not
> > used.
> >
> > The number of transactions on our database makes it impossible to store
> > profiler information / Index Tuning Wizard for an extended period of time.

"Linchi Shea" wrote:

> If you dump out all the execution plans to a text file, you can use the
> following little Perl script (or a variation of it) to produce an index usage
> summary report:
>
> ####Begin Perl script####
>
> #!perl -w
> # Author:        Linchi Shea
> # Last Modified: 2002/02/12
> #
> # The code is tested with ActiveState Perl version 5.005_03, Binary Build 522.
>
> use strict;
>
> # get command line parameters
> my ($query_plan, $db, $server) = @ARGV;
> unless (-e $query_plan) {
>    print "Usage:\n";
>    print "\tPerl IndexSummary.pl <QueryPlan log> [<database> <server>]\n";
>    exit;
> }
>
> # get the indexes and operators from query plans
> my $idxRef = getIdxOperators($query_plan);
>
> # print a summary of the indexes/operators
> printIdxOperators($db, $idxRef);
>
> # get and print unused indexes
> if ($server && $db) {
>    my $unIdxRef = getUnusedIdx($server, $db, $idxRef);
>    printUnusedIdx($db, $unIdxRef);
> }
>
> exit;
>
> sub getIdxOperators {
>     my ($query_plan) = shift;
>    
>     open(LOG, "$query_plan")
>        or die "***Err: could not open $query_plan.";
>
>     my $idxRef;
>     while(<LOG>) {
>     
>     # e.g. looking for
>     # Index Scan(OBJECT:([pubs].[dbo].[jobs].[jobs_ix])
>       if (m{^[\s | \d | \|]*\|\-\-
>                        ( Clustered\s+Index\s+Seek     |
>                          Clustered\s+Index\s+Scan     |
>                          Clustered\s+Index\s+Delete   |
>                          Clustered\s+Index\s+Update   |
>                          Clustered\s+Index\s+Insert   |
>                          Index\s+Seek                 |
>                          Index\s+Scan                 |
>                          Index\s+Delete               |
>                          Index\s+Update               |
>                          Index\s+Insert               |
>                          Table\s+Scan
>                        )      
>                        \(OBJECT\:\(
>                          (  
>                            (\[([^\]] | \]\])+\])
>                             \.\[([^\]] | \]\])+\]
>                             \.\[([^\]] | \]\])+\]
>                             (\.\[([^\]] | \]\])+\])?
>                          )
>                        \)
>          }ix) {
>
>         # count the index and operator usage
>         $idxRef->{$3}->{$2}->{count}++;
>         $idxRef->{$3}->{$2}->{ops}->{$1}->{count}++;
>       }
>     }
>     close(LOG);
>     
>     return $idxRef;
> } # getIdxOperators
>
> sub printIdxOperators {
>     my ($database, $idxRef) = @_;
>
>     # system tables to be exclused from summary
>     my @systables = qw(sysobjects syscolumns syscomments
>                        sysindexes sysdepends sysreferences
>                        sysfiles sysfilegroups syspermissions
>                        sysprotects sysindexkeys
>                        sysforeignkeys sysallocations sysusers
>                        systypes);
>
>     print "\nIndex summary for database $database...\n\n";
>     printf " %-45s %-5s %-7s %-25s\n", 'Index/Table', 'Total', 'Op Cnt',
> 'Operator';
>     printf " %-45s %-5s %-7s %-25s\n", '-' x 45, '-' x 5, '-' x 7, '-' x 25;
>     foreach my $db (keys %{$idxRef}) {
>       next if $db ne "[$database]";
>
>       foreach my $idx (sort keys %{$idxRef->{$db}}) {
>         # skip system table indexes
>         next if grep {$idx =~ /\[dbo\]\.\[$_\]/i} @systables;
>            
>           printf " %-45s %s\n", $idx, $idxRef->{$db}->{$idx}->{count};
>           foreach my $op (sort keys %{$idxRef->{$db}->{$idx}->{ops}}) {
>              printf " %-45s %-5s %-7s %-25s\n", ' ' x 45, ' ' x 5,
>                     $idxRef->{$db}->{$idx}->{ops}->{$op}->{count}, $op;
>           }
>        }
>     }
> } # printIdxOperators
>
> sub getUnusedIdx {
>     my ($server, $database, $idxRef) = @_;
>  
>     # get indexes from the database
>     my $sql = "use $database " .
>               q/set nocount on
>               select   '[' + replace(db_name(), ']', ']]') + ']' + '.'
>                    + '[' + replace(user_name(o.uid), ']', ']]') + ']' + '.'
>                    + '[' + replace(o.name, ']', ']]') + ']' + '.'
>                    + '[' + replace(i.name, ']', ']]') + ']'
>                from sysindexes i, sysobjects o
>               where i.indid > 0 and i.indid < 255 and (i.status & 64)=0
>                 and o.id = i.id
>                 and OBJECTPROPERTY(o.id, 'IsMSShipped') <> 1/;
>
>     my @result = `osql -S$server -E -Q\"$sql\" -n -h-1 -l2`;
>     my @allIndexes;
>     foreach (@result) {
>         s/\s+$//;
>         s/^\s+//;
>         next if /^\s*$/;
>         push @allIndexes, $_;
>     }
>    
>     # get the list of indexes used in the query plan    
>     my @usedIndexes = ();
>     foreach my $db (keys %$idxRef) {
>          next unless ($db eq $database) or ($db =~ /\[$database\]/);
>          @usedIndexes = keys %{$idxRef->{$db}};
>     }
>
>     # compare the two lists to get a list of unused indexes
>     getSetDiff(\@allIndexes, \@usedIndexes);   
> } # getUnusedIdx
>
> sub printUnusedIdx {
>     my ($database, $unusedIdxRef) = @_;
>
>     if (scalar @$unusedIdxRef) {
>          print "\nUnused indexes in database $database...\n\n";
>          foreach (sort @$unusedIdxRef) {
>             print " $_\n";
>          }
>     }
>     else {
>          print "\nNo unused indexes in database $database.\n";
>     }
> } # printUnusedIdx
>
> sub getSetDiff {
>     my($setRef1, $setRef2) = @_;
>     my %mark = ();
>     my @diff = ();
>
>     grep($mark{$_}++, @$setRef2);
>     @diff = grep(!$mark{$_}, @$setRef1);
>     return \@diff;
> } # getSetDiff
>
> ####End Perl script####
>
> Linchi
>
>
> "RL" wrote:
>
> > I think all of the information I need can be captured by profiler using the
> > ExecutionPlan or Show Plan All events, but again, it's not feasible to run
> > for an extended period of time.
> >
> > Show plan all captures some interesting data that gets presented, though I
> > have not found a way to extract this data from a trace file or trace table --
> > only view it  through profiler.
> > PhysicalOp,  LogicalOp, Argument  are pieces of data I would like to see in
> > columns so that I could filter and query on them.
> >
> > "RL" wrote:
> >
> > > I'm looking for a counter that will help me identify which indexes are used
> > > and how frequently they are used.  My goal is to remove indexes that are not
> > > used.
> > >
> > > The number of transactions on our database makes it impossible to store
> > > profiler information / Index Tuning Wizard for an extended period of time.

"RL" <R*@discussions.microsoft.com> wrote in message
news:73135CA9-BF5F-4ED9-8B70-3CCCC1E3A6D3@microsoft.com...
> I'm looking for a counter that will help me identify which indexes are
> used
> and how frequently they are used.  My goal is to remove indexes that are
> not
> used.
>
> The number of transactions on our database makes it impossible to store
> profiler information / Index Tuning Wizard for an extended period of time.

"Adam Machanic" wrote:

> If you're running SQL Server 2005, you can look at the
> sys.dm_db_index_usage_stats DMV.  In SQL Server 2000, there is unfortunately
> no good way to get this data.
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
> "RL" <R*@discussions.microsoft.com> wrote in message
> news:73135CA9-BF5F-4ED9-8B70-3CCCC1E3A6D3@microsoft.com...
> > I'm looking for a counter that will help me identify which indexes are
> > used
> > and how frequently they are used.  My goal is to remove indexes that are
> > not
> > used.
> >
> > The number of transactions on our database makes it impossible to store
> > profiler information / Index Tuning Wizard for an extended period of time.
>
>
>