Daily Archives: August 10, 2015

SQL Server Interview Question – Which index will be used?

10 Monday Aug 2015

Posted by in SQL Server Interview Questions, Tricky SQL Queries

≈ 1 Comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Interview Question – Which index will be used?

In a recent technical interview I was asked a question about multicolumn indexes. The question says that we have table with
3 columns say column a, column b and column c. Now we create Nonclustered index on b,c & a,b. Now there a query like
select name from testIndexes WHERE b LIKE ‘Pawan’ , so which index it will use. I gave him the correct answer. Lets create a sample
table with some data and indexes. 

--

CREATE TABLE testIndexes
(
a INT
,b VARCHAR(10)
,c VARCHAR(10)
)
GO

INSERT INTO testIndexes VALUES (1,'Pawan','Nisha'),(2,'Nisha','Isha'),(3,'Isha','Pawan')

CREATE NONCLUSTERED INDEX Ix_testIndexes1 ON testIndexes (b,c)
CREATE NONCLUSTERED INDEX Ix_testIndexes2 ON testIndexes (a,b)

SELECT b FROM testIndexes WHERE b LIKE 'Pawan'

--

Execution plan

Index - Seek Predicate

Index – Seek Predicate

Now it is clear that it is using the first index, now the question is why the optimizer is using the first index. This is because the histogram builds on the first column. Now lets force the second and check out the execution plan


SELECT b FROM testIndexes
WHERE b LIKE 'Pawan'
GO

SELECT b FROM testIndexes WITH(INDEX(Ix_testIndexes2))
WHERE b LIKE 'Pawan'
GO

--

Execution plans

Index Comparisions

Index Comparisions

In the first case it is using index seek and in the second case it is using index scan. I hope you have enjoyed the article. In the next post I will explain one more interview question asked to me in one of technical interviews I faced recently. Thanks for reading !

-Pawan Khowal

MSBISkills.com

SQL Performance Tuning and Optimization – Where CTEs are stored?

10 Monday Aug 2015

Posted by in SQL Performance Tuning

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Performance Tuning and Optimization – Where CTEs are stored ?

Well before that let’s first understand what is CTE?

1. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

2. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

3. A CTE can also be used for recursion.

4. So you cannot say whether CTE are stored are somewhere or not? The answer to this question is check the execution plan of the query. Just as with any other query, look at the query optimizer to see how it’s resolving the CTE. Now, in the case of a recursive CTE, you may see the optimizer choose to use table spool operations. In that case, yeah, it’s going to be probably putting stuff into tempdb. Again, look to the execution plan to see how the query is resolved.

Example of a Recursive CTE – storing data in a temp DB [Table Spool]

--

;WITH CTE(EmpName , EmpId, Level,FullyQualifiedName) AS (
     Select E.EmpName, E.EmpID, 0 Level
     , Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName         
     From Employees E Where E.ReportsTo IS NULL
     UNION ALL
     Select E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName  
     From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo 
)
SELECT SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) Hierarchy , * FROM CTE H 
ORDER BY H.FullyQualifiedName

--

--

Execution Plan

Pawan Khowal - RecursiveCTEs - Stored data in tempDB [TableSpools]

Pawan Khowal – RecursiveCTEs – Stored data in tempDB [TableSpools]

Example of a CTE – not storing anything in a temp DB

--

WITH CTE AS 
(
	SELECT * FROM Employees
)
SELECT * FROM CTE b INNER JOIN CTE a on a.EmpID = b.EmpID 
GO
 

--

--

Execution Plan

Pawan Khowal - CTEs - Not storing anything anywhere

Pawan Khowal – CTEs – Not storing anything anywhere

So all in all we can say CTEs are not stored anywhere but there are exceptions where data needs to be stored in a tempdb as a part performance optimization technique, so optimizer stored data in tempDB. I hope you have enjoyed the article. In the next post I will explain an interview question asked to me in one of technical interviews I faced recently. Thanks for reading !

-Pawan Khowal

MSBISkills.com

Which one is better for performance Table Variables or Temp Tables – Only in terms of Performance Optimization?

10 Monday Aug 2015

