Author Archives: johnsterrett

Austin, Texas Best Microsoft Technical Training Oppertunity

If you’re looking to master SQL Server, Power BI, or Microsoft Fabric, attending the SQL Saturday event in Austin, Texas, is one of the smartest moves you can make for your career.

Note: Austin, Texas, is hosting their event on May 2nd and 3rd.

Here’s why:

Free, High-Quality Training

SQL Saturday events are renowned for offering a full day of technical sessions that are entirely free of charge (pay for lunch). Whether a beginner or an experienced professional, you’ll find sessions tailored to your skill level, led by Microsoft employees, industry experts, and Microsoft MVPs passionate about sharing their knowledge. This includes all-day hands-on workshops (usually a paid add-on) and deep dives into the latest features of SQL Server, Power BI, and Microsoft Fabric, ensuring you stay current with the rapidly evolving Microsoft data platform.

Learn from The Experts

Austin Texas SQL Saturday session

Speakers at SQL Saturday events are practitioners who solve real business problems with these technologies daily. You’ll gain practical insights, best practices, and tips you can immediately apply to your job to add value instantly. You’ll see how other companies and consultants leverage SQL Server, Power BI, and Microsoft Fabric to drive their business success.

Networking Opportunities in Austin, Texas

Experts go to and share their knowledge at SQL Saturdays because of their desire to connect, share, and learn together. These connections lead to mentorship, job opportunities, and lasting professional relationships. SQL Saturdays are more than just technical content. It’s a community gathering. You’ll connect with fellow data professionals, speakers, and recruiters. The supportive, grassroots atmosphere makes it easy for newcomers to feel at home and get involved. You never know, your next boss might be sitting next to you in a session.

Career and Community Growth

Attending SQL Saturday is a proven way to invest in your professional development. My company, ProcureSQL, is a living example. We wouldn’t exist without the technical and professional development at SQL Saturdays. It is a key reason why we continue to invest time and money to help these events succeed.

You’ll sharpen your technical skills and gain exposure to leadership and volunteering opportunities that can accelerate your career. Plus, you’ll become part of a global network of data professionals passionate about learning and sharing.

John Sterrett teaching performance tuning
SQL Saturday training class

In short, if you want to learn SQL Server, Power BI, or Microsoft Fabric, SQL Saturday offers an unbeatable combination of free training, expert guidance, and community support. Don’t miss your chance to level up. Join us at SQL Saturday Austin on May 2nd and 3rd, 2025.

PS: If you cannot attend SQL Saturday in Austin and still would like help with your Microsoft Data Platform problems, I am happy to chat one-on-one.

Weekly Content – Nov 4 2024

The following is content I created, links, videos, and other things I found interesting and wanted to share this week.

My Content

Microsoft Fabric Mirroring is Change the Game for Data Analytics – In this quick write-up, you will learn about a no-code, near real-time solution to get your data from your applications into your data lake.

Cloud / Data / Programming

Tech Links

Non-Tech

Videos

Azure Managed Instance Changing DNS prefix

Azure Managed Instances are provisioned by default with a yourname.uniqueid.databases.windows.net DNS fully qualified domain name (FQDN). Even on your private virtual network you will still have to use this FQDN.

If you want your Azure Managed Instance to connect with your DNS prefix like yourname.domain.com this blog post is for you.

Azure Managed Instance Virtual Network

Your first step is to make sure you are either using Azure DNS service or providing one on your own.

In my example we have hybrid setup with Active Directory and DNS Servers in both our virtual network and also on-premises so we will be utilizing an VM in the virtual network to provide DNS.

Azure Virtual Network DNS configuration for your Azure Managed Instance network.
Azure Virtual Network DNS Servers

DNS CNAME Alias Configuration

To allow private users to connect utilizing your DNS Zone you need to create a CName alias in DNS. The alias needs to have the same name as your managed instance. In this example, I created a Azure Managed Instance named “procuresql01mi”. Its FQDN is procuresql01mi.b6d698c00851.database.windows.net. The Domain name of my lab is PASS2020.com. I will configure a CName alias so all requests to procuresql01mi.pass2020.com internally will be routed to procuresql01mi.b6d698c00851.database.windows.net.

DNS CName Alias for changing DNS Zone when connecting to a Azure Managed Instance
DNS CName Alias for Azure Managed Instance

Azure Managed Instance DNS Zone Change Wrap Up

Now all you have to do is connect with the new DNS name used with the alias and you are good to go! If you use Azure AD to connect make sure enable “Trust Server Certificate” on your connection.

rust server certificate for Azure Managed Instance DNS CName Alias
Trust server certificate for Azure Managed Instance DNS CName Alias

Here you can see both connections via SSMS.

Azure managed instance with your DNS domain
Azure Managed Instance with your DNS domain.

If you have any problems and want some help contact us. Also, if you like these tips subscribe to our newsletter.

Join us for FryeDay!

Once again it is PASS Summit week in Seattle. This is the biggest event in the world for SQL Server and Microsoft Data Professionals to gather to connect,

Frye First Time Speaker

Frye First Time Speaker

share and learn.

I will never forget being anxious and scared the first time I gave a presentation at PASS in front of hundreds of people.  Therefore, one of my favorite traditions during PASS Summit is to find a first-time speaker at PASS and try to make them at ease by having their friends and peers wear something special during their first presentation.

This year, I couldn’t think of a better person than Jeremy Frye. I have known Jeremy for years. I have been blessed to work with him at RDX. While everyone in the community knows him as the speaker who wears a Pittsburgh Pirates hat at SQL Saturday’s he is an inspiration to me. He is proof that good guys can be successful in this community. He is one of the most humble, kind and helpful people I know.  I have been

Dress like Frye Day!

Dress like Frye Day!

blessed to see him share his knowledge for years and am excited for everyone to do so this week as well. Therefore, to help Jeremy for his first session we will be giving out Pittsburgh Pirates hats to support Jeremy. If you can make Jeremy’s session on “Speed Up Your SSAS Data Refresh with Dynamic Partition Processing” at 11:00 am in room 604 on Friday (I like to call it FryeDay) come on by the RDX booth and ask for a Pirates hat. When you see Jeremy around this week tell him you got this and you cannot wait for his session!

 

Best Hidden Feature in SQL Server 2019

With Microsoft’s Ignite conference this week a lot of new features are being advertised all over the internet.  Most likely if you are following along you have heard of Big Data Clusters, Spark, Performance Tuning, and security features.  I am really excited about this new feature and hope you are as excited about it too.

 

SQL Server Automatic Tuning in the Real-World

In SQL Server 2016 we saw Query Store.  Query Store was a game changer to help database administrators identify troublesome queries. Query Store helps DBAs make those queries run faster.  Microsoft’s marketing team even jumped on to help coin the phrase, “SQL Server It Just Runs Faster.” With SQL Server 2017, this started to get even better with automatic tuning. Don’t worry database administrators.  Automatic Tuning will just enhance your career and not replace it.

SQL Server 2017 Automatic Tuning looks for queries where execution plans change and performance regresses. This feature depends on Query Store being enabled. Note, even if you don’t turn on Automatic Tuning you still get the benefits of having access to the data. That is right. Automatic Tuning would tell you what it would do if it was enabled.  Think of this as free performance tuning training.  Go look at the DMVs and try to understand why the optimizer would want to lock in an execution plan. We will actually go through a real-world example:

Automatic Tuning with SQL Server 2017

First, let’s take a quick look at the output of the data. You can find the query and results we will focus on below.

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)
                  *(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
--INTO DBA.Compare.Tunning_Recommendations
FROM sys.dm_db_tuning_recommendations
  CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            [current plan_id] int '$.regressedPlanId',
            [recommended plan_id] int '$.recommendedPlanId',

            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,

            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float

          ) as planForceDetails;

I will break the results down into two photos to make them fit well in this blog post.

Free Tuning Recommendations with Automatic Tuning in SQL Server 2017

Free Tuning Recommendations with SQL Server 2017 (1/2)

Automatic Tuning Results in SQL Server 2017

Free Tuning Recommendations with SQL Server 2017 (2/2)

Now we know in the query store query_id 2271 has CPU time changing from 7,235ms to 26ms. That’s a big difference. Let’s take that query and look at its findings by using the tracked query report inside SSMS.

Query Store find history of a query

Find my Changed Query. Did the plans change?

Here we can see the major difference between the two execution plans. One is averaging over 14 seconds in duration while the other is under a second.

Reviewing Query performance in Query Store

Query Store showing the performance difference between the two plans

Now we can select both plans on the screen above and look at the execution plans side by side inside of SSMS. When doing so, we see the common example of the optimizer determining if it is better to scan an index vs a seek with a key lookup.

Side by Side Execution Plan Review in SSMS.

Using SSMS to compare auto tuning recommended query.

To complete the example I want to point out that automatic tuning would lock in the index seek plan (Plan 2392). In SQL Server 2016 you can do this as well manually inside Query Store. With SQL Server 2017 it can be done automatically for you with Automatic Tuning. If you have ever woken up to slow performance due to an execution plan changing and performance going through the drain this might be a life saver.

If you would like to learn about performance tuning changes in SQL Server 2016 and 2017 sign up for our newsletter or catch me talking about these features at SQL Saturday Denver and SQL Saturday Pittsburgh this month.  If you need any help with tuning or upgrading contact us. We would love to chat with you!

 

Why We’re Organizing SQL Saturday in Wheeling, WV on April 28th

Some of my friends know I am a huge fan of the song “Havana” by Camila Cabello. They also know I like to remix songs and if I was to remix the song I would just change the word “Havana” to “Wheeling, WV” because half of my heart is in the Ohio Valley.

Why?

Wheeling is where I grew up as an adult and to this day it is one of the special happy places I like to visit. On April 28th Wheeling will host their the third SQL Saturday. Anyone can attend for FREE! l look forward to sharing my favorite city with the SQL Community and my SQL Family.

Free SQL Server Training in Wheeling, WV

Free SQL Server Training on April 29th in Wheeling, WV

Procure SQL will be teaming up with the Wheeling Chapter of AITP (only IT group in Ohio Valley) to bring some expert SQL Server training from MVPs, MCTs, and community experts to the Ohio Valley. I hope Data Platform professionals in nearby cities like Columbus, Pittsburgh, Harrisburg, Cleveland and Washington DC join is un the fun as well.

Things to Do?

Check out this quick five-minute video to find out some of the great things you can do in Wheeling, WV and why I fell in love with Wheeling!

Food

Colemans Fish Market – It is #1 on TripAdvisor for a reason. Best-fried fish sandwiches.
Ye Old Alpha – The Bridgeport brownie is legendary good.
DeCarlos Pizza – Wheeling’s special version of Pizza. If you get it make sure to eat it quick. Locals typically will eat it on the hood of their cars.
Undos – My personal favorite Italian food restaurant.

Places to See:

Good Mansion Wine – If you like wine, the selection here is fantastic. They will also have an open wine tasting event April 27th at 6 pm. If you are looking for something fun to do the night before the event I would recommend this.
Suspension BridgeIf you like history. You have to check out one of the oldest suspension bridges in the USA. You can still walk and drive across it.
Wheeling Artisan CenterGreat small tour of the history of Wheeling, WV.
Center Market
– Historic part of town with a lot of shops and places to eat. Its an easy walk from the SQL Saturday venue.
Oglebay Resort – Depending on the weather the driving range or ski lift will be open. Seriously, a great five-star resort with epic holiday events including Christmas lights, ogalbayfest, and 4th of July.
Wheeling Island Casino – If you like to play cards and win money its a great location. Used to do it a lot on lunch breaks.

SQL Saturday Chicago

Last weekend I had a blast speaking at the SQL Saturday in Chicago. It was awesome to share my knowledge and also catch up with some good friends.  My talk was on Automating the Pain Away with Query Store and Automated Tuning.  I hope this session helped people leverage Query Store and Automated Tuning to resolve their parameter sniffing problems.

John Sterrett Teaching Performance Tuning

John Sterrett teaching Performance Tuning at SQL Saturday Chicago

John Sterrett teaching performance tuning

SQL Saturday Chicago training class

 

Finding Top Offenders with Extended Events

This week at MVP Summit I got to talk with a friend who loves profiler. We were talking about capturing workloads and doing analysis on them. T-SQL or other 3rd party tools like ClearTrace, ReadTrace would be used to aggregate the data to get insight into the top offenders. I mentioned that workload analysis could be done with extended events without writing a single line of T-SQL.  This was a lightbulb moment for him.  Quickly, I learned that he is not alone and that there are a lot of people in the community who didn’t know this either.

I am including a quick video below to show you why Extended Events is a great solution for finding top offenders in a workload.

 

T-SQL Tuesday #096: The Group Who Changed My Career Forever!

T-SQL Tuesday #96: Folks Who Have Made A Difference

T-SQL Tuesday #96: Folks Who Have Made A Difference

Today’s blog post is about T-SQL Tuesday.  If you haven’t seen T-SQL Tuesday before its a great monthly call for all SQL Server bloggers to write about one topic that always changes from month to month.  In this months installment, we’re focusing on folks who made a difference.

Looking back,  I wrote about this subject almost seven years ago.  While I covered a lot of great people there is a group left out. I wished I included them because they were the first to believe in me, mentor me, and help me become the IT Pro I am today.

Being A SQL Server MVP you might think I would focus on data people.   I am actually going to focus on a forgotten IT organization.  This would be the Association of Information Technology Professionals or AITP for short.  In fact, its really for my local chapter the Greater Wheeling Chapter of AITP (GWC of AITP). For those who didn’t know I grew up as an adult in Wheeling, WV.

The Greater Wheeling Chapter of AITP taught me how to be the best IT Professional I could be. While I attended to eat great food and to learn tech. I learned a whole lot more. I learned that it’s the people who matter and that technology will always change. I started to learn how to lead here as well.  I became a chapter president, heck even became the VP of the region.  I learned a lot of skills here that helped make me the IT person and business owner I am today.

When I wanted to bring SQL Saturday #36 to Wheeling, WV the whole Greater Wheeling AITP Chapter helped and supported me. Even though none of us knew what we were doing.

Earlier this year, Dolph Santorine the current President of GWC of AITP had a speaker cancel. In a bind, he asked if I could do a webinar to help. I refused to do the webinar because I told him I would jump on a flight and be there to do it in-person.  During this trip, over a Bridgeport brownie, I learned there might be another way I could pay things forward back to the GWC of AITP.

How I Will Payback Those Who Helped Me

The GWC of AITP could use another great event to raise awareness and drive up membership.  I will be using my knowledge and #sqlfamily to help bring a third SQL Saturday to Wheeling, WV. 

That’s right, we’re going to go from SQL Saturday #36 to #717 with a good old Throwback SQL Saturday. Save the date: April 28th. Like the old days, this will be a low budget grassroots event that brings some of the best SQL Server training to West Virginia. We are going to prove again that if a SQL Saturday can happen in Wheeling, WV it can happen anywhere!

I look forward to showing my data friends why Wheeling, WV is one of my favorite places in the world! You will quickly see that Wheeling is very different from your typical American small town.