September 22, 2008 at 8:55 am
Do we have something similar to oracle's statspack report in sql server2005.
I just want to know which are the slow running procedures etc to tune the database.(Design changes for the next version)
I know I can get them from profiler(running a trace), but is there any other way?
Thanks,
Pavan.
September 22, 2008 at 9:24 am
Statspack is a pretty comprehensive report with alot of parts to it. Some of the statistics you can get using the Performance Monitor in the Administrative Tools of Control panel or querying the system view sys.dm_os_performance_counters. If all you are looking for is what statements in stored procedures are taking the longest, most people are using queries such as the following:
-- most time queries
SELECT TOP 100
[Average seconds] = qs.total_elapsed_time / qs.execution_count / 1000000.0,
[Total seconds] = qs.total_elapsed_time / 1000000.0,
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),
[Parent Query] = qt.text,
DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.dbid = DB_ID('TestSS1SIS')
ORDER BY [Average seconds] DESC;
-- most I/O queries
SELECT TOP 100
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total IO] = (total_logical_reads + total_logical_writes),
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),
[Parent Query] = qt.text,
DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.dbid = DB_ID('TestSS1SIS')
ORDER BY [Average IO] DESC;
September 22, 2008 at 9:45 am
Thank you, I was looking for some thing like this only.
I wanted similar to that as, I wanted to see the usage of the whole system, once we have done any service bulletin or upgrade to a new version.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply