Things to Remember on SQL Server Azure Managed Instance

 Hi Mates,

This is my first article on Azure Managed Instance. Till now I didn't get the opportunity to work on Cloud however by God's grace finally I started working on it.

In this blog I would like to share my  few of my Observations

Just like in on-premises if we don't configure properly we  will have some negative impacts.

1) Backup compression is not enabled by default

2) Cost threshold for parallelism is 5

3)optimize for Adhoc Workloads is not enabled

4)MaxDop is Zero.

As many of you know we can't stop/start/restart/Pause SQL Server instance and also we can't set max/min Memory settings.

One thing that surprised me a lot is Instant file initialization is not enabled in MI. As I could see the below messages inside my error logs. If you have configured IFI then we will see Zeroing messages only for Log files but not for Data files.

Zeroing C:\WFRoot\DB24C.3\Fabric\work\Applications\Worker.CL_App15\work\data\7fb2af3e-4a45-4eed-9e7a-fcea65576681.mdf from page 33716224 to 33718272 (0x404f000000 to 0x4050000000)

Zeroing completed on C:\WFRoot\DB24C.3\Fabric\work\Applications\Worker.CL_App15\work\data\7fb2af3e-4a45-4eed-9e7a-fcea65576681.mdf (elapsed = 4 ms)

We can check if IFI is enabled or not by running the DMV or searching for the string in error logs like below

When we run sys.dm_server_services on MI it will not give you any output which is Expected. I will try to figure out if there are any DMV'S which runs only in on-premises but not on MI.

Until then Happy Reading.


Thanks for sharing valuable information,
Also can you please let me know is there any possibility to manage system databass in managed instances...