How to find table and index statistics in sql server

Introduction
Identifying Table and Index Statistics with T-SQL
T-SQL description
Making Informed Decisions
Conclusion

Introduction

Are you curious about the statistics behind your tables and indexes in SQL Server? We've got you covered! In this blog post, we'll walk you through how to find detailed statistics about your tables and indexes, empowering you to make informed decisions about database optimization and performance tuning.

Identifying Table and Index Statistics with T-SQL

This SQL query retrieves information from the dynamic management views, providing the table and index statistics for a given database.

use dbdocs; --Replace it with your Application database

select
    object_schema_name(s.object_id) object_schema,
    object_name(s.object_id) object_name,
    c.name column_name,
    s.name statistics_name,
    convert(varchar, sp.last_updated, 23) last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    s.auto_created,
    s.user_created,
    s.no_recompute,
    sp.steps,
    sp.unfiltered_rows,
    s.has_filter,
    s.filter_definition
from
    sys.stats as s
    cross apply
        sys.dm_db_stats_properties(s.object_id, s.stats_id) as sp
    inner join
    sys.stats_columns as sc
        on s.object_id = sc.object_id
        and
        s.stats_id = sc.stats_id
    inner join
    sys.tables t
        on t.object_id = s.object_id
    inner join
    sys.schemas sch
        on sch.schema_id =  t.schema_id
    inner join
    sys.columns c
        on sc.object_id = c.object_id
        and sc.column_id = c.column_id
where
    sch.name <> 'sys'
order by
    object_name,
    column_name;

how-to-find-table-and-index-statistics

T-SQL description

Now, let's break down each component and understand its significance

Column               | Description
-------------------- | --------------------------------------------------------------------------------------
schema_name          | Schema name.
object_name          | Object name.
column_name          | Column name.
statistics_name      | Name of the statistic. Unique to each object.
late_updated         | Date of last statistics update.
rows                 | Total number of rows in the table or indexed since last update.
rows_sampled         | Total number of rows sampled for statistics calculations.
modification_counter | Total number of modifications for the leading statistics column since last update.
auto_created         | Statistics were automatically created by SQL Server (0=No, 1=Yes).
user_created         | Statistics were created by a user (0=No, 1=Yes).
steps                | Number of steps in the histogram.
unfiltered_rows      | Total number of rows in the table before applying the filter expression.
no_recompute         | Statistics were created with the NORECOMPUTE option (0=No, 1=Yes).
has_filter           | 0 = Computed on all rows, 1 = Computed on filtered rows only.
filter_definition    | Filter statistics express, NULL = no filter.

Making Informed Decisions

By leveraging this SQL query, you gain access to valuable statistics information about your tables and indexes. From the number of rows to the last update date and modification counter, you have the data you need to optimize database performance, identify areas for improvement, and fine-tune your database schema and indexing strategies.

Conclusion

Understanding table and index statistics is essential for effective database management. With the insights provided by this SQL query, you can take proactive measures to enhance database performance, improve query efficiency, and ensure the smooth operation of your applications. So, dive into your SQL statistics today and unlock the full potential of your database!

Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access