ADX Dashboards August 2020 update

by Scott Muniz | Aug 6, 2020 | Uncategorized

This article is contributed. See the original author and article here.

If you’ve been using ADX dashboards, you know it is now in public preview. Thank you for your great feedback on ease of use, performance, and deep integration with the Web UI client. You can continue sending your feedback to adxdbdsup@microsoft.com. You can also provide feedback in the feedback box in the dashboard app.

In this update we will present the new ADX dashboards features and capabilities released on August:

Auto refresh

Due to popular customer request, we recently introduced the capability to auto refresh dashboards. This feature allows you to display your ADX dashboards on team and hallway monitors. The dashboards are refreshed regularly to see the latest data. This feature allows you to control the minimal time interval of continuous refresh to reduce the load on the ADX cluster.

Free text parameter

The new “free text” parameter type lets you specify a text value to be used when filtering your dashboards. This is very useful in high cardinality situations where it’s not practical to display thousands or even millions of filter values in the filter control.

Filter search

Filter search allows you to quickly and easily search for specific values in the filter drop-down. This prevents the need to scroll through the whole list of filter values to find the value you need.

Visual formatting options

Using the visual formatting options, you can easily customize various aspects of the visuals.

Sharing and permission improvements

In addition to adding support for AAD groups, we now also fully support guest users within AAD groups.

Stay tuned for more upcoming ADX dashboard features!

Thanks,

ADX Team

Query Design performance issues #02 – Column-side conversions and Function usage in WHERE

by Scott Muniz | Aug 6, 2020 | Uncategorized

This article is contributed. See the original author and article here.

When designing queries and filters (WHERE) sometimes we need to convert some data before filtering, and depending on what you do you may get a bad plan.

Find below some scenarios

• 1 – Date filter
  • 1.1 Solution – Using range filter
  • 1.2 Solution – Using DATE data type
• 2 – Function on Column / TRIM function
  • 2.1 Solution – Cleanup source data
  • 2.2 Solution – Using like
  • 2.3 Solution – Using computed column + index
• 3 – Implicit conversions
  • 3.1 Solution – Using parameters with correct data type

Creating environment

To create a test environment you can use adventure works + this scrip to create a table with data types needed

