New stored procedure sp_BlitzInMemoryOLTP

[ktaranov]

Do you want to request a feature or report a bug? Feature: New stored procedure for In-Memory objects analyze https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql

Tested on Case Sensitive SQL Server 2014, 2016, 2017 instances. License MIT - we discuss it with original author Ned Otter.

This stored procedure will make you more attractive, teeth whiter and back healthier.

I can create pull request after you check requirements and take some test on your environments.

[BrentOzar]

Cool! First, can you make sure to get Ned to give you official permission somewhere publicly in writing, like on his blog post or in Github? His site says it's copyrighted, so we have to be careful.

Are you sure it worked on 2014? I just took a quick glance and saw STRING_SPLIT.

It doesn't work on my 2017 VM, looks like a problem with NVARCHAR database names. I ran it in debug mode and got this error:

SELECT DISTINCT 'ಠ_ಠ' AS databaseName,
5 AS database_id
 FROM ಠ_ಠ.sys.database_files
 INNER JOIN ಠ_ಠ.sys.filegroups ON database_files.data_space_id = filegroups.data_space_id WHERE filegroups.type = 'FX')
SELECT InMemDatabases.*, sys.databases.log_reuse_wait_desc
FROM InMemDatabases 
INNER JOIN sys.databases ON sys.databases.name = InMemDatabases.databaseName;

Error: 102, Severity: 15, State: 1, Procedure: -, Line: 10, User name: dbo
Incorrect syntax near '¯'.

[BrentOzar]

Also, I should have said this before - thanks for getting started with this! There's definitely a need for understanding it in the community, so I'm excited to see how it goes.