Posted by in SQL Performance Tuning

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?

So as always the answer is very simple. It depends on your usage. I have personally uses Temp table most of the time. I will try to explain the differences here in detail.

1. The primary difference between table variables and temporary tables is that table variables do not have statistics. Temporary tables do have statistics on them.

2. So, when you are manipulating data within temporary tables it can lead to recompiles. Using a table variable avoids the recompiles, possibly speeding up your processes. That’s regardless of the number of rows.

3. But, if you are then using a JOIN or a WHERE clause on a table variable, they have no statistics, so the optimizer will assume a set number of rows (1 for all versions prior to 2014 and 100 for 2014 and 2016). This is fine if you have only a few rows, but if you have more than the set number, it can lead to really bad performance caused by bad choices by the optimizer.

4. So, the limit is, how are you using the table variable? If you’re loading some data into it, but then you don’t have to filter it using a WHERE clause or JOIN against it, great. It’ll help with recompiles. If you’re loading data into it and then you’re doing an UPDATE so that you have to use a WHERE clause or a JOIN, you’re usually better off using a temporary tables except in the case where you have a very small number of rows, say, less than 200.

I hope you have enjoyed the article. In the next post I will explain where CTEs are stored ?. Thanks for reading !

-Pawan Khowal

MSBISkills.com

SQL Performance and Optimization – How to write optimized queries

10 Monday Aug 2015

Posted by in SQL Performance Tuning

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Performance and Optimization – How to write optimized queries

In this post we will talk about some precautions we can take while writing queries. Precaution is always better than cure. If we can take care of following things then we can definitely write queries which are optimizer friendly. Some of the points are-

1. Where Clause
Keep your columns in where clause clean. Do not put any functions around columns in the where clause. If you put any functions around the columns in the where clause your query will get a performance hit. Optimizer will give you a scan instead of a seek. And when the table grows people will start complaining that the query is slow. https://msbiskills.com/2015/06/19/avoid-using-function-in-where-clause-why/

2. Join
Keep your columns in join clause clean. Do not put any functions around columns on join conditions.

3. DataTypes
When ever you are joining two tables then always use columns with same datatypes in the joins clause. Also use same data type of scalars while comparison with columns in the where clause. If you don’t the SQL Server will up cast the lower data type to the upper one and you will get an implicit conversion, which is bad. For details please check https://msbiskills.com/2015/06/26/implicit-conversions-are-bad-for-performances-why/

4. Join Predicate
Always have a join predicate, other wise you will end up with cross joining two table and you will get an warning in the execution plan

5. Try to avoid Order by
Try to avoid an order clause on columns that don’t have indexes. Otherwise we will have to pay for Sort operator in the execution plan. If possible use Top with order by. Top with order by is fine. After the top operator is satisfied it stops the execution.

6. Exists
Always use exists if you are not fetching any columns from the right hand side table. Don’t use IN, they are bad for performance. Exists are good for performance as they use Semi joins internally.

7. Like
If you are using like operator to match string then use like ‘Pawan%’ rather than ‘%Pawan%’, this will give us a scan. Where as ‘Pawan% can give index seek which is great. For details click https://msbiskills.com/2015/06/23/like-is-sargable-how-to-write-better-where-clause-ii-lets-check/

7. SELECT *
Don’t ever do select * when fetching data from physical table.
For details please click https://msbiskills.com/2015/06/12/dont-ever-do-select-its-really-a-bad-idea-why/

8. Loops, cursors
Don’t use loops or cursors, Even loops are also cursors. You always wanted to insert bunch of rows

9. Nested Views,Triggers
Always try not to nest views and trigger because after a certain level you don’t know what’s going on in your system.

10. Which one is better? Equal to (=), IN, Between, etc.
a. = is better than not equal to (!=)
b. Between is better than IN
c. IN is better than NOT IN

11. Cross Apply
Try to use Cross apply(Corelation) rather than using joins, almost every time it will give you Nested Loop join.

I hope you have enjoyed the article. In the next post I will explain which one you should use – Temp table or table variable for performance ?. Thanks for reading !

-Pawan Khowal

MSBISkills.com