May 27, 2008 at 6:08 am
Hi,
We currently uses an SSIS package to import data from a flat file, every few minutes. These files can be very large (>100,000 records) and the process can bog down a bit.
We have located the issue to 1 particular table, that has about a dozen indexes on it. We would like to remove some of the un-used indexes but want to make sure they are un-used before doing anything.
I have written a query that looks at the sysComments table to search the SP's and find the column name used in any clauses where it might be used, which looks something like:
DECLARE @vColumnRequired VARCHAR(50)
SET @vColumnRequired = 'ColumnName1'
SELECT [name]
FROM pass01.dbo.syscomments sc WITH(NOLOCK)
INNER JOIN
pass01.dbo.sysObjects so WITH(NOLOCK)
ON so.id = sc.id
WHERE [text] LIKE '%and ' + @vColumnRequired + '%'
OR [text] LIKE '%and ' + @vColumnRequired + '%'
OR [text] LIKE '%where ' + @vColumnRequired + '%'
OR [text] LIKE '%where ' + @vColumnRequired + '%'
OR [text] LIKE '%or ' + @vColumnRequired + '%'
OR [text] LIKE '%or ' + @vColumnRequired + '%'
ORDER BY
[name]
(they are not used on any joins).
This works ok, but when I looked at the help file, I noticed that the [text] field in syscomments only holds the 1st 4000 characters of the SP. Therefore this could miss anything that is longer.
Is there a better way of doing it than this, and I'm guessing there probably is!
Thanks in advance...
May 27, 2008 at 6:20 am
Why don't you just query the DMV sys.dm_db_index_usage_stats. It will show you which idexes have been used and how (Seek vs. scan)and even when it was used the last time.
Only thing to keep in mind is because it's a dynamic view, the system has to be running a while before the values become meaningfull.
[font="Verdana"]Markus Bohse[/font]
May 27, 2008 at 7:15 am
That's fantastic - exactly what I needed...
We're only a small company and only have 2 developers, no DBA's so have a hole in our knowledge when it comes to this sort of stuff :rolleyes:
Thanks
May 27, 2008 at 3:19 pm
Here is a query you can use.
Make sure your SQL instance has been up for at least 1 business cycle (week/month/longer???) before you start eliminating indexes! :w00t:
SELECT
OBJECT_NAME( s.[object_id] ) AS TableName
, i.name AS IndexName
, s.user_updates
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE
db_name(s.database_id) = db_name(db_id())
AND objectproperty(i.object_id, 'IsIndexable') = 1
AND LEFT(i.name, 3) NOT IN ('PK_', 'UC_')
AND s.user_updates > 0 AND s.user_seeks = 0
AND s.user_scans = 0 AND s.user_lookups = 0
ORDER BY
OBJECT_NAME( s.[object_id] )
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 28, 2008 at 3:14 am
Hi, call me stupid if you want but I like automating/automated things. What I do is run the database tuning advisor and that tells me exactly where I should delete/update/insert indexes and statistics. I had a problem on my database that all the queries on the web application linked to the database was very slow. Ever since I started running the tuning advisor and applied its recommendations, rebuild indexes and statistics on a regular basis I have no problems and all the unused indexes was deleted and new (very necessary) indexes and statistics was created.
I might just note that I am a single developer in a small company and not a dba at all but as you rightly said that hole has to be filled. Hope this is more helpful than stupid!:hehe::hehe::hehe:
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 28, 2008 at 8:08 am
Manie Verster (5/28/2008)
Hi, call me stupid if you want but I like automating/automated things. What I do is run the database tuning advisor and that tells me exactly where I should delete/update/insert indexes and statistics. I had a problem on my database that all the queries on the web application linked to the database was very slow. Ever since I started running the tuning advisor and applied its recommendations, rebuild indexes and statistics on a regular basis I have no problems and all the unused indexes was deleted and new (very necessary) indexes and statistics was created.I might just note that I am a single developer in a small company and not a dba at all but as you rightly said that hole has to be filled. Hope this is more helpful than stupid!:hehe::hehe::hehe:
SQL Server 2005 DMVs are a fantastic resource.
Learn them, use them, love them! 😀
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 28, 2008 at 9:03 am
We've managed to remove half of our indexes which has reduced the time the imports take considerably... So thanks for the help.
As for sql performance tuner, I haven't had time to look at it, but I'll try next time I get a few minutes :ermm:
Its usually a case of getting it to work with minimal impeact and performance tuning can go on the wish list... :blush:
May 28, 2008 at 9:33 am
May I suggest a couple of great links on this topic, ones that have helped me tremendously?
Gathering Unused Index Information:
http://www.grumpyolddba.co.uk/sql2005/working%20with%20indexes%201.mht%5B/url%5D
[b]Uncover Hidden Data to Optimize Application Performance:[/b]
[url]http://msdn.microsoft.com/en-us/magazine/cc135978.aspx">mhtml:http://www.grumpyolddba.co.uk/sql2005/working%20with%20indexes%201.mht%5B/url%5D
Uncover Hidden Data to Optimize Application Performance:
http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 29, 2008 at 12:17 pm
Do you know of a similar query that can be used on a SQL Server 2000 database?
May 29, 2008 at 12:18 pm
There is no such thing for SQL 2000 unfortunately.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 2, 2008 at 2:17 pm
Hi.
I have seen lots of examples on the 2005 index usages DMV, but one thing has eluded me : it only seems shows access stats for the dbo. How could I get the actual stats as run by the SQL user using the index? (web app, different users assigned for admin/user access)
If that does not make sense, here is an example. We have an address table with ca 28million addresses in and only 2 indexes
1. Primary Key clustered on ID
2. Post code non-clustered (I suspect this could benefit from adding the PK column as well)
This table has a lot of inserts, as well as lookups done (ca 4mil inserts since restart, guessing a few hundred K lookups)
A stored procedure executed by the web application looking up customer with a certain postcode uses the index (2) - no doubt! (common sense, query plan, etc tells me this)
YET, looking at the stats, it only increments the user_seeks, user_scans and user_lookups when *I* execute the stored procedure.
The numbers most certainly do not the web user's use of that index.
How can I find the stats for all db users?
Any help would be greatly appreciated, as I mistakenly once dropped an index based on these numbers that most definitely should not have been dropped 🙂
June 2, 2008 at 2:22 pm
Stress (6/2/2008)
Hi.I have seen lots of examples on the 2005 index usages DMV, but one thing has eluded me : it only seems shows access stats for the dbo. How could I get the actual stats as run by the SQL user using the index? (web app, different users assigned for admin/user access)
If that does not make sense, here is an example. We have an address table with ca 28million addresses in and only 2 indexes
1. Primary Key clustered on ID
2. Post code non-clustered (I suspect this could benefit from adding the PK column as well)
This table has a lot of inserts, as well as lookups done (ca 4mil inserts since restart, guessing a few hundred K lookups)
A stored procedure executed by the web application looking up customer with a certain postcode uses the index (2) - no doubt! (common sense, query plan, etc tells me this)
YET, looking at the stats, it only increments the user_seeks, user_scans and user_lookups when *I* execute the stored procedure.
The numbers most certainly do not the web user's use of that index.
How can I find the stats for all db users?
Any help would be greatly appreciated, as I mistakenly once dropped an index based on these numbers that most definitely should not have been dropped 🙂
That certainly hasn't been my impression.
Do you have VIEW SERVER STATE permission?
see sys.dm_db_index_usage_stats: http://msdn.microsoft.com/en-us/library/ms188755.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 2, 2008 at 2:40 pm
Marios Philippopoulos (6/2/2008)
Stress (6/2/2008)
Hi.I have seen lots of examples on the 2005 index usages DMV, but one thing has eluded me : it only seems shows access stats for the dbo. How could I get the actual stats as run by the SQL user using the index? (web app, different users assigned for admin/user access)
If that does not make sense, here is an example. We have an address table with ca 28million addresses in and only 2 indexes
1. Primary Key clustered on ID
2. Post code non-clustered (I suspect this could benefit from adding the PK column as well)
This table has a lot of inserts, as well as lookups done (ca 4mil inserts since restart, guessing a few hundred K lookups)
A stored procedure executed by the web application looking up customer with a certain postcode uses the index (2) - no doubt! (common sense, query plan, etc tells me this)
YET, looking at the stats, it only increments the user_seeks, user_scans and user_lookups when *I* execute the stored procedure.
The numbers most certainly do not the web user's use of that index.
How can I find the stats for all db users?
Any help would be greatly appreciated, as I mistakenly once dropped an index based on these numbers that most definitely should not have been dropped 🙂
That certainly hasn't been my impression.
Do you have VIEW SERVER STATE permission?
see sys.dm_db_index_usage_stats: http://msdn.microsoft.com/en-us/library/ms188755.aspx
Thanks for a quick response.
I log on to the DB as sa, so I guess I would. Also, would it not flat-out deny my query?
More info: the web-users (2, admin/user) are in their own groups, etc, for added security, sprocs have execute permission defined per group...
The user_updates looks accurate, it's just the seeks/scans/lookups that are,imho, WAY off. Am I looking at this wrong?
I just use this simple query to check things:
SELECT i.name,
o.name,
s.*
FROM sys.dm_db_index_usage_stats s
inner join sys.indexes i on i.index_id = s.index_id and i.object_id = s.object_id
inner join sys.objects o on o.object_id = s.object_id
WHERE database_id = DB_ID('MYDBNAMEHERE')
order by user_updates / (user_seeks + user_scans + user_lookups + 1) desc
edit: the row for the address table returns this:
IX_q_Address_lookup q_Address 7 2105058535 2 0 7 0 4390270
giving dbid, objid, user_SEEKS, user_SCANS, user_LOOKUPS and then user_UPDATES
June 2, 2008 at 2:51 pm
I think sys.dm_db_index_usage_stats is sensitive ONLY to index operations in the WHERE clause:
eg. index operations taking place in a JOIN are not counted.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 2, 2008 at 2:54 pm
Marios Philippopoulos (6/2/2008)
I think sys.dm_db_index_usage_stats is sensitive ONLY to index operations in the WHERE clause:eg. index operations taking place in a JOIN are not counted.
Even more reason they should be accurate, seeing as that index is on postcode, and we most certainly have that in the where clause 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply