Maintain Line Breaks in SSMS

Problem

This isn’t a problem, more an annoyance.

When you write a script in SSMS, then past the results into SSMs to run as a new set of scripts, SSMS removes the line breaks.

For Example (I’m only using this script to show here as the script and results would be common to all SQL installations):

SELECT ‘USE [‘ + name + ‘]
SELECT *
FROM sys.tables;
‘
FROM sys.databases
Where database_id < 5;

Here, when I paste the generated code into a new window, it’s all on one line. In some cases, this might be ok, but mostly it isn’t.

Solution

You need to change a setting in SSMS that will maintain line breaks when you paste text.

To get there go to Tools >> Options >> Query Results >> Results to Grid and select Retain CR/LF o copy or save.

Memory Optimized Table Type

Problem

Long running query. A lot of data was loaded into a TVP (Table Value Parameter) and passed to a function.

They query ran for over ten minutes – I killed it then. Ten minutes is enough to try my patience and also show that there’s a problem.

The query was overly complex and I spent a few minutes breaking it down and checking for indexes etc, all the usual stuff.

I found, when I tested some of the smaller sections, it ran quite fast. I ran the whole SELECT statement and it completed in about 10 seconds. Which mean the rest of the time was spent loading the data into a table variable.

The table variable was actually a defined data type.

When I loaded the data into a Temp Table, it took about 12-13 seconds, ie only a couple of seconds longer than the query. I could have stopped here, except the data needed to be passed to a function.

Solution

Instead of having the table variable defined as a table, I changed the type to a Memory optimized table.

I actually still needed to load the data into a temp table first, then load that straight into the variable. This now took 20 seconds. I could still have worked on it a bit longer to improve the timings of the SELECT etc, but this was good enough.

To create a memory optimized table, you first need to create a filegroup specifically for Memory optimized objects:

ALTER DATABASE [DatabaseName]
ADD FILEGROUP MemOpt CONTAINS MEMORY_OPTIMIZED_DATA

You will then need to add at least one file to the file group:

ALTER DATABASE [DatabaseName] ADD FILE (name=’MemOptFile’, filename=’G:\Microsoft SQL Server\data\mem\’) TO FILEGROUP MemOpt

Note: The path does not have a file name. That location must not exist – in this case the mem subdirectory will be created when the ADD FILE command is executed.

Now you can change the table value type to be memory optimized. To do this, create the table type in the normal way, but add WITH (MEMORY_OPTIMIZED=ON) 

CREATE TABLE dbo.MyTable (columnlist varchar(250), nextcol int, etc ) WITH (MEMORY_OPTIMIZED=ON) ;

Note: Once you create this table, you won’t be able to remove the memory optimized file group.

Availability Group Fails to Failover

The Problem

Technology: SQL Server 2019 (possible other versions)

The Availability Group won’t fail over. There is nothing in the windows or SQL error log to give a clue as to why the failover failed to happen.

The Solution

This was our solution. There are many reasons why an AG might fail to failover, but this happened to a few of our servers.

The problem related to the setting for HealthCheck Time out.

To reach this you need to go to Fail Over Cluster Manager > Roles > click on the AG Name > At the bottom of the page, click on Resources > under other resources Right click on the AG name > Properties > and then the properties tab

Make sure the HealthCheck Timeout setting is 30000

What cause our setting to be wrong? Good question. It looks like it might have been Veeam tools which reset it, but there could be other apps which change the setting in other environments.

Once I’d fixed our servers, I found that a server which was working had stopped. When I checked, the HealthCheck Timeout had been changed. I set it back and failover worked again. In this case, I’m about 90% sure Veeam made the change as it was installed on this server after the fixes to the other boxes. A bit of a pain, but I’m glad as it happened as it confirmed that this solution worked. Before this, there was a degree of uncertainty.

Note: Once the changes are made, you do need to restart the SQL service before they take effect.

SQL Server Crashes

Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) – 13.0.5081.1 (X64) Developer Edition (64-bit)

