SQL Server Query Tuning Series - Parameter Sniffing Issues with Stored Procedures #jbswiki #querytuning
Restoring Required Databases for Query Tuning Session - [ Ссылка ]
drop table Table1_temp
drop table Table2_temp
select * into Table1_temp from table1
select * into Table2_temp from table1
create clustered index ix_test on Table1_temp(Col1)
create clustered index ix_test on Table2_temp(Col1)
CREATE INDEX IX_TEST1 ON [dbo].[Table1_temp] ([Col3]) INCLUDE ([Col1])
drop proc sp_table1
go
create procedure sp_table1 @col3 int
as
begin
select a.Col1,b.Col2,b.Col3 from Table1_temp a
INNER JOIN Table2_temp b
ON a.col1 = b.col1
where a.Col3=@col3
end
exec sp_table1 @col3=38
exec sp_table1 @col3=500
dbcc freeproccache
exec sp_table1 @col3=500
exec sp_table1 @col3=38
ALTER procedure sp_table1 @col3 int
as
begin
select a.Col1,b.Col2,b.Col3 from Table1_temp a
INNER JOIN Table2_temp b
ON a.col1 = b.col1
where a.Col3=@col3 option (RECOMPILE)
end
exec sp_table1 @col3=38
exec sp_table1 @col3=500
ALTER procedure sp_table1 @col3 int
as
begin
declare @Col4 int = @col3
select a.Col1,b.Col2,b.Col3 from Table1_temp a
INNER JOIN Table2_temp b
ON a.col1 = b.col1
where a.Col3=@Col4
end
exec sp_table1 @col3=38
exec sp_table1 @col3=500
drop table Table1_temp
drop table Table2_temp 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.
