<= Drawing a Sector (Slice of Pizza) in SSMS |
Query execution statistics can be retrieved from DMV "sys.dm_exec_query_stats".
You can extract these data in a grid, but to enjoy the data visually you can use a SQL Spatial data.
Here is a query to transform SQL query results into a Pie Chart:
;WITH Agg as (
SELECT DISTINCT [Range] = DB_Name(DatabaseID)
, [Value] = SUM(total_worker_time) OVER (PARTITION BY DatabaseID)
, Total = SUM(total_worker_time) OVER (ORDER BY ( SELECT NULL))
FROM sys.dm_exec_query_stats AS qs with (NOLOCK)
CROSS APPLY (
SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
WHERE DatabaseID < 32767
), dat as (
SELECT *
, StartPoint = SUM([Value]) OVER ( ORDER BY [Value] DESC ) - [Value]
, EndPoint = SUM([Value]) OVER ( ORDER BY [Value] DESC )
FROM Agg
), geo as (
SELECT [Range], [Value]
, Percentage = CAST(CAST([Value] * 100. / Total as DECIMAL(5,2)) as VARCHAR) + ' %'
, X1 = SIN(StartPoint * 2 * Pi() / Total)
, XM = SIN(.5*((StartPoint + EndPoint) * 2 * Pi() / Total))
, X2 = SIN(EndPoint * 2 * Pi() / Total)
, Y1 = COS(StartPoint * 2 * Pi() / Total)
, YM = COS(.5*((StartPoint + EndPoint) * 2 * Pi() / Total))
, Y2 = COS(EndPoint * 2 * Pi() / Total)
FROM dat)
SELECT [Database] = [Range] + CHAR(10) + Percentage
, CAST('CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING ('
+ CAST(X1 as VARCHAR) + ' ' + CAST(Y1 as VARCHAR) +
',' + CAST(XM as VARCHAR) + ' ' + CAST(YM as VARCHAR) +
',' + CAST(X2 as VARCHAR) + ' ' + CAST(Y2 as VARCHAR) +
'),(' + CAST(X2 as VARCHAR) + ' ' + CAST(Y2 as VARCHAR) +
',0 0,' + CAST(X1 as VARCHAR) + ' ' + CAST(Y1 as VARCHAR) + ')))' AS GEOMETRY)
FROM geo ORDER BY [Value] DESC;
GO
|
Here is a Chart from my test server:
That query can be easily reused to report "total_physical_reads", "total_logical_writes" or "total_logical_reads replace" by Database. Just replace "total_worker_time" column int the first CTE.
Actually, the query in the first CTE can be replaced completely by your own to report anything you want.
No comments:
Post a Comment