Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

SQL Server Statistics Demo

Statistics Demo
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 --Estimated...1 or 10.....
--Step6:
create index indx1 on emp(ename)
select * from emp where ename='Rakesh'
select * from emp where empid=100 --check execution plan and rows
sp_updatestats
select * from emp where empid=100 --check execution plan and rows
 
--How to update Statistics
--To update total db statistics

EXEC sp_updatestats; 

--To update on table level

USE AdventureWorks2014;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO  

--Particular index

USE AdventureWorks2014;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;  
GO 

--To check last updated statistics details using command

USE AdventureWorks2014;  
GO  
SELECT name AS stats_name,   
    STATS_DATE(object_id, stats_id) AS statistics_update_date  
FROM sys.stats   
WHERE object_id = OBJECT_ID('Person.Address');  
GO  

--To display stats of a table

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'Sales.TestSalesOrderDetail');  --Mention here table name