Secrets of SQL Server Execution plan (2/5)

Haja Batcha

In the previous post we saw some limitations of execution plan, along with some tweaks. In this post we will go through some of the backstage things which are happening on the SQL Engine.

First i would like share some truths regarding the query optimizer, actually it was the disclaimer by the query optimizer that the indexes suggested by the optimizer should be reviewed by human brain. But some guys will apply the index suggested by the optimizer without much analysis, in that case this index will not be used by the query at all.

For example you are optimizing a code, and the optimizer is suggesting for a missing index. And you have created the suggested index and go back to the query execution plan but still it is suggesting the same missing index which you have created. Once this happens you can make sure, what you have created is not appropriate for that query. In this case the best way to resolve is to look deeper into the query. The query may need the same index with some small changes, which include changing the order of the key column or included column according to the need or adding index filter according to your where condition. By this way you will get the full benefit of the query optimizer.

Secrets of SQL Server Execution plan (3/5)

Haja Batcha

So for we have saw somelimitations of execution plan, some alarming signs which tell us about the need for update statistics etc on my previous blogs. Now we will see how to analyse some more complex execution plans. As usual we will start with tip. It is always better to set the IO Statistics on while optimizing the query, which gives some clear picture about your query. While working with very big execution plan it will be little bit hard for us to check whether our indexes are used are not. for such situation just wright click anywhere on the plan and click “Show Execution Plan on XML” then you can use CTL+F to find any specific index. Also generating a XML plan has lot more benefits, such as memory fractions, parameters list used while optimization etc..

Also we should keep in mind the execution plan distributes the cost across the operators,  you may saw high number on Key lockups which require covering index. And you may saw warning symbol over hash match join etc.. which are all just instigators. There is no one rule fits all in query optimization which is the reason why we don’t have a query tuning robot yet.

Secrets of SQL Server Execution plan (1/5)

In this blog we are going to see some basic things on how to interpret a execution plan for a query . The first first step on enhancing a query for optimal performance is to understand the query execution on cost basis , which part of the of the query is costly and which part is not . To do so, SQL Servers query execution plan is the best choice, by which we can point out which part of our query is more costlier.

Learning a query execution plan is like learning a new language, which is iconic language. Just explaining each and every icon of this language will be wage for you. More things on execution plan can be garbed while practicing it .

Before dive deeper into the subject , we should know the limitations of the execution plan. Execution plan doesn’t tell you below things

• Locks acquired
• Wait statistics
• Data is in the cache or not
• Not a replacement of SET STATISTICS IO
• Not a replacement of SET STATISTICS TIME

Below are some easy usage ways to get full benefit of the execution plan

“We have to read the execution plan from right to left and from down to top”. 

I am using AdventureWorks2008 database on my examples. In this blog we will see only some basic things and spice up things on the coming series

Query execution plan secrets

Haja Batcha

As part of my Job i was optimizing a Data warehouse query which builds the update for the Fact table . The query Contains numerous update statements. Run The query and it took 365 Seconds to complete .

I saw the Execution plan , there is no missing index and most of the thinks looks normal at the First glance.Hence i had too many update statements i was puzzled, there is no missing index showing from the execution plan. A Big question mark. Didn’t gave up and continued about which update is causing me the trouble, best thing i did was. I Started executing one by one. From among the updates , one of the update statement took more than 340 seconds . So i got the area where i need to do my analysis .

Breaking a query into smaller parts and examine one by one helped me in lot of cases . So a part which gave a challenge is a single update statement,  updating the a  value to a column based on the numerous case statements.

This image throws me big hint. Resembling update on your clustered index. I saw the execution plan and noticed 75% cost on updating the clustered index , also this update statement affects 15 Lacks records .

Performance Improvements in SQL 2014(3/4)

Hi dears, so for we have seen about the incremental update statistics and index enhancement on our previous blog post.

Now we will see about, how SQL server 2014 can cache temp tables.  Before we move further into the subject, will see about temp tables in crisp. These are the tables created at the run time and  these tables are created within the “tempdb” database.  To identify a table as a Temporary Table within the SQL statement, prefix the table name with the “#” character. And also we should know one more fact the caching is not per procedure , the caching is per objects.

SQL Server 2014 has given us some room, by which if we make our temp table in a proper way the temp table can be cached. We will see how this is possible.

Analyzing the indexes using DMVs

Hi,

This article is about assessing the index, regarding which one is needed and which one is not.  By the way it willbe  helpful for intermediate level guys on SQL Server

SQL Server has no magic to improve the performance of your query, it is the correct utilization of indexes which will do the magic for you. We are going to do our assessment based on Dynamic Management View(DMV)  which is  the out of box feature from SQL Server 2005 on wards.

We should know one common fact regarding DMVs, the informations obtained on a DMV will last upto the restart of that instance. So it is the best practice to store those information on a table periodically.

Here we go, we are going to do our assessment based on below DMV

sys.dm_db_index_usage_stats

SELECT db.name AS [database] ,

ob.name [table],

ix.name [index],

dm_ix.user_seeks ,

dm_ix.user_scans ,

dm_ix.user_lookups

FROM sys.dm_db_index_usage_stats dm_ix

INNER JOIN sys.databases db ON db.database_id = dm_ix.database_id

INNER JOIN sys.indexes ix ON ix.object_id = dm_ix.object_id

INNER JOIN sys.objects ob ON ob.object_id = dm_ix.object_id

WHERE ob.type = 'U'

Now we are done, the above SQL will show you the database name, table name, and what are all the index exist etc.  Now we should concentrate on below threee columns

• user_seeks
• user_scans
• user_lookups

user_seeks :-  A seek is when the index is used, as an index, to retrieve data. The key values and the b-tree that make up the index are referenced to find exact values and locations within the index.

user_scans:- A scan is when the index is used, well, like a stack of papers. They’re gone through one at a time to find the interesting values. Needless to say, in most situations, seeks are preferable to scans.

user_lookups:- Number of bookmark lookups by user queries.

You can filter the results of one database by using database id on the where class.

Feel free to pass your comments and feedback.