SQL SERVER – Example of Performance Tuning for Advanced Users with DB Optimizer

Performance tuning is such a subject that everyone wants to master it. In beginning everybody is at a novice level and spend lots of time learning how to master the art of performance tuning. However, as we progress further the tuning of the system keeps on getting very difficult. I have understood in my early career there should be no need of ego in the technology field. There are always better solutions and better ideas out there and we should not resist them. Instead of resisting the change and new wave I personally adopt it.

Here is a similar example, as I personally progress to the master level of performance tuning, I face that it is getting harder to come up with optimal solutions. In such scenarios I rely on various tools to teach me how I can do things better. Once I learn about tools, I am often able to come up with better solutions when I face the similar situation next time.

A few days ago I had received a query where the user wanted to tune it further to get the maximum out of the performance. I have re-written the similar query with the help of AdventureWorks sample database.

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID;

User had similar query to above query was used in very critical report and wanted to get best out of the query. When I looked at the query – here were my initial thoughts

  • Use only column in the select statements as much as you want in the application
  • Let us look at the query pattern and data workload and find out the optimal index for it

Before I give further solutions I was told by the user that they need all the columns from all the tables and creating index was not allowed in their system. He can only re-write queries or use hints to further tune this query.

Now I was in the constraint box – I believe * was not a great idea but if they wanted all the columns, I believe we can’t do much besides using *. Additionally, if I cannot create a further index, I must come up with some creative way to write this query. I personally do not like to use hints in my application but there are cases when hints work out magically and gives optimal solutions.

Finally, I decided to use Embarcadero’s DB Optimizer. It is a fantastic tool and very helpful when it is about performance tuning.

I have previously explained how it works over here.

First open DBOptimizer and open Tuning Job from File >> New >> Tuning Job.

Once you open DBOptimizer Tuning Job follow the various steps indicates in the following diagram.

Essentially we will take our original script and will paste that into Step 1: New SQL Text and right after that we will enable Step 2 for Generating Various cases, Step 3 for Detailed Analysis and Step 4 for Executing each generated case. Finally we will click on Analysis in Step 5 which will generate the report detailed analysis in the result pan.

The detailed pan looks like. It generates various cases of T-SQL based on the original query. It applies various hints and available hints to the query and generate various execution plans of the query and displays them in the resultant. You can clearly notice that original query had a cost of 0.0841 and logical reads about 607 pages. Whereas various options which are just following it has different execution cost as well logical read. There are few cases where we have higher logical read and there are few cases where as we have very low logical read.

If we pay attention the very next row to original query have Merge_Join_Query in description and have lowest execution cost value of 0.044 and have lowest Logical Reads of 29. This row contains the query which is the most optimal re-write of the original query. Let us double click over it.

Here is the query:

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID
OPTION (MERGE JOIN)

If you notice above query have additional hint of Merge Join. With the help of this Merge Join query hint this query is now performing much better than before.

The entire process takes less than 60 seconds.

Please note that it the join hint Merge Join was optimal for this query but it is not necessary that the same hint will be helpful in all the queries. Additionally, if the workload or data pattern changes the query hint of merge join may be no more optimal join. In that case, we will have to redo the entire exercise once again. This is the reason I do not like to use hints in my queries and I discourage all of my users to use the same.

However, if you look at this example, this is a great case where hints are optimizing the performance of the query. It is humanly not possible to test out various query hints and index options with the query to figure out which is the most optimal solution. Sometimes, we need to depend on the efficiency tools like DB Optimizer to guide us the way and select the best option from the suggestion provided.

Let me know what you think of this article as well your experience with DB Optimizer. Please leave a comment.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – TechEd India 2013 Sessions and Relevent Pluralsight Courses

I am presenting at TechEd India 2013 two SQL Server session. You can read about my session in this blog post.

Yesterday I presented on topic SQL Server Performance Troubleshooting: Ancient Problems and Modern Solutions. Today I will be presenting on the subject Indexes – The Unsung Hero. If you are at TechEd India you must show up in my session – we will have fun talking about Indexes and performance tuning together. You can read about various details about the session over here.

However, if you are not at TechEd India 2013 and still want to know what I am going to cover in the session here is something you can do it. You can watch the following two of my Pluralsight courses which absolutely covers what I am going to talk about in TechEd India 2013.

SQL Server Performance: Introduction to Query Tuning 

