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.
Comments
Also can you please let me know is there any possibility to manage system databass in managed instances...