SQL Server Query Tuning Series - Performance Impact on Gather Streams and Sort Operators #jbswiki

SQL Server Query Tuning Series - Performance Impact on Select Queries utilizing Gather Streams and Sort Operators During CPU Intensive Workload #jbswiki #querytuning

set nocount on
go
set statistics time off
go
drop table Table8
go
create table Table8 (Col1 datetime, Col2 int identity,
Col3 int, Col4 char(1000),
primary key clustered (Col1, Col2))
go
create unique index IX_Col2 on Table8 (Col2)
go
begin tran
declare @d datetime, @i int
set @d = '2020-01-01'
set @i = 1
while @d <='2021-12-31'
begin
set @d = dateadd(minute, 1, @d)
end
commit tran
go
update statistics Table8 with fullscan
go

set statistics time on
declare @Col1 datetime, @Col3 int, @Col2 int, @Col4 varchar(4000)
from Table8 where Col1 between '2020-01-01' and '2020-06-30'
order by Col2
go

declare @i int
while 1=1
go

declare @Col1 datetime, @Col3 int, @Col2 int, @Col4 varchar(4000)
from Table8 where Col1 between '2020-01-01' and '2020-06-30'
order by Col2
go

CREATE INDEX [IX_TEST] ON [Table8]([Col2] ASC,[Col1] ASC) INCLUDE([Col3],[Col4])

declare @Col1 datetime, @Col3 int, @Col2 int, @Col4 varchar(4000)
from Table8 where Col1 between '2020-01-01' and '2020-06-30'
order by Col2

set statistics time off
drop table Table8

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.