(Also, funny coincidence - your FANTASTIC trace flags list is in this week's Monday newsletter, heh.)

[ktaranov]

It doesn't work on my 2017 VM, looks like a problem with NVARCHAR database names. I ran it in debug mode and got this error:

Oh, Brent I forget you crazy database names with emojy style, tomorrow (+3 UTC time zone) with Alexey we fixed this issue. Could you test on normal database names using @dbName parameter?

(Also, funny coincidence - your FANTASTIC trace flags list is in this week's Monday newsletter, heh.)

Wow, I am happy like russian bear with a balalaika and vodka - thanks for FANATASTIC epithet, I really takes pride in it.

Could you also include another our work in next your awesome newsletter (all my developers must read it every monday before go to home:):

1. 256 SQL Server Tools and Utilites (in your podcast you always ask Richie about difference tools, stop doing it - ask me:): https://github.com/ktaranov/sqlserver-kit/tree/master/Utilities
2. 35 SSMS Tips (maybe you dont know some of them - I will be glad to here what SSMS tricks yoг see for the first time): https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Tips.md
3. 34 SSMS Addins (yep, we tested all of them): https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Addins.md
4. SQL Server version I know you have nifty https://sqlserverupdates.com/ (and of course we subscribed:) but our version more detailed and its MIT license: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Version.md
5. SQL Server Datatypes (most of information here its Microsoft Docs complication but look carefully on last tables - I think its useful for cross database developers): https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Data%20Types.md

[ktaranov]

Are you sure it worked on 2014? I just took a quick glance and saw STRING_SPLIT.

Yes, we tested it on our production database (yes, Russians sometimes do crazy things) and it works really good (and, yes, we dont use emojy for names - its forbidenn according our convention name https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Name%20Convention%20and%20T-SQL%20Programming%20Style.md).

[nedotter]

Hi Brent/Konstantin,

I changed the header on my blog post to remove the copyright notice, and include the MIT license. Please let me know if I need to do anything else.

And thanks to you both for helping to get this script out there.

[nedotter]

Brent is correct, STRING_SPLIT will cause this script to fail on SQL 2014. Konstantin, perhaps the reason it didn't fail for you is that you had not executed any natively compiled modules before running the script?

[ktaranov]

Hi, guys. Great thanks for fast and detailed feedback.

I can repeat 2 issues:

1. Space in database name: @dbName = N'My super database name'
2. Unicode symbols in database name: @dbName = N'ಠ ಠ'

Both issues fixed via https://github.com/ktaranov/sqlserver-kit/commit/d34d41a9a18c24425380e12e854caa764472fb7e @BrentOzar Could you retest on your environments new procedure version: https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql

Also in this commit I changed STRING_SPLIT function on XML alternative but unfortunately I can't find examples for creating simple natively compiled modules in internet. @nedotter , could you give me some code examples for creating this modules to test this part of code?

[BrentOzar]

I’m tied up with work issues for the time being, so I can’t really help test. But once you’ve got it to the point where it successfully analyzes Hekaton stuff on Unicode and case sensitive databases, including all the features folks would be using like compiled DLLs, let us know. You could also check in the FirstResponderKit room in Slack to see if other folks want to help.

On Dec 18, 2017, at 8:35 AM, Konstantin notifications@github.com wrote:

Hi, guys. Great thanks for fast and detailed feedback.

I can repeat 2 issues:

Space in database name: @dbName = N'My super database name' Unicode symbols in database name: @dbName = N'ಠ ಠ' Both issues fixed via ktaranov/sqlserver-kit@d34d41a @BrentOzar Could you retest on your environments new procedure version: https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql

Also in this commit I changed STRING_SPLIT function on XML alternative but unfortunately I can't find examples for creating simple natively compiled modules in internet. @nedotter , could you give me some code examples for creating this modules to test this part of code?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[nedotter]

@ktaranov, not sure if you are aware, but there is also a hekaton channel on slack.

Here is a code snippet to test for loaded native modules:

CREATE DATABASE testdb GO ALTER DATABASE testdb ADD FILEGROUP testdb_InMemFG CONTAINS MEMORY_OPTIMIZED_DATA
GO ALTER DATABASE testdb ADD FILE (name='testdb_Containter1', filename='E:\SQL2016DATA\testdb_Containter1') TO FILEGROUP testdb_InMemFG GO

USE testdb go CREATE TABLE dbo.InMemTable1 ( keyColumn INT IDENTITY PRIMARY KEY NONCLUSTERED ,description CHAR(100) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

INSERT dbo.InMemTable1 ( description ) VALUES (REPLICATE('A', 100)) ,(REPLICATE('B', 100)) ,(REPLICATE('C', 100)) ,(REPLICATE('D', 100)) ,(REPLICATE('E', 100)) ,(REPLICATE('F', 100)) GO

CREATE OR ALTER PROCEDURE dbo.native_sp WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT keyColumn ,description FROM dbo.InMemTable1; END; GO EXECUTE dbo.native_sp;

[nedotter]

I'll install a case-sensitive instance of SQL 2014 tonight, and do some more testing.

[ktaranov]

@BrentOzar we are finished fixed several issues and improvements, now procedure works on all instance with Unicode databases names (tested on SQL Server 2014, 2017 CS). Latest version here: https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql

Tested scripts for creating SQL server 2017 and 2014 In-memory databases with different objects here: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2017.sql https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2014.sql

@nedotter great thanks for improvements and testing, congrats with first pull request (see email for detailed information).

[BrentOzar]

OK, I'm going to give you a little tough love here.

I couldn't step through the 2014 example script - it failed with this error:

Msg 41337, Level 16, State 100, Line 26
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

I know how to fix that, but this is where the tough love comes in: I need you to realize that the person on the other side of the screen is a volunteer too, donating their time. You need to be respectful of their time too. This isn't like a deployment at work where you can throw the code into production and go, "Trust me, this will work."

It has to actually work, and you have to creatively try to break it yourself before you send it to someone else. Believe me, our users are way, way, WAY tougher than I am. They haven't read the documentation, they have crazy SQL Server setups, and any script we put out there has to at least be able to run through its example scripts in order to run.

So I'm going to take a break here and ask you to work with a complete stranger who's actually using Hekaton to get it to work on their systems first. Use the Slack channels, a blog post, whatever, but go find someone who's using Hekaton in the wild. (It can't be yourself - you need to walk a total stranger through using this stored procedure.)

The way we do this with our own scripts is that we first test in our own lab environments, we give it to a coworker to test, and then finally we start using it privately with our clients.

Once you've had at least one stranger use the script successfully, have them post their testing notes in here, and then we'll pick it up from there. Is that fair?

[nedotter]

Hey Brent, thanks for the tough love....will follow up with your recommendations and have user(s) report back here. I don't think anyone with actual field experience with Hekaton would have caught the fact that no In-Memory filegroup had been created. You'd have to be a good tester (clearly not me ;)

[BrentOzar]

Right - testing starts by just taking an empty VM and running the script as-is, heh. That's minimum qualifications for a tester.

[ktaranov]

Good evening, @BrentOzar

OK, I'm going to give you a little tough love here.

You killing me with your tough love. How you find this errors (its talent I think)?) Anyway Great thanks for detailed explanation and recommendation.

Once you've had at least one stranger use the script successfully, have them post their testing notes in here, and then we'll pick it up from there. Is that fair?

I find some In-Memory beginners and ask them to test this stored procedure, waiting for feedback from them.

[Nelo-cool]

Hello guys! @BrentOzar ololo. I had tested on Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64) Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor). My steps:

1. Created little database with very strange name 'ಠ_ಠ' (https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2017.sql)
2. Install sp_BlitzInMemoryOLTP on sql-server
3. Run sp: EXEC sp_BlitzInMemoryOLTP
4. I have saw filled all results sets, but someone is empty. look at the screen below
5. In message page i have saw a few warnings or messages: Warning: The join order has been enforced because a local join hint is used. Verifying collection stats of usp_ingest_table1 Verifying collection stats of native_sp --No modules found that have collection stats enabled

[nedotter]

Thanks very much for your feedback --

The empty result sets - yeah, I've been thinking about a way to avoid that, but it would require either inserting a dummy row, or doing an existence check which would bloat the code. Not sure which way to handle it, but I'll think about it.

For example, if the database does not use memory-optimized temporal tables, we display an empty result set.

Ned Otter

On Mon, Dec 25, 2017 at 2:39 PM, Nelo-cool notifications@github.com wrote:

Hello guys! @BrentOzar https://github.com/brentozar ololo. I had tested on Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64) Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor). My steps:

1. Created little database with very strange name 'ಠ_ಠ'
2. Install sp_BlitzInMemoryOLTP on sql-server
3. Run sp: EXEC sp_BlitzInMemoryOLTP
4. I have saw filled all results sets, but someone is empty. look at the screen below [image: image] https://user-images.githubusercontent.com/902820/34342534-21f521d0-e9c4-11e7-918d-abee3fd238b2.png [image: image] https://user-images.githubusercontent.com/902820/34342536-25b761b6-e9c4-11e7-8d01-1d8c9ff8d9f4.png
5. In message page i have saw a few warnings or messages: Warning: The join order has been enforced because a local join hint is used. Verifying collection stats of usp_ingest_table1 Verifying collection stats of native_sp --No modules found that have collection stats enabled

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-353887529, or mute the thread https://github.com/notifications/unsubscribe-auth/AdfxqZIxnsGawXlm83SCFsG6fcz-_q2hks5tD_ncgaJpZM4RExAV .

[Dm1tr1ch]

Hello guys! I have tested on Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) Dec 22 2017 11:25:00 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

My steps:

1. Created little database with https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2014.sql
2. Install sp_BlitzInMemoryOLTP on sql-server
3. Run sp: EXEC sp_BlitzInMemoryOLTP

Results:

Some warnings: Warning: The join order has been enforced because a local join hint is used. Warning: The join order has been enforced because a local join hint is used. Verifying collection stats of native_sp --No modules found that have collection stats enabled

[Dm1tr1ch]

And another test:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

My steps:

1. Install sp_BlitzInMemoryOLTP on sql-server
2. Run sp: EXEC sp_BlitzInMemoryOLTP

Result Msg 207, Level 16, State 1, Line 4 Invalid column name 'temporal_type_desc'.

[nedotter]

Thanks very much for the feedback -- I will have a look at this tonight.

• Ned

On Fri, Feb 16, 2018 at 4:45 AM, SQLCom notifications@github.com wrote:

And another test:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

My steps:

1. Install sp_BlitzInMemoryOLTP on sql-server
2. Run sp: EXEC sp_BlitzInMemoryOLTP

Result Msg 207, Level 16, State 1, Line 4 Invalid column name 'temporal_type_desc'. [image: 2018-02-16_12-27-51] https://user-images.githubusercontent.com/23631256/36301835-40eb0c22-1317-11e8-9dc4-4f9fa315cf92.png

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-366188626, or mute the thread https://github.com/notifications/unsubscribe-auth/AdfxqTsZV73iF542Kfc-GOfRQEL7KsTiks5tVU5FgaJpZM4RExAV .

[nedotter]

The bug you reported about "Invalid column name 'temporal_type_desc'" has been fixed. I've created a pull request for Konstantin.

The warnings on SQL 2016 were expected.

On Fri, Feb 16, 2018 at 10:17 AM, Ned Otter nedotter@gmail.com wrote:

Thanks very much for the feedback -- I will have a look at this tonight.

• Ned

On Fri, Feb 16, 2018 at 4:45 AM, SQLCom notifications@github.com wrote:

And another test:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

My steps:

1. Install sp_BlitzInMemoryOLTP on sql-server
2. Run sp: EXEC sp_BlitzInMemoryOLTP

Result Msg 207, Level 16, State 1, Line 4 Invalid column name 'temporal_type_desc'. [image: 2018-02-16_12-27-51] https://user-images.githubusercontent.com/23631256/36301835-40eb0c22-1317-11e8-9dc4-4f9fa315cf92.png

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-366188626, or mute the thread https://github.com/notifications/unsubscribe-auth/AdfxqTsZV73iF542Kfc-GOfRQEL7KsTiks5tVU5FgaJpZM4RExAV .

[ktaranov]

@Dm1tr1ch Thank you very much for your testing and feedback! @nedotter, great thanks for quick fix - fixed via https://github.com/ktaranov/sqlserver-kit/pull/139

[BlitzErik]

@ktaranov we haven't heard from you in a while on this. Do you want us to keep the issue open?

[ktaranov]

Hi, @BlitzErik .

We have completed the revision of the procedure in February 2018 according to issues from @Nelo-cool and @Dm1tr1ch and also fixed some another bugs. The latest version of the procedure is in the same place: https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/sp_BlitzInMemoryOLTP.sql

We use it on regular basis on our production and development systems.

If you are not ready to add this procedure to your awesome kit- no problem, open new issue for me or ask to create pull request at any time in future.

[BlitzErik]

I'm ready if you are. Go ahead and create a pull request for me to merge it in.

[BrentOzar]

Is there any documentation on how to use it?

Also, just FYI - on Azure SQL DB, it returns this:

Msg 55000, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 115 [Batch Start Line 4]
For SQL 2014, In-Memory OLTP is only suppported on Enterprise Edition. You are running SQL Server edition: SQL Azure

On SQL Server 2017, it doesn't appear to work with nvarchar database names:

spBlitzInMemoryOLTP @dbName = '¯_(ツ)/¯'

Result:

Msg 55002, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 147 [Batch Start Line 7]
@dbName not found in sys.databases

Those aren't showstoppers, just letting you know. (The documentation would be kind of a big deal though.)

[nedotter]

Gents,

The heading in the script is clear: "NOT tested on Azure SQL Database"

I'll tend to the dbname issue tonight, thanks for the feedback (although I thought we nailed down stuff with your sneaky db names a while ago).

Will get to the documentation shortly.

Thanks,

Ned

On Wed, May 23, 2018 at 11:30 AM, Brent Ozar notifications@github.com wrote:

Is there any documentation on how to use it?

Also, just FYI - on Azure SQL DB, it returns this:

Msg 55000, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 115 [Batch Start Line 4] For SQL 2014, In-Memory OLTP is only suppported on Enterprise Edition. You are running SQL Server edition: SQL Azure

On SQL Server 2017, it doesn't appear to work with nvarchar database names:

spBlitzInMemoryOLTP @dbname = '¯(ツ)_/¯'

Result:

Msg 55002, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 147 [Batch Start Line 7] @dbName not found in sys.databases

Those aren't showstoppers, just letting you know. (The documentation would be kind of a big deal though.)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-391391206, or mute the thread https://github.com/notifications/unsubscribe-auth/AdfxqSFKEjxd9oVJn_hsnhk95Fba-wsmks5t1YCvgaJpZM4RExAV .

[nedotter]

Just checking the script now, and Konstantin has the following listing, which explains the parameters and usage.

Brent, were you looking for more than this, i.e. a blog post?

Thanks,

Ned

######################################

