SQL Server Query Tuning Series - Nested Loop Prefetch #jbswiki #querytuning

SQL Server Query Tuning Series - Nested Loop Prefetch #jbswiki #querytuning

Restoring Required Databases for Query Tuning Session - https://www.youtube.com/watch?v=LK_Lau0-BmU

set nocount on
go
drop table Table1
go
create table Table1(Col1 int identity primary key clustered, Col2 char(200))
go
begin tran
go
declare @tab table (Col1 int identity primary key clustered, Col2 int)
declare @i int
set @i = 1
while @i &LT= 100000
begin
insert into @tab(Col2) values (1)
set @i = @i + 1
end
set @i = 1
while @i &LT= 60
begin
insert into Table1(Col2) select 'a' as Col2 from @tab
checkpoint
set @i = @i + 1
end
go
commit tran
go
update statistics Table1 with fullscan
go

drop table Table2
go
create table Table2(Col0 int identity primary key clustered, Col1 int)
insert into Table2(Col1) select Col1 * 5000 * rand(convert(varbinary, newid())) from Table1 where Col1 &LT 1050
go

drop table Table3
go
create table Table3(ID int identity primary key clustered, Col0 char(20), Col1 int)
go
create index test on Table3(Col0)
go


insert into Table3 select 'Chennai', Col1 from Table2 where Col0 between 1 and 24 order by Col0
go
insert into Table3 select 'Madurai', Col1 from Table2 where Col0 between 25 and 50 order by Col0
go
insert into Table3 select 'Trichy', Col1 from Table2 where Col0 between 51 and 1050 order by Col0
go 200
update statistics Table3 with fullscan
go


drop proc Table3Proc
go
create proc Table3Proc @Col0 char(20)
as
begin
declare @Col1 int,@Col2 char(200)
select @Col1=o.Col1,@Col2=o.Col2
from Table3 ao inner join Table1 o on(o.Col1=ao.Col1)
where Col0=@Col0
end
go

set statistics time on

exec Table3Proc 'Chennai'
exec Table3Proc 'Madurai'
exec Table3Proc 'Trichy'

dbcc freeproccache

exec Table3Proc 'Madurai'
exec Table3Proc 'Trichy'

-- What is Prefetch

-- If CPU is not an issue. Adding recompile is good
alter proc Table3Proc @Col0 char(20)
as
begin
declare @Col1 int,@Col2 char(200)
select @Col1=o.Col1,@Col2=o.Col2
from Table3 ao inner join Table1 o on(o.Col1=ao.Col1)
where Col0=@Col0
option(recompile)
end
go

exec Table3Proc 'Chennai'
exec Table3Proc 'Madurai'
exec Table3Proc 'Trichy'

-- if CPU an issue
alter proc Table3Proc @Col0 char(20)
as
begin
declare @Col1 int,@Col2 char(200)
select @Col1=o.Col1,@Col2=o.Col2
from Table3 ao inner join Table1 o on(o.Col1=ao.Col1)
where Col0=@Col0
option(optimize for(@Col0='Madurai'))
end
go

exec Table3Proc 'Chennai'
exec Table3Proc 'Madurai'
exec Table3Proc 'Trichy'

set statistics time,io off
drop table Table1
drop table Table2
drop table Table3
drop proc Table3Proc


Disclaimer:
The views expressed on this Video are mine alone and do not reflect the views of my company or anyone else. All postings on this Video are provided “AS IS” with no warranties, and confers no rights.

Тэги:

##sqlserver_#alwayson #prefetch #nested_loop #loop_join #optimize_for #unordered_prefetch #With_Unordered_prefetch #withunorderedprefetch #sql_server #sqlserver #query_tuning #querytuning #performance #SQL_Server_Query_Tuning_Series #Nested_Loop_Prefetch #SQL_Server #Nested_Loop #Prefetch #Performance_Optimization #SQL_Server_Optimization #Database_Tuning #SQL_Tuning #Query_Performance #SQL_Query_Optimization #SQL_Server_Performance #Indexing #SQL_Indexes #Execution_Plan

Скачать видео