Need Scripts
-
Need Scripts to identify below...
1) Script to Identify what Indexes are set on tables.
2) Script to Identify unwanted Indexes are set on tables.
3) Script to Identify what new recommended Indexes needs to be set on tables.
4) Script to Check fragmentation of DB & tables & how to resolve the issue..
please give an understanding for this.. coz google search gives hell lots of scripts..
:w00t:
************************************
Every Dog has a Tail !!!!! :-D -
Forgot to add, I found this script here; it might be useful?
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC
-
If you're looking for "finished" scripts, I've found these to be useful.
Kimberly Tripp's sp_helpindex: http://www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites/
Michelle Ufford's Index Defrag script: http://sqlfool.com/2011/06/index-defrag-script-v4-1/
Hope this helps.
_____________________________________________________________________
@nate_hughes
- Nate -
No it doesn't, not for the pdf.
To clarify, when I say pdf I mean the downloadable version, which is free. The book does cost money if you buy it from Amazon, yes.
-
Hi Beatrix, i have limited access to Links out .. Would appreciate if you share any of such if you have downloded 🙁
************************************
Every Dog has a Tail !!!!! :-D -
I would recommend that you review Glenn Berry's diagnostic queries. He has different scripts for each of the relevant versions of SQL Server.
http://sqlserverperformance.wordpress.com/tag/dmv-queries/
Likely, the two most relevant queries for your question are:
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
AND user_seeks + user_scans + user_lookups = 0
ORDER BY 1,[Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
-- Consider your complete workload
-- Investigate further before dropping an index
-- Missing Indexes current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC;
A word of CAUTION: Always TEST ANYTHING thoroughly before even thinking about running it in production. The index you're thinking about dropping may only be used once or month or once a quarter for a single report and not show up in the above listed queries. You may want to persist the data from these queries over a period of time for future evaluation since the data from dm_db_missing_index_details (and many other DMV's) is only valid as of the last SQL Server restart.
-
You could try Brent Ozar's spBlitzIndex
-
I downloaded Brents script.. its huge & didnget which to use from them..
can anyone help me with scripts one by one for below need 🙁
1) Script to Identify what Indexes are set on tables.
2) Script to Identify unwanted Indexes are set on tables.
3) Script to Identify what new recommended Indexes needs to be set on tables.
4) Script to Check fragmentation of DB & tables & how to resolve the issue..
************************************
Every Dog has a Tail !!!!! :-D -
:w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:vv
Thanks for the SCRIPT:w00t::w00t:
************************************
Every Dog has a Tail !!!!! :-D -
??....why the "shouting"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
OnlyOneRJ (10/12/2013)
I downloaded Brents script.. its huge & didnget which to use from them..can anyone help me with scripts one by one for below need 🙁
1) Script to Identify what Indexes are set on tables.
2) Script to Identify unwanted Indexes are set on tables.
3) Script to Identify what new recommended Indexes needs to be set on tables.
4) Script to Check fragmentation of DB & tables & how to resolve the issue..
1) Script to Identify what Indexes are set on tables.....there are many available...Google and select which meets your requirements......eg all databases/specific database/specific table etc and the level of detail you require
2) Script to Identify unwanted Indexes are set on tables.
3) Script to Identify what new recommended Indexes needs to be set on tables.
the script from Brent Ozar does give you what you need for 2)/3) above.....did you view the video on the site to explain usage?
4) Script to Check fragmentation of DB & tables & how to resolve the issue
try here
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston SQL (10/12/2013)
??....why the "shouting"?No, m=not shouting.. just went Wacko...:w00t:
as i thought member 'motherstechbpo' provided me script.. but he rather posted his jobs and vacancy info 😀
************************************
Every Dog has a Tail !!!!! :-D
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply