SQL Server Techniques: Spatial

<= Drawing a Sector (Slice of Pizza) in SSMS

As you probably know, SQL Server collects a lot of data about engine itself and all executed queries.

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;

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.

Drawing Pie Chart in SSMS using spatial data =>

I was needed to draw a lot of Sectors for my next blog post, but I've never done it before.
So, will draw a slice of Pizza and learn how to draw a Sector.

To draw a sector we need to choose following parameters:

Radius of our circle (@Radius)
X and Y coordinates of our circle's center (@CenterX & @CenterY)
Starting Angle of our sector (@Angle1_Degrees)
Angle of a Sector itself (@Angle_Degrees)

The next step is to transfer angle's values in radians, because SQL Server "Sin" and "Cos" functions does not accept angles in degrees:

Starting Angle of a Sector in Radians (@Angle1_Radians)
Ending Angle of a Sector in Radians (@Angle2_Radians)
Midpoint Angle, which lays right between the first and the second angles (@AngleM_Radians)

We will build a sector by layers.
The first layer is a semicircle built by "CIRCULARSTRING":

DECLARE @Radius DECIMAL = 10;

DECLARE @CenterX DECIMAL = 1;

DECLARE @CenterY DECIMAL = 2;

DECLARE @Angle1_Degrees DECIMAL = 10;

DECLARE @Angle_Degrees DECIMAL = 60;

DECLARE @Angle1_Radians DECIMAL(38,17) = @Angle1_Degrees * 2 * Pi() / 360;

DECLARE @Angle2_Radians DECIMAL(38,17) = (@Angle1_Degrees + @Angle_Degrees) * 2 * Pi() / 360;

DECLARE @AngleM_Radians DECIMAL(38,17) = (@Angle1_Radians + @Angle2_Radians) / 2;

SELECT CAST('CIRCULARSTRING

(' + CAST(@Radius * SIN(@Angle1_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle1_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@AngleM_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@AngleM_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@Angle2_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle2_Radians) + @CenterY as VARCHAR) +

')' as GEOMETRY);

At the second layer we need to add to lines to our semicircle using "COMPOUNDCURVE"

DECLARE @Radius DECIMAL = 10;

DECLARE @CenterX DECIMAL = 1;

DECLARE @CenterY DECIMAL = 2;

DECLARE @Angle1_Degrees DECIMAL = 10;

DECLARE @Angle_Degrees DECIMAL = 60;

DECLARE @Angle1_Radians DECIMAL(38,17) = @Angle1_Degrees * 2 * Pi() / 360;

DECLARE @Angle2_Radians DECIMAL(38,17) = (@Angle1_Degrees + @Angle_Degrees) * 2 * Pi() / 360;

DECLARE @AngleM_Radians DECIMAL(38,17) = (@Angle1_Radians + @Angle2_Radians) / 2;

SELECT CAST('COMPOUNDCURVE(CIRCULARSTRING

(' + CAST(@Radius * SIN(@Angle1_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle1_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@AngleM_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@AngleM_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@Angle2_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle2_Radians) + @CenterY as VARCHAR) +

'),(' + CAST(@Radius * SIN(@Angle2_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle2_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@CenterX as VARCHAR) + ' ' + CAST( @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@Angle1_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle1_Radians) + @CenterY as VARCHAR) + '))' as GEOMETRY);

And the last third layer is "CURVEPOLYGON", needed to convert our compounded curve into a polygon:

DECLARE @Radius DECIMAL = 10;

DECLARE @CenterX DECIMAL = 1;

DECLARE @CenterY DECIMAL = 2;

DECLARE @Angle1_Degrees DECIMAL = 10;

DECLARE @Angle_Degrees DECIMAL = 60;

DECLARE @Angle1_Radians DECIMAL(38,17) = @Angle1_Degrees * 2 * Pi() / 360;

DECLARE @Angle2_Radians DECIMAL(38,17) = (@Angle1_Degrees + @Angle_Degrees) * 2 * Pi() / 360;

DECLARE @AngleM_Radians DECIMAL(38,17) = (@Angle1_Radians + @Angle2_Radians) / 2;

SELECT CAST('CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING