Query optimization

Question

Can anyone give an example that the sql query optimizer do the following ?

outer joins
sub-queries
aggregation
stars and snowflakes
indexed views
index plans
update plans
partitioned table elimination
correlation elimination
sub-query elimination

Answer 1

This is a broader subject and I do not think anyone can answer in a Q&A.

I would suggest you to go through good books/BOL etc. One of my reference to start with as below.

https://dl.ebooksworld.ir/motoman/Apress.SQL.Server.2017.Query.Performance.Tuning.5th.Edition.www.EBooksWorld.ir.pdf

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn

Answer 2

Hi @sakuraime ,

This can’t be clarified by one or two sentences. Each point in your question is one theme or even one project.
Firstly, you need the example codes of them, and then you can go to test.
So could you please support the sample codes of them?

You can also test by your side as next:
And if you have sample codes, before you execute the query, please execute the next code firstly:

SET SHOWPLAN_XML ON;

Because the execution information provided by the display plan in XML format is the most abundant. Some plan attributes only appear in this format, but not in the text format and graphical format. These attributes include missing indexes, whether the plan is trivial, the actual parallelism used by the query, the actual memory grant, etc.

1.Examples:
Outer joins: example:leftouter join

--outer join  
use AdventureWorks2012  
go  

set showplan_xml on;  

select C.CustomerID,StoreID   
from sales.Customer As C  
left outer join Sales.SalesOrderHeader As O  
On C.CustomerID= O.CustomerID  

2.Sub-queries: example:Subquery that returns the smallest missing value

use tempdb  
go  

if object_id('dbo.t1') is not null  
drop table dbo.t1;  
go  

create table dbo.t1   
(keycol int not null primary key  check(keycol>0),  
 datacol varchar(10) not null);  

 insert into dbo.t1 (keycol,datacol) values  
 (3,'a'),  
 (4,'b'),  
 (6,'c'),  
 (7,'d');  

 select * from dbo.t1;  

 SET SHOWPLAN_XML ON;  
 --Subquery that returns the smallest missing value  
select   
case  
when not exists (select * from dbo.t1 where keycol =1) then 1  
else (select min(a.keycol)+1  
from dbo.t1 as a  
where not exists (select * from dbo.t1 as b  
where b.keycol=a.keycol+1))  
end;  

…

More information: query-optimization-techniques-in-sql-server-the-basics
BR,
Mia
If the reply is helped, please do “Accept Answer”.

Answer 3

Hi @sakuraime ,

Is the reply helpful?

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

BR,
Mia