Ok, just a sample PIVOT

DECLARE @DatabaseObjects TABLE
  (
    DatabaseName sysname,
    ObjectType sysname,
    ObjectName sysname
  );
DECLARE
  @DatabaseName sysname,
  @SQLStatement varchar(max),
  @ExecuteStatement varchar(max),
  @Columns varchar(max);

SET NOCOUNT ON;
SET @SQLStatement = 'SELECT ''{DatabaseName}'' AS [DatabaseName], [type_desc] AS [ObjectType], [name] AS [ObjectName] FROM [{DatabaseName}].[sys].[objects];';
DECLARE
  DatabaseCrawler CURSOR
  FOR SELECT Name FROM sys.Databases ORDER BY Name ASC;
OPEN DatabaseCrawler;
FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @ExecuteStatement = REPLACE(@SQLStatement, '{DatabaseName}', @DatabaseName);
  INSERT INTO @DatabaseObjects
  EXEC (@ExecuteStatement);
  FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
END;
CLOSE DatabaseCrawler;
DEALLOCATE DatabaseCrawler;
SET NOCOUNT OFF;

SELECT * INTO #DatabaseObjects FROM @DatabaseObjects

SELECT @Columns = ISNULL(@Columns, '') + '[' + [Column] + '],' FROM (SELECT DISTINCT ObjectType AS [Column] FROM @DatabaseObjects) Columns

SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

SET @SQLStatement = 'SELECT DatabaseName, {Columns} FROM #DatabaseObjects PIVOT (Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt'

SET @ExecuteStatement = REPLACE(@SQLStatement, '{Columns}', @Columns);

EXEC (@ExecuteStatement)
DROP TABLE #DatabaseObjects

By request

DECLARE @start datetime, @numberofyears int
SET @start = '1900-01-01'
SET @numberofyears = 180
;WITH a1(col)
AS (
SELECT NULL
UNION ALL
SELECT NULL
), a2(col)
AS (
SELECT NULL FROM a1 x1 CROSS JOIN a1 x2
), a3(col)
AS (
SELECT NULL FROM a2 x1 CROSS JOIN a2 x2
), a4(col)
AS (
SELECT NULL FROM a3 x1 CROSS JOIN a3 x2
), a5(col)
AS (
SELECT NULL FROM a4 x1 CROSS JOIN a4 x2
), nums(num)
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM a5
), dates(datetime)
AS (
SELECT DATEADD(day, num, @start) FROM nums WHERE DATEADD(day, num, @start) < DATEADD(year, @numberofyears, @start)
)
SELECT YEAR(datetime) * 10000 + MONTH(datetime) * 100 + DAY(datetime) AS DATEKEY, datetime FROM dates

If you don't like your query plan!

In one of the cool channels us SQLMVPs communicate through the question was raised about why do the execution plan do what they do, and we started to elaborate about tweaks and twerks, and apply hinting in one way or the other. Sometimes you need to do these ninja moves, but usually it's because your statistics are skewed and/or your cached query plan is to old and the data has changed to much. This is usually fixed by updating the statistics or rebuild/reorganize your indexes and updating the statistics. It might be that you need to revamp your indexes too. Maybe wrong cluster key or you need a non-clustered index and include some columns. This if the query you're trying to run is a common query or not. Sometimes even a filtered index might be needed, or sometimes partitioning can be of help. Major changes should only be done if this is a common and re-occuring query.

I'd like to end this post with a lovely quote:

I encourage you to consider updating statistics rather than trying syntactic rewrites. Such things worked on rule-based optimizers but it is a goal of the SQL Server QP to avoid these kinds of gymnastics to the extent possible. Focus on the plans that come out and whether the optimizer has the right info to get you the plan shape you think is best more than the syntax. -Conor Cunningham

Do I have that backup somewhere...

Yesterday evening I just cooked up a simple backup recipe, reason was just a simple weekly full/differential backup over all non-system databases on one instance. It's far from perfect, but it works.

It consists of a database to store information about backups of databases in a table with information about the last backups, and a procedure to do the backup. The below script also adds all databases with an database_id higher than 4, i.e. excludes master, model, msdb, and tempdb. If you want specific databases only just change the content of the Managed_Databases table.

USE master;
GO
CREATE DATABASE Simple_Maintenance;
GO
USE Simple_Maintenance;
GO
CREATE TABLE Managed_Databases
 (
  [db_id] int NOT NULL,
  [db_name] sysname NOT NULL,
  Last_Full_Backup datetime2(0) NULL,
  Last_Full_Backup_Number int NULL,
  Last_Differential_Backup datetime2(0) NULL,
  Last_Differential_Backup_Number int NULL
 );
GO
INSERT INTO Managed_Databases([db_id], [db_name])
SELECT database_id, name FROM sys.databases WHERE database_id > 4;
GO

And then we have the procedure, it's some lines of code. Hopefully you can read through it.

USE Simple_Maintenance
GO
CREATE OR ALTER PROCEDURE Make_Backup
 @backup_destination varchar(max) = 'F:\Databases\MSSQL13.MSSQLSERVER\MSSQL\Backup',
 @full_backup_day int    = 7,
 @debug int       = 1
/*
 Day for full backup
 1 Monday
 2 Tuesday
 3 Wednesday
 4 Thursday
 5 Friday
 6 Saturday
 7 Sunday
*/
AS
BEGIN
SET DATEFIRST 1;
DECLARE
 @database varchar(max),
 @name_template varchar(max)  = '@database_@type_@number',
 @type varchar(max),
 @number varchar(max);
DECLARE
 @backup varchar(max) = '
BACKUP DATABASE [@database]
TO DISK = ''@backup_destination\@name.BAK''
WITH
 STATS = 10,

INIT, -- I FORGOT THIS IN THE ORIGINAL RECIPE
 COMPRESSION
',
 @exec varchar(max),
 @db_id int,
 @day int = DATEPART(weekday, getdate()),
 @name varchar(max);

DECLARE dbs CURSOR FOR
SELECT [DB_ID], [DB_NAME] FROM Managed_Databases;

OPEN dbs;

FETCH NEXT FROM dbs INTO @db_id, @database;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @day = @full_backup_day
BEGIN
 SET @type = 'FULL';
 SELECT @number = CAST((ISNULL(Last_Full_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
 SET @name = REPLACE(
    REPLACE(
    REPLACE(
     @name_template,  '@database',@database),
        '@type', @type),
        '@number', @number);
 SET @exec = REPLACE(
    REPLACE(
    REPLACE(
     @backup, '@database',@database),
        '@backup_destination', @backup_destination),
        '@name', @name);
END
ELSE
BEGIN
 SET  @type = 'DIFFERENTIAL';
 SELECT @number = CAST((ISNULL(Last_Differential_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
 SET @name = REPLACE(
    REPLACE(
    REPLACE(
     @name_template,  '@database',@database),
        '@type', @type),
        '@number', @number);
 SET @exec = REPLACE(
    REPLACE(
    REPLACE(
     @backup, '@database',@database),
        '@backup_destination', @backup_destination),
        '@name', @name) +
    ', ' + @type;
END;
IF @debug = 1
BEGIN
PRINT @exec;
END
ELSE
BEGIN
IF @day = @full_backup_day
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
 SET
  Last_Full_Backup = getdate(),
  Last_Full_Backup_Number = CAST(@number as int)
 WHERE [DB_ID] = @db_id;
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM Managed_Databases WHERE [DB_ID] = @db_id AND Last_Full_Backup IS NOT NULL)
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
 SET
  Last_Differential_Backup = getdate(),
  Last_Differential_Backup_Number = CAST(@number as int)
 WHERE [DB_ID] = @db_id;
END;
END;
END;
FETCH NEXT FROM dbs INTO @db_id, @database;
END;
CLOSE dbs;
DEALLOCATE dbs;
END;

And finally you just need to run the procedure. If you execute it with default parameters it will run in debug mode and only print out the syntax for doing backups. The prinout do not care if you have a full backup or not when there is a differential backup day. It gives you the syntax.

The procedure do not have any error checking either, you should probably add try...catch logic, and also checks in the database exists, and are in a backupable state. As I said, I cooked this up for fixing a simple need.

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.