.SYNOPSIS Get detailed information about In-Memory SQL Server objects .DESCRIPTION Get detailed information about In-Memory SQL Server objects Tested on SQL Server: 2014, 2016, 2017 NOT tested on Azure SQL Database .PARAMETER @instanceLevelOnly Only check instance In-Memory related information .PARAMETER @dbName Check database In-Memory objects for specified database .PARAMETER @tableName Check database In-Memory objects for specified tablename .PARAMETER @debug Only PRINT dynamic sql statements without executing it .EXAMPLE EXEC sp_BlitzInMemoryOLTP; -- Get all In-memory information .EXAMPLE EXEC sp_BlitzInMemoryOLTP @instanceLevelOnly = 1; -- Get only instance In-Memory information .EXAMPLE EXEC sp_BlitzInMemoryOLTP @debug = 1; -- PRINT dynamic sql statements without executing it

On Wed, May 23, 2018 at 11:39 AM, Ned Otter nedotter@gmail.com wrote:

Gents,

The heading in the script is clear: "NOT tested on Azure SQL Database"

I'll tend to the dbname issue tonight, thanks for the feedback (although I thought we nailed down stuff with your sneaky db names a while ago).

Will get to the documentation shortly.

Thanks,

Ned

On Wed, May 23, 2018 at 11:30 AM, Brent Ozar notifications@github.com wrote:

Is there any documentation on how to use it?

Also, just FYI - on Azure SQL DB, it returns this:

Msg 55000, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 115 [Batch Start Line 4] For SQL 2014, In-Memory OLTP is only suppported on Enterprise Edition. You are running SQL Server edition: SQL Azure

On SQL Server 2017, it doesn't appear to work with nvarchar database names:

spBlitzInMemoryOLTP @dbname = '¯(ツ)_/¯'

Result:

Msg 55002, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 147 [Batch Start Line 7] @dbName not found in sys.databases

Those aren't showstoppers, just letting you know. (The documentation would be kind of a big deal though.)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-391391206, or mute the thread https://github.com/notifications/unsubscribe-auth/AdfxqSFKEjxd9oVJn_hsnhk95Fba-wsmks5t1YCvgaJpZM4RExAV .

[BrentOzar]

Yeah, I think the output is going to require a lot of interpretation. Keep in mind that we have tens of thousands of subscribers, and if we suddenly include a new script like this, we (as a community, and in Slack) are going to get a ton of support workload with questions about what the output means.

Here are a couple of examples of starting points for documentation:

https://www.brentozar.com/archive/2017/05/announcing-sp_blitzbackups-check-wreck/

https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlocks/

Just something we can at least point people to to say, "Here's what it is, here's what it does, and here's what the output means. If you have questions, go here."

[nedotter]

Totally agree, and I'll get on it.

So would I be submitting the content to you, and it would appear on your site, or am I creating a post that would appear on my blog?

I can also camp out on Slack when it's released, and monitor for related questions.

Thanks,

Ned

On Wed, May 23, 2018 at 12:02 PM, Brent Ozar notifications@github.com wrote:

Yeah, I think the output is going to require a lot of interpretation. Keep in mind that we have tens of thousands of subscribers, and if we suddenly include a new script like this, we (as a community, and in Slack) are going to get a ton of support workload with questions about what the output means.

Here are a couple of examples of starting points for documentation:

https://www.brentozar.com/archive/2017/05/announcing-sp_ blitzbackups-check-wreck/

https://www.brentozar.com/archive/2017/12/introducing- sp_blitzlock-troubleshooting-sql-server-deadlocks/

Just something we can at least point people to to say, "Here's what it is, here's what it does, and here's what the output means. If you have questions, go here."

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-391402357, or mute the thread https://github.com/notifications/unsubscribe-auth/Adfxqb41fXnPR-599mo_VtyhqfZ90tHVks5t1YgOgaJpZM4RExAV .

[BrentOzar]

Great question - it should be yours, since you'd be doing the support for it. (I wouldn't want people coming to BrentOzar.com thinking we'd be a good resource for it, heh.)

[ktaranov]

In SQL Server 2017, it doesn't appear to work with nvarchar database names:

spBlitzInMemoryOLTP @dbname = '¯(ツ)_/¯'

Result:

Msg 55002, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 147 [Batch Start Line 7] @dbName not found in sys.databases

