September 21, 2010 at 6:48 am
Just wanted to share some useful queries in Indexes:
Index count on tables
select object_name(object_id) Table_name,count(*) index_Count from sys.indexes group by object_name(object_id) Order by count(*) desc
Index usage stats
select object_name(a.object_id) Table_Name,name Index_name,a.* from sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b on a.object_id=b.object_id and a.index_id=b.index_id where database_id=db_id() and name='IDX_t_Account_Card_Perf04'
Index Physical Stats
select object_name(object_id) Table_name,* from sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED') order by avg_fragmentation_in_percent desc
Unused Indexes
select object_name(i.object_id) as ObjectName, i.name as [Unused Index],s.user_updates
from sys.indexes i
left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = db_id() where OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1 AND
objectproperty(i.object_id, 'IsIndexable') = 1 AND objectproperty(i.object_id, 'IsIndexed') = 1 and s.index_id is null -- and dm_db_index_usage_stats has no reference to this index
or (s.user_updates >= 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) -- index is being updated, but not used by seeks/scans/lookups
order by object_name(i.object_id) asc
Index ScanDensity and Logical fregmentation
"CREATE TABLE #Index_data(Object_name nvarchar(255),
objectid varchar(50),
IndexName nvarchar(255),
Indexid int,
[Level] int,
Pages int,
[rows] bigint,
MinimumRecordSize int,
MaximumRecordSize int,AverageRecordSize int,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
averageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFregmentation float,
ExtentFregmentation float)
insert into #Index_data
EXEC ('DBCC showcontig WITH TABLERESULTS,no_infomsgs')
select scanDensity,LogicalFregmentation,* from #Index_data a where (scanDensity <70 OR LogicalFregmentation>10) Order by a.ScanDensity desc
drop table #Index_data"
Indexes used by Queries
"WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.query('.'),
i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,
i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,
i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,
i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,
i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,
i.query('.'),
(SELECT DISTINCT cg.value('(@Column)[1]', 'VARCHAR(128)') + ', '
FROM i.nodes('./OutputList/ColumnReference') AS t(cg)
FOR XML PATH('')) AS output_columns,
(SELECT DISTINCT cg.value('(@Column)[1]', 'VARCHAR(128)') + ', '
FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicate//ColumnReference') AS t(cg)
FOR XML PATH('')) AS seek_columns,
i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate
FROM (
SELECT query_plan
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
--SELECT @xml
) as tab (query_plan)
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY n.nodes('.//RelOp') as s(i)
WHERE i.exist('./IndexScan') = 1
AND i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') ='[online_ts2ip_PE_Noida_App]'
AND i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)')='[t_Account_Card]'
AND i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)')='[IDX_t_Account_Card_Perf04_sk]'"
Missing Indexes
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,--improvement_measure is rough indicator of the (estimated) improvement that might be seen if the index was created.
statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle=migs.group_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO
Exact Duplicate Indexes
"with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;"
Partial Duplicate indexes
"with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and ((c1.cols like c2.cols + '%' and SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ')
or (c2.cols like c1.cols + '%' and SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' ')) ;"
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
ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC ;
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 21, 2010 at 7:26 am
You forgot to attribute some of those queries to the people who wrote them
What queries are using the indexes: http://www.sqlservercentral.com/Forums/FindPost966688.aspx Jonathan Kehayias
Missing indexes: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx Bart Duncan
Unused indexes: http://blogs.technet.com/b/vipulshah/archive/2007/12/04/finding-missing-indexes-and-unused-indexes-using-dmvs.aspx Vipul Shah
Duplicate indexes: http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx Paul Nielsen
Possible bad indexes: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!7053.entry Glenn Berry
If you're going to 'share' things that other people have written, you need to attribute them. Otherwise it's borderline plagiarism (presenting other people's work as your own)
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
September 21, 2010 at 7:30 am
Sorry, I surely should have mentioned that....
I picked these queries from net from different sources with some modification here and there from my side.
I really dont remember what exactly the source was, so can't put individual thanks.
Just thought that putting them all together might help someone to have them as reference....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 21, 2010 at 8:07 am
By the way , in Index ScanDensity and Logical fregmentation
, there is some creativity from my side as well 🙂
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 21, 2010 at 9:02 am
GilaMonster (9/21/2010)
You forgot to attribute some of those queries to the people who wrote themWhat queries are using the indexes: http://www.sqlservercentral.com/Forums/FindPost966688.aspx Jonathan Kehayias
Missing indexes: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx Bart Duncan
Unused indexes: http://blogs.technet.com/b/vipulshah/archive/2007/12/04/finding-missing-indexes-and-unused-indexes-using-dmvs.aspx Vipul Shah
Duplicate indexes: http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx Paul Nielsen
Possible bad indexes: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!7053.entry Glenn Berry
If you're going to 'share' things that other people have written, you need to attribute them. Otherwise it's borderline plagiarism (presenting other people's work as your own)
Hey Gail, I'm using wishfull thinking here :w00t: :
Is there a way to take a backup of prod and restore on a dev / test server to be able to run the "Indexes used by Queries" query without hammering the prod servers, and yet get the correct info?
September 21, 2010 at 9:13 am
GilaMonster (9/21/2010)
You forgot to attribute some of those queries to the people who wrote themWhat queries are using the indexes: http://www.sqlservercentral.com/Forums/FindPost966688.aspx Jonathan Kehayias
Missing indexes: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx Bart Duncan
Unused indexes: http://blogs.technet.com/b/vipulshah/archive/2007/12/04/finding-missing-indexes-and-unused-indexes-using-dmvs.aspx Vipul Shah
Duplicate indexes: http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx Paul Nielsen
Possible bad indexes: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!7053.entry Glenn Berry
If you're going to 'share' things that other people have written, you need to attribute them. Otherwise it's borderline plagiarism (presenting other people's work as your own)
Hmmm 2 of the queries are not working (Sql 2005 but compatibility level 80).
Since we are dropping that vendor in 4 months I don't really care about losing support (db performance is horrendus and I've been tasked to make it better for the comming rush).
Is there any risk in upgrading from compatibility level 80 to 90 for that db? The db has been on sql 2005 server for 6+ months after being upgraded from 2000 and we've had no issue whatsoever.
September 21, 2010 at 9:20 am
Ninja's_RGR'us (9/21/2010)
Is there a way to take a backup of prod and restore on a dev / test server to be able to run the "Indexes used by Queries" query without hammering the prod servers, and yet get the correct info?
No. The query queries the procedure cache (in memory)
What you can do, what I've done in the past, is Select into with a query across sys.dm_exec_cached_plans and sys.dm_exec_query_plans, copy that table to another serverm then do the xml crunching at leisure, possibkly even with xml indexes added.
The dumping of the cache into a table shouldn't be that intense, not compared to xquery across unindexed fields.
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
September 21, 2010 at 10:27 am
GilaMonster (9/21/2010)
Ninja's_RGR'us (9/21/2010)
Is there a way to take a backup of prod and restore on a dev / test server to be able to run the "Indexes used by Queries" query without hammering the prod servers, and yet get the correct info?No. The query queries the procedure cache (in memory)
What you can do, what I've done in the past, is Select into with a query across sys.dm_exec_cached_plans and sys.dm_exec_query_plans, copy that table to another serverm then do the xml crunching at leisure, possibkly even with xml indexes added.
The dumping of the cache into a table shouldn't be that intense, not compared to xquery across unindexed fields.
Hmm great alternative.... and it can be scripted for re-use so awesome all around.
Last time I ran that query I busted all 4 processor at 100% for almost 1 hour on the prod server... needless to say the users weren't too happy... and I finnally got a new dev environement out of it :w00t:.
In case you missed it... what about the compatibility upgrade from 80 to 90 in my other post?
September 21, 2010 at 11:34 am
Didn't miss it, but was trying to get out to usergroup meeting.
I wouldn't recommend changing compat level. Could cause queries to change behaviour or break. However, what people often don't realise is that it's the compat level of the database you're running queries in that matters, not the compat level of the database that you're querying.
Hence, run the queries in master and use 3-part naming as necessary.
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
September 21, 2010 at 11:50 am
GilaMonster (9/21/2010)
Didn't miss it, but was trying to get out to usergroup meeting.I wouldn't recommend changing compat level. Could cause queries to change behaviour or break. However, what people often don't realise is that it's the compat level of the database you're running queries in that matters, not the compat level of the database that you're querying.
Hence, run the queries in master and use 3-part naming as necessary.
Holly crap, that worked. Talk about your hidden gems!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply