Prev: transation file
Next: SQL Server 2005 Failure - Logical consistency-based I/O error
From: Ooogy on 27 May 2010 11:54
While I'm very aware of the plethora of scripts out there utilizing
2005/2008's dmvs to display underused, most used, & most needed
indexes, I have the need to see if I can determine through internals
exactly what indexes are being used by what procs. Meaning; if I have
a database with 100 different stored procedures being called
regularly, can I list what indexes have been used by these calls? My
ultimate goal is to help know what indexes may need tuning the most by
simply knowing it's used by many different procs calling it. I hope
I'm making sense.

Thanks in advance for any assistance you may provide...Ooogy
From: John Bell on 27 May 2010 16:30
On Thu, 27 May 2010 08:54:23 -0700 (PDT), Ooogy <ooogaleee1(a)yahoo.com>
wrote:

>While I'm very aware of the plethora of scripts out there utilizing
>2005/2008's dmvs to display underused, most used, & most needed
>indexes, I have the need to see if I can determine through internals
>exactly what indexes are being used by what procs. Meaning; if I have
>a database with 100 different stored procedures being called
>regularly, can I list what indexes have been used by these calls? My
>ultimate goal is to help know what indexes may need tuning the most by
>simply knowing it's used by many different procs calling it. I hope
>I'm making sense.
>
>Thanks in advance for any assistance you may provide...Ooogy

Hi

Indexes are only one tool that you can use to help performance tune
slowly performing procedures. The most common performance problems for
a procedure tends to be poorly written SQL. Once you have located the
procedure and determined the code is optimal you can look finding what
part of the procedure is slow, look at the query plans to determine if
better indexing could help etc.

John
From: Erland Sommarskog on 27 May 2010 18:08
Ooogy (ooogaleee1(a)yahoo.com) writes:
> While I'm very aware of the plethora of scripts out there utilizing
> 2005/2008's dmvs to display underused, most used, & most needed
> indexes, I have the need to see if I can determine through internals
> exactly what indexes are being used by what procs. Meaning; if I have
> a database with 100 different stored procedures being called
> regularly, can I list what indexes have been used by these calls? My
> ultimate goal is to help know what indexes may need tuning the most by
> simply knowing it's used by many different procs calling it. I hope
> I'm making sense.

It is possible - but it is a lot of work. In the query plans you find
in sys.dm_exec_query_plan(), the indexes are named. But you have to
write a lot of XQuery to extract the information. And, no, I definitely
will not show an example.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: transation file
Next: SQL Server 2005 Failure - Logical consistency-based I/O error