October 27, 2010 at 6:20 am
I am using SQL Server Migration Assistant for Sybase to migrate a few small Sybase db's into SQL Server 2008. (working fine so far)
There is a Sybase VIEW called sysquerymetrics which sources it's data from Sybase's sysqueryplans.
What is the equivalent table in SQL Server 2008 to house abstract query plan info -- found in Sybase's sysqueryplans?
Overview:
Sybase - Query metrics are captured in the default running group (the running group with a group id (gid) of 1) in the sysquerymetrics view. Note that the sysquerymetrics view contains a self join & decoding of the sysqueryplans table, which contains abstract query plan information (among other things).
SSMA for Sybase is not migrating this table to SQL.
October 27, 2010 at 11:43 am
MS SQL Server query plan information is documented in Books OnLine under "Database Engine", "Technical Reference", "Transact-SQL Reference", "System Views" and finally "Execution Related Dynamic Management Views and Functions". You will need to join the views to get a consolidated view of query plan resource usage, such as the below SQL that reports the top 50 stored procedures by resources used:
;Withexec_query_stats as
(selectRANK() OVER ( ORDER BY
CAST(dm_exec_query_stats.total_logical_reads + dm_exec_query_stats.total_logical_writes as numeric(38,0) )
/ dm_exec_query_stats.execution_count DESC
) AS ResourceRank
,dm_exec_query_stats.last_execution_time
,dm_exec_query_stats.creation_time
,dm_exec_query_stats.execution_count
,dm_exec_query_stats.total_logical_reads
,dm_exec_query_stats.min_logical_reads
,dm_exec_query_stats.max_logical_reads
,dm_exec_query_stats.total_logical_writes
,dm_exec_query_stats.min_logical_writes
,dm_exec_query_stats.max_logical_writes
,dm_exec_query_stats.total_elapsed_time
,dm_exec_query_stats.min_elapsed_time
,dm_exec_query_stats.max_elapsed_time
,dm_exec_query_stats.plan_handle
fromsys.dm_exec_query_stats
wheretotal_logical_reads > 1000
)
selectResourceRank
,DB_NAME(dm_exec_query_plan.dbid) as DatabaseName
,OBJECT_NAME ( dm_exec_query_plan.objectid, dm_exec_query_plan.dbid) as ProcedureName
,last_execution_time
,creation_time
,execution_count
,total_logical_reads
,min_logical_reads
,max_logical_reads
,total_logical_writes
,min_logical_writes
,max_logical_writes
,total_elapsed_time
,min_elapsed_time
,max_elapsed_time
,dm_exec_query_plan.query_plan
fromexec_query_stats
CROSS APPLY
sys.dm_exec_query_plan ( exec_query_stats.plan_handle )
whereResourceRank <= 50
order by ResourceRank asc
SQL = Scarcely Qualifies as a Language
August 15, 2016 at 11:29 pm
Hi,
Have created the view in SQL server side
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply