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;
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