Index usage stats

Every sql server developer/dba should have some “utility” scripts, for looking at particular point.
One of this script is about index usage , that is looking to a table and get information about index, how they are used, statistics …

set nocount on
DECLARE 
	@objname nvarchar(128);
select OBJECT_NAME(i.object_id) as tblName
	,i.name	
	,i.is_disabled as esteDezactivat
	,STUFF(ca.IndexColumnsNames,1,1,'') as IndexColumns
	,STUFF(ia.IncludedColumnsNames,1,1,'') as IncludedColumns
	,STATS_DATE(ius.object_id, i.index_id) as  Stat_Date
	,ps.reserved_page_count as noPages
	,ius.user_seeks
	,ius.user_scans
	,ius.user_lookups
	,ius.last_user_seek
	,ius.last_user_scan
	--,i.*
	,case when i.index_id  =1 then 1 else 0 end as is_clustered
	,i.is_unique 
	,i.is_primary_key
	,i.is_unique_constraint
	,i.is_disabled
	,i.has_filter
	,i.filter_definition
	,ps.data_compression_desc
	,'ALTER INDEX ' +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + '.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ' DISABLE;' as DisableStmt
	,'DROP INDEX ' +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + '.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ';'  as dropStmt
from
	sys.objects as o
	left join sys.indexes as i
		on o.object_id = i.object_id
	left join sys.dm_db_index_usage_stats as ius
		ON ius.object_id = i.object_id
			AND ius.index_id = i.index_id	
			AND ius.database_id = DB_ID(DB_Name())
	cross apply
		(select ', '+ c.name +''
			from sys.index_columns as ic
					inner join sys.columns as c
						on c.object_id = ic.object_id
						and c.column_id = ic.column_id
			where ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 0
			order by ic.key_ordinal	
			for xml path('')
		)ca(IndexColumnsNames)
	outer apply
		(select ', '+ c.name +''
			from sys.index_columns as ic
					inner join sys.columns as c
						on c.object_id = ic.object_id
						and c.column_id = ic.column_id
			where ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 1
			order by ic.index_column_id ASC
			for xml path('')
		)ia(IncludedColumnsNames)
	left join (select		
					ps.object_id
					,ps.index_id
					, sum(ps.reserved_page_count) as reserved_page_count
					,p.data_compression_desc as data_compression_desc
				from sys.dm_db_partition_stats  as ps
					inner join sys.partitions as p
						ON ps.partition_id = p.partition_id
						AND ps.object_id = p.object_id
				group by ps.object_id,ps.index_id,p.data_compression_desc
				)ps
		on ps.object_id = i.object_id
			and ps.index_id = i.index_id
where
	1=1
	--ius.user_seeks  = 0  
	--and ius.user_scans = 0
	--and ius.user_lookups = 0
	--and i.type > 1 -- nonclustered index
	--and i.is_unique = 0 -- is not unique
	--and i.is_primary_key = 0 -- not PK
	--and i.is_unique_constraint = 0 --not Unique Constraint
	/********and i.is_disabled = 0 -- is active*******/
	--and i.has_filter = 0
	--and database_id = DB_ID(DB_Name())  -- database actuala
	and OBJECT_NAME(o.object_id) = @objname 
order by OBJECT_NAME(ius.object_id),IndexColumns,IncludedColumns, QUOTENAME(i.name);

Thanks,
S

Importance of Statistics and Update of it

How important is to have statistics up to date versus fragmentations.

I start with a fresh copy of WideWorldImporters , call it WideWorldImporters_Test, in case of already existing database.

use master
go

restore database WideWorldImporters_Test
from disk = ‘D:\Kituri\SQL Server\WideWorldImporters-Full.bak’
with
move ‘WWI_Primary’ to ‘D:\Databases\DATA\WideWorldImporters_Test.mdf’,
move ‘WWI_UserData’ to ‘D:\Databases\DATA\WideWorldImporters_Test_UserData.ndf’,
move ‘WWI_Log’ to ‘D:\Databases\DATA\WideWorldImporters_Test.ldf’,
move ‘WWI_InMemory_Data_1’ to ‘D:\Databases\DATA\WideWorldImporters_Test_InMemory_Data_1’,
replace, stats=10

Here is a simple query:

select *
from Sales.Orders
where PickedByPersonID = 1

The execution plan is:

Let’s take a look at fragmentation:

select i.name as idxName
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,ips.avg_page_space_used_in_percent
,ips.page_count
,STATS_DATE(o.object_id, i.index_id) as statsDate
from sys.dm_db_index_physical_stats(db_id(‘WideWorldImporters_test’),OBJECT_ID(‘Sales.Orders’),NULL,NULL,’DETAILED’) as ips
inner join sys.objects as o
ON o.object_id = ips.object_id
inner join sys.indexes as i
ON o.object_id = i.object_id
AND ips.index_id = i.index_id
where
i.name =’FK_Sales_Orders_PickedByPersonID’

Output:

99 precent (external/logical fragmentation) with 53 percent (internal/physical fragmentation)
So, high fragmentation, and the index (FK_Sales_Orders_PickedByPersonID) is not picked.

Now, let’s update the statistic of this index :
update statistics Sales.Orders (FK_Sales_Orders_PickedByPersonID) With fullScan