SQL Server was crashing. It happened a few times, seemingly at random. The server was a dev box so I just restarted it and ignored the errors as they consisted as an error dump. However, it happened again on a day where I had a bit of spare time so looked into it.

The main error message was:
Error: 25725, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Looking up that error message I got: Error: 25725, Severity: 16, An error occurred while trying to flush all running Extended Event sessions. Some events may be lost.
Then I remembered I had an Extended Event Session that I’d left running to check Tempdb contention. I didn’t need to run it anymore so I turned it off.

Other error messages which came with this one were mainly related to the fact the service was stopping:

• Error: 19032, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
• Error: 19032 = SQL Trace was stopped due to server shutdown. Trace ID = ‘%d’. This is an informational message only; no user action is required.
• Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
• Error 17300 = SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option ‘user connections’ to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
• Error: 17189, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
• Error 17189 = SQL Server failed with error code 0x%x to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems.%.*ls
• Error: 28709, Severity: 16, State: 19. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
• Error 28709 = Dispatcher was unable to create new thread.

Before those simple messages came a series of Memory Broker messages, Big Gateway, Medium Gateway and Small Gateways as part of a general dump.

You can get a list of error messages by running this script:
SELECT *
FROM master.dbo.sysmessages

A useful list of error messages can be found here: System Error Messages

Trace Flags T1117 and T1118

The Problem

When checking the error logs on a SQL 2016 box I noticed this error message: Trace flag 1117 is discontinued. Use the options provided with ALTER DATABASE.
Trace flags T1117 and T1118 have been discontinued as of SQL Server 20016.

T1118 tells SQL to avoid mixed extents and use full extents.
T1117 makes sure all files in a filegroup grow at an even rate.

I now need to replicate this behavior on the SQL 2016 box

The Solution

T1118 doesn’t matter so much to me. The database where it has the most effect is tempdb and this now uses full extents by default.

T1117 has been replaced with an Alter Database Command:
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Unfortunately you can’t apply this to the model db. So, to apply this to all current databases I wrote a script to loop through and check the status of the filegroup and change it if needed.

Please test this script before you run it. You might want to change the two Exec command with Print and so check the output.

Note: You can check the status of a database by running this script :
SELECT * from sys.filegroups
You will see there is a column is_autogrow_all_files. This should be set to 1

This script will not work if the database is in use

The Script

SELECT [name] as DBName, cast(0 as bit) as Checked
into #DBTest
FROM sys.Databases as db
where database_id > 4
and is_read_only = 0;

DECLARE @DBName as Varchar(250);
DECLARE @sql as Varchar(max);

