unused index on sql

NavStudent

Hello

Navision erp system have a lot of index. Sql might only use 70 percent of the index. I would like to know if there is a way to get index statistics on how many times they've been used.

Marije_Brummel

Use sys.dm_db_index_usage_stats on SQL 2005 for that.

If you have the SQL Perform tools I have a great script + codeunit that automaticaly disables those indexes in Navison.

NavStudent

This view has the index info since the last time the sql server has been rebooted.

is there a way I can do a joint on find the table Name?

Where can I find the relationship between object_id and table name?

NavStudent

Found this tsql that does what I was looking for.

Declare @dbid int

Select @dbid = db_id('Northwind')

Select  objectname=object_name(i.object_id)

                        , indexname=i.name, i.index_id

from sys.indexes i, sys.objects o

where objectproperty(o.object_id,'IsUserTable') = 1

and i.index_id NOT IN (select s.index_id

       from sys.dm_db_index_usage_stats s

               where s.object_id=i.object_id and

                        i.index_id=s.index_id and

                        database_id = @dbid )

and o.object_id = i.object_id

order by objectname,i.index_id,indexname asc

NavStudent

---

unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

So for indexes just look at not equal to 0 or 1

NavStudent

modified a little and I have what I need.

Declare @dbid int

Select @dbid = db_id('Northwind')

Select  objectname=object_name(i.object_id)

                        , indexname=i.name, i.index_id

from sys.indexes i, sys.objects o

where objectproperty(o.object_id,'IsUserTable') = 1

and i.index_id NOT IN (select s.index_id

       from sys.dm_db_index_usage_stats s

               where s.object_id=i.object_id and

                        i.index_id=s.index_id and

                        database_id = @dbid )

and o.object_id = i.object_id

and i.index_id >= 2
order by objectname,i.index_id,indexname asc

Waldo

It's based on a DM-view .. and like you said, that info is from the time the server was rebooted (or de SQL Service was restarted). That is very important.

The query that Mark was talking about, is a slight enhancement on this one. We incorperated the NAV information that can be easily extraced from SQLPerform views into a query like yours. On top of that, we created a codeunit to disable the keys ... . But again, if you base this codeunit on statistics of two days ... it's getting quite dangerous. That, you probably understand .

kriki

Waldo wrote:

It's based on a DM-view .. and like you said, that info is from the time the server was rebooted (or de SQL Service was restarted). That is very important.

The query that Mark was talking about, is a slight enhancement on this one. We incorperated the NAV information that can be easily extraced from SQLPerform views into a query like yours. On top of that, we created a codeunit to disable the keys ... . But again, if you base this codeunit on statistics of two days ... it's getting quite dangerous. That, you probably understand .

If you keep your customer constantly monitored (for at least a few days) you can delete a key without problems, if a user then tells you a certain function he uses is slower then before, you can check it with the client monitor and directly create the index.

Waldo

True ... that's part of the methodology we use.
But it doesn't stand when using the script we were talking about. We ran it for the first time at one of our customers ... and it disabled about 160 keys. I wouldn't have wanted to do that if the statistics were only based on two days of monitoring.

colin_leversuch-roberts

I'm in the process of posting a series of articles on this subject; here are the starting posts - I hope to have ther next two parts posted by the end of next week ( these posts directly relate to my support of navision )

http://sqlblogcasts.com/blogs/grumpyold ... art-1.aspx
http://sqlblogcasts.com/blogs/grumpyold ... art-1.aspx
http://sqlblogcasts.com/blogs/grumpyold ... art-2.aspx

I actually captured 72 days data before any analysis, there is a monthly cycle in our use of navision - and they tried to drop indexes before I started work here with disasterous consquences- so I've had to go fairly easy. If you do go to my blog you might want to check out the post concerning TokenAndPermUserStore - I still have an incident open with microsoft on this despite being at sql version 3179

thaug

Also look for a tool here in the downloads section called Unused Keys. This doesn't examine SQL at all, but it does examine the code to look for keys that are never specified anywhere. I've used this on a limited set of tables to squeeze out extra performance.

cnicola

thaug wrote:

Also look for a tool here in the downloads section called Unused Keys. This doesn't examine SQL at all, but it does examine the code to look for keys that are never specified anywhere. I've used this on a limited set of tables to squeeze out extra performance.

Does your code take into consideration flowfields and calcsums?

thaug

Not my code!

This tool, which also can be used on Native, is a tool to just find keys that are seemingly no longer needed based on the code of all your objects. I've used this on some tables, but rather than delete or inactivate a key, I have changed the key properties to not maintain the SQL index. This is definitely not a set it and forget it tool, you need some reasonable knowledge as to how your system works with any customizations. It did allow me to cut down on some indexes that were carried over from upgrades over the years.

cnicola

thaug wrote:

Not my code!

This tool, which also can be used on Native, is a tool to just find keys that are seemingly no longer needed based on the code of all your objects. I've used this on some tables, but rather than delete or inactivate a key, I have changed the key properties to not maintain the SQL index. This is definitely not a set it and forget it tool, you need some reasonable knowledge as to how your system works with any customizations. It did allow me to cut down on some indexes that were carried over from upgrades over the years.

Ok ... Does this tool take into consideration flowfields and calcsums?
I don't care much about which keys are used since I can tell that easily on SQL 2005.
But everytime I try to disable a key (or worse change it) I always wonder where else it is used. NDT works reasonably but not that great as far as I recall (though I admit haven't tried it in the latest version).
So if it looked at all the code and include possible use by calcsums and flowfields I think would be a really cool solution.

DenSter

That's why you shouldn't disable the whole key, just the MaintainSQLIndex property.

cnicola

DenSter wrote:

That's why you shouldn't disable the whole key, just the MaintainSQLIndex property.

Hi Daniel,

I normally do not disable a key. I meant exactly what you said: I take off MaintainSQLIndex.
But sometimes you do need to change it or replace it:

1. Due to the stupid Order By that Navision sends along with Setcurrentkey sometimes I do need to redesign the Navision key as well not just the SQL index to obtain maximum performance (or at least convince SQL that it should be using the intelligent SQL Index I just defined on it).
2. On Sales Line for instance due to legacy code and such I already have 40 keys (dont worry I only have about 5 enabled ). But when I do need to add another key I have to take out one of the existing ones and aside from "Let's do it and see if anyone gets any errors" :oops: I don't have a better idea.