DROP TABLE IF EXISTS dbo.[Customer_TEST]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer_TEST](
	[CustomerID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	[NameStyle] [dbo].[NameStyle] NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [dbo].[Name] NULL,
	[MiddleName] [dbo].[Name] NULL,
	[LastName] [varchar](50) NULL,
	[Suffix] [nvarchar](10) NULL,
	[CompanyName] [nvarchar](128) NULL,
	[SalesPerson] [nvarchar](256) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[Phone] [dbo].[Phone] NULL,
	[PasswordHash] [varchar](128) NULL,
	[PasswordSalt] [varchar](10) NULL,
	[rowguid] [uniqueidentifier] NULL,
	[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
GO


INSERT INTO [dbo].[Customer_TEST]
	([NameStyle],[Title],[FirstName],[MiddleName],[LastName]
	,[Suffix],[CompanyName],[SalesPerson],[EmailAddress]
	,[Phone],[PasswordHash],[PasswordSalt],[rowguid],[ModifiedDate])   
SELECT 
	 NameStyle
	,Title
	,FirstName
	,MiddleName
	,LastName = CONVERT(VARCHAR(50),LastName)
	,Suffix
	,CompanyName
	,SalesPerson
	,EmailAddress
	,Phone
	,PasswordHash
	,PasswordSalt
	,rowguid
	,ModifiedDate
FROM [SalesLT].[Customer] 
GO 100 -- LOAD 100x


CREATE NONCLUSTERED INDEX IX_Customer_TEST_LastName ON dbo.[Customer_TEST](LastName)
CREATE NONCLUSTERED INDEX IX_Customer_TEST_ModifiedDate ON dbo.[Customer_TEST](ModifiedDate)
GO

1 – Date filter

One classic scenario we see this is when customer is looking to filter data range or when you want to ignore the hour part of the datetime data type.

DECLARE @ModifiedDate DATETIME2(7) = '2009-05-16 16:33:33.077'
SELECT 
     CustomerID
    ,ModifiedDate
    ,CONVERT(VARCHAR(10),ModifiedDate,120)
    ,CONVERT(VARCHAR(10),@ModifiedDate,120)
FROM dbo.[Customer_TEST]
WHERE CONVERT(VARCHAR(10),ModifiedDate,120) = CONVERT(VARCHAR(10),@ModifiedDate,120)

This conversion “CONVERT(VARCHAR(10),ModifiedDate,120)” converts full date + time to “YYYY-MM-DD” format. The problem here is that SQL will have to convert all values in the table to this format before filtering and will not use an index seek.

The plan will show this conversion as a warning that you can quickly find this

1.1 Solution – Using range filter

Use some date math to create two parameters and do like sample below

DateColumn >= DAY 00:00:00 and DateColumn < DAY+1 00:00:00

DECLARE @ModifiedDate DATETIME2(7) = '2009-05-16 16:33:33.077'
SELECT 
     CustomerID
    ,ModifiedDate
    ,STARTDATE = CONVERT(VARCHAR(10),@ModifiedDate,120)
    ,ENDDATE = CONVERT(VARCHAR(10),DATEADD(day, 1, @ModifiedDate),120)
FROM dbo.[Customer_TEST]
WHERE 
    ModifiedDate >= CONVERT(VARCHAR(10),@ModifiedDate,120)
    AND
    ModifiedDate < CONVERT(VARCHAR(10),DATEADD(day, 1, @ModifiedDate),120)
GO

1.2 Solution – Using DATE data type

If time part of the date is not needed for the business you can use DATE data type. It will also consume less space in disk as a bonus.

2 – Function on Column / TRIM function

Sometimes you are searching data that was not cleanup and you may have spaces before and after data and you need to TRIM data before you filter

• “XXX”
• ” XXXX”
• “XXX “

DECLARE @LastName VARCHAR(50)= 'Gates'
SELECT *
FROM dbo.[Customer_TEST]
WHERE TRIM(LastName) = @LastName
GO
DECLARE @LastName VARCHAR(50)= 'Gates'
SELECT LastName, CustomerID
FROM dbo.[Customer_TEST]
WHERE TRIM(LastName) = @LastName
GO

You will get a Cluster Index Scan or Noncluster Index Scan ( If its not needed all columns (*) ) but on both ways you will never be able to get a SEEK

2.1 Solution – Cleanup source data

You can cleanup empty spaces in the data before using it, like a big update in the source data and do not use function in the column side

DECLARE @LastName VARCHAR(50)= 'Gates'
SELECT LastName, CustomerID
FROM dbo.[Customer_TEST]
WHERE LastName = @LastName

2.2 Solution – Using like

If only right trim is needed you can use like with % wildcard at end.

*If you use % at start it will also avoid index usage and is not recommended

DECLARE @LastName VARCHAR(50)= 'Gates'
SELECT * 
FROM dbo.[Customer_TEST]
WHERE LastName LIKE @LastName + '%'

2.3 Solution – Using computed column + index

You can create a computed column and create noncluster index over the computed column.

*I also included LastName in index to avoid lookup when looking for original LastName without the trim

ALTER TABLE dbo.[Customer_TEST]
ADD LastName_Test AS TRIM(LastName)
GO
CREATE NONCLUSTERED INDEX IX_Customer_TEST_LastName_Test 
ON dbo.[Customer_TEST](LastName_Test) INCLUDE (LastName)
GO

You can use the new column or the function used to create computed column and it will be using the index automatically.

This is good because you can fix the performance issue without changing the code. Be aware that as you are adding new column and may affect queries using * to return all columns. That is also not a best practice

3 – Implicit conversions

You should always match the parameter in procedures / application with the table data type. Depending on the data type you have SQL will have to convert the source data and will cause performance issues and avoid index usage

DECLARE @LastName VARCHAR(50) = 'Gates'
SELECT LastName, CustomerID
FROM dbo.[Customer_TEST]
WHERE LastName = @LastName
GO
DECLARE @LastName NVARCHAR(50) = 'Gates'
SELECT LastName, CustomerID
FROM dbo.[Customer_TEST]
WHERE LastName = @LastName

This implicit conversion is shown as a warning

SQL will use Data Type precedence to check if data need to be converted and what the destination.

In this sample you should convert VARCHAR to NVARCHAR, because you can lose information if you convert NVARCHAR to VARCHAR

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql

3.1 Solution – Using parameters with correct data type

As said before match the parameter in procedures / application with the table data type

Update Rollup 2 for System Center Virtual Machine Manager 2019 is here with exciting new features!

by Scott Muniz | Aug 6, 2020 | Uncategorized

This article is contributed. See the original author and article here.

System Center team at Microsoft is committed to partner with customers in their journey to modernize their data centers. We are excited to announce that Update Rollup 2 (UR2) for System Center Virtual Machine Manager (VMM) 2019 is just released and it is packed with exciting new features!

VMM 2019 UR2 release includes new features and bug fixes. In this blog, we will introduce you to all the new features in this release. For a quick update on all the bugs fixed in this release, please refer to the KB article here.  System Center team is also working on next Update Rollup (UR3) for SCVMM 2019.  

Following are the new features released in SCVMM 2019 UR2: 

• Support for managing Windows Server 2012 R2 hosts 
• Support for managing VMware vSphere 6.7 ESXi hosts 
• Support for new Linux versions as guest OS
• Ability to set affinity between vNICs and pNICs 
• IPv6 Support for SDN 
• Simplifying Logical Switch Creation 

Support for managing Windows Server 2012 R2 hosts  

We understand that some enterprises might be using hardware that is not capable of being upgraded to latest Windows Server OS and hence these enterprises face the challenges of managing host servers on various Windows Server OS versions. To make the Windows Server management easier for such enterprises, VMM 2019 UR2 now supports managing 2012 R2 hosts.  

Enterprises can now manage Windows Server 2012 R2 servers as hosts, SOFS and remote library shares in addition to the already supported versions of Windows Server 2016 and Windows Server 2019 OS servers. For details of all the hardware OS versions supported, please refer to the documentation here.   

Windows Servers in the VMM fabric 

Support for managing VMware vSphere 6.7 ESXi hosts 

We are glad to announce that SCVMM 2019 UR2 now extends management support to ESXi 6.7 hosts and vCenter 6.7. Enterprises that have both VMware and Hyper-V environments can make use of this feature in VMM so that both VMware and Hyper-V hosts can be managed within the same fabric management tool. Also, customers looking to migrate their VMware environments to Hyper-V can make use of our support for vSphere 6.7 so that there is seamless migration.  For more details on ESXi server support, please refer here.

VMware servers in the VMM 2019 fabric 

Support for new Linux versions as guest OS

In SCVMM 2019 UR2, we have added support for the following Linux versions as guest OS: 

• Red Hat 8.0 
• Centos 8 
• Debian 10 
• Ubuntu 20.04 

Ability to set affinity between vNICs and pNICS  

VMM 2019 UR2 now supports affinity between vNICs and pNICs. Affinity between vNICs and pNICs brings in flexibility to route network traffic across teamed pNICs. With this feature, customers can increase throughput by mapping RDMA capable physical adapter with a RDMA settings enabled vNIC. 

Use Cases  

• Customers can route specific type of traffic (eg: Live Migration) to a higher bandwidth physical adapter. 
• In HCI deployment scenarios, by specifying affinity, customers can leverage SMB multichannel to meet high throughput for SMB traffic. 

Pre-Requisites to set Affinity between vNICs and pNICs  

•  Logical Switch is deployed on a host 
• SET teaming property is enabled on the logical switch. 

Configuration  

• Open Fabric > Servers > All Hosts > Host group > Hosts > Host.  Right-click Host, select Properties, and navigate to Virtual Switches tab. 
• Verify that the physical adapters to be teamed are added here. Affinity can be mapped only for physical adapters that are added here.

• Click New virtual network adapter to add a new vNIC to the virtual switch 
• By default, the affinity value is set as None. This setting corresponds to the existing behavior, where the operating system distributes the traffic from vNIC to any of the teamed physical NICs. 
• Set the affinity between a vNIC and physical NIC by selecting a physical adapter from the drop-down menu. Once the affinity is defined, traffic from the vNIC is routed to the mapped physical adapter. 

• For more information on vNIC to pNIC mapping, please refer to the documentation here.

IPv6 Support for SDN 

VMM 2019 UR2 now supports IPv6 for SDN deployments. IPv6 support is another exciting feature that helps our customers in their journey to modernize their data centers.  

Advantages of IPv6 over IPv4 

IPv6 was mainly created to overcome the IP address space limitation posed by IPv4. Apart from increasing the number of available IP addresses, IPv6 also provides some other advantages like Improved Security and Auto-Configuration. Customers can now enable IPv6 using VMM in their SDN deployments.

Regulatory and Compliance Requirements 

IPv6 support for SDN not only helps the SC VMM customers who want to take advantage of IPv6 features but also helps the customers who want to have IPv6 support for meeting regulatory and compliance requirements.  

Configuration

To enable IPv6 for SDN deployments, the required changes to setup NC (Network Controller) Gateway, MUX, and SLB (Load Balancer) are highlighted below. In this blog, we will cover the key configuration changes needed for IPv6 support at a high level. For a more detailed explanation about various IPv6 SDN configuration options, please refer to the documentation here.

Create the HNV provider network and Create the IPv6 address pool

• Start the Create Logical Network Wizard. Type a name and optional description for this network.
• In Settings, select the network type as Virtualized Network and choose Microsoft Network Controller managed Network virtualization (SDN v2).

• Right-click the HNV Provider logical network > Create IP Pool.
• Provide a name and optional description, and ensure that the HNV Provider logical network is selected for the logical network.
• In Network Site you need to select the subnet that this IP address pool will service. 
  Note: To enable IPv6 support, add an IPv6 subnet and create IPv6 address pool. To use IPv6 address space, both IPv4 and IPv6 subnets should be added to the network site. 

VM Network

• When you create VM network, to enable IPv6 support, select IPv6 from the dropdown ‘IP address protocol for the VM network’. Please note that dual stack (IPv4 + IPv6) support is not available for VM networks in the current release.

SLB (Load Balancer) 

• To use IPv6 address space, add IPv6 subnet to network site and create IPv6 address pools for the private and public VIP networks.  
• Add IPv6 address pools when you onboard an SLB service 

Gateway 

• While creating GRE VIP logical network, add IPv6 subnet site and create IPv6 address pool. 
• While onboarding Gateway service, click on ‘Enable IPv6’ checkbox and select IPv6 GRE VIP subnet that you have created previously. 
• Also select public IPv6 pool and provide the public IPv6 address 

Site to Site VPN connection 

• To enable IPv6 for site to site VPN connection, routing subnet must be both IPv4 and IPv6. For gateway to work in IPv6, Provide IPv4 and IPv6 addresses separated by ‘;’. 

Simplifying Logical Switch Creation 

Simplifying Logical switch creation is the second step in our journey to simplify VMM networking for our customers. In 2019 UR1 release, we simplified the process to create logical networks. In 2019 UR2 release, we have made it easier for the customers to configure logical switches. 

Based on the feedback we received from the customers with respect to VMM networking, we understood that we need to provide the following to make it easier for the customers to configure VMM networking:

• Smart Defaults and Visual Representations
• Clear explanation of different options 
• Topology View 

Smart Defaults and Visual Representations

General Screen

In General Screen, the default uplink mode is now shown as Embedded Team. There is now clear explanation suggesting the users to use “Embedded Team” as uplink mode for Windows Server 2016 and above. Similarly, there is explanation suggesting the users to use “Team” as uplink mode for Windows Server 2012. There are also visual representations for “Embedded Team” and “Team” options so that it is more clear for the customers.

Extensions Screen

In the Extensions Screen, the default option is now not to have any extensions pre-selected. 

Uplink Screen

In the Uplink screen, we now show only the relevant load balancing algorithms corresponding to the Uplink mode selected. If the customers choose Embedded Team as the uplink mode, then the only supported load balancing algorithms are Hyper-V Port and Dynamic and the default is Hyper-V Port. When the user moves the cursor on Hyper-V Port and Dynamic algorithms, then we show user friendly informational message that Hyper-V Port is the highly recommended algorithm. 

Clear explanation of different options 

Settings Screen

In Settings Screen, we have now provided explanation for various options related to Minimum Bandwidth mode. As the customer changes the minimum bandwidth mode, the corresponding explanation also changes.   

Virtual Port Screen

In Virtual Port Screen, we now show the mapping of Port Classification to Port Profile. 

We have also simplified the text and layout of the screen where the customers add port classification and port profile as shown below. 

We have now added a check and user-friendly error message when the customer tries to proceed to the next screen by only adding port classification and not port profile.   

 Topology View

Once the logical switch is created, then the customer can right click on Logical Switch name and then click on View Topology option to view the topology.

The topology diagram shows the uplink port profiles and virtual network adapters for this logical switch.  

Uplink Port Profiles – Information regarding Load Balancing Algorithm, Teaming Mode and Network Sites is shown in the topology diagram.

Virtual Network Adapters – Information regarding VM Networks, VLANs and Port Classifications is shown in the topology diagram. 

We also want to notify our customers that Logical Network Simplification (introduced in 2019 UR1) and Logical Switch Simplification (introduced in 2019 UR2) are just intermediate steps in simplifying VMM networking and our end goal is to have a revamped UX/UI for networking section so that users can easily and intuitively configure VMM networking settings.