How to make Automated?

Question

How to make Automated?

EasyBoy

SSCertifiable

Points: 5301
More actions
August 25, 2010 at 6:49 am

#387655

Hi ,
I do have one script for ununsed index, listed below.
We have nearly 50-60 Database.
Everyweek i have to run this script for each and every database and store that result to one table like
Unused_Index_Wk1,Unused_Index_Wk2 and so on.
Then later i compare Wk1 with Wk2 to see which index are unchanged and i have to drop the index that are unchanged.
So my question is, how to make that automated (rather everyweek i have to run the script for each and every database)?
Is there any way to make it automated, so i will get the output store in any table?
Your response will be greatly appreicated.
Thanks for your help in advance.
Script:
SELECTOBJECT_NAME(A.object_id) AS table_name,
si.name AS index_name,
sind.used/128 AS size_mb,
(A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) AS index_hits,
A.user_updates AS updates
FROMSYS.DM_DB_INDEX_USAGE_STATS A INNER JOIN sysindexes sind
ON A.index_id = sind.indid AND a.object_id = sind.id
INNER JOIN sys.indexes si
ON A.index_id = si.index_id AND a.object_id = si.object_id
INNER JOIN sys.objects so
ON A.object_id = so.object_id
WHEREdatabase_id = DB_ID()
AND (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) = 0
AND so.TYPE = 'U'-- EXCLUDE SYSTEM TABLES
AND si.IS_UNIQUE = 0-- NO UNIQUE INDEXES
AND si.TYPE = 2-- NONCLUSTERED INDEXES ONLY
AND si.IS_PRIMARY_KEY = 0-- NO PRIMARY KEYS
AND si.IS_UNIQUE_CONSTRAINT = 0-- NO UNIQUE CONSTRAINTS
AND si.IS_DISABLED = 0-- ONLY ACTIVE INDEXES
ORDER BY OBJECT_NAME(A.object_id),
A.index_id

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

We have many procs that need to run in multiple databases. What we do is use MultiScript to push those out to the databases.

The procs are then run by an SQL Agent job that calls one master proc with statements to run each of the database specific procs.

Does that make sense?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

EasyBoy (8/25/2010)
Then later i compare Wk1 with Wk2 to see which index are unchanged and i have to drop the index that are unchanged.

What happens to indexes that are only needed for the month-end processes?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 3

jerry-621596 (8/25/2010)
We have many procs that need to run in multiple databases. What we do is use MultiScript to push those out to the databases.
The procs are then run by an SQL Agent job that calls one master proc with statements to run each of the database specific procs.
Does that make sense?

Does that mean i have to create a job in each and every database?

Can you please explain in details?

Thanks

EasyBoy SSCertifiable Points: 5301 More actions · Answer 4

GilaMonster (8/25/2010)
EasyBoy (8/25/2010)
Then later i compare Wk1 with Wk2 to see which index are unchanged and i have to drop the index that are unchanged.
What happens to indexes that are only needed for the month-end processes?

We are doing on monthly bases as well.

I have created one table on 1st week of the month, middle of the month and one at last.

Then we have to look for the index that doesn't used at all.

I hope that will be clear for you.

TakeITeasy SSC Enthusiast Points: 181 More actions · Answer 5

Hi,

I would suggest you to create stored procedure with your script and setup job in SQL server agent.

For multiple database just update databasename1 --> your db name

SELECT OBJECT_NAME(A.object_id) AS table_name,

si.name AS index_name,

sind.used/128 AS size_mb,

(A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) AS index_hits,

A.user_updates AS updates

FROM databasename1.SYS.DM_DB_INDEX_USAGE_STATS A INNER JOIN databasename1.sysindexes sind

ON A.index_id = sind.indid AND a.object_id = sind.id

INNER JOIN databasename1.sys.indexes si

ON A.index_id = si.index_id AND a.object_id = si.object_id

INNER JOIN databasename1.sys.objects so

ON A.object_id = so.object_id

WHERE database_id = DB_ID()

AND (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) = 0

AND so.TYPE = 'U' -- EXCLUDE SYSTEM TABLES

AND si.IS_UNIQUE = 0 -- NO UNIQUE INDEXES

AND si.TYPE = 2 -- NONCLUSTERED INDEXES ONLY

AND si.IS_PRIMARY_KEY = 0 -- NO PRIMARY KEYS

AND si.IS_UNIQUE_CONSTRAINT = 0 -- NO UNIQUE CONSTRAINTS

AND si.IS_DISABLED = 0 -- ONLY ACTIVE INDEXES

ORDER BY OBJECT_NAME(A.object_id),

A.index_id

hope this would help you..

Regards,

Raj