Measuring GPU Utilization in Remote Desktop Services

I recently spent some time experimenting with GPU Discrete Device Assignment in Azure using the NV* series of VM.  As we noticed that Internet Explorer was consuming quite a bit CPU resources on our Remote Desktop Services session hosts, I wondered how much of an impact on the CPU using a GPU would do by accelerating graphics through the specialized hardware.  We did experiments with Windows Server 2012 R2 and Windows Server 2016. While Windows Server 2012 R2 does deliver some level of hardware acceleration for graphics, Windows Server 2016 did provide a more complete experience through better support for GPUs in an RDP session.

In order to enable hardware acceleration for RDP, you must do the following in your Azure NV* series VM:

  1. Download and install the latest driver recommended by Microsoft/NVidia from here
  2. Enable the Group Policy Setting  Administrative Templates\Windows Components\Remote Desktop Services\Remote Desktop Session Host\Remote Session Environment\Use the hardware default graphics adapter for all Remote Desktop Services sessions as shown below:

To validate the acceleration, I used a couple of tools to generate and measure the GPU load. For load generation I used the following:

  • Island demo from Nvidia which is available for download here.
    • This scenario worked fine in both Windows Server 2012 R2 and Windows Server 2016
    • Here’s what it looks like when you run this demo (don’t mind the GPU information displayed, that was from my workstation, not from the Azure NV* VM):
  • Microsoft Fish Tank page which leverages WebGL in the browser which is in turn accelerated by the GPU when possible
    • This proved to be the scenario that differentiated Windows Server 2016 from Windows Server 2012 R2. Only under Windows Server 2016 could high frame rate and low CPU utilization was achieved. When this demo runs using only the software renderer, I observed CPU utilization close to 100% on a fairly beefy NV6 VM that has 6 cores and that just by running a single instance of that test.
    • Here’s what FishGL looks like:

To measure the GPU utilization, I ended up using the following tools:

In order to do a capture with Windows Performance Recorder, make sure that GPU activity is selected under the profiles to be recorded:

Here’s a recorded trace of the GPU utilization from the Azure VM while running FishGL in Internet Explorer that’s being visualized in Windows Performance Analyzer:

As you can see in the WPA screenshot above, quite a few processes can take advantage of the GPU acceleration.

Here’s what it looks like in Process Explorer when you’re doing live monitoring. As you can see below, you can see which process is consuming GPU resources. In this particular screenshot, you can see what Internet Explorer consumes while running FishGL my workstation.

Windows Server 2016 takes great advantage of an assigned GPU to offload compute intensive rendering tasks. Hopefully this article helped you get things started!

Near Real-time Power BI Dashboard using SQL Server Change Data Capture

As I wanted to have a near real-time dashboard of some of our core databases, I started to look at various solutions to achieve this. It also didn’t hurt that I worked with another proof of concept using CDC in C# and Stream Insight in the past. It gave me some inspiration on that new process and I also took the opportunity to rewrite/improve some of the parts when writing the new PowerShell code.

Here are some of the pieces I ended up selecting:

Dashboard : Microsoft Power BI
Data Extraction: PowerShell
Message Queue: Azure Event Hub
Real-time aggregation engine: Azure Stream Analytics
Darabase Change Tracking: SQL Server Change Data Capture

Overall Process

  1. Configure which table will be tracked by the DataCollector PowerShell process
  2. Configure which columns on those table will be collected
  3. Enumerate the databases that will be collected
  4. Enumerate the tables collected
  5. Enumerate the columns collected
  6. Capture the CDC changes starting after the last LSN that was captured and output that change as a custom PSObject
  7. The PSObject is then converted to a hashtable to facilitate the transmission to Azure Event Hub
  8. The hashtable is then converted to JSON and included as the body of an Azure Event Hub Message.
  9. The message is then queued in Azure Event Hub
  10. The Azure Stream Analytics will then pull the messages from the Azure Event Hub queue
  11. Aggregation/filtering will be performed as per the query defined
  12. The result of the query is then sent as a record in a Power BI table in a dataset.
  13. The Power BI report is based on the dataset, which, when included in a Power BI dashboard, will refresh as new data comes in.

Here’s how the collection process would be invoked:

Get-DataCollectorCDCChange | Convert-DataCollectorCDCChangeToHashTable | Send-SBEventHubMessage -url "<path to the event hub queue" -sasKey "the event hub SAS key (authentication)"

The overall process has a fairly low impact on your source SQL Server. In our case, we’re talking below 25 reads per collection and 15ms of CPU  time.

Here’s a diagram of what the process looks like at a high level:

cdc_stream_analytics_poc_v2

Here’s more information on the various pieces involved in the process.

SQLServer\libSQLServerCDC.psm1

Set-DataCollectorCDCConfiguration: Creates the required tables to track the DataCollector operations. It will create the CDCTrackedTables (tracks which table on which server, database, schema) and CDCTrackedTableColumns (which tracks which columns are captured per tracked table).
Get-SQLCDCTable: Lists the tables on a server/database that have CDC enabled.
New-DataCollectorCDCTrackedTable: Creates a record in the CDCTrackedTables table in the DataCollector configuration database. This determines that this table will be collected by the process. It also keeps track of the last LSN that was captured in the process. By default, when the configuration is created for the table, it will take the first LSN that’s available for that object.
New-DataCollectorCDCTrackedTableColumn: This will create a configuration record in the CDCTrackedTableColumns table that contains the name of the column that will be captured by the process. By default, no columns are configured.
Get-DataCollectorCDCChange
: Overarching process for the data collection, contains the loop that will call Get-DataCollectorCDCTrackedDatabase at specific interval.
Get-DataCollectorCDCTrackedDatabase: Gets the list of databases for which the CDC changes will be collected.
Get-DataCollectorCDCTrackedTable: Gets the list of tables for a table for which the CDC changes will be collected.
Get-DataCollectordCDCTrackedTableColumn: Gets the list of columns that will be extracted from the CDC change record. This allows you to select only a few columns that will be sent further down the process. This helps keeping the data/event message size down that will be sent to Azure Event Hub.
Get-DataCollectorCDCDatabaseChange: Gets the CDC change records from the SQL Server instance/database/table selected. A custom PSObject is sent to the output stream that contains the columns selected by Get-DataCollectordCDCTrackedTableColumn.
Set-DataCollectorCDCTrackedTableLastLSN: Updates the CDCTrackedTables record in the DataCollector database with the last LSN captured by the data collection process.
Convert-DataCollectorCDCChangeToHashTable:
Converts the PSObject to a hashtable to facilitate sending the Azure Event Hub message.

ServiceBus\libServiceBus.psm1

Send-SBEventHubMessage: Sends the CDC change message to Azure Event Hub using JSON as message body.

Azure Event Hub

An Azure Event Hub queue is required to store all the CDC change messages. Those will then be consumed by the Azure Stream Analytics job.

Azure Stream Analytics

An Azure Stream Analytics job is required to process the CDC change messages to perform the filtering/aggregation. You first configure the job input to point to the previously created Event Hub Queue. You will then create a new output that will point to a Power BI account.

You can then define the query based on the defined input and output. Here’s an example of the query I’ve used to generate the dashboard:

SELECT ServerName,
DatabaseName,
SchemaName,
TableName,
_CDC_OperationDescription
COUNT(*)
INTO [power-bi-output]
FROM [cdc-eventhub]
GROUP BY ServerName,
DatabaseName,
SchemaName,
TableName,
TUMBLINGWINDOW(mi,2)

In that particular example, a simple COUNT aggregation is performed on all the messages received per server,database,schema and table. That aggregation operates on tumbling window of 2 minutes. Once the window ends, the result of the aggregation is sent to the output, which in our case is a Power BI table in a dataset.

Once that’s defined, you can then start the Azure Stream Analytics job. The process takes a few minutes, once the job has started, it will start outputting data to Power BI. Note that the Power BI dataset/table will not be created until the job output data.

Power BI

The Power BI part is pretty straightforward. You first define a report that connects to your Azure Stream Analytics dataset/table. You then pin that report to an existing or a new Power BI dashboard. Once you are in the dashboard view and the Azure Stream Analytics job outputs data, you will then see the graph update as new data is received.

Here’s what the end results looks like:

cdc_power_bi_dashboard

Next Steps

Here are some of the future improvements I would like to add:

  • Add a way to enrich the captured data (i.e. foreign key descriptions)
    • By adding reference tables in Azure SQL or
    • By enriching the data as it’s going through the PowerShell pipeline
  • Add infrastructure to orchestrate collection process across multiple servers. i.e Right now the process assumes that all the data collection is done from a single server.

Conclusion

The first release of the code is available on Codeplex with version 3.4.0.2 and forward. I hope this might be valuable to some of you! Should you have any questions regarding this, feel free to let me know through the comments below!

For more information

SQL Server Change Data Capture
Azure Stream Analytics & Power BI: Live dashboard for analytics in real-time on streaming data

