最新更新主题

月度关注热点

网络推荐

查明SQL Server表和模式空间使用情况

字体: 小 中 大 | 打印 发表于: 2008-4-10 15:53    作者: sitedir    来源: 飞酷经典资源网

提供及时全面的经典资源 SQL Server数据库的管理员常常会头疼硬盘可用空间太少，往往需要编写很多查询来查明哪些表和模式占用了大量的硬盘空间，以便能够定期清理这些表和模式。本文将为大家阐述如何查询系统目录来确定磁盘空间的使用情况，可以帮助SQL Server数据库管理员识别占用最大空间的表和模式，以便将老旧的数据归档，将不需要的数据清除。本文所列脚本适用于SQL Server 2005和SQL Server 2008 CTP5。 　　步骤1：如图一所示，登录到SQL Server实例。
　　
　　步骤2：确定你想要获得空间使用信息的数据库，导航到该数据库，如下所示(见图二)
　　Use AdventureWorks
　　go
　　
　　步骤3.1：将下面的代码添加到查询窗口，并执行(见图三)。
　　
　　
begin try
SELECT
(row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
   (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
   (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
  ps.object_id,
  SUM (
   CASE
    WHEN (ps.index_id < 2) THEN row_count
    ELSE 0
   END
   ) AS [rows],
  SUM (ps.reserved_page_count) AS reserved,
  SUM (
   CASE
     WHEN (ps.index_id < 2) THEN
   (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
     ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
   END
   ) AS data,
  SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
  it.parent_id,
  SUM(ps.reserved_page_count) AS reserved,
  SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name
end try
begin catch
select
-100 as l1
, 1 as schemaname
,       ERROR_NUMBER() as tablename
,       ERROR_SEVERITY() as row_count
,       ERROR_STATE() as reserved
,       ERROR_MESSAGE() as data
,       1 as index_size
,   1 as unused
end catch

我也来说两句 查看全部回复

最新回复

  • sitedir (2008-4-10 15:53:44)

    　 　　执行上述代码的时候，我们可以看到表空间使用报告(见图四)。
    　　
    　　步骤3.2：上述脚本可进行一定修改，使用SUM、COUNT函数并通过从句进行分组，以显示模式空间的使用情况。将以下代码添加到查询窗口，并运行(见图五)。
    　　
    begin try
    SELECT
    (row_number() over(order by a3.name, a2.name))%2 as l1,
    a3.name AS [schemaname],
    count(a2.name ) as NumberOftables,
    sum(a1.rows) as row_count,
    sum((a1.reserved + ISNULL(a4.reserved,0))* 8) AS reserved,
    sum(a1.data * 8) AS data,
    sum((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
       (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 )AS index_size,
    sum((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
       (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8) AS unused
    FROM
    (SELECT
      ps.object_id,
      SUM (
       CASE
        WHEN (ps.index_id < 2) THEN row_count
        ELSE 0
       END
       ) AS [rows],
      SUM (ps.reserved_page_count) AS reserved,
      SUM (
       CASE
        WHEN (ps.index_id < 2) THEN
         (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
        ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
       END
       ) AS data,
      SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
    GROUP BY ps.object_id) AS a1
    LEFT OUTER JOIN
    (SELECT
      it.parent_id,
      SUM(ps.reserved_page_count) AS reserved,
      SUM(ps.used_page_count) AS used
      FROM sys.dm_db_partition_stats ps
      INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
      WHERE it.internal_type IN (202,204)
      GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
    WHERE a2.type <> 'S' and a2.type <> 'IT'
    group by a3.name
    ORDER BY a3.name
    end try
    begin catch
    select
    -100 as l1
    , 1 as schemaname
    ,       ERROR_NUMBER() as tablename
    ,       ERROR_SEVERITY() as row_count
    ,       ERROR_STATE() as reserved
    ,       ERROR_MESSAGE() as data
    ,       1 as index_size
    ,   1 as unused
    end catch

  • sitedir (2008-4-10 15:54:07)

    　 　　运行返回模式空间使用情况的结果(见图六)
    　　
    　　总结
    　　通过执行上述查询，我们就能够根据表或根据模式来查明空间使用情况，并将返回的结果在一个表中显示。
查看全部回复 我也来说两句