SQL server数据库表碎片比例查询语句
程序员文章站
2022-03-24 13:35:18
For rebuilding index, here is also a script to figure out the fragmentation and decide whether rebuilding index is in need: When the avg_fragmentation ......
for rebuilding index, here is also a script to figure out the fragmentation and decide whether rebuilding index is in need:
use [database_name]
select dbschemas.[name] as 'schema',
dbtables.[name] as 'table',
dbindexes.[name] as 'index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
from sys.dm_db_index_physical_stats (db_id(), null, null, null, null) as indexstats
inner join sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
inner join sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
inner join sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
and indexstats.index_id = dbindexes.index_id
where indexstats.database_id = db_id()
order by indexstats.avg_fragmentation_in_percent desc
when the avg_fragmentation_in_percent >30, please rebuild the index (alter index rebuild). if the 5 < avg_fragmentation_in_percent < 30, please reorgnize the index (alter index reorganize)
however, as you mentioned that it finished quickly, maybe you can manage it before you run the job each time. just arrange it as the preparation for you job. please update statistics each time you want to run the job.
推荐阅读
-
数据库(SQL Server )经典例题(二):对S表、P表、J表、SPJ表的操作——单表查询
-
SQL server数据库查询语句使用方法详细讲解
-
【转载】C#常用数据库Sqlserver通过SQL语句查询数据库以及表的大小
-
SQL SERVER查询数据库所有表的大小,按照记录数降序排列
-
数据库(SQL Server )经典例题(三):对S表、P表、J表、SPJ表的操作——多表查询
-
数据库多表查询关联查询SQL语句(最多可三表关联)实例讲解
-
Sql Server函数的操作实例(执行多条语句,返回Select查询后的临时表)
-
SQL server数据库表碎片比例查询语句
-
在SQL SERVER中查询数据库中第几条至第几条之间的数据SQL语句写法
-
使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名,字段名