Geeks With Blogs
Sreenivas Mogullapalli Microsoft Technologies Geek

-- Query to identify the missing indexes in t-sql of sql server

-- from the dynamic views

BEGIN

-- Do not lock anything, and do not get held up by any locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 20

st.text AS [SQL]

, cp.cacheobjtype

, cp.objtype

, DB_NAME(st.dbid)AS [DatabaseName]

, cp.usecounts AS [Plan usage]

, qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%<MissingIndexes>%'

ORDER BY cp.usecounts DESC

END

-- some variations of the same to find some other patterns which need optimization.

1. exec dbo.dba_SearchCachedPlans '%<MissingIndexes>%'
2. exec dbo.dba_SearchCachedPlans '%<ColumnsWithNoStatistics>%'
3. exec dbo.dba_SearchCachedPlans '%<TableScan%'
4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

 

-- to find Top 20 Expensive Queries in sql server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.min_logical_reads, qs.max_logical_reads,

qs.total_elapsed_time, qs.last_elapsed_time,

qs.min_elapsed_time, qs.max_elapsed_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE qt.encrypted=0

ORDER BY qs.total_logical_reads DESC

 

-- to find Who did What When in sql server?

select sys.dm_exec_sessions.session_id,sys.dm_exec_sessions.host_name,sys.dm_exec_sessions.program_name,

sys.dm_exec_sessions.client_interface_name,sys.dm_exec_sessions.login_name,

sys.dm_exec_sessions.nt_domain,sys.dm_exec_sessions.nt_user_name,

sys.dm_exec_connections.client_net_address,

sys.dm_exec_connections.local_net_address,sys.dm_exec_connections.connection_id,sys.dm_exec_connections.parent_connection_id,

sys.dm_exec_connections.most_recent_sql_handle,

(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,

(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,

(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname

from sys.dm_exec_sessions inner join sys.dm_exec_connections

on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id

these, i found on some other sites and posting here for my reference so that i don't miss them

Posted on Wednesday, July 8, 2009 6:41 AM Microsoft , Bookmark | Back to top


Comments on this post: some helpful stuff for identifying which need optimization in sql server

# re: some helpful stuff for identifying which need optimization in sql server
Requesting Gravatar...
Added link to my site.

http://geekswithblogs.net/Rusty/archive/2009/07/09/sql-server-optimization-candidates.aspx
Left by rusty on Jul 09, 2009 11:39 PM

Your comment:
 (will show your gravatar)


Copyright © Sreenivas Mogullapalli | Powered by: GeeksWithBlogs.net