I am very excited to announce I will be delivering a pre-conference session on SQL Server Performance Tuning on November 5th at PASS Summit 2019. If you have ever attended one of my sessions, you know how passionate I am about this particular topic. So, you also know how very excited I am to have this opportunity to spend a full day talking about SQL Server Performance Tuning and Optimization. Performance Tuning is one of my favorite things to do with SQL Server. There is nothing like seeing performance improvements in an environment as your reward for your hard work. It’s tangible and gratifying. In this session you will be guaranteed to walk away with a list of items to evaluate in your environments and ways you can resolve common issues.
What is PASS Summit?
According to PASS it’s “Interactive training on the latest technologies and spotlights on hot topics such as security, cloud, and AI will be led by the best data minds in the industry”.
To me it’s a 5-day convention that bring geeks together to celebrate and learn from each other. It’s networking, learning, mind melds, fun, inspiration and renewal.
What’s a Pre-Con?
Pre-conference learning opportunities offer a deep dive into new skills and knowledge before the conference officially begins. These highly interactive sessions will help you build expertise and develop the skills that you can immediately put to work in your organization. The all-day time frame lets speakers delivers topics in greater detail, showcasing more demos and answering more of your questions than in a normal session.
SQL Server Performance Tuning and Optimization Abstract
Do your users complain about slow reports? Are your database servers overwhelmed during times of high usage? Every SQL Server environment can benefit from performance tuning whether your environment has one server or thousands. In this full-day session you will learn about how identify problems using a wide variety of tools and scripts and how to implement best practices across your environment. Additionally, you will learn how to begin reading execution plans and how to tune queries to improve your performance within SQL Server. You will walk away with a list of items to evaluate in your environments and ways to resolve common issues. This session will guide you through real-life performance problems which can be solved by best practices and practical skills. Taught on a level anyone can understand, this session will focus on Microsoft SQL Server 2016 and forward.
You will also learn about maintenance activities and how they affect your server’s overall performance, and how to identify when your infrastructure is affecting your performance. Lastly, we will cover the newest performance enhancements coming with the latest release, SQL Server 2019. You’ll leave this demo-filled session better prepared to tackle many issues that can plague SQL Server performance along with the knowledge of how to resolve them.
You can read more here.
How to Attend
You can register here https://www.pass.org/summit/2019/RegisterNow.aspx and using the discount code below you can save $150 on your conference admission.
Want to know more on why you should attend PASS Summit? Check out my blog on Why I Go to Summit Each Year
Another quick post of simple changes you can make to your code to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type to another when comparing values, moving data or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance.
Here is a great chart by Microsoft that shows you conversions and which will cause an implicit or explicit conversion. In this post I will not go into explicit, just know that is what you explicitly tell SQL Server to CAST or CONVERT a value.
Image credit: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017
Let’s look at a very simple but common implicit conversion scenario. Here we have a table Employee with a NationalIDNumber column defined with a NVARCHAR data type. In the query we will use a WHERE clause to search for a specific ID.
In the query below, we have requested NationalIDNumber equal to the integer value 14417807. For SQL Server to compare these two data types it must convert that NVARCHAR to a INT. Which means every value in the that column must go through a conversion process which causes a table scan.
USE AdventureWorks2016CTP3
GO
SET STATISTICS IO ON
GO
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
In the execution plan you will see an exclamation point warning you that there is a potential issue with the query. Hovering over the SELECT operator, you will see that a CONVERT_IMPLICIT is happening which may have affected the optimizer from using a SEEK.
(1 row affected)
Table ‘Employee’. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now the question is how we fix it. It’s really simple but it does require a code change. Let’s look back at our query.
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
Remember we asked for a integer value. Just by add single quotes to the value we can eliminate our issue. It’s important to always know what data types your columns are when querying them. In this case since it is a NVARCHAR all I need to do is supply a character value. This is accomplished by adding single quotes around the value.
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'
It’s simple to see the results. Note above the Scan count 1, logical reads 9, physical reads 0. When we rerun it we get the below.
(1 row affected)
Table ‘Employee’. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
You can also wee in the graphical plan that the warning is now gone, and we have a SEEK instead of the SCAN which is much more efficient.
The only thing left to clean up in this plan is the key lookup. To fix that take a look at my blog from last week here. There are many ways you can end up with implicit or explicit conversion issues and the additional overhead they can create. In most cases they are extremely easy to fix with a little code change, this was just one example. To help you find these in your environment Jonathan Kehayias has written a query to find column side implicit conversions in your plan cache be sure to check it out.
One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to satisfy the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap. It uses a row id instead of a primary key to do the lookup.
As you can see these can very expensive and can result in substantial performance hits in both I/O and CPU. Imagine a query that runs thousands of times per minute that includes one or more key lookups. This can result in tremendous overhead which is generated by these extra reads it effects the overall engine performance.
Let’s look at an example.
SELECT [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],
[UnitPrice],[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[SalesOrderDetail]
Where [ModifiedDate]> 2014/01/01 and [ProductID]=772
The cost of the key lookup operator is 99% of the query. You can see it did an Index Seek to the IX_SalesOrderDetail_ProductID which is very effective, but that index did not have all the columns needed to satisfy the query. The optimizer then went out to the clustered index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to retrieve the additional columns it needed. You can see what it got by hovering over the key lookup in the query plan window.
The good thing about Key and RID look ups is that they are super easy to fix. With a little modification to the non-clustered Index IX_SalesOrderDetail_ProductID we can change to query plan from an Index Seek and a Key Lookup to a very small index seek. All we have to do is recreate that index and add the Output List fields as Included columns on that index.
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID]
ON [Sales].[SalesOrderDetail]([ProductID] ASC)
INCLUDE ([CarrierTrackingNumber],[UnitPrice], [ModifiedDate], [OrderQty])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
And as you can see, we now have an Index Seek only and a more efficient plan.
Key Lookups can cause performance headaches, especially for queries that run many times a day. Do yourself and your environment a favor and start hunting these down and get them fixed. Jonathan Kehayias (B|T) has a great blog on how to locate theses in plan cache be sure to check it out.
March is Women’s History Month, to celebrate the PASS Women in Technology Virtual Group (@PASS_WIT) held a webcast featuring prominent woman in the Data Platform community. I had the pleasure of being one of five panelists alongside these amazing women Lori Edwards (B|T), Malathi Mahadevan (B|T), Kellyn Pot’Vin-Gorman (B|T), and Catherine Wilhemsen (B|T).
Discussion ranged from direct technical topics to what it’s like being a woman in tech. Women took turns providing advice around having a degree and/or certification to succeed in tech. We discussed how to keep up with advances in the field and skills that technologists should have. Specific to being a woman in tech, we talked about having impostor syndrome, having a mentor, being a mentor and how that helped our career. We talked about what question we’re tired of being asked on these panels and what we need to do so we’re never asked that again. We wrapped up with a great question that I’d love for you to answer, “If you could go back in time & speak to yourself before you entered IT, what would you say?”
If you missed the discussion, no worries, you can see it here.
https://www.youtube.com/watch?v=uoyoL5Gd-Z8&t=1s
The PASS Women in Tech Virtual Group, lead Rie Irish (@IrishSQL) and Kathi Kellenberger (@auntkathi), highlights all the great work women are doing in the data platform speaking, writing, educating and building great products. They encourage, empower & energize women to succeed in the tech field. Their mission is to lift as we climb. You can join the group https:/wit.pass.org. Join the mailing list to receive the monthly newsletter that highlights the great work women are doing in tech along with topics important to women in their daily lives.