While Exists (select 1 FROM #DBTest where Checked = 0)
BEGIN
SELECT top 1 @DBName = DBName
FROM #DBTest
where Checked = 0;

SET @sql = ‘USE ‘ + @DBName + ‘
DECLARE @sqlinner as Varchar(max);
DECLARE @fg as Varchar(250);
SELECT ”ALTER DATABASE ‘ + @DBName + ‘ MODIFY FILEGROUP [” + name + ”] AUTOGROW_ALL_FILES ” as sl , cast(0 as bit) as Checked, name as FG
INTO #FileGroups
from sys.filegroups
WHERE is_autogrow_all_files = 0;
While Exists (select 1 FROM #FileGroups where Checked = 0)
BEGIN
SELECT top 1 @sqlinner = sl, @fg = FG
FROM #FileGroups where Checked = 0;

EXEC(@sqlinner);

UPDATE #FileGroups
SET Checked = 1
WHERE FG = @fg;

END
DROP TABLE #FileGroups
‘

exec(@sql);

UPDATE #DBTest
SET Checked = 1
WHERE DBName = @DBName;
END

DROP TABLE #DBTest

Speed up SSIS Data Flow

I use this script all the time when setting up an SSIS package. (Unfortunately, I can’t remember where I found the original code. I’ve adapted it slightly, so if anyone recognises the original then let me know and I’ll link to it.)

The Problem

When setting up a data flow in SSIS the data transfer speed can be very slow because the default settings in the package have not been optimised.

The Solution

The code below will show you each table in the database. I take the column MaxBufferSize and round it down to the nearest hundred – so 87235 becomes 87000. I use this value as the DefaultBufferMaxRows value. I change the DefaultBufferSize from 10485760 to 104857600 (same number but add a zero to the end). Finally, I’ll add values to the BlobTempStoragePath and BufferTempStoragePath, normally I’ll use C:\temp, but make sure the directory exists and you’re probably better choosing a value not on the C drive.

SELECT s.[name] + '.' + t.[name] as TableName, SUM (max_length) as [row_length], 10485760/ SUM (max_length) as MaxBufferSize
FROM sys.tables as t
JOIN sys.columns as c
ON t.object_id=c.object_id
JOIN sys.schemas s
ON t.schema_id=s.schema_id
GROUP BY s.[name], t.[name];

These changes will allow SSIS to load more rows simultaneously and so should speed up your loading. I tend to use OLD connection for Source and Destination.

Installing ‘R’ on 2016

It’s been a long time since I’ve posted on here, mainly because I no longer work primarily as a DBA but more as a SQL Developer, I was also looking after thousands of instances so came across a lot of issues. However, this one had me stuck for a few days. We have a developer who wanted to run ‘R’ and a 2016 instance which should have let him, but no dice.

The Problem

When running the following ‘R’ test script:

EXEC sp_execute_external_script
@language =N’R’,
@script=N’OutputDataSet<-InputDataSet',
@input_data_1 =N’SELECT 1 AS hello'<
WITH RESULT SETS (([hello world] int not null));
GO

I got the following error:
Msg 39021, Level 16, State 1, Line 1 Unable to launch runtime for ‘R’ script.
Please check the configuration of the ‘R’ runtime. Msg 39019, Level 16, State 1, Line 1 An external script error occurred: Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

The Solution

I was running on SQL Server 2016 (13.0.4001.0) with no previous ‘R’ installations or CTP instalattions.
The solution was to uninstall and install the dll.
In my case the path to it was C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt

So, first I opened up dos with admin rights and ran:
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt” /uninstall

After that I ran
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt” /install

Note: Each time you run the uninstall or install script it will stop and start your SQL instance

This was for a default instance. I think, if you are using a named instance you need to add: /instance:InstanceName after the /install flag

 

Adding extra files to Tempdb

This isn’t actually a problem. When I build a new instance I like a tempdb file for each cpu. This script will create those files in the directory where the current tempdb data file is. Please note that it sizes tempdb data and log files too, you may want to change those values

/* Script Starts*/
DECLARE @cpuCount as int;
DECLARE @Files as int;

SELECT @Files = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = ‘ROWS’;

SELECT @cpuCount = cpu_count /hyperthread_ratio
FROM sys.dm_os_sys_info
–Print @cpuCount
–Print @Files

Alter Database Tempdb modify file(Name=tempdev, Size=300, filegrowth = 150MB);
Alter Database Tempdb modify file(Name=templog, Size=50, filegrowth = 50MB);

DECLARE @FileLocation as Varchar(750);

–You may need to check that the name of the tempdb data file is tempdb.mdf (select * from tempdb.sys.master_files)
Set @FileLocation = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N’tempdb.mdf’,
LOWER(physical_name)) – 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 AND FILE_ID = 1)
–Print @FileLocation

DECLARE @diff as int;
SET @diff = @cpuCount – @Files
If @diff > 7
set @diff = 8 – @Files

DECLARE @x as TinyInt;
SET @x = @Files;
DECLARE @file as Varchar(10);
DECLARE @fileName as Varchar(250);
While @diff > 0
BEGIN
SET @file = ‘tempdev’ + Cast(@x as varchar(2))
SET @fileName = @FileLocation + ‘\’ + @file + ‘.ndf’;
DECLARE @sql as Varchar (8000);
SET @sql = ‘ALTER DATABASE TempDb ‘;
SET @sql = @sql + ‘ADD FILE ‘;
SET @sql = @sql + ‘( ‘;
SET @sql = @sql + ‘NAME = ‘ + @file + ‘, ‘;
SET @sql = @sql + ‘FILENAME = ”’ + @fileName + ”’, ‘;
SET @sql = @sql + ‘SIZE = 300MB, ‘;
SET @sql = @sql + ‘FILEGROWTH = 150MB’;
SET @sql = @sql + ‘);’;
Exec (@sql);
SET @diff = @diff -1;
SET @x = @x + 1;
END;

USING CTE to compare different values in the same table

The Problem

I had a list of different routes and stops along the route (for the benefit of this example they are called seq). What I wanted to do was get the start time and the end time for each journey and so work out the journey times.
Each journey – here called route – may have different start and stop points (seq values)

The Solution

In the real case I found the min and max seq for each journey and added these as columns to my base table. This was all done in SQL 2014

CREATE TABLE #Journey(
[JourneyID] [int] NOT NULL,
[Route] [int] NOT NULL,
[seq] [int] NOT NULL,
[tme] [smalldatetime] NOT NULL,
[maxSeq] [int] NOT NULL,
[minSeq] [int] NOT NULL);

INSERT INTO #Journey
Values(1056975,20,2,’2015-06-03 09:34:00′,99,2),
(1056975,20,5,’2015-06-03 09:38:00′,99,2),
(1056975,20,6,’2015-06-03 09:39:00′,99,2),
(1056975,20,99,’2015-06-03 09:44:00′,99,2),
(1056975,20,99,’2015-06-03 09:45:00′,99,2),
(1056975,20,99,’2015-06-03 09:49:00′,99,2),
(1056975,20,99,’2015-06-03 09:53:00′,99,2),
(1056975,20,99,’2015-06-03 09:56:00′,99,2),
(1056975,20,99,’2015-06-03 09:57:00′,99,2),
(2471362,1,1,’2015-06-06 07:48:00′,99,1),
(2471362,1,1,’2015-06-06 07:49:00′,99,1),
(2471362,1,2,’2015-06-06 07:56:00′,99,1),
(2471362,1,5,’2015-06-06 07:57:00′,99,1),
(2471362,1,5,’2015-06-06 07:59:00′,99,1),
(2471362,1,8,’2015-06-06 08:05:00′,99,1),
(2471362,1,11,’2015-06-06 08:08:00′,99,1),
(2471362,1,14,’2015-06-06 08:15:00′,99,1),
(2471362,1,15,’2015-06-06 08:21:00′,99,1),
(2471362,1,99,’2015-06-06 08:23:00′,99,1)

;WITH journeyStart(JourneyID, [Route], seq, tme, RN)
as (
SELECT JourneyID, [Route], seq, tme, RN=row_number()
OVER (PARTITION BY JourneyID ORDER BY seq desc)
FROM #Journey WHERE seq = [minSeq])

SELECT JS.[Route], JS.seq as TrainSequenceStart, JS.tme as StartTime,

RJ2.seq as TrainSequenceEnd, RJ2.tme as EndTime, JT.JourneyTime as
[JourneyTime (minutes)]
FROM journeyStart as JS

CROSS APPLY (select top (1) RJe.*
FROM #Journey as RJe
where RJe.JourneyID = JS.JourneyID
AND RJe.[Route] = JS.[Route]
AND RJe.Seq = RJe.maxSeq
order by RJe.JourneyID, RJe.seq) as RJ2

CROSS APPLY (SELECT DateDiff(mi,JS.tme,RJ2.tme) as journeyTime) as JT

WHERE JS.RN = 1;

DROP TABLE #Journey;

The important point it in the 1st cross apply, RJe.Seq = RJe.maxSeq, which just says, get me the max seq value.
I added the second cross apply for the date calculation just to make the code easier to read.

Using the Apply operator

I started this blog to remind myself how I solved certain problems or to dump code snippets. I haven’t been using it much but am going to get it going again. From now on I’m also going to include links to interesting SQL resources (mainly videos) I watch.

To start this off, I watched this one today: Boost your T-SQL with the Apply Operator. Certainly worth a watch if you are doing any T-SQL coding. It’s presented by Itzik Ben-Gan who also wrote the book T-SQL Querying, which I happen to be reading at the moment.