Trace Flags enabled in Azure Managed Instance

-> There are many trace flags enabled by default in Azure Managed Instance.

-> At this point, I am not sure if these trace flags will be enabled after the preview. But I can see trace flags that are used to write finer details to errorlog. So I think these may be removed later.

-> Lets see what these trace flags do. Some details can be incorrect, I will change it going forward as required.

Trace Flag	Description
1800	1) Details related to SQL Server startup. 2) This article mentions this trace flag which is related to “Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments”
2551
2591
3004	Lists the database Backup / restore details step by step.
3447
3605	Redirects output to SQL Server errorlog
3701	Script level upgrade scripts will be written to SQL Server errorlog during failures. This gets added automatically to On-Premise SQL Server during patching.
3978
4141
5521
7838
8015	Ignores physical NUMA detection. This article has more details.
8017	Avoid creating a lot of offline schedulers.
8037
8054
8057
8063
8065
8086
9041
9537
9570
9837	Details related to checkpoint
9850	Details related to checkpoint
9883
9905
9934
9940
9941

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted by

Vivek Janakiraman

Posted on

June 11, 2018

Posted under

Azure, Azure Managed Instance

Comments

Checking SQL Server errorlog in Azure Managed Instance

-> I executed xp_readerrorlog in SSMS and could see the command working fine in Managed Instance same as the On-Premise SQL server.

-> Azure Managed Instance version details from error log,

-> Azure Managed Instance version details using @@version,

Blog40_2

-> The version numbers are different just as the type. I think this will reflect same details in future. The official documentation states that “A Managed Instance runs with all of the features of the most recent version of SQL Server, including online operations, automatic plan corrections, and other enterprise performance enhancements.”.

Update from Microsoft Engineer : Azure Managed Instance is still considering as a PAAS offering which is similar to SQL Azure database, so the “select @@version” will return a similar thing as SQL Azure database.
The Errorlog is something that can be considered as a backed service data for SQL Azure, so it is reflecting the real version of the database engine.

-> There are many trace flags enabled by default. We will discuss what these trace flags do in a separate post.

-> SQL Server errorlog shows that 24 logical processors are detected.

-> Checking the instance properties, the processor count is 24.

Blog40_8

-> But, I have selected 8 cores while building the managed instance. Checking sys.dm_os_schedulers shows that we have 8 online schedulers based on the below screenshot,

Blog40_7

-> Checking the processors tab in server properties shows that we only have 8 cores assigned to the Managed Instance,

-> The extra cores may be used in case the managed instance is scaled up.

-> Memory manager uses conventional memory and not locked pages in memory. Instance collation is SQL_Latin1_general_CP1_CI_AS.

-> Instant file initialization is disabled.

-> The errorlog log contains detailed messages than the On-Premise SQL Server. There are many debug-level messages in the errorlog. It seems like these messages will be used by Microsoft Engineers to troubleshoot issues related to Managed Instance. The traceflags enabled by default on a managed Instance could be the reason for these debug-level messages. I am not sure if these trace flags and messages will be removed after the preview.

-> This article explains about the procedure sp_readmierrorlog that removes most of the debug-messages that are not really required for a normal user.