GEM Automation 3.0.0.0

Today I took some time to check-in some of the code I’ve been working on lately on CodePlex. Since it’s been about 6 months since the last release, it’s pretty significant. Here are some of the highlights:

  • Windows crash dumps analysis and automation (Get-CrashDump.ps1,Get-CrashDumpAnalysis,Get-CrashDumpAnalysisReport)
    • Gathers crash dumps from all the computers present in Active Directory by default or from a list of computers in a text file and copies them to a central location (just noticed the path is hardcoded in the script, will fix this soon)
    • Run cdb over the memory dumps gathered in an incremental fashion
    • Extract core attributes from the cdb log files (i.e. module faulting, process name, etc.)
    • Create a summary of the collected crash dump attributes and output it to a csv file (I’ll try to post the Excel workbook I use to analyze the output)
  • libWindowsPerformance.psm1
    • Get-PerformanceMonitoring : Capture perfmon counters from a list of computers and output to a file or to the PowerShell pipeline
    • Filter-CounterValues : Filter perfmon counter samples from the PowerShell pipeline. This is useful to remove the samples that have little interest to you. In one case I used this to get only samples that exceeded 50% Processor time on 275 computers
    • Convert-PerformanceCounterToHashTable: I mainly wrote this as an helper function for when I send the perfmon samples to Azure EventHub
    • Store-PerformanceCounter : A function that persist counter samples from the pipeline to a SQL Server database
    • Execute-CounterTrigger: This is a function I use to execute particular action on a particular counter sample. For instance, in the case where I was gather CPU perfmon samples, I executed a trigger to get the list of active processes when the threshold was met to get an idea of what is using CPU on the 275 computers
    • Get-CounterStatistics: On an already collected perfmon log file, query it to get generic statistics (min, max, avg, total)
    • Start-PerfmonOnComputers: An helper function to make sure the required services are running on remote computers to collect perfmon data
  • libStorageSpaces.psm1
    • Series of core helper function that I used while developing automated tests for Storage Spaces (mainly setup of pool, virtual disks)
  • libSQLServerStatistics.psm1
    • Added new functions to gather buffer pool composition (database and object level)
    • Added functions to persist buffer pool composition over time
  • Small change in Get-VHDHierarchy and Get-VMStorageInformation to use CredSSP (required when you have remote storage on SOFS for instance)
  • libHyperVStatistics.psm1
    • Add function to workaround a bug in resource metering where the metering duration is empty while collecting samples
    • Now capturing individual VHD statistics appropriately
  • Monitor-VMUsage.ps1
    • Now capturing individual VHD statistics appropriately
  • libConfiguration.psm1
    • Added new functions to validate configuration files against the centralized configuration store
  • libIIS.psm1
    • New Get-RemoteWebSite function
    • New Get-ImportedLogFiles function
  • libUtilities
    • Improved Assert-PSSession function
    • New Test-FileLock function
  • Initial release of libNetworking.psm1
    • New Test-Port function which allows you to test TCP and UDP ports
    • New Test-ComputerConnectivity function to test whether a computer is responding through various methods
  • Initial release of libNeo4j.psm1
    • Core functions to manipulate and query data in a Neo4j graph database
    • This is used for a POC of a discovery process written in PowerShell that creates a graph in Neo4j that is used as a CMDB.

You can download the latest release here: GEM Automation 3.0.0.0

Here are some of my goals for future releases:

  • Improve documentation (both in PowerShell and on CodePlex)
  • Publish CMDB discovery processes that persist data in Neo4j
  • Ensure the code is using the standard configuration store
In the meantime, try to enjoy this minimally documented release! If you have questions about the code, feel free to ask via the comments below.

Streaming Windows Performance Counters to Azure EventHub and Stream Analytics

I’ve had an interesting problem at work where I needed to capture high-resolution perfmon counters samples in order to highlight better an IO consumption issue. Sampling in something like Operations Manager or Hyper-V resource metering just didn’t cut is as the data was too averaged out. This meant we were losing significant IO spikes information. In order to get a better feeling of the data, I used my old friend PowerShell.

In order to achieve this, I used a helper function called Get-PerformanceMonitoring I wrote around the Get-Counter cmdlet. What this wrapper does is to facilitate the setup of capturing pre-determined performance counters for a list of computers. This saves you having to manually pick each and every counter for each machine you want to monitor. There’s also a few options as to what happens when that setup is done. You can send the samples to a file, capture only one sample and create a file (in case you want to do real-time monitoring, you can use the file with all the counters setup) or you can simply output the samples to the standard PowerShell pipeline as a stream.

