Procure SQL - Data Architect as a Service - Weekly Newsletter


Here are some videos, tips, and exciting links the Procure SQL team would like to share!

Xbox and SQL Server

Learn how Xbox reduced patching downtime significantly in their SQL Server environment.

Fabric Common Tasks with Python

Are you building complex fabric orchestrations in Python? Microsoft Spark Utilities can be your best friend. It’s like DBATools for Python and Fabric.

Time Series Data

Need to work with Time Series data?  The new Generate_Series and Date_Bucket functions can make your life a lot easier!

State of Database Landscape in 2024

Data management is more complex, and people struggle to support multiple data platforms.

Toys We Always Wanted

Do you remember the number one toy you always wanted but never could have as a kid? Allen’s favorite is the Millennium Falcon. What was the toy you always wanted? Did it make the list?

AI + Microsoft = 3 Trillion Valuation

Microsoft is riding AI to new record heights. See how they became the second company to reach a three trillion valuation.

Free Azure and SQL Server Training in Austin, Texas!

On Saturday, March 9, 2024, SQL Saturday will be coming to Austin, Texas. This is a free training day around SQL Server, Azure, and the Microsoft Data Platform. If you would like lunch to be provided, it’s $20. We will also have two all-day deep dive training classes on performance tuning and Microsoft Analytics on Friday, March 8, 2024, for $125.

Need a Data Architect?

Have you got questions? Need some help? Are you curious to know the cost of procuring a Remote Data Architect?

Procure SQL - Data Architect as a Service - Weekly Newsletter


Here are some videos, tips, and interesting links we have viewed at Procure SQL.

Comparing NULLS

Struggling to compare many columns that contain null values? Here is a great way to do so before SQL 2022; here is a newer way with SQL Server 2022.

Azure SQL Database Limiting Resources

Like life with Azure SQL Databases, you get what you pay for. Here is an overview of why the Azure SQL Database team has resource governance around providing a balanced service. Are you being throttled down due to your tier of Azure SQL Database?

Optimized Locking with Azure SQL Database

Aaron Bertrand writes about long-running transactions leading to fewer lock escalations with optimized locking enabled with Azure SQL Database.

Tesla’s caught out in the cold.

Who knew you needed a high-availability plan for using your car? Not all batteries like subzero temperatures.

Christmas Tree Closet

Is Daniel McCutchen the only one to slide his Christmas tree in and out of his closet?

Finding Changes with Cumulative Updates

John describes why cumulative updates are needed. You can also see why he predicts that external data governance sources will come to SQL Server 2022 soon!

Free Azure and SQL Server Training in Austin, Texas!

On Saturday, March 9, 2024, SQL Saturday will be coming to Austin, Texas. This is a free day of training around SQL Server, Azure, and the Microsoft Data Platform. If you would like lunch to be provided, it’s $20. We will also have two all-day deep dive training classes on performance tuning and Microsoft Analytics on Friday, March 8, 2024, for $125.

Need a Data Architect?

Have you got questions? Need some help? Are you curious to know the cost of procuring a Remote Data Architect?

Every so often, when a new cumulative update (CU) comes out for SQL Server, I like to see what system objects are new and which ones have changed. With the cumulative SQL Server 2022 update eleven this month, I noticed some exciting views not documented in the release notes. I figured I would blog about them and the process I used to identify them. If you didn’t look at the title of this post, they are related to external governance 🙂

New Views In SQL Server 2022 CU11

Don’t just take my word for it. Here are the new views added. Here is an image showing new or modified views. A little later, I will show you how you can find these yourself below.

NOTE: As of January 23rd, 2023, the Microsoft Release notes for Cumulative Update 11 with SQL Server 2022 do not include anything relating to external governance.

External Governance Might Be Coming to SQL Server 2022.

How to Find New or Updated Objects with Cumulative Updates

In this six-minute video, I explain cumulative updates with SQL Server. More importantly, I will show you how I get nosy and check whether views or modules were added or changed.

Learn why Cumulative Updates are important and how they benefit you. Also, learn how to be a detective and see if other updates occurred that might be missing in the release notes.

How Do I Know When Cumulative Updates Are Released?

Microsoft shares the latest updates and version history on their website. I recommend using Visual Ping to notify you when the update page changes.

Scraping System Views After Update is Applied

Here is a script I would use to pull data after applying the update. You can pull more or less, but here is some of the data I looked at to see what has changed in the system views and objects.

use [procuresql]
go
-- New Minor verision number for SQL 2022 CU11 is 4105 --
CREATE SCHEMA [4105] AUTHORIZATION dbo;
GO

USE [master];
go

select * INTO [ProcureSQL].[4105].all_sql_modules From sys.all_sql_modules
select * INTO [ProcureSQL].[4105].all_views FROM sys.all_views
select * INTO [ProcureSQL].[4105].all_columns FROM sys.all_columns
select * INTO [ProcureSQL].[4105].all_objects FROM sys.all_objects
select * INTO [ProcureSQL].[4105].all_parameters FROM sys.all_parameters

SELECT * INTO [ProcureSQL].[4105].assemblies FROM sys.assemblies
SELECT * INTO [ProcureSQL].[4105].assembly_files FROM sys.assembly_files
SELECT * INTO [ProcureSQL].[4105].assembly_modules FROM sys.assembly_modules
SELECT * INTO [ProcureSQL].[4105].assembly_references FROM sys.assembly_references
SELECT * INTO [ProcureSQL].[4105].assembly_types FROM sys.assembly_types
SELECT * INTO [ProcureSQL].[4105].asymmetric_keys FROM sys.asymmetric_keys
select * INTO [ProcureSQL].[4105].spt_values from master.dbo.spt_values

use [msdb]
go
select * INTO [ProcureSQL].[4105].mssqldb_views from sys.views
order by name

Suppose you pulled the same System data for cumulative update ten (CU10), minor number 4035, before applying the cumulative update eleven. We could compare the differences between the modules, views, objects, etc…

Using the free data comparison we provided in a previous blog post, we can see modules that are either new, removed, or changed between CU11 and CU10.

/* What modules are new or different? */
DROP TABLE IF EXISTS #tmp1
DROP TABLE IF EXISTS #tmp2

SELECT o.name AS ObjName, o.[type_desc] 
INTO #tmp1 
FROM [4105].all_sql_modules m join [4105].all_objects o on m.object_id=o.object_id
EXCEPT
SELECT o.name AS ObjName, o.[type_desc] 
FROM [4035].all_sql_modules m join [4035].all_objects o on m.object_id=o.object_id

SELECT o.name AS ObjName, o.[type_desc] 
INTO #tmp2 
FROM [4035].all_sql_modules m join [4035].all_objects o on m.object_id=o.object_id
EXCEPT
SELECT o.name AS ObjName, o.[type_desc] 
FROM [4105].all_sql_modules m join [4105].all_objects o on m.object_id=o.object_id

SELECT ObjName, [type_desc] FROM #tmp1
UNION ALL
SELECT ObjName, [type_desc] FROM #tmp2
order by ObjName

DROP TABLE IF EXISTS #tmp1
DROP TABLE IF EXISTS #tmp2

What is External Governance?

Good question; if you look at the image above, you will notice several new Microsoft shipped views (is_ms_shipped = 1). These views are related to external governance.

My educated guess is that you can add or import external 3rd party sources to help you discover, classify, label & report the sensitive data in your databases. Here is a guide if you are new to data discovery and clarification with SQL Server.

Procure SQL - Data Architect as a Service - Weekly Newsletter


Hopefully, everyone is back into the swing of things as we are in the middle of week three of 2024! Here are some videos, tips, and interesting links we have viewed at Procure SQL.

The Feature That Should Have Been Scrapped

If you take anything away from reading this newsletter, please make sure you don’t have xp_cmdshell enabled. Once again, it was used in multiple attacks!

What’s New in SQL Server 2023

Anna Hoffman shares an amazing list of everything that changed last year in Azure SQL Database, Managed Instance, SQL Server in VM’s.

A New Word: Candling

Steve Jones provides an insightful daily editorial. Last week he introduced a new word, Candling.

AI’s Greatest Accomplishment to Date

AI cooks the perfect steak in 90 seconds or less. Yes, we are foodies at Procure SQL!

Cruises + Power BI Training = A Great Time

Do you like cruises? Do you want to learn Power BI? If so, this training opportunity is for you!

Future of Your Database Ecosystem

Gardner Magic Quadrant for databases is out. Are you also concerned about the complexity of Microsoft DBMSs ecosystem capabilities? If so, we would love to chat to see if we can help make it easier for you.

Free Azure and SQL Server Training in Austin, Texas!

On Saturday, March 9, 2024, SQL Saturday will be coming to Austin, Texas. This is a free day of training around SQL Server, Azure and the Microsoft Data Platform. If you would like lunch to be provided, it’s $20. We will also have two different all-day deep dive training classes on performance tuning and Microsoft Fabric on Friday, March 8, 2024, for $125.

Need a Data Architect?

Have you got questions? Need some help? Are you curious to know the cost of procuring a Remote Data Architect?

Procure SQL - Data Architect as a Service - Weekly Newsletter


Hopefully, everyone is back into the swing of things as we are in the middle of week two of 2024! Here are some videos, tips, and interesting links we have viewed at Procure SQL.

Free Government Data

Russ Loski helps us understand why data that has changed in 2023 might relate to data you do not have.

Understanding Fabric

Can you learn Fabric in 30 days? Microsoft will also be doing a weekly series.

Are Your PowerPoint Accessible?

Microsoft provides excellent tips for making your presentations accessible to everyone, including those with disabilities.

Crawfish?!?!?

Allen and John love themselves some crawfish. See why they are sad that crawfish will go from $5 per lb to $8 per lb.

SSMS + Lakehouse = More DBAs Data Engineers

Did you know you could query a lakehouse with Management Studio?

Soggy Chips

Can you really put fluid into chips?

Procure SQL’s Video Tip of the Week!

This week, we focus on how to query a Microsoft Fabric Lakehouse with SSMS.

Need a Data Architect?

Have you got questions? Need some help? Are you curious how much it costs to procure a Remote Data Architect?

This week, John asked, “Can you query data inside a lakehouse using SQL Server Management Studio?

Yes, did you know you could query your data inside a Microsoft Fabric Lakehouse with SSMS (SQL Server Management Studio)? John didn’t so Justin Cunninham shows you how to leverage SSMS with your data in four minutes In this data architect tip.

If you find yourself needing some Data Architect as a service contact us. If you enjoyed this video, sign up for our newsletter.

Procure SQL - Data Architect as a Service - Weekly Newsletter


Happy New Year! We hope everyone had a happy holiday season. Here are some videos, tips and links we found interesting at Procure SQL.

Developer’s Still Love Free Stuff!

Stack overflow’s 2023 survey shows that developers like free databases. Maybe this is why Microsoft made a Free version of Azure Managed Instances?

Why are SQL Server Queries slow!

Identify, Analyze and Fix database bad coding practices. FYI, this feature doesn’t come with the free open-source database platforms. 🙂

The Cost of Downtime

ITIC’s Global Server Hardware and Server OS Reliability Survey found 91% of respondents estimate that one hour of downtime costs over $301,000. How much does your business loose if it’s down for an hour?

Atari 2600

Now including simulated wood grain finish faceplate.

Do you know this SQL Pioneer?

If you work with SQL, you’ve likely interacted with Stonebraker’s work.

Video Series for Program Management

Matthew Roche also known as BI Polar is working on completing a 52-video series on Program Management.

Procure SQL’s Video Tip of the Week!

This week we focus on how you can utilize a new extended event in Azure SQL and SQL Server 2022 to find query anti-patterns.

Need a Data Architect?

Got questions? Need some help? Curious to see how much it cost to procure a Remote Data Architect?

In the beginning of my IT career, I was a developer. I wrote a lot of bad code that didn’t perform well. As more data entered my tables my stored procedures got slower and slower. More data, more problems! I started my career in performance tuning by making my crappy code a little less crappy. Therefore, I wanted to share with you an extended event added in SQL Server 2022 to help you identify some bad coding practices in your code that is also known as query anti-patterns.

Query Anti-Pattern’s Video

In the following video, you can learn how to capture query anti-patterns via Extended Events. Once you have found them, you can aggregate them to find the most occurring offenders or the offenders causing the most harm!

Query Anti-Pattern Demos

To start this demo, you will need to download a copy of the WildWorldImporters sample database.

You will need SQL Server 2022 as the query anti-pattern extended event is new in SQL Server 2022.

You will also need SQL Server Management Studio if you don’t already have it.

Demo Setup

Create Index

First, we will need to create an index we will use for our implicit conversion demo. If this index already exists, we will drop it.

USE [WideWorldImporters]
GO

DROP INDEX IF EXISTS [idx_Invoices_PurchaseOrder] ON [Sales].[Invoices]
GO

/****** Create Index for Anit-Pattern Implicit Conversion Demo ******/
CREATE NONCLUSTERED INDEX [idx_Invoices_PurchaseOrder] ON [Sales].[Invoices]
(
	[CustomerPurchaseOrderNumber] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [USERDATA]
GO

Create Query Anti-Pattern Extended Event

Next, we will create our extended event used to capture the query anti-pattern. If it exists, we will drop it before creating the extended event.

/***** Create Extended Event *****/
IF EXISTS (SELECT name FROM sys.dm_xe_sessions  WHERE name = 'query_antipattern')
DROP EVENT SESSION [query_antipattern] ON SERVER
GO

CREATE EVENT SESSION [query_antipattern] ON SERVER 
ADD EVENT sqlserver.query_antipattern(
    ACTION(sqlserver.session_id, sqlserver.client_app_name,sqlserver.client_hostname
	,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,
	sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(51200))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
-- Start Extended Event
ALTER EVENT SESSION [query_antipattern] ON SERVER STATE = START;
GO

QDS and Plan Cache Cleanup

Before we start, we will want to clear out or query store data and execution plans in cache. NOTE: Only do this in non-production. We shouldn’t be testing demo’s in production.

/* Start the Demo by puring plans and QDS */
USE [WideWorldImporters]
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR
GO

What are query anti-patterns?

What are query anti-patterns? Great question. I like Andreas Schörghuber’s definition .. “Anti-patterns are common solutions to ineffective problems and cause more problems than they solve.” [1] In simpler terms, a query is written to get the correct data but consumes more reads, CPU or duration than rewriting the query to get the same results in a way that allows the query optimizer to utilize less reads, CPU or duration.

SQL Server’s Extended Events map values also lets you know which anti-patterns can be captured by the query_antipattern event.

/* What types of query_antipatterns are included? */
SELECT map_value 
FROM sys.dm_xe_map_values 
WHERE name = N'query_antipattern_type'; 


Below are the query anti-patterns included in the extended event as of SQL Server 2022 CU 10.

query anti-pattern extended event map values.

Start Query Anti-Pattern’s Demo

Finally, we can now utilize that index we created to go over our query anti-pattern example.

Implicit Conversion AKA ConvertPreventingSeek

In this example, we a have a simple query. As you can see in the video above. The column is NVARCHAR (20) but the predicate filter is INTEGER data type. The following image should help you visualize the data type mismatch.

The query has to convert the data in CustomerPurchaseOrderNumber column to integer to utilize the filter.

/* 
Examine what an implicit conversion is and 
how it will hurt performance! 
*/
SELECT InvoiceID
FROM [Sales].[Invoices]
WHERE [CustomerPurchaseOrderNumber]=17913


If you look at the live data of the extended event we added. You will see the following data collected.

Fix Implicit Conversion Query Anti-Pattern

This fix isn’t magic. It’s actually quite simple. You just have to make the data type of the predicate filter match the data type of the column.

/* How to fix an implicit conversion? 
Change the data type to match the column.
*/
SELECT InvoiceID
FROM [Sales].[Invoices]
WHERE [CustomerPurchaseOrderNumber]=N'17913'

Large Number of In Predicate

For our next query anti-pattern we will focus on an IN statement that has too many values.

/* Long List of IN Statements.
	Do we get alerts for both IN statements and Convert? */
 SELECT *
 FROM Sales.Invoices
 WHERE CustomerPurchaseOrderNumber IN (12126,15342,12211,17129,10369,
 13383,17913,14518,17577,18030,17639,10941,16374,17521,11911,10690,
 10255,13260,16825,12227,16070,16793,12467,13700,11503,13916,17783,
 13042,10411,15084,14159,11389,12559,19457,18468,14486,19057,18036,
 13079,19402,15267,12781,17334,16645,17591,14618,19857,17849,10947,
 11610,14853,10821,15491,14474,10079,11612,11991,19321,10210,14970,
 17468,11643,16411,19525,10996,16474,15832,12134,13630,14902,15212,
 19609,19809,13551,12805,18797,11553,11156,18583,19122,11615,12262,
 10310,19254,13954,13960,14630,18585,18834,18451,19842,18893,13004,
 19890,12941,13508,15403,19391,17938,19140,16362,18795,16388,18492,
 15272,14938,13868,18901,15236,19782,11134,10182,18651,14169,18514,
 18108,17328,13949,12727,17486,13783,15165,17627,13215,18992,10815,
 17466,12153,16275,10695,14744,12253,13328,18450,12232,19473,14441,
 19357,19208,14194,14468,16930,14270,13096,19458,13731,14609,17253,
 13190,16905)

A good question you might be wondering is which query anti-pattern will this query return. Is it the previous TypeConvertPreventingSeek or LargeNumberOfOrINPredicate?

If you cleared the Live Data Viewer for your extended event you will notice that its actually both anti-patterns. In the screen shot below I put a box around the query_hash so you know its the same unique query text. I also include the session_id and timestamp as well.

Fix Large Number of In Predicate

Now there are multiple ways on how you can convert this massive IN clause into joining to a Temp Table, Table Variable, Table-Valued Parameters, etc. I will leave up which one is best for another day. Today, we are going to use the Table-Valued Parameter.

NOTE: I am creating and dropping the table type in the demo code because this is a demo. Usually, you might have a table type per common data type filter you might use.

SECOND NOTE: Hopefully, you will see we also converted the list of integers into a list of NVARCHAR values. This also removes the Implicit Conversion query anti-pattern.

/*  How to fix this anti-pattern?  */
DROP TYPE IF EXISTS KeyList;
GO
CREATE TYPE KeyList AS TABLE ( 
	KeyValue NVARCHAR(50)
	)
GO
	DECLARE @Values KeyList
	INSERT INTO @Values VALUES (N'12126'),(N'15342'),(N'12211'),(N'17129'),(N'10369'),(N'13383'),(N'17913'),(N'14518'),(N'17577'),(N'18030'),(N'17639'),(N'10941'),(N'16374'),(N'17521'),(N'11911'),(N'10690'),(N'10255'),(N'13260'),(N'16825'),(N'12227'),(N'16070'),(N'16793'),(N'12467'),(N'13700'),(N'11503'),(N'13916'),(N'17783'),(N'13042'),(N'10411'),(N'15084'),(N'14159'),(N'11389'),(N'12559'),(N'19457'),(N'18468'),(N'14486'),(N'19057'),(N'18036'),(N'13079'),(N'19402'),(N'15267'),(N'12781'),(N'17334'),(N'16645'),(N'17591'),(N'14618'),(N'19857'),(N'17849'),(N'10947'),(N'11610'),(N'14853'),(N'10821'),(N'15491'),(N'14474'),(N'10079'),(N'11612'),(N'11991'),(N'19321'),(N'10210'),(N'14970'),(N'17468'),(N'11643'),(N'16411'),(N'19525'),(N'10996'),(N'16474'),(N'15832'),(N'12134'),(N'13630'),(N'14902'),(N'15212'),(N'19609'),(N'19809'),(N'13551'),(N'12805'),(N'18797'),(N'11553'),(N'11156'),(N'18583'),(N'19122'),(N'11615'),(N'12262'),(N'10310'),(N'19254'),(N'13954'),(N'13960'),(N'14630'),(N'18585'),(N'18834'),(N'18451'),(N'19842'),(N'18893'),(N'13004'),(N'19890'),(N'12941'),(N'13508'),(N'15403'),(N'19391'),(N'17938'),(N'19140'),(N'16362'),(N'18795'),(N'16388'),(N'18492'),(N'15272'),(N'14938'),(N'13868'),(N'18901'),(N'15236'),(N'19782'),(N'11134'),(N'10182'),(N'18651'),(N'14169'),(N'18514'),(N'18108'),(N'17328'),(N'13949'),(N'12727'),(N'17486'),(N'13783'),(N'15165'),(N'17627'),(N'13215'),(N'18992'),(N'10815'),(N'17466'),(N'12153'),(N'16275'),(N'10695'),(N'14744'),(N'12253'),(N'13328'),(N'18450'),(N'12232'),(N'19473'),(N'14441'),(N'19357'),(N'19208'),(N'14194'),(N'14468'),(N'16930'),(N'14270'),(N'13096'),(N'19458'),(N'13731'),(N'14609'),(N'17253'),(N'13190'),(N'16905')
	select * 
	FROM Sales.Invoices
	JOIN @Values Tst on (Tst.KeyValue = CustomerPurchaseOrderNumber)   	
GO

Query Anti-Pattern Wrap Up

It’s now easier than before, to find your top offending anti-patterns. Just create the extended event and aggregate the results.

If you would like some help making your queries go faster contact us. We also do health checks where you get results. We guarantee value provided or you get a 100% refund!

Also, if you liked this tip join our newsletter so you can get more free videos and tips.