--step1: Creating sample table
create table emp(empid int,ename varchar(40),sal money)
insert emp values(1,'Rakesh',4000)
--step2
select * from emp where empid=1
--Check that automatically temporary
statistics are created on empid colum
--step3
insert emp values(100,'Rakesh',4000)
go 99
--step4: Check the empid column
statistics not updated
--step5: Check execution plan of the
following query
--Estimated no of rows not
100
select * from emp where empid=100 --ctrl+L
--Step6:
create index indx1 on emp(ename)
select * from emp where ename='Rakesh' --ctrl+L
select * from emp where empid=100 --check execution
plan and rows
--step7: To verify statistics
SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id]
AS "Statistic ID",
[s].[name]
AS "Statistic",
[sp].[last_updated]
AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter]
AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'emp');
go
--step8: To update total db stats
sp_updatestats
go
--step9: Verify again (Run step7 and
check that modifications are 0)
--step10: Check the execution plan and
now Estimated Number of Rows=99
select * from emp where empid=100
No comments
Post a Comment
Note: Only a member of this blog may post a comment.