Index Related Dynamic Management Views and Functions (Transact-SQL)

-

Name:  sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure SQL Managed Instance

Description: Provides current rowgroup-level information about all of the columnstore indexes in the current database.

Permission: Requires CONTROL permission on the table and VIEW DATABASE STATE permission on the database.

Examples:

Calculate fragmentation to decide when to reorganize or rebuild a columnstore index.

For columnstore indexes, the percent of deleted rows is a good measure for the fragmentation in a rowgroup. When the fragmentation is 20% or more, remove the deleted rows.

This example joins sys.dm_db_column_store_row_group_physical_stats with other system tables and then calculates the Fragmentation column as an estimate of the efficiency of each row group in the current database. To find information on a single table, remove the comment hyphens in front of the WHERE clause and provide a table name.

SELECT i.object_id,

    object_name(i.object_id) AS TableName,

    i.name AS IndexName,

    i.index_id,

    i.type_desc,

    CSRowGroups.*,

    100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0) AS 'Fragmentation'

FROM sys.indexes AS i 

JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups

    ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_id

-- WHERE object_name(i.object_id) = 'table_name'

ORDER BY object_name(i.object_id), i.name, row_group_id;




Name:  sys.dm_db_index_operational_stats (Transact-SQL)

Applies to: SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance

Description: Returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Memory-optimized indexes do not appear in this DMV.

sys.dm_db_index_operational_stats do not return information about memory-optimized indexes.

Syntax: 

sys.dm_db_index_operational_stats (    

    { database_id | NULL | 0 | DEFAULT }    

  , { object_id | NULL | 0 | DEFAULT }    

  , { index_id | 0 | NULL | -1 | DEFAULT }    

  , { partition_number | NULL | 0 | DEFAULT }    

)  

Permission: 

Requires the following permissions:

• CONTROL permission on the specified object within the database

• VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id = NULL

• VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL

Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Also, when the database wildcard @database_id=NULL is specified, the database is omitted.


Examples:

Returning information for all tables and indexes

The following example returns information for all tables and indexes within the instance of SQL Server. Executing this query requires VIEW SERVER STATE permission.

SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);    

GO




Name:  sys.dm_db_index_usage_stats (Transact-SQL)

Applies to: SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Parallel Data Warehouse

Description: Returns counts of different types of index operations and the time each type of operation was last performed.

Permission: 

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. 

On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

Examples:

Sys.dm_db_index_usage_stats will help us to pull the information for both user run queries and system usage like collecting data for statistics.

1. When was my index last used and how efficient is my index?

           Check for the last_user_seek/scan/lookup and counts of user_seeks vs scans vs lookup. In most cases Seeks are better than Scans except few exceptions like table having varchar data.

2. What is the maintenance overhead due to my index?

         If the index usage is low and still you see more number of user_updates and high system counters then you have to think about modifying or dropping the index as it is adding more overhead than its usage.

Ref: 






Comments

Post a Comment

Popular posts from this blog

What is DACPAC and how to use it?

-
Image
Few days before someone asked me a question about DACPAC and BACPAC. To be honest I was really not aware of it and I replied that I have heard these terms for the first time. That person replied DACPAC is used for deploying and upgrading database objects. Even after the clear statement, my reply was like developers would answer this better. Even though DBAs are responsible for migration and deployments. After few days, I got a requirement to migrate database schema (Only Schema) to another instance. In such a case most of us would have used the traditional way to script out the database objects and run the script on another instance. But what if there are many objects? I was exploring the easiest way to migrate the objects and got to know about DAC. After reading about DAC, I felt very sorry myself that I was not aware of such a simplest way of migrating and deploying database objects. I went to my friends who are senior developers and asked this question. None of them was aware ...
Read more

Backdoor in SQL server. Is this hacking, loop hole or Feature ?

-
Image
DBAs should always be ready to face any kind of disaster in SQL environment. That doesn't matter whether it is PROD or Non- PROD.  Recently I have been asked regarding recovering the sysadmin password. I couldn't understand the question hence investigated more to understand actual problem. Then I came to know that there is one SQL Server for which admin login and password is lost. I was Like seriously ? How one can be that much of irresponsible? But rather than getting into that kind of discussion I quickly got ready to help the team.  In SQL Server there is a way to access SQL Instance without login. Lets check that out. Steps : 1) Open SQL Server Configuration  Manager. 2) Right click on SQL Server service and go to Properties. 3) Go to "Startup Parameters" Tab and Add -m.   This will Start the server in Single user mode.  4) Click Apply and Ok. This change would require Service restart.  5) Once Service is restarted then, open CMD as Administrator. ...
Read more

Always on setup end to end - Part 1 || Pre-requisites

-
Image
Hello readers,  Welcome to the new blog after a long time. In this blog I would like to discuss complete end to end always on setup guide for beginners. I will be posting couple of blogs regarding always on in SQL server. I believe that would be helpful to understand Always on in SQL server in easy way. Always on setup involved multiple steps. Hence one may find it difficult. However, if you could plan it well then you may find that always on is much easier to configure and handle. So, without wasting the time lets proceed further. Firstly, we will see what is Always on in SQL Server ? Always on is the high availability and disaster recovery solution introduced in SQL server 2012. As the name suggest, Always on means availability of database all the time even in case of disaster. We can say it is the alternative of mirroring. Only thing is windows failover cluster service also required to be configured as a pre-requisite for always on. In this blog, we will only d...
Read more