Knowing your indexes


This following query gives you
list of all the indexes
,the tables that they reside in
,how many times they’re been searched
,how many times they’ve been updated
,and the size of the indexes in memory.


SELECT 
	sch.name + '.' + t.name AS [Table Name], 
	i.name AS [Index Name], 
	i.type_desc,  
	ISNULL(user_updates,0) AS [Total Writes], 
	ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
	s.last_user_seek, 
	s.last_user_scan ,
	s.last_user_lookup,
	ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],  
	p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)  
	LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s	WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,'IsUserTable') = 1  
	INNER JOIN		sys.tables					AS t	WITH (NOLOCK) ON i.object_id = t.object_id  
	INNER JOIN		sys.schemas					AS sch	WITH (NOLOCK) ON t.schema_id = sch.schema_id  
	LEFT OUTER JOIN sys.dm_db_partition_stats	AS p	WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
	--AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used  
	--AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used  
	--AND i.index_id > 1			-- Only non-first indexes (I.E. non-primary key)
	--AND i.is_primary_key<>1		-- Only those that are not defined as a Primary Key)
	--AND i.is_unique_constraint<>1 -- Only those that are not classed as "UniqueConstraints".  
ORDER BY [Table Name], [index name]

Source : Internet

Advertisements

I should’ve looked the other way

I am feeling exactly the same as Rob Farley must have felt 🙂

© 2011 Rob Farley

Verse 1:
My query sucks – it takes too long
So long I wrote this song
The plan’s not big – it ain’t a giant
And yet I have an angry client
Performance now has made her weary
So I’ve come in to fix her query
I promise I won’t ever fail her
Say “Trust me, love, I’m from Australia!”

Chorus:
I need to find you
But I don’t want to search every row
My predicate’s residual
My seek just runs too slow
I thought I’d caught a glimpse of you
Been searching for all day
But all along, I’d done it wrong
I should’ve looked the other way

Verse 2:
A trace is on, I know the reads
That fetch the bytes the query needs
There’s spooling from a CTE
They’ve got recursion needlessly
I need to dig a little further
I worry there might be a cursor
The DBA has the plan_handle
He says it’s not corrupt, he knows Paul Randal!

Repeat chorus

Bridge:
There is an index covering predicates with keys
But my developer has used inequalities
There is a range scan
Hiding truth
Hiding cost
Hiding you…

Repeat chorus x2

I should’ve looked the other way