I have queried sys.dm_db_index_usage_stats and found an index that has a hig
h
number of IndexLookups vs. IndexSeeks. This index is on a table that is
refernced in over 1200 stored procedures. The index is comprised of a single
column.
I could examine each of these 1200 stored procedures individually to see wha
t
additional columns might be added or included in the index to reduce the
lookups, or run Profiler and include Event Performance > ShowPlan Text and
search for the table or index in ShowPlan Text, but neither of these options
thrill me.
Any other ideas on how one might go about determining the additional columns
needed to cover these lookups?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1cbrichards via droptable.com,
That index could be the clustered index for that table. I do not think you
want to add more columns to the key of the clustered index. The look_up is
from a nonclustered to a clustered, so you will have to identify first the
nonclustered indexes participating in the look_up and see if you can add mor
e
columns, either to the key or to the leaf nodes using the new feature
"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
AMB
"cbrichards via droptable.com" wrote:
> I have queried sys.dm_db_index_usage_stats and found an index that has a h
igh
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a sing
le
> column.
> I could examine each of these 1200 stored procedures individually to see w
hat
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these optio
ns
> thrill me.
> Any other ideas on how one might go about determining the additional colum
ns
> needed to cover these lookups?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>|||Perhaps there is anything in the missing_indexes DMV's?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6ed5f925db6ae@.uwe...[vbcol
=seagreen]
>I have queried sys.dm_db_index_usage_stats and found an index that has a hi
gh
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a sing
le
> column.
> I could examine each of these 1200 stored procedures individually to see w
hat
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these optio
ns
> thrill me.
> Any other ideas on how one might go about determining the additional colum
ns
> needed to cover these lookups?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>[/vbcol]|||Thanks Alejandro. You are correct...this is a clustered index.
So I believe you have clarified a misunderstanding on my part. I thought the
User_Lookups value returned from sys.dm_db_index_usage_stats was the number
of times the index itself caused a lookup, but from your statement, it
appears the User_Lookups value is the number of times the index is used by
other indexes to lookup information. Is that a correct understanding?
Alejandro Mesa wrote:[vbcol=seagreen]
>cbrichards via droptable.com,
>That index could be the clustered index for that table. I do not think you
>want to add more columns to the key of the clustered index. The look_up is
>from a nonclustered to a clustered, so you will have to identify first the
>nonclustered indexes participating in the look_up and see if you can add mo
re
>columns, either to the key or to the leaf nodes using the new feature
>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>AMB
>
>[quoted text clipped - 9 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||It may just be a misunderstanding in the way indexes are organized. Remember
that the clustered index IS the data. So anytime you are going to the actual
data pages, you are using the clustered index.
It's not clear what you mean by 'the index caused a lookup'.
The clustered index keys are used as the bookmark values in nonclustered
indexes. So anytime a nonclustered index is used to find the data you need,
SQL Server will find the corresponding clustered key in the nonclustered
index,and then use it to find the row in the clustered index (the data).
So user_lookups is the total number of times the index was used for a
lookup. And for a clustered index, the number will probably be bigger than
for any other index, because you'll have all the queries that use the
clustered index key in the user query, plus all the queries that use a
nonclustered index to get to the data.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ed74f8c473ce@.uwe...
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought
> the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the
> number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>|||cbrichards via droptable.com,
That is my understanding also, but I have not found anything in BOL to
support that. You can do some test in your local computer (personal - NOT
PRODUCTION). Restart the db engine, and execute this:
use northwind
go
-- index seek on PK_Orders (clustered)
select *
from dbo.orders
where orderid = 10250
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where orderdate = '19980101'
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where employeeid = 5 and customerid= cast(N'WARTH' as nchar(5))
select *
from sys.dm_db_index_usage_stats
go
AMB
"cbrichards via droptable.com" wrote:
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought t
he
> User_Lookups value returned from sys.dm_db_index_usage_stats was the numbe
r
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>
Monday, March 12, 2012
Index Lookups
Labels:
database,
dm_db_index_usage_stats,
highnumber,
index,
indexlookups,
indexseeks,
lookups,
microsoft,
mysql,
oracle,
queried,
server,
sql,
sys,
table
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment