Changing How I Write Abstracts

Published On: 2020-03-06By:

Sometimes a swift kick in the rear is needed to become motivated.  Recently, I was the recipient of the business end of a boot and it drove me to take a serious look at how I write abstracts for new sessions that I would like to teach.  After reviewing things, I found that my methods weren’t the best and I needed to improve on how I write (not just abstracts but other things like this blog post).

So, I’m trying something new. I’m going to crowd source feedback for new abstracts using GitHub.  Yes, you heard that correctly.  I want you to give me honest, full bore, good, bad, or whatever feedback.

Each title is link to a Gist on GitHub where you can leave your feedback.  Please, be honest and thorough.  If you think the abstract is great, tell me that.  If you think it sucks and I should start over, tell me that too.  Don’t like the title?  Shout it out.  Tell me all of it.

Let’s get this party started!

Improving Performance with Intelligent Query Processing

One of the core pillars in the role of a database administrator is to ensure their systems run as efficiently as possible. We don’t sit around and want a SQL Server to run slower, so we implement steps to ensure our SQL servers perform at maximum capacity. Re-branded from SQL Server 2017 Adaptive Query Processing (AQP) to  Intelligent Query Processing (IQP) with, Microsoft continues to bring forth enhancements in making the query optimizer a learning machine to help intelligently improve performance. Walking away after this session, you’ll have a better understanding on how IQP features, such as scalar user defined function improvements, table variable deferred compilation, or adaptive joins, will help ensure your SQL Servers run at optimal performance.

Indexes: The Voodoo of SQL Server

Microsoft SQL Server is a large data ecosystem with many facets that can affect how your queries perform. Facets like what kind of hardware are you using, how much memory, CPUs. Many of these facets cost money and there are many things about them that can be turned on, tweaked, or implemented to help improve performance. Did you know that you can implement a low-cost solution of proper indexing? Proper indexes can help not only improve query performance but also help save money on hardware! In this session, we’ll examine the foundation of how indexes work, what the moving parts are and why they are important. We’ll examine some real-world examples of where queries were falling short but then were successful with proper indexing. You’ll walk away with techniques on how to know where and how to add indexes to help you start to save money!

SQL Server Performance Tuning for Beginners

We’ve all had to start somewhere in our current career path. My own adventure started what feels like eons ago when I became an accidental DBA overnight and I had absolutely zero clue on how to performance tune our SQL Servers. I was stuck and wasn’t quite sure where to turn. Things had to perform better but no idea where to start. Does this sound familiar? Ringing any bells? If so, this session is for you! We’ll start at the ground floor and talk about the basics of how-to performance tune your SQL servers so that they run at peak performance! We’ll look at configuration settings, database options, trace flags, query tuning and a few tools that can help you squeeze every ounce of performance out of SQL Server! By the end of this session, you’ll walk away with a good solid understanding of how to start performance tuning. You will take away some scripts and tool suggestions that will enable you to hit the ground running back at the office!

The Award for the Two Best SQL Server 2019 Features Goes to…

SQL Server 2019, in my opinion, is one of the greatest releases that has arisen from Microsoft in the last decade. It comes with a multitude of enhancements across the board in the data platform ecosystem. However, there are two features that significantly impact the day to day lives of data professionals everywhere, namely Accelerated Database Recovery (ADR) and Resumable Index Creation. How impressive would it be to have a rollback operation complete in seconds where previously it would be hours or days? Would you like to have the ability to manage how you create that index on your multi-billion row table? Now you can! In this session we will examine in detail these two new features and demonstrate how they can help to accomplish both of those goals and improve your life when dealing with rollbacks and managing index operations. The days of horror stories around these two activities are a thing of the past thanks to SQL Server 2019.

Data Migration to Azure Made Easy

When you decided to move to any cloud provider, the thought about how to migrate all your data can seem like a daunting task. Thankfully, it isn’t as daunting as you might think thanks to some native tools as well as tools offered by Microsoft. In this session we’ll examine these methods and tools that will help you to migrate your data to Azure in a safe, secure, cost effective and successful manner. We will also look how these migrations work when working with three of the Azure data platform products, namely virtual machines, SQL DB, and Managed Instances. By the end of this session you will have gained newfound confidence to help you get your data up into the cloud!

Optimizing Query Performance in Azure SQL Database

Many think that moving to the cloud will not only help brighten your teeth but also solve all your bad coding practices that give you poorly performing queries. If it’s done correctly, implementing Azure SQL Database can help with one of those two and while it can mask things well, the best solution is to fix the bad code. In this hour-long session, we’ll examine several different methods that you can utilize to help fix bad query performance starting with the underlying service tier. Next, we’ll investigate what options are available directly from the Azure portal to determine where the bottlenecks might reside along with possible ways to fix them. Lastly, we will interrogate which native SQL Server tools exist within Azure SQL Database that can really help solve performance issues you might be having. You’ll leave this session with a solid understanding of how to trouble shoot performance issues in Azure SQL Database and what you might be able to do to help fix them.

Summary

Sometimes it is good to look at how you develop new things whether it’s writing, building, or communication.  Deeper investigations into those processes should lead you to improvements and hopefully success.  In this case, I’m trying to get better at writing abstracts.  The competition is fierce these days for speaker selection and I want to be better.  You can help me get there so please do so!

Thank you in advance for your honest feedback!

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC

Finding Foreign Key Child Records In SQL Server

Published On: 2020-01-31By:

Foreign keys help to ensure referential integrity between tables.  In other words, parent records cannot be deleted if there are child records present.   This is a great thing and if you aren’t using foreign keys currently, you really should be.  While very helpful with referential integrity foreign keys can introduce slowness when deleting data, especially if you are wanting to delete the parent record.  In order for the transaction to complete, SQL Server has to check all foreign keys to make sure there aren’t any child records present.

Recently, I had to purge some parent records from a table.  In this case, the parent table had foreign keys, which itself isn’t an issue.  The fact that there were more than 30 of them was.   While SQL Server will happily tell you that you are violating a foreign key if a child record is present when deleting the parent record, finding all of them can be cumbersome.  This is even more true when you have a larger number of foreign keys.

Thankfully, SQL Server can tell us a lot of information about foreign keys including both the parent and child tables as well as the column used.  From this information, we can dynamically create a SELECT statement that would tell us the number of child records that are tied to the parent ID.

Tables

We can use the following system tables to help generate our SELECT statement.

Sys.foreign_key_columns

This table tells us which column is used in the foreign key definition.  We can also use this table to determine the parent table as well as the child table.

Sys.columns

When we join the previous table to sys.columns we obtain the column names which is then used in the ultimate SELECT statement.  We use the column names to construct the JOIN statement that we need to join the parent table to the child table.

Sys.objects

Many applications use different schemas for various reasons.  We have to account for this and using sys.objects allows us to determine the schema name of both the parent and child tables.  The schema name is used in the dynamic query to ensure that we JOIN the right tables together.

The Query

Now that we know the foundation tables that we need, we can build out a SELECT statement that will tell us

  • Parent table name
  • The column name used in the parent table
  • The child table name
  • The column name used in the child table
  • A SELECT statement
-- Parent Table
DECLARE @tableName VARCHAR(150) = 'dbo.Product'

SELECT
    OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'
    , parentcolumns.name AS 'Parent Column'
    , OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'
    , childcolumns.name AS 'Child Column'
    , ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from ' + quotename(schema_name(o1.schema_id)) + '.' + object_name(fkc.referenced_object_id) + ' x 
            INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.' + parentcolumns.name + ' = y.'+ childcolumns.name + ' UNION'  
FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- get child columns
    INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- get parent columns
    INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- get the parent schema name
    inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- get the child schema name
WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)

If you wanted to see all of the tables, you can omit the WHERE clause in the above query.  Let’s take a look at the Product table in AdventureWorks2014.

The SELECT statement is generated using a UNION at the end of it.  This will allow you to run the entire block of statements and have it returned into a single data set.  You will have to remove the final UNION otherwise the query will error out.

Results

From the image below, we can now see all of the child tables that have corresponding records to the parent foreign key.  This information is helpful to determine what children rows might need to be deleted in order to remove parent records.

Ad-hoc Tables

You can also adjust the query to find records for a particular data set.  In other words, if you had a sub-set of unique identifiers based on the foreign key definition you can adjust the query such that you find any child records based on those values.

In this example, I’m using a table variable just for ease of the demo, but this could easily be a physical table or a temporary table. Just remember that a table variable or a temporary table would need to be created within which ever session that you run the larger select statements.

-- Parent Table
DECLARE @tableName VARCHAR(150) = 'Production.Product'
DECLARE @IDs TABLE (ids int)

INSERT @IDs (ids)
    SELECT 316 UNION
    SELECT 317 UNION
    SELECT 318 UNION
    SELECT 319 UNION
    SELECT 320

SELECT
    OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'
    , parentcolumns.name AS 'Parent Column'
    , OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'
    , childcolumns.name AS 'Child Column'
    , ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from @ids x 
            INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.ids = y.'+ childcolumns.name + ' UNION' 
FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- get child columns
    INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- get parent columns
    INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- get the parent schema name
    inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- get the child schema name
WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)

Taking the resulting SELECT statement from the above query, removing the last UNION, and executing, we can then see where child rows exist within the children tables.  We can then go and address those rows before deleting the parent rows.

Summary

SQL Server holds a lot of information on the underlying structures of database objects.  It’s just a matter of knowing where to find the information to build out a solution.  In this case, this script helped me to find and handle a number of children records so that I could finish up deleting their parent records.

It should go without saying, however, run that at your own risk and don’t blindly run code you download from the internet.

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC

Changing MaxDop For Resumable Index Create Operations

Published On: 2020-01-17By:

I’ll admit it, sometimes I’m wrong.

Recently I blogged about the usefulness of a newer feature that was enhanced with the release of SQL Server 2019.  Resumable Indexes operations was introduced with SQL Server 2017 however it was limited to only REBUILD operations.  With the release of SQL Server 2019, Microsoft has extended that ability to CREATE operations now.  Two features that accompany these new abilities, which I consider very versatile, is the ability to specify a maximum degree of parallelism (MAXDOP) or a maximum duration.   Both provide database administrators more granular control over their index operations and how much impact they will affect to their production environment.

In my previous blog, I discussed how you could use the ability to resume an index create operation with a different specified MAXDOP value.  However, in the course of observing additional behavior related to the new system view, sys.resumable_index_operations along with a discussion with Microsoft, it turns out that it doesn’t behave the way I originally thought.  According to Microsoft, the command will accept the syntax of a higher MAXDOP value but internally it is reverted to the originally specified value.  Therefore, my previous blog post was slightly incorrect.  While you can adjust the MAXDOP value during a REBUILD operation, you cannot do the same with a resumable CREATE operation.

Let’s see it in action, but first let’s talk a little bit about  my test environment to make sure everyone gets on the same page

In both examples, I’ll be using the AdventureWorksDWCTP3 sample database along with the dbo.FactResellerSalesXL_PageCompressed table.  This has approximately 11.6 million rows in it.

Also, since my laptop has 8 cores available to it, I will be limiting my test SQL Server instance to only 2 cores by using processor affinity.  I will also use the % Processor Time metric in Performance Monitor to view CPU utilization.

Here is what my processor affinity looks like:

WARNING – DO NOT DO USE PROCESSOR AFINITY IN PRODUCTION!! MODIFYING PROCESSOR AFFINITY CAN CAUSE SERIOUS ISSUES!!

I have also limited the cost threshold for parallelism to 0 and the maximum degree of parallelism.  I did this because I wanted to ensure that the CREATE operation would go parallel if allowed to do so and to limit it to only two cores.

Alright now let’s get started.

Increasing MAXDOP

In this example, first, I’ll create a new non-clustered index by specifying ONLINE, RESUMABLE, and a MAXDOP of two.  With the given configuration of process affinity, a very low-cost threshold of parallelism, and an appropriate maximum degree of parallelism, SQL Server will utilize two cores to build this index.

Here is the synax:

CREATE NONCLUSTERED INDEX ix_factresellersalesXL_pagecompressed_OrderDate
ON dbo.FactResellerSalesXL_PageCompressed (OrderDate)
WITH (ONLINE=ON,RESUMABLE=ON,MAXDOP=2)
GO

We can see in the above video that SQL Server does indeed take the two cores available to it in order to build the index. Once the CREATE operation has completed, we can see CPU utilization drop to within normal limits.

Now, I’ll repeat that process, however, this time I will specify a MAXDOP of one.  In the middle of the CREATE operation I will pause it.  Once the process is paused, I’ll verify that it is in a resumable state and then attempt to restart it with a higher MAXDOP value of two.

CREATE NONCLUSTERED INDEX ix_factresellersalesXL_pagecompressed_OrderDate
ON dbo.FactResellerSalesXL_PageCompressed (OrderDate)
WITH (ONLINE=ON,RESUMABLE=ON,MAXDOP=1)
GO

During this process we can see that only a single core is used even though we’ve specified to resume the operation with a maxdop of two.

Decreasing MAXDOP

If you attempt to lower the MAXDOP value when you resume the operation, it will throw an error:

Msg 8622, Level 16, State 1, Line 15
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Since it’s a CREATE operation, it must reuse the original execution plan so therefore it cannot adjust the MAXDOP value in either case.

Summary

While resumable index operations are still a powerful addition to the database administrator arsenal of tools to help maintain their environments, this is a gotcha that might sneak up on you if you aren’t aware.  If you were planning on utilizing this feature to utilize off hours to speed up the creation of new indexes, you might need to adjust accordingly.   Remember that index rebuilds do not show this behavior so you can adjust as needed!

Make sure to subscribe so that you don’t miss out on future posts!

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC

Happy New Year!

Published On: 2020-01-03By:

Happy New Year!!

Yes, I know it was two days ago but I’m still pseudo-on-vacation-not-really so just another simple blog post wishing you prosperity, health, and good tidings this new year.  I hope that 2020 brings you warmth and joy throughout the year and may it be better than 2019!

Here are some thoughts for the new year:

  • Don’t wait to start that project.  Some day never comes around.
  • You are perfect the way you are.  However, if you want to get healthy, know that you aren’t alone.  You got this.
  • Tell those people close to you how you feel about them.  Tomorrow may not come for some.
  • Tell your kids (fur babies count too!) that you love them as often as you can.  My kids hear it from as often as I can.
  • Finally, be kind to each other.  The world is harsh enough as it is without us beating each other up.

Peace.

 

 

© 2020, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC
1 2 3 4 5 24

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   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers