SQL Server Techniques: September 2019

Wednesday, September 25, 2019

SQL Server CEIP service

Very recently, by monitoring SQL Server activity found that some service creates Extended Events and then drop them.

The user, who run these traces is "SQLTELEMETRY" and an application is "SQL Server CEIP service (MSSQLSERVER)"

Usually I missed that, because I look at running tasks only when there is a problem and if any running task does not generates any extra CPU activity and does not cause any current issues I just ignore it while hunting for the real problem.

But recently I've started monitoring DDL events activity and one of the servers showed me that "CEIP service for Sql server" produces a lot of DDL event on a regular base.

I do not like any extra noise in my environment and started to hunt that issue.

Here is what I've found: Configure usage and diagnostic data collection for SQL Server tools (CEIP)

That Microsoft article describes how "How the Customer Experience Improvement Program (CEIP) helps Microsoft identify ways to make our software better."

In other words that service collects information within your SQL Server and sends it to Microsoft. Even though Microsoft claims that it does not collect and send passwords and certificates it still admits that some Customer Content can be sent to Microsoft.

That means if you have any PII - Personal Identifiable Information, which can be Social Security Numbers, Dates of Birth, Names, addresses, Credit Card numbers, financial information can easily be transferred outside of your server and being unprotected.

How have you got that scary service?

Whoever installed SQL Server on that box once allowed that service to exist and send data to Microsoft.

How to stop/disable that service?

The Books Online article describes how you can turn sending that information to Microsoft off, but as paranoid DBA I'd prefer to disable the service completely.

You can find it in the list of services on the server:

Just open it, stop and select "Disabled" in the "Startup Type"

And keep your customers' data protected.

Friday, September 6, 2019

SSMS Feature generates Severity 20 Error in the Errorlog.

Yesterday I've needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an "Severity 20" alert.

As you probably know, Severity 20 Errors "Indicate system problems and are fatal errors" (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)

Even though "Severity 20" does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.

I looked at my Log file and found following record:

Error: 17810, Severity: 20, State: 2.
Could not connect because the maximum number of '1' dedicated administrator connections already exists.
Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 192.168.234.54]

I knew that I'm using the DAC on that server right now, but it was a surprise for me that "somebody else" is also trying to connect to the same SQL server via DAC.

I've set the simplest extended event session (script is below) to monitor the cause of the problem.

CREATE EVENT SESSION [Tracking DAC Errors] ON SERVER

ADD EVENT sqlserver.error_reported(

WHERE ([category]=(4) AND [error_number]=(17810) AND [severity]=(20))),

ADD EVENT sqlserver.errorlog_written

ADD TARGET package0.ring_buffer

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

And by doing that I discovered a little "Aero" SSMS window, which is driven by IntelliSense, and usually appears for a fraction of a second when you connect to a new server or a database and then disappears. It reads the schema of current database to provide you suggestions. However, I noticed this time that this small popup window did not disappear and it also had an error.

I've tried to hit a "Restart" button and immediately got an error in my extended event's "Live View" window:

As you can see from the screenshot IntelliSense was trying to read the schema of my current database using the same connection string I used to connect to the server, but because I've used DAC, which allows you to have only one that kind of connection IntelliSense got an error.

I've tried to turn IntelliSense off, however it hasn't fixed the issue. Behind the seen SSMS still tried to pull some information from a SQL Server using the same connection string to as my connection in a tab.

At first, my thought was that it might be a bug, but after little thinking I realized that SSMS is not so smart and can't determine correct connection string to the server you want and for simplicity just uses same connection string, which was already provided.
It would be nice though, if SSMS realized that I'm using DAC and did not try to connet to the Server itself and not producing unnecessary error.

What can we learn from that?
If you are at the point when you have to use DAC to connect to your server, you are on your own and SSMS interface become useless. All its reports and GUI features are unavailable and only pure T-SQL can help you.
I'm glad I'm using my own set of scripts embedded into SSMS shortcuts, if you want to check it out, here is the link to a downloadable archive: https://drive.google.com/open?id=0B5yWoyX1eEWqZ3FJUnNHZm80bzQ

Thursday, September 5, 2019

SQL Server CPU usage by Database

<= 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.

Wednesday, September 4, 2019

Drawing Slice of Pizza by drawing a Sector

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