PowerShell for Parsing DiskSpd Results to CSV

Published On: 2019-12-04By:

We have a customer who is moving platforms, and as part of this, I’ve been tasked with testing a lot of various storage configurations. This means lots of utilization of DiskSpd, which is a disk performance benchmarking tool from Microsoft. We could argue about the value of synthetic disk benchmarks, but they are good for testing a variety of disk configurations with a standardized tool for comparing results. It also has the benefit of add the runtime configuration into the results file. So as long as you have your results file, you can know what parameters you ran the test with. (You do have to document your disk configuration–we are using the name of our output file for this).

muscles food

Photo by Elle Hughes on Pexels.com

Anyway, I have a bunch of these files, and I needed to get the data into Excel. Since I was too lazy to figure out how to parse a text file in C#, my first thought was to use some combination of sed, awk, and grep in a bash shell. I reached out to my friend Anthony Nocentino (b|t) about his thoughts on the best way to do this, and he immediately said PowerShell.

When I asked about how to do things I wanted to do with specific bash commands, he mentioned the fact that I could use bash statements that supported standard input and output in PowerShell. The linked blog shows how to do this in Windows, however I wrote all of this code in PowerShell natively on my MacBook Pro.

$path='$InsertYourPathHere'

foreach ($file in $files)

{

$content = get-content $file

$command= ($content)|select -first 1 -skip 1

$results= ($content)|grep total -m1|sed 's/"|"/","/g'|sed 's/"total:"//g'




$results= $results.split(",")

$Output = New-Object -TypeName PSObject -Property @{

FileName = $File.Name

Command = $Command

TotalBytes = $Results[0].Trim()

TotalIOs = $results[1].Trim()

MiBperSec = $results[2].Trim()

IOPs = $results[3].Trim()

AvgLatency = $results[4].Trim()

LatStdDev = $results[5].Trim()}| Select-Object FileName,Command,TotalBytes, TotalIOs, MiBperSec, IOPs,AvgLatency,LatStdDev

$Output|Export-CSV results.csv -Append
}
As you can see, I’m passing output in my $Results variable to a grep to give me the first match of the word “total” and then using sed to do a couple of find and replace commands to make parsing the file a little bit easier. After I’ve done all that, I split the array into a comma delimited set of results, and output it to a CSV file. This allows you to grab the results, with headers and open then in your favorite spreadsheet software. For posterity, the code is available at in our GitHub repo here.

Contact the Author | Contact DCAC

PowerShell for Parsing DiskSpd Results to CSV

Published On: By:

We have a customer who is moving platforms, and as part of this, I’ve been tasked with testing a lot of various storage configurations. This means lots of utilization of DiskSpd, which is a disk performance benchmarking tool from Microsoft. We could argue about the value of synthetic disk benchmarks, but they are good for testing a variety of disk configurations with a standardized tool for comparing results. It also has the benefit of add the runtime configuration into the results file. So as long as you have your results file, you can know what parameters you ran the test with. (You do have to document your disk configuration–we are using the name of our output file for this).

muscles food

Photo by Elle Hughes on Pexels.com

Anyway, I have a bunch of these files, and I needed to get the data into Excel. Since I was too lazy to figure out how to parse a text file in C#, my first thought was to use some combination of sed, awk, and grep in a bash shell. I reached out to my friend Anthony Nocentino (b|t) about his thoughts on the best way to do this, and he immediately said PowerShell.

When I asked about how to do things I wanted to do with specific bash commands, he mentioned the fact that I could use bash statements that supported standard input and output in PowerShell. The linked blog shows how to do this in Windows, however I wrote all of this code in PowerShell natively on my MacBook Pro.

$path='$InsertYourPathHere'

foreach ($file in $files)

{

$content = get-content $file

$command= ($content)|select -first 1 -skip 1

$results= ($content)|grep total -m1|sed 's/"|"/","/g'|sed 's/"total:"//g'




$results= $results.split(",")

$Output = New-Object -TypeName PSObject -Property @{

FileName = $File.Name

Command = $Command

TotalBytes = $Results[0].Trim()

TotalIOs = $results[1].Trim()

MiBperSec = $results[2].Trim()

IOPs = $results[3].Trim()

AvgLatency = $results[4].Trim()

LatStdDev = $results[5].Trim()}| Select-Object FileName,Command,TotalBytes, TotalIOs, MiBperSec, IOPs,AvgLatency,LatStdDev

$Output|Export-CSV results.csv -Append
}
As you can see, I’m passing output in my $Results variable to a grep to give me the first match of the word “total” and then using sed to do a couple of find and replace commands to make parsing the file a little bit easier. After I’ve done all that, I split the array into a comma delimited set of results, and output it to a CSV file. This allows you to grab the results, with headers and open then in your favorite spreadsheet software. For posterity, the code is available at in our GitHub repo here.

Contact the Author | Contact DCAC

Introduction to the performance features on SQL Server on Linux

Published On: By:

I’ve been tinkering with SQL Server on Linux lately and noted a few things in regards to performance I thought I would share with you. SQL Server 2019 on Linux uses the same SQL Server database engine with many of the performance features and services you would find on Windows. There are more similarities than you would initially think. However, if you’re a Linux user who is new to SQL Server, I thought the following introduction to some performance features on SQL Server 2019 on Linux will be helpful.

Columnstore index

As I’ve written about before in my 3-part blog series that you can find here, a columnstore index allows you to store and query large stores of data in a columnar data format, called a columnstore. A columnstore index eliminates the need to maintain separate systems for operational and analytics workloads. The columnstore index maintains a copy of the data so that OLTP and analytics workloads run against separate copies of the data. This minimizes the impact of both workloads running simultaneously.

Let’s take a look again at creating one. You’ll note it’s exactly the same as in Windows.

Create a columnstore index

Using TSQL to add a Columnstore index to the SalesOrderDetail table

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]

   ON Sales.SalesOrderDetail

   (UnitPrice, OrderQty, ProductID)

Now let’s run a query to use our new index.

SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
   SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM Sales.SalesOrderDetail
   GROUP BY ProductID
   ORDER BY ProductID

To verify that the columnstore index was used,  we can view the execution plan which we can easily do just like in Windows or we can look up the object_id for the columnstore index and confirm that it appears in the usage stats for the SalesOrderDetail table:

SELECT * FROM sys.indexes
WHERE name = 'IX_SalesOrderDetail_ColumnStore'

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('AdventureWorks')
   AND object_id = OBJECT_ID('AdventureWorks.Sales.SalesOrderDetail');

In-Memory OLTP

So, here is something I haven’t yet written about. SQL Server’s In-Memory OLTP features can significantly improve the performance of your application systems. However, it’s not for everyone. Here is a couple example that a particularly good candidates for OLTP:

  • Environments that are highly transactional with large numbers of SQL INSERTs concurrently
  • Environments that process heavy calculations using TSQL

Some things In-Memory gives us, whether Linux or Windows  are

  • Memory-optimized tables—A memory-optimized table stores data of itself in active memory and a secondary copy on the disk. It does this in the event the data needs recovery after a shutdown-then-restart of the server or database. The memory-plus-disk duality is unseen and happens only behind the scenes.
  • Natively compiled modules—A native module references memory-optimized tables only it does not pertain to the disk-based copy. Microsoft has seen native compilation result in durations that are 1/100th of the interpreted duration. You can read more about the performance here.

So how do we configure and use In-Memory OLTP

First step is to set the database compatibility level to at least 130, SQL Server 2016. If you don’t know your compat level you can check it by doing the below. If you find you need you change it I have given you the script to do so.

Use the following query to check the current compatibility level of AdventureWorks:

USE AdventureWorks
GO
SELECT d.compatibility_level
FROM sys.databases as d
WHERE d.name = Db_Name();
GO

If necessary, update the level to 130.

ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 130;
GO

When a transaction involves both a disk-based table (regular table) and a memory-optimized table, the memory-optimized portion of the transaction must operate at the transaction isolation level named SNAPSHOT. Execute the following to turn on MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT  to enforce this level for memory-optimized tables in a cross-container transaction:

ALTER DATABASE CURRENT 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO

Now let’s create a Memory Optimized FILEGROUP and a container for data files. Remember we are using Linux, so we use containers for the files, but its just a file group like in windows.

ALTER DATABASE AdventureWorks 
ADD FILEGROUP AdventureWorks_mod CONTAINS memory_optimized_data
GO 

ALTER DATABASE AdventureWorks 
ADD FILE (NAME='AdventureWorks_mod', FILENAME='/var/opt/mssql/data/AdventureWorks_mod') 
TO FILEGROUP AdventureWorks_mod
GO

It’s that easy. Now let’s talk about one of my favorite performance tuning tools, Query Store which is also available in Linux.

Query Store

Query store collects detailed performance information about queries, execution plans, and runtime statistics. Query store isn’t active by default, so you need to enable it on database level for each database you want it. I could write many blogs on Query Store, this is only meant to let you know it exists on Linux too, so I am only scratch the very top surface for you here.

Enable query store with ALTER DATABASE

ALTER DATABASE AdventureWorks 
SET QUERY_STORE = ON;

Here is a query you can run to get information about queries and plans in the query store

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
   JOIN sys.query_store_query AS Qry
      ON Pl.query_id = Qry.query_id
   JOIN sys.query_store_query_text AS Txt
      ON Qry.query_text_id = Txt.query_text_id ;

Query dynamic management views

SQL Server on Linux as well as Windows gives us Dynamic management views that provide server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. This is no different then what we are used to in Windows.

Here is one for example. Query the dm_os_wait stats dynamic management view and you can see for yourself, there is nothing different.

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;

Here I just went over a few performance related things inside SQL Server 2019 in regards to SQL Server on Linux. I did this just to shed some light on the fact that Linux and Windows offer the same tools with just a different underlying OS. Hopefully you can put some of them to use in your own environment. To learn more about what you can do with Microsoft SQL 2019, Microsoft has a free Packt guide Introducing Microsoft SQL 19.


Contact the Author | Contact DCAC

DCAC Supporting Elizabeth Glaser Pediatric AIDS Foundation (@egpaf) #WorldAIDSDay – Redux

Published On: 2019-12-02By:
Tis the season and Denny Cherry & Associates Consulting has a quick way for you to make a difference. All you have to do, is click through to our donation webpage, and that’s it.  For clicking through, DCAC will donate $1 to Elizabeth Glaser Pediatric AIDS Foundation. Every dollar accessed by your participation matters. Having assisted 30 million pregnant women at more than 5,000 clinics in 19 countries, The Elizabeth Glaser Pediatric AIDS Foundation continues to seek an end to global pediatric HIV/AIDS through prevention and treatment programs, research, and advocacy. We’re working with the Elizabeth Glaser Pediatric AIDS Foundation in part because 500 children are newly infected with HIV because their families lack access to the health services they need to prevent transmission.  And this is something that everyone at DCAC wants to help solve.  We have come so far, but progress towards ending AIDS in children and youth is stalling. We must redouble our efforts or risk losing the progress we’ve gained! Want to help out by DONATING MORE? EXCELLENT! We’re matching donations! Click through here to donate to EGPAF! DCAC will match every dollar you choose to donate through our link up to a total of $10,000 US for donations made between December 1st, 2019 through December 24th, 2019 at 11:59:59pm Pacific Time. We announced this yesterday, which was World AIDS Day, but we wanted to announce it again today to make sure that as many people as possible see it.  We urge you to click through and make the $1 donation and if you can make your own donation which DCAC will match to the Elizabeth Glaser Pediatric AIDS Foundation. Denny The post DCAC Supporting Elizabeth Glaser Pediatric AIDS Foundation (@egpaf) #WorldAIDSDay – Redux appeared first on SQL Server with Mr. Denny.
Contact the Author | Contact DCAC
1 2 3 4 444

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link