Looking, again , at the fragmentation :

The index statistics are updated, but the fragmentation is still high.
Key Question: the execution plan is still the same , or …

select *
from Sales.Orders
where PickedByPersonID = 1

Execution plan has changed.


Conclusion:
1 – Have statistics up to date, is the most important thing
2 – Fragmentation is important, but not as statistics.
3 – Rebuilding an index is doing :

• Put in order the pages
• Update statistics with FULL SCAN

So, first do an update statistics with full scan, then run the query. If this doesn’t take you over the cross line, the next step is to rebuild it. And if this doesn’t take you , over the line, maybe you need to rewrite the Query or call someone.

S

Help Index

There are a tone of scripts to display your indexes, in different type of form.

At some point, I decided to write my own version, and put it into SSMS -> Optins->Environment->Keyboard ->Query Shortcuts.

CREATE PROCEDURE dbo.sp_SQL_helpIndex
@objname nvarchar(128)
AS
SELECT OBJECT_NAME(ius.object_id) as tblName
,i.name
,STUFF(ca.IndexColumnsNames,1,1,”) as IndexColumns
,STUFF(ia.IncludedColumnsNames,1,1,”) as IncludedColumns
,STATS_DATE(ius.object_id, i.index_id) as Stat_Date
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,ius.last_user_seek
,ius.last_user_scan
,i.is_unique
,i.is_primary_key
,i.is_unique_constraint
,i.is_disabled
,i.has_filter
–,’ALTER INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + ‘.’+ QUOTENAME(OBJECT_NAME(i.object_id)) + ‘ DISABLE;’ as DisableStmt
–,’DROP INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + ‘.’+ QUOTENAME(OBJECT_NAME(i.object_id)) + ‘;’ as dropStmt
FROM sys.dm_db_index_usage_stats as ius
inner join sys.indexes as i
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
cross apply
(select ‘, ‘+ c.name +”
from sys.index_columns as ic
inner join sys.columns as c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.is_included_column = 0
order by ic.key_ordinal
for xml path(”)
)ca(IndexColumnsNames)

outer apply
(select ‘, ‘+ c.name +”
from sys.index_columns as ic
inner join sys.columns as c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.is_included_column = 1
order by ic.index_column_id ASC
for xml path(”)
)ia(IncludedColumnsNames)

WHERE
i.type > 1 — nonclustered index
and database_id = DB_ID(DB_Name()) — database in it
and OBJECT_NAME(ius.object_id) =@objname
ORDER BY OBJECT_NAME(ius.object_id),QUOTENAME(i.name)
go

And then mark it as a system stored procedure:

exec [sys].[sp_MS_marksystemobject] ‘sp_SQL_helpIndex’
GO

ps: please here is a condition on it:

and database_id = DB_ID(DB_Name()) — database in it

S

Pages

If you have a fill factor with 70 , what will be the effect , good or bad.

For this I will have :
– table with fill factor 70 – Table70
– table with fill factor 100 = Table100

Each table will have a cluster index on an identity column.
Then Inserting records so that we have like 1000 pages:
4 records per page * 1000 pages = 4000 records needed to be inserted.
Check pages for both tables.
Do a rebuild with the right fill factor.
Check the level of fragmentation.

 

 

 

use db_workspace
go

DROP TABLE dbo.Table70;
CREATE TABLE dbo.Table70
(
idTable INT IDENTITY(1,1) NOT NULL
,colText CHAR(2000) NOT NULL DEFAULT ‘ 70 table some text 2000 char’
,CONSTRAINT PK_Table70_idTable PRIMARY KEY CLUSTERED(idTable)
);

DROP TABLE dbo.Table100;
CREATE TABLE dbo.Table100
(
idTable INT IDENTITY(1,1) NOT NULL
,colText CHAR(2000) NOT NULL DEFAULT ‘ 100 table some text 2000 char’
,CONSTRAINT PK_Table100_idTable PRIMARY KEY CLUSTERED(idTable)
);

INSERT INTO dbo.Table70(colText)
DEFAULT VALUES
GO 4000

INSERT INTO dbo.Table100(colText)
DEFAULT VALUES
GO 4000

Check the number of pages:

 

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table70
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1000 distinct pages

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table100
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1000 distinct pages

Do rebuild with the fill factor :

ALTER INDEX PK_Table70_idTable ON dbo.Table70
REBUILD WITH (FILLFACTOR = 70);

ALTER INDEX PK_Table100_idTable ON dbo.Table100
REBUILD WITH (FILLFACTOR = 100);

Check pages:

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table70
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1334 distinct pages

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table100
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1000 distinct pages

So , we have more pages, in Table70, because of the fill factor = 70. This will trigger more logical reads, more memory …

A plus , in a lower fill factor then 100, and depending of your situation/table/activity . is that it will not generate an expensive page split, in case of a update/insert.

In this case, new records are always added to the end of table , like an append. For update , also it’s not a problem because, you have CHAR data type, and it’s fully allocated.

So , in such a case,  Table100 it’s a better option.

 

In other cases, analyze it, check the cluster index, what kind of DML activity , monitor page splits and finally hire a good DBA.

 

 

S