查看数据库大小

1.exec sp_spaceused

2.SELECT DB_NAME(database_id) AS [Database Name],
[Name] AS [Logical Name],
[Physical_Name] AS [Physical Name],
((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC

修改数据库初始大小

ALTER DATABASE AIS20221226165455 modify FILE ( NAME=AIS20221226165455, size=15360MB ); 

查询单数据库表数据量及其空间大小

1.查询表数据量：

SELECT *
FROM   (SELECT t.NAME      AS 表名,
               Sum(p.rows) AS 数据量
        FROM   sys.tables t
               INNER JOIN sys.partitions p
                       ON t.object_id = p.object_id
                          AND p.index_id IN ( 0, 1 )
        GROUP  BY t.NAME) A
ORDER  BY 数据量 DESC;

2.查询表空间大小：

SELECT DB_NAME(database_id) AS [Database Name],
        [Name] AS [Logical Name],
        [Physical_Name] AS [Physical Name],
        ((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC

查询SQL语句耗时

DECLARE @begin_date DATETIME
DECLARE @end_date DATETIME
SELECT @begin_date = Getdate()
SELECT ID,
       SecZJBM,
       WLBM,
       Rank bomRank,
       bomroute
FROM   dbo.rpt_ZJQTRate
WHERE  1 = 1
       AND ZJBM = '9010100058'
       AND XMH = 'GPC23101059B2'
SELECT @end_date = Getdate()
SELECT Datediff(MS, @begin_date, @end_date) AS '用时/毫秒' 

查看数据库索引大小

USE ldtcasedossier
GO
SELECT tn.[name] AS [Table name], ix.[name] AS [Index name],
SUM(sz.[used_page_count]) * 8 AS [Index size (KB)]
FROM sys.dm_db_partition_stats AS sz
INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id]
AND sz.[index_id] = ix.[index_id]
INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
GROUP BY tn.[name], ix.[name]
ORDER BY tn.[name]

查看每张表的占用空间及行数

SELECT 
　　 db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 0
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    总共占用空间MB desc



--查看数据库使用率
exec sp_spaceused
--查看单表的空间使用情况
exec sp_spaceused 'DM_LA_HSAGPF_BAK'; 

查询表每个索引的大小

SELECT
    i.name                  AS IndexName,
    SUM(s.used_page_count) * 8   AS IndexSizeKB
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.indexes                AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.DM_LA_HSAGPF_BAK')
GROUP BY i.name
ORDER BY i.name

查看每张表的行数

select * from (
SELECT tables.NAME, 
       (SELECT rows 
        FROM   sys.partitions 
        WHERE  object_id = tables.object_id
       )AS numberofrows 
FROM   sys.tables tables 
WHERE  Objectproperty(tables.object_id, N'TableHasClustIndex') = 0
)t where numberofrows is not null;

查看每张表的创建时间，最后一次的修改时间，行数，压缩情况，分区情况

select 
    A.NAME,
    A.create_date,
    A.modify_date,
    B.rows,
    B.data_compression_desc,
    B.partition_number
from sys.tables A 
left join sys.partitions B 
on A.object_id=B.object_id

查看表的数据行

SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

查询数据库服务器各数据库日志文件的大小及利用率

DBCC SQLPERF(LOGSPACE)

查询各个磁盘分区的剩余空间
Exec master.dbo.xp_fixeddrives

查看数据文件的大小及是否自增
select * from db_glory_id.[dbo].[sysfiles] --size 文件大小，maxsize为0表示不自增为-1表示一直自增到空间加满