Hi, @BrentOzar . This works for Unicode name, try to run with N prefix EXEC sp_BlitzInMemoryOLTP @dbname = N'¯_(ツ)_/¯' Also updated stored procedure to better handle this situation https://github.com/ktaranov/sqlserver-kit/commit/54a49fe151b2c65dbecc2b4d8b143a40dbc9d59f:

IF (@dbName IS NOT NULL AND @dbName <> N'ALL')
         AND (NOT EXISTS (SELECT 1 FROM #inmemDatabases WHERE name = QUOTENAME(@dbName)) AND @instanceLevelOnly = 0)
    BEGIN
        SET @errorMessage = N'Database [' + @dbName  + N'] not found in sys.databases!!!' + @crlf +
                            N'Do you add N if you has unicode name?' + @crlf +
                            N'Try to exec this: EXEC sp_BlitzInMemoryOLTP @dbName = N''ಠ ಠ_Your_Unicode_DB_Name_ಠ ಠ''';
        THROW 55002, @errorMessage, 1;
        RETURN;
    END;

[ktaranov]

Also, just FYI - on Azure SQL DB, it returns this:

Msg 55000, Level 16, State 1, Procedure sp_BlitzInMemoryOLTP, Line 115 [Batch Start Line 4] For SQL 2014, In-Memory OLTP is only suppported on Enterprise Edition. You are running SQL Server edition: SQL Azure

I will try create new test Azure account in this holidays for testing this procedure on Azure instance.

[ktaranov]

@nedotter , could you prepare extended version of your original post (http://nedotter.com/archive/2017/10/in-memory-oltp-diagnostic-script/) with detailed documentation about using this stored procedure (i can help you with this)? Also if you have some time for testing on Azure that will be cool.

[Nelo-cool]

Ha ha, @dbname = N'¯(ツ)/¯' is made my day, thanks you guys! :) But is logical that ツ is unicode symbol.

[nedotter]

Konstantin et al,

I started to test on Azure SQL db last night, and also began a new blog post. I have not yet tested on Azure Managed Instance.

So it will take me a few days to get this together.

On Thu, May 24, 2018 at 4:19 AM, Nelo-cool notifications@github.com wrote:

Ha ha, @dbname = N'¯(ツ)/¯' is made my day, thanks you guys! :) But is logical that ツ is unicode symbol.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-391630124, or mute the thread https://github.com/notifications/unsubscribe-auth/Adfxqf2TA_7_9aWKNFDPuWL9fXsux510ks5t1mz_gaJpZM4RExAV .

[nedotter]

Gents,

Just wanted to let you know that Konstantin has tested this script on Azure SQL DB (as have I), and now I'm writing a tutorial/blog post. Will keep you updated.

Thanks --

On Thu, May 24, 2018 at 8:27 AM, Ned Otter nedotter@gmail.com wrote:

Konstantin et al,

I started to test on Azure SQL db last night, and also began a new blog post. I have not yet tested on Azure Managed Instance.

So it will take me a few days to get this together.

On Thu, May 24, 2018 at 4:19 AM, Nelo-cool notifications@github.com wrote:

Ha ha, @dbname = N'¯(ツ)/¯' is made my day, thanks you guys! :) But is logical that ツ is unicode symbol.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-391630124, or mute the thread https://github.com/notifications/unsubscribe-auth/Adfxqf2TA_7_9aWKNFDPuWL9fXsux510ks5t1mz_gaJpZM4RExAV .

[BlitzErik]

Thanks Ned!

[nedotter]

Hi Everyone,

I've attached a Word doc that would be the foundation for the blog post I'll update, for sp_BlitzInMemoryOLTP.

All feedback welcomed.

Thanks,

Ned

On Tue, May 29, 2018 at 2:50 PM, BlitzErik notifications@github.com wrote:

Thanks Ned!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1346#issuecomment-392895110, or mute the thread https://github.com/notifications/unsubscribe-auth/AdfxqYjqXmrHXBTsP930dUffC54fU3pVks5t3Zh6gaJpZM4RExAV .

[BrentOzar]

Howdy sir - I think you replied to a Github email, but that doesn't include the attachments in the issue. (I was kinda surprised that it didn't - Github usually seems to do everything else pretty automagically.) If you can't upload it here, you can email it to help@brentozar.com if you want and both Erik & I will get it. I don't think you can upload it in the community Slack - that keeps running out of storage space for attachments.