It was packed room of some 40 Odd people in Noida and from developers to DBAs anb my 8 Hour long Seminar/Training/ Presentation for them.
I have used lots of examples in that seminar worth mentioning are the last 13 examples but you can just try reading from here and hopefuly after page reads you will be on track
WARNING First few pages are un formatted and might confuse you but carry on soon you will see a nice road ahead.Read SARGS,OPTIMIZER, and INDEXs for refernce here
Lets start from here:-
CASE 1
USE Northwind
go
SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'
--Results--
/* When the Query Optimizer receives this request it analyzes the statement and determines how the data should be accessed (the execution plan). The Query Optimizer can decide to do one of three things to determine which rows have
‘Mexico’ in the Country column. It can do a table scan, which looks at all the rows
in the table and examines the contents of Country ; it can use a clustered index scan; or it can use an index that references Country . If the Query Optimizer cannot choose a specific index for an operation, it will always choose to use a clustered index scan if one exists on the target table. When a clustered index is not present, a table scan is used. In order for the Query Optimizer to choose the third option, the column on which the comparison operation is being performed must have an associated index.*/
/*In order for the Query Optimizer to use an index with a statement the comparison
operation must be able to be resolved into a search argument (SARG). To
produce a valid SARG, the comparison operation must be of the form shown here.
Column ComparisonOperator Constant/Variable*/
If either the Column or Constant/Variable is acted on by a function (e.g., SUBSTRING) a valid SARG is not
produced.
CASE 2
The following example uses the SUBSTRING function on the Country column, so a SARG cannot be produced and an index cannot be used.
USE Northwind
go
SELECT CompanyName,
Country
FROM Customers
WHERE SUBSTRING(Country,1,1) = 'A'
---Adding an Index to Reduce Query Costs The SELECT statements executed thus far have referenced the
-- Customers table in the Northwind database. You can use the system stored procedure sp_helpindex
-- to see the existing indexes on Customers.
CASE 3
USE Northwind
go
sp_helpindex 'Customers'
--Results--
index_name index_description index_keys
------------- ------------------------------------------------- -------------
City nonclustered located on PRIMARY City
CompanyName nonclustered located on PRIMARY CompanyName
PK_Customers clustered, unique, primary key located on PRIMARY CustomerID
PostalCode nonclustered located on PRIMARY PostalCode
Region nonclustered located on PRIMARY Region
CASE 4
---What is all about Lets delve into the details of the estimated execution plan,back to PPt --Hope you know to use (:):):))
SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'
--The Estimated Cost for the Clustered Index Scan is .0392 seconds; this makes
--up 100 percent of the time required to execute the statement.
--Create a non-clustered index on Country and then re-examine the estimated execution plan.
CREATE NONCLUSTERED INDEX VEERSQL_CustomersCountry
ON Customers (Country)
SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'
-- Two operators are used in the new execution plan. The first is Index Seek and the second is Bookmark Lookup.
-- Notice that the cost to perform the Index Seek is 34 percent of the total costs and the Bookmark Lookup is 66 percent of -- the total costs.
-- When you add the Estimated Cost for each operator (you can’t see the value
-- for the Bookmark Lookup) you get .018918.
-- When the costs for pre- and post-index queries are compared, you see that
-- the query that used the index realized a 52 percent reduction in costs
Simple Select ((.0392-.0189)/.0392)*100=51.78)
--- If this type of query is performed on a regular basis and Customers
--- is going to contain a large number of rows, then the index should remain in place.
--- On the other hand, if the query is only executed periodically or
--- Customers is not going to contain a larger number of rows, the index should be deleted.
/* Creating Valid Search Arguments BUt I ll come back on Indexes again
VALID SEARCH ARGS is */
Column ComparisonOperator Constant/Variable
/* In order for the Query Optimizer to use an existing index, however, the comparison operation
must be able to be resolved into a valid SARG.
SELECT
presented in the previous section is affected when the SARG requirement is not met.
*/
SELECT CompanyName
FROM Customers
WHERE SUBSTRING(Country,1,1) = 'A'
/*The SUBSTRING function caused the Query Optimizer to ignore the index and
use a Clustered Index Scan. This caused the estimated cost to revert back to .0392,
But I doubt it wont here because the Data is less
and the OPtimizer will take Cache Plan*/
SELECT CompanyName
FROM Customers
WHERE Country LIKE 'A%'
--WRONG QUERRY
SELECT CompanyName
FROM Customers
WHERE Country LIKE '_A%'
IMPORTANT CASE 5
--Let us try to determine the most Effective Querry
--Query 1 -- LEFT JOIN
SELECT CompanyName
FROM Customers a
LEFT JOIN ORDERS b ON a.CustomerID = b.CustomerID
WHERE b.CustomerID IS NULL
--Query 2 -- NOT EXISTS
SELECT CompanyName
FROM Customers a
WHERE NOT EXISTS (SELECT *
FROM Orders b
WHERE a.CustomerID = b.CustomerID)
--Query 3 -- NOT IN
SELECT CompanyName
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID
FROM Orders)
Check the batch
Now what I am doing is loading the Data
SELECT *
INTO Orders2
FROM Orders
go
INSERT Orders2
SELECT CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry
FROM Orders
-- Now let me run teh First THree querries...
-- and see the difference by modifying the
--
SELECT CompanyName
FROM Customers a
LEFT JOIN ORDERS2 b ON a.CustomerID = b.CustomerID
WHERE b.CustomerID IS NULL
--Query 2 -- NOT EXISTS
SELECT CompanyName
FROM Customers a
WHERE NOT EXISTS (SELECT *
FROM Orders2 b
WHERE a.CustomerID = b.CustomerID)
--Query 3 -- NOT IN
SELECT CompanyName
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID
FROM Orders2)
The relative cost for Query 3 increased to over 44 percent. This is a good
example of how the size of the database affects a poorly written query.
Wat is good today may not be tomorrow..
CASE 6
--We will Use profiler and trace the events and then Querry analyser to suggest for Index Tuning wizard
--I wil use the same Orders2 ans see what it sugegst
USE Northwind
go
SELECT *
FROM Orders2
Go
SELECT *
FROM Orders2
WHERE CustomerID = 'CENTC'
Go
--Some updates
SET ShipName = 'El Centro Rocket Shop'
WHERE CustomerID = 'VICTE'
GO
SELECT *
FROM Orders2
WHERE CustomerID = 'VICTE'
Go
DEMOnstrate how to use Profiler tracing
---Create the Index and then we can drop it
-- check for the Estimation after index is created..do map
-- the performanmce Benefits
/*Beware SQL Profiler trace files grow at an alarming rate when they capture data on
a server that generates a lot of activity. Once you create a trace file, make
sure you monitor its growth rate for the first few hours so you can ensure
that you have plenty of available disk space to hold the contents.*/
--Dropping Index Foo
DROP INDEX Orders2.Orders21
--Use ITW via Query Analyzer to analyze the following SELECT.
SELECT *
FROM Orders2
WHERE ShipName LIKE 'A%'
ORDER BY ShipName
/*
we can analyse more with Set Statistics IO
It is very importantcommand
LETS take a tour of recommendatiosn on Query Writimng ..A very important tour..*/
SOME RECOMMENDATIONS
1. operate on Small result Sets
Limit the numebr of Columns in select list
set statistics IO on
select productId,SupplierId from products where supplierid = 1
--Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
select * from products where supplierid = 1
---Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
2. Use Highly Selective Where Clause
-- A column has a high degree of selectivity
-- If it is likely to return a small number of rows
-- for a given value specified in a predicate argument.
-- The distribution statistics can be used to estimate the degree of selectivity:
-- As they say selectivity governs the rules of chossing Non clustered indexes also
3. Using Indexes Effectviely as we did some query on this feature already.
4. Important is to avoid Non INdexable Search COnditions
Example INdexable = , > , <=, >= , Between , and some like conditions
Non indexable Exclusion != , !> and ,!< , Not Exists, not in , not like , or ,like '%%'
Select * from [Order Details]
where OrderId In (10300, 10301,10302,10303)
---Table 'Order Details'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.
-- A clustered Index Seek wovv then whats the issue:):):) Just check the Argument and scan count
--HERE WE HAVE MORE THAN FOUR sCANS HAPPENING
Select * from [Order Details]
where OrderId between 10300 and 10303
--Table 'Order Details'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
5. LETS CHECK lIKE CONDITION
SELECT * FROM pRODUCTS WHERE pRODUCTNAME LIKE 'S%'
--Table 'Products'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0.
--IT CONVERTS THE LIKE CONDITION INTO sEARCHABLE INDEX CHECK TH >= COPNDITIOPN
SELECT * FROM PRODUCTS WHERE PRODUCTNAME >='s' AND pRODUCTNAME <'t'
--Table 'Products'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0.
THUS LEADING CHARACTERS IN liKE OPTIMIZE THE sEARCH TO ALLOW THE USAGE OF InDEX
6. AVOID ARITHMETIC OPERATORS
Select * from orders where orderid*2 = 22000
--Table 'Orders'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 2.
--See the Execution Plan which prevented the use of index on the column
Select * from orders where orderid = 22000/2
--Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
7. Sunstring vs like
select * from orders where substring(Shippostalcode,1,1)='V'
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 20.
--see the Execplan where index on ShipPostalcode index is not used and Clustered is used
--Now lets see with LIke
select * from orders where Shippostalcode like 'V%'
--Table 'Orders'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0. and a
-- also see the Index Selectivity Shippostal code is used
8 always use datepart queries for Date opperators
A known truth ..dont cast them
9 Avoid Optimizer hints lets see how they can mislead us
---JOin Hint , Index hint and Firceplan hint let Optimizer do it
Select * from [order details] Od join Orders O
On Od.Orderid = O.Orderid
where O.shipCountry = 'spain'
54 row(s) affected)
--Table 'Order Details'. Scan count 23, logical reads 46, physical reads 0, read-ahead reads 0.
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
--it went for nested Loop with Inner Join HInt with Order Details outside and Order PK inside
--So if we suppose it will use this as hint we are wrong Lets see how
Select * from [order details] Od inner loop join Orders O
On Od.Orderid = O.Orderid
where O.shipCountry = 'spain'
---Table 'Orders'. Scan count 2155, logical reads 4504, physical reads 0, read-ahead reads 0.
---Table 'Order Details'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.
-- THus Join Hint costed us more here OrderDetail is inside and Orders is inside as such the Scans where more
-- because the JOIN hint forced the Optimizer to use the first table
-- So you must check the iterative and come to the conclusion by Analysisng the Hash Mapping of Joins
10 Dont use INDEX HInts unless you are prety Sure
Select * from Orders With (Index(Pk_orders))Where OrderId * 2 = 2200
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
Select * from Orders Where OrderId * 2 = 2200
--Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
--Check the Displyed Subtree Costa nd also see the NOnClustered Index selectivity
11 Avoid Implicit Conversion
For Example
Declare @int INT , @flt FLOAT
Set @int = 10
Set @flt = 10
Select * from products where SupplierID = @int -- Int
Select * from products where SupplierID = @flt -- Int
/*
(1 row(s) affected)
Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
(1 row(s) affected)
Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
*/ All same from Statis IO
---But See th Disply Estims Exec pla and you will see how Implicti Conversion Behaved in Case of Float ..
-- We must understand the Data TYpe Precedence
/* Implicit conversions are those conversions that occur without specifying either
the CAST or CONVERT function. Explicit conversions are those conversions that
require the CAST (CONVERT) function to be specified. This chart shows all
explicit and implicit data type conversions allowed for SQL Server
system-supplied data types, including bigint and sql_variant. */
--Column Type is kept as it is and the Constant for comparison is
--Implicted COnverted with disregard to Precedence
-- I will show more on thsi SQL Help File where
--we Have precedence Chart Lower to upper is Implicited Converted
12 QUESTION what is Better UNION or UNION All
select * from products where SupplierID = 1
union
Select * from products where SupplierID =2
0.0311
--Sp_help Products
--Table 'Products'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0.
--Merge Join cost 18%
select * from products where SupplierID
= 1
union all
Select * from products where SupplierID
=2
0.0254
--Table 'Products'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0.
--THis uses Concatenation whose cost is 0
--Both return Same rows???
--Use UnION All as it avoids the Detecting of removing of duplicates
13 Use of Subqueries over Joins
Select * into OD from [Order Details]
Create Clustered Index ProdId On OD(ProductID)
Select * into Prod from products
create Nonclustered Index SuppId On prod(SupplierId)
-------------------------
Select Od.* from OD where OD.ProductID in (Select p.productId from Prod P
Where P.SupplierId = 10)
--0.0311 -- 61%
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Select Od.* from OD , prod P
where Od.ProductiD = P.ProductId
and SupplierId = 10
--0.0197 -- 38%
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Now if I create the a Non clustered Index ProdId on Prod to avoid this Sort in the Middle cabn the Query behave better
Create Unique NonClustered Index ProdID On Prod(ProductId)
Now we execute thequery again
Select Od.* from OD where OD.ProductID in (Select p.productId from Prod P
Where P.SupplierId = 10)
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-->>>>>>>>>>.But what has changed then Cost has come to 0.0197 from 0.0311
--> No more SORT for Distinct Selects as such Query has improved
14 In Sps
Set NOCOUNT ON
--Batch Querries
Set noCOunt OF