SQL Server performance tuning is an art to master – for developers and DBAs alike. This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems. This includes an introduction to understanding execution plans inside SQL Server.

In this almost four hour course we cover following important concepts.

  • Introduction 10:22
  • Execution Plan Basics 45:59
  • Essential Indexing Techniques 20:19
  • Query Design for Performance 50:16
  • Performance Tuning Tools 01:15:14
  • Tips and Tricks 25:53
  • Checklist: Performance Tuning 07:13

The duration of each module is mentioned besides the name of the module.

SQL Server Performance: Indexing Basics

This course teaches you how to master the art of performance tuning SQL Server by better understanding indexes.

In this almost two hour course we cover following important concepts.

  • Introduction 02:03
  • Fundamentals of Indexing 22:21
  • Practical Indexing Implementation Techniques 37:25
  • Index Maintenance 16:33
  • Introduction to ColumnstoreIndex 08:06
  • Indexing Practical Performance Tips and Tricks 24:56
  • Checklist : Index and Performance 07:29

The duration of each module is mentioned besides the name of the module. You will have to login to watch above courses. 

So if you are going to miss my session at TechEd India, you still have a chance to catch up on what I am going to present by watching the Pluralsight courses listed above.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Presenting Two Technology Sessions at TechEd India 2013 – Today and Tomorrow

Every year I am looking forward to TechEd India as it presents a wonderful opportunity to meet community in first hand. Community is my passion and I love to get involved with it at every single opportunity it presents. This year once again I am presenting a technology session at TechEd India. I will be presenting on the following subject.

Here is something I promise – if you attend my session – when you walk out of my session you will immediate action items which you can use it for your production server and improve the performance of the database. Additionally, I will have some goodies with me for everyone. I will have few of my books, free subscription access to Pluralsight’s library as well something totally interesting as a giveaway.

Here are the details of the sessions:

SQL Server Performance Troubleshooting: Ancient Problems and Modern Solutions

Date: March 18, 2013 – Bangalore
Date: March 25, 2013 - Pune
Time: 2:45 – 3:45 PM

Many believe Performance Tuning and Troubleshooting is an art which has been lost in time. The truth is that art has evolved with the time and there are more tools and techniques to overcome ancient troublesome scenarios. There three major resource when bottlenecked creates performance problem: CPU, IO, and Memory. In this session we will focus on High CPU scenarios detection and their resolutions. If time permits we will cover other performance related tips and tricks. At the end of the session attendee will have clear ideas and action items regarding what to do in when facing any of the above resource intensive scenarios.

Indexes – The Unsung Hero

Date: March 19, 2013 – Bangalore
Date: March 26, 2013 – Pune
Time: 2:45 – 3:45 PM

Slow Running Queries  are the most common problem that developers face while working with SQL Server. While it is easy to blame the SQL Server for unsatisfactory performance, however the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.

Looking forward to see you in session!

Pluralsight will be sponsoring the TechEd India event and don’t forget to stop by the Pluralsight booth for some goodies and meet the Pluralsight executives.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Avoid Using Function in WHERE Clause – Scan to Seek

“Don’t use functions in the WHERE clause, they reduce performance.”

I hear this quite often. This is true but this subject is hard to understand in a single statement. Let us see what it means and how to use the function in the WHERE clause.

We will be using sample database AdventureWorks in this example. Additionally, turn on STATISTICS IO ON settings so we can see various statistics as well.

USE AdventureWorks2012
GO
SET STATISTICS IO ON
GO

Let us first execute following query and check the execution plan and statistics.

-- SCAN - Select values from SalesOrderDetail
SELECT [SalesOrderID], [SalesOrderDetailID], [ModifiedDate]
FROM [Sales].[SalesOrderDetail]
WHERE DATEDIFF(YEAR,ModifiedDate,GETDATE()) < 0
GO

Table 'SalesOrderDetail'. Scan count 1, logical reads 1246

You can see that the above query is scanning the whole table as well even though it is not returning any result it is reading 1246 pages from database.

In this case we are retrieving the data based on the ModifiedDate so we will create an index on the ModifiedDate Column.

-- Create Index on ModifiedDate
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ModifiedDate]
ON [Sales].[SalesOrderDetail] ([ModifiedDate])
GO

Now we have created an index on the ModifiedDate column we will once again execute the same query which we had run earlier.

-- SCAN - Select values from SalesOrderDetail
SELECT [SalesOrderID], [SalesOrderDetailID], [ModifiedDate]
FROM [Sales].[SalesOrderDetail]
WHERE DATEDIFF(YEAR,ModifiedDate,GETDATE()) < 0
GO

Table 'SalesOrderDetail'. Scan count 1, logical reads 338

You can see that the above query is still scanning the whole table as well even though it is not returning any result it is read over 338 pages from database.

The reason for the same is because in the query we are using the function DATEDIFF over the column ModifiedDate. As the outcome of this function is evaluated at the run time, the SQL Server Query engine has to scan the whole table to get necessary data.

To avoid this situation, let us try to avoid using the function on the column of the table and re-write the query. To re-write the query let us first understand what the query is retrieved. It is retrieving all the rows where the year difference between ModifiedDate and Current Date is less than 0. In other words what it means is that we need to retrieve the records which have a future date. We can simply re-write above query in the following way.

-- SEEK - Select values from SalesOrderDetail
SELECT [SalesOrderID], [SalesOrderDetailID], [ModifiedDate]
FROM [Sales].[SalesOrderDetail]
WHERE ModifiedDate > GETDATE()
GO

Table 'SalesOrderDetail'. Scan count 1, logical reads 3

Now let us execute the query and see the execution plan. We can see that there are only 3 logical read now and execution plan is also displaying Seek. This is because now we are not using any function over the column used in the WHERE clause.

To clean up you can run following script to drop the newly created index.

-- Cleanup
DROP INDEX [IX_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]
GO

In our case both the queries are returning the same result, a simple re-write can make a lot of difference.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Development Productivity Tool – dbForge Studio for SQL Server

I have talked a little bit about dbForge before, but I would like to do a full product review now. Devart’s dbForge Studio for SQL Server is a fantastic tool for SQL Server development, as soon as I installed it I found so many things to learn.

First off, it will increase SQL coding almost instantly. There is very little to learn, you are not just memorizing codes to “cheat” off of. DbForge Studio provides code completion options and automatic SQL formatting, so that you know your code will work. One of my favorite feature is “snippets,” which stores parts of code that you use over and over to cut down on typing and searching – because you know there always a few commands you use again and again! Another time saver is the hints option, which will show you information about objects, and the navigation tool that allows toggling between items using only the F12 key.

Of course, all these features would simply be interesting trivia if you couldn’t design and alter tables, too. But dbForge Studio has that covered as well. Tables are always the hardest part, but with Table Designer, you can quickly set the properties using the visual table editors, then edit the script and rebuild as needed, previewing changes before going live. It really takes a lot of the stress out of altering tables.

Changing your database and the information stored there can also be a stress-filled event. But with Database Diagram tool, you can also edit and manipulate your database quickly and preview changes.  Scaling, grouping, and printing are no problem with Database Diagram. You can also build virtual connections to help visualize your data.

Of course, coding and modifications can seem simple in comparison to tackling the data within the database, especially when there is a complex structure and multiple dependencies. With Schema Compare, you can synchronize your database, even when they are extremely complex. You will also be alerted if there are problems so you can solve problems early, before they become catastrophes.  Schema Compare takes “snap shots” of your SQL Server database for easy comparisons later, which heads off drift. And if you are thinking that this is too good to be true, Schema Compare also has deployment script that help target different SQL Server editions, so it doesn’t matter what version you use, you can still use this tool.

DbForge Studio also allows automating deployment of the database to the production server. There are multiple tools available to help with this, including T-SQL Debugger, Query Profiler, Query Builder, and Data Import & Export. All of these tools can be scheduled and automated, and use easy “point and click” options rather than tedious coding.

While all this automating sounds great, anyone who has had to create and deal with reports is probably wondering if this can be automated, as well. The answer is yes! The Data Report Designer will compile your data and create a report in any of nine formats, so you have the perfect feedback. You can also schedule automatic report delivery.

And last, but certainly not least, let’s deal with the security features in dbForge Studio . I cannot say enough about how important security is, and how well Devart’s tool deals with this subject. The Security Manager feature allows the user to create visual editors for users, log-ins, and roles, and provides a feature for batch editing of objects. Of course, one of the most difficult tasks in security is fine-tuning the security to each individual user. You probably see where I am heading with this – the Security Manager allows setting up different privileges per schema object.

