Some In Memory OLTP analytical scripts

I will add descriptions about this in time.

Please use this as an inspiration for your own In Memory OLTP analysis.

CREATE DATABASE InMemDemos
ON PRIMARY (NAME=InMemDemos_Data, FILENAME='D:\Data\InMemDemos_Data.mdf', SIZE=10MB, FILEGROWTH=10MB),
FILEGROUP InMemDemos_FG CONTAINS MEMORY_OPTIMIZED_DATA (NAME=InMemDemos_FG, FILENAME='D:\Data\InMemDemos_FG')
LOG ON (NAME=InMemDemos_Log, FILENAME='D:\Data\InMemDemos_Log.ldf', SIZE=10MB, FILEGROWTH=10MB)

First part creates the database, Second part creates three tables:

USE InMemDemos
GO
CREATE TABLE InMem_BC100
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)

Let us populate the tables :

WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC100(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000

How about some checkpoint statistics:

SELECT * FROM sys.dm_db_xtp_checkpoint_stats

 And status about the Data and Delta files:

SELECT
da.container_guid,
da.relative_file_path AS Data_File,
de.relative_file_path AS Delta_File,
da.internal_storage_slot,
da.state_desc,
da.file_size_in_bytes AS Data_File_Size,
da.file_size_used_in_bytes AS Data_File_Size_Used,
de.file_size_in_bytes AS Delta_File_Size,
de.file_size_used_in_bytes AS Delta_File_Size_Used,
da.inserted_row_count,
de.deleted_row_count
FROM sys.dm_db_xtp_checkpoint_files da
INNER JOIN sys.dm_db_xtp_checkpoint_files de
ON da.checkpoint_file_id = de.checkpoint_pair_file_id
WHERE da.file_type_desc = 'DATA' AND de.file_type_desc = 'DELTA'

And continue with some Table statistics:

SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
xtms.memory_allocated_for_table_kb,
xtms.memory_used_by_table_kb,
xtms.memory_allocated_for_indexes_kb,
xtms.memory_used_by_indexes_kb
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.dm_db_xtp_table_memory_stats xtms
ON t.object_id = xtms.object_id

 All the way to specific indexes:

SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
i.name AS Index_Name,
i.type_desc AS Index_Type,
xhis.total_bucket_count,
xhis.empty_bucket_count,
xhis.avg_chain_length,
xhis.max_chain_length,
xmc.allocated_bytes,
xmc.used_bytes,
xis.*
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
INNER JOIN sys.dm_db_xtp_index_stats xis
ON t.object_id = xis.object_id AND i.index_id = xis.index_id
INNER JOIN sys.dm_db_xtp_hash_index_stats xhis
ON xis.object_id = xhis.object_id AND xis.index_id = xhis.index_id
INNER JOIN sys.dm_db_xtp_memory_consumers xmc
ON xis.object_id = xmc.object_id AND xis.index_id = xmc.index_id

Remeber to throw some checkpoint from time to time:

CHECKPOINT

 Ok, I'm done now and I hope you can make use of the queries I shared here.

Counting currency...

I was just stumbling over an problem on a forum. They wanted to count currency. Let us imagine you have an ATM that delivers every single coin and bill from a specific currency, and people will withdraw any specific amount, and you want to calculate how many of each to deliver.

Well, look no further.

DECLARE @Pengar int = 9888;
DECLARE
@Valörer TABLE (Värde int, Typ char(5), Benämning varchar(25))
INSERT INTO @Valörer (Värde, Typ, Benämning)
VALUES (1, 'Mynt', '1 kr')
, (2, 'Mynt', '2 kr')
, (5, 'Mynt', '5 kr')
, (10, 'Mynt', '10 kr')
, (20, 'Sedel', '20 kr')
, (50, 'Sedel', '50 kr')
, (100, 'Sedel', '100 kr')
, (200, 'Sedel', '200 kr')
, (500, 'Sedel', '500 kr')
, (1000, 'Sedel', '1000 kr');
WITH
cash AS (
SELECT
ROW_NUMBER() OVER(ORDER BY Värde DESC) AS Ordning,
Värde, Typ, Benämning
FROM @Valörer WHERE Värde <= @Pengar
),
b AS (
SELECT
Ordning, Värde, Typ, Benämning,
(@Pengar - (@Pengar % Värde))/Värde Antal,
@Pengar % Värde AS Rest
FROM cash a WHERE Ordning = 1
UNION ALL
SELECT
a.Ordning, a.Värde, a.Typ, a.Benämning,
(b.Rest - (b.Rest % a.Värde))/a.Värde,
b.Rest % a.Värde
FROM cash a INNER JOIN b ON a.Ordning = b.Ordning + 1
),
cashing as (
SELECT
Typ, Benämning, Antal,
Antal * Värde AS [Valör Värde],
SUM(Antal * Värde) OVER(ORDER BY Ordning ASC) AS [Totalt Värde]
FROM b WHERE Antal > 0
)
SELECT * FROM cashing;

Some ideas are great, and some are not, and a simple trick...

First of all I'd like to say thanks to the minority. Last week I was in Copenhagen and delivered the worst session ever. A minority liked it, and the majority didn't. So, to the majority I would like to say, I'm sorry. My idea for the session was about sharing some from the soft side of a large project, with some technology, and try to add some "I recognise this"-feeling in it. The idea might have been great in my mind, and I do Believe that some sessions need to be like that, but in the context my idea was not so great. So once again, I'm deeply, truly, from the bottom of my heart, I'm sorry.

Lesson learned!

To cite the King of Sweden, Carl XVI Gustav, about a not so great decision, "let us turn the page, to a new chapter, like when you are reading those books, the ones that make a sound when it's time to turn the page".

On the other hand, I'm looking forward to fulfill the promise I made. There will be a long series of posts and videos covering all the topics of the session. With details and easy to follow guides of implementing and solving issues. And I will try to get the first one done this week.

For the future, I also have a promise, next time you have the chance of attending a session of mine. Give me the opportunity to make it different back again. In the future I'll keep it back to only Technical, and majority of demos, and "normal".

There are some reasons behind this post, first of all the appology, secondly to remind myself, and thirdly to turn the page, and finally as launchpad for the series of posts and videos.

Actually, I'll start with the simplest of simple, the following is a copy of an unattended configuration file that install an instance of the database Engine. The parameters QUIETSIMPLE="True" set the install to automatic install, INDICATEPROGRESS="False" hides the progress bar. And by the way, create them with the SQL Server Installation Center, and edit them with Notepad:

;Configuration File
[OPTIONS]
ACTION="Install"
ENU="True"
QUIETSIMPLE="True"
UpdateEnabled="False"
ERRORREPORTING="False"
USEMICROSOFTUPDATE="False"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCENAME="InstanceName"
SQMREPORTING="False"
INSTANCEID="InstanceName"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
AGTSVCACCOUNT="DOMAIN\groupManagedServiceAccount$"
AGTSVCSTARTUPTYPE="Automatic"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="DOMAIN\groupManagedServiceAccount$"
SQLSYSADMINACCOUNTS="DOMAIN\groupSQLAdmins"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Automatic"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$InstanceName"

And to start the unattended install, /IAcceptSQLServerLicenseTerms=true automatically accepts the license:
Setup.exe /ConfigurationFile=ConfigFile.INI /IAcceptSQLServerLicenseTerms=true

It's about time...

It's been a while since last post, and it is not as frequent as I wish, but it's about time or more lack of time.

First I want to celebrate Microsoft SQL Server for yet another amazing release. Now it is Microsoft SQL Server 2019, there are so many new features I guess I must start writing some about everything I love in this product. And that is almost a promise, I'll try to do more posts, and feature all this goodness.

Now over to todays topic, time. I found a question on the SQL Server 2008 - 2019 Facebook group, https://www.facebook.com/groups/7193059737/10157742825254738. And it was from Islam Selim:

Hi all
I need to create time column in view calculate differeance between two other datetime columns.. Plz help

And my response was this:

CREATE FUNCTION dbo.fn_TimeBetween (
 @start datetime,
 @end datetime,
 @days int  = 0 -- 0 for 00H,00M,00S (Default), 1 for 00D,00H,00M,00S
 )
RETURNS varchar(max)
AS
BEGIN
DECLARE
 @distance int = DATEDIFF(second, @start, @end),
 @result varchar(max);
DECLARE
 @seconds int = @distance % 60
SET
 @distance = (@distance - @seconds) / 60
DECLARE
 @minutes int = @distance % 60
SET
 @distance = (@distance - @minutes) / 60
DECLARE
 @hours int = @distance % 24
SET
 @distance = (@distance - @hours) / 24
IF @days = 0
 SET @result = CAST(@distance * 24 + @hours AS varchar(max)) + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    +'S'
ELSE
 SET @result = CAST(@distance AS varchar(max))     + 'D,' +
 RIGHT('0' +  CAST(@hours AS varchar(max)), 2)    + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'S'
RETURN @result
END