After the first 24 hours of collecting that data on only 4 servers, I was left with a 7.7GB file. A file this size is quite painful to consult in perfmon. I then used perfmon to export that data as csv to consume it in Excel. This made it a bit easier to manipulate/summarize the data but it was still an unsustainable process in the long run and it’s also difficult to scale to a large number of computers.

I then started to stream the output of the first function into a filter function called Filter-CounterValues I had used to capture CPU spikes on about 350 PCs. By excluding IO spikes below a specific number, I was able to reduce considerably the data captured but I wanted to still capture more accurately minimum and averages for those servers’ disks.

Then I realized that it might finally be a good excuse to kick the tires on Azure Stream Analytics. I had developed a POC in the past using SQL Server Stream Insights, so I was already familiar with the core concepts and capabilities. Here’s an overview diagram of what that looks like:

streaming_analytics_diagram

First step in getting the data processed by a Stream Analytics job was to send it to an Azure Event Hub. Since I already had a library for Azure Service Bus, I figured it would be straightforward to get this POC going. First step was to download the new Service Bus libraries from NuGet. After that was done, I started to write the function to send messages to Event Hub. Unfortunately, I was never quite able to do it using the .Net API. When calling the method on the EventData object to add properties or data to it, I received an error from PowerShell saying it couldn’t find that specific method even though it was showing up while using Get-Member. Someone might have a hint as to what’s going on here! So after much head scratching and not going anywhere, I decided to use Event Hub REST API instead. After spending some time figuring out how to authenticate (not as obvious as you would think), I finally managed to send messages to Event Hub. Step 1, done!

Creating the Stream Analytics job was a simple process. Define your input by picking the Event Hub queue, define your output by picking an Azure SQL Database and specifying a table to store your results and then finally write the actual Stream Analytics query that crunches that data. I was hitting an issue at first where the job would fail with only a message saying that diagnostic data was not available at this time (it never became available). So I spend some time rewriting the query and casting numerical properly (coming from JSON that didn’t happen automatically it seems) and then everything started working as expected.

In summary, here’s what the process look like at a high level:

1) Capture perfmon counter sample
2) Filter counter samples below a certain value
3) Convert the counter sample object to a hashtable
4) Send the event to Azure Event Hub
5) The Azure Stream Analytics picks the event data
6) Query runs according to tumbling window size
7) Results are persisted

Here’s what the PowerShell command look like:

Get-PerformanceMonitoring -computersListFilePath C:\Code\Windows\MonitoringComputersLists\Production_All_SQL_Servers.txt -counters "\LogicalDisk(*)\Disk Transfers/sec" -outputType Stream | Filter-CounterValues -minimumValue 250 -counterPath "\LogicalDisk(*)\Disk Transfers/sec" -instanceNameExclusion "_total"  | Convert-PerformanceCounterToHashTable -thresholdValue 250 -counterPath "\LogicalDisk(*)\Disk Transfers/sec" | Send-SBEventHubMessage -url "https://<eventhubname>.servicebus.windows.net/perfmoncountersampleshub/messages"

Right now I have two jobs defined that are consuming the same stream of messages/events. I have one that calculating the min/max/avg/count for each counter instance using a 5 minutes tumbling window and another using an hour window. Now it’s very straightforward to have data aggregates created using various windows size (5min,60min, daily,etc) without having to persist highly detailed intermediary data. Here’s what the Stream Analytics query looks like:

SELECT
    ComputerName,CounterName,InstanceName,System.TimeStamp AS SampleTime,MIN(CounterValue) AS MinCounterValue,MAX(CounterValue) AS MaxCounterValue,AVG(CounterValue) AS AvgCounterValue,Count(*) AS SampleCount 
INTO
    SQLOutput
FROM
    EventHubInput
GROUP BY ComputerName,CounterName,InstanceName,TumblingWindow(hour,1)

You can then monitor the jobs execution using the dashboard to keep track of inbound messages and output events:

stream_dashboard

You can also check Event Hub statistics to have an idea of the rate at which the event are sent:

eventhub_dashboard

Once everything was running smoothly, I just built a simple Excel workbook to consume this new data. Here’s what that looks like with some data:

IOPS Per Server
summary_by_srv

IOPS Per Logical Volume
summary_by_vhd

I will also investigate using Power BI to create a proper dashboard. Let me know if you have any questions about this, I’ll be glad to share more detail about my experience.