I hope this quick review has convinced some DBAs (and non DBAs!) to give dbForge Studio for SQL Server a try. I am sure you will not be disappointed. I certainly was not! Check out – dbForge Studio for SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query

In earlier blog post SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query, we learned how we can use NOWAIT query hint to not wait on any locked query and return error. The Query Hint works on query and table level. There is similar setting which can work at a connection level as well,  it is SET LOCK_TIMEOUT. When any connection starts the value of the SET LOCK_TIMEOUT is -1, which means that the query has to wait for infinite time for the lock to be released on another query. If you want to simulate the scenario of SET LOCK_TIMEOUT to match NOWAIT query hint, it should be set to value 0. Let us see a similar example where we demonstrate how SET LOCK_TIMEOUT works.

First Let us create a table:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now let us open two different connections.

Run following command in the First Connection:

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Run following command in the Second Connection:

SET LOCK_TIMEOUT 2000
BEGIN TRAN
SELECT
ID, Col1
FROM First
WHERE ID = 1

In this case, I have set the value of the SET LOCK_TIMEOUT to 2000 milliseconds. This query will wait for 2 seconds to another query to release the lock. If another query does not release the lock in 2 seconds, it will display the following error:

Msg 1222, Level 16, State 45, Line 3
Lock request time out period exceeded.

This is the same error which we have discussed in an earlier blog post here.

Here are a couple of very interesting differences between SET LOCK_TIMEOUT and NOWAIT query hint.

  • User can configure the time to wait before throwing error in SET LOCK_TIMEOUT, whereas in case of NOWAIT it is always zero (o). 
  • The scope of the SET LOCK_TIMEOUT is wholly connected however the scope of NOWAIT is the table where it is applied.

I guess, now it is very clear when to use what. In my daily routine I usually use SET LOCK_TIMEOUT as it gives me more flexibility and achieves the same purpose as NOWAIT.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query

Everybody knows about NOLOCK  but not everyone knows about NOWAIT. They are different and they have an entire different purpose. In this blog post we will not talk about NOLOCK but we will see how NOWAIT will work. The idea of writing about blog post is based on the question I received in recent Bangalore User Group presentation. Here is the quick conversation with one of the attendee I had after my presentation. I did not ask the name of the attendee so I will have to address him as an attendee here. If you are reading this blog post, please let me know your name and I will replace it with your name.

Attendee: Pinal, in SQL Server when we have one query under transaction, when we have to read the data from the table we use NOLOCK, which gives us dirty data.

Pinal: Yes, that is correct.

Attendee: In our application we do not want to wait for the query to execute, as the way the application we have built, we have to either return the results immediately or inform user that they should retry after a while as an application (database) is busy right now. Is there any other mechanism besides NOLOCK which we can use which inform us that the table from which we are trying to retrieve the data is locked under other transaction. This way, we can pass the same message to the user and they can re-attempt to fetch the data after a specific amount of the time.

Pinal: So, basically you do not want to use NOLOCK as it gives you dirty data and you do not want to wait also till the tables under under transactions is available. You rather prefer error instead or message that your query is not going to execute immediately.

Attendee: Yes, absolutely correct.

Pinal: Use NoWait or SET LOCK_TIMEOUT.

Attendee: I have never used either of them, do you have an example on your blog?

Pinal: No, I have not blogged about it but I will for sure blog how they work.

I hope the above conversation also explains the real world scenario and business need of such feature.

NOWAIT is table hint and it instructs the database engine to return a message as soon as a lock is encountered on a table. Let us see an example.

First Create the following table in the database:

USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO

Now open two different connections:

Connection 1: Run following query

BEGIN TRAN
DELETE FROM
First
WHERE ID = 1

Connection 2: Run following query

BEGIN TRAN
SELECT
ID, Col1
FROM First WITH(NOWAIT)
WHERE ID = 1

As soon as you run above query in connection 2 it will give you following error:

Msg 1222, Level 16, State 45, Line 2
Lock request time out period exceeded.

The reason is because we have used NOWAIT as a query hint. When this hint is encountered by SQL Server it will give an error 1222 back instead of waiting for transaction on another window to complete. As I said NOWAIT is very different than NOLOCK but very similar to SET SET LOCK_TIMEOUT. In future blog posts, we will see how SET SET LOCK_TIMEOUT works. The matter of fact SET SET LOCK_TIMEOUT is more flexible than NOWAIT and I often use it.

Reference: Pinal Dave (http://blog.sqlauthority.com)