性能空间内存相关

select count(*)*8/1024 as 'cache size(MB)',
       case database_id
           when 32767 then 'ResourceDb'
           else DB_NAME(database_id)
       end as 'database'
from sys.dm_os_buffer_descriptors
group by DB_NAME(database_id),
         database_id
order by 'cache size(MB)' desc

CREATE TABLE #DBspace(
    [DBname] [sysname] NOT NULL,
    [DBsize] [decimal](18, 2) NULL,
    [DataFileSize] [decimal](18, 2) NULL,
    [LogFileSize] [decimal](18, 2) NULL,
    [UnallocatedSize] [decimal](18, 2) NULL,
    [ReservedSize] [decimal](18, 2) NULL,
    [DataSize] [decimal](18, 2) NULL,
    [IndexSize] [decimal](18, 2) NULL,
    [UnusedSiz] [decimal](18, 2) NULL,
    [AcquisitionTime] [datetime] NULL ) 
    EXEC master.sys.sp_MSforeachdb  @command1='use [?] ', @command2=N'
        use [?];
     DECLARE @pages FLOAT 
     DECLARE @dbsize FLOAT  
     DECLARE @logsize FLOAT 
     DECLARE @reservedpages FLOAT 
     DECLARE @usedpages FLOAT 
     SELECT @dbsize = SUM(CONVERT(BIGINT, CASE WHEN status & 64 = 0 THEN size ELSE 0 END)) ,@logsize = SUM(CONVERT(BIGINT, CASE WHEN status & 64 <> 0 THEN size ELSE 0 END))
     FROM   dbo.sysfiles 
     SELECT @reservedpages = SUM(a.total_pages) ,
            @usedpages = SUM(a.used_pages) ,
            @pages = SUM(CASE WHEN it.internal_type IN ( 202, 204, 207, 211,212, 213, 214, 215,216, 221, 222, 236 ) THEN 0 WHEN a.type <> 1 AND p.index_id < 2 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
     FROM   sys.partitions p
            JOIN sys.allocation_units a ON p.partition_id = a.container_id
            LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 
     INSERT INTO #DBspace
            select  ''?'' ,
                    (( CONVERT (dec(15, 2), @dbsize)+ CONVERT (dec(15, 2), @logsize) ) * 8192 / 1048576 ),
                    (( CONVERT (dec(15, 2), @dbsize) ) * 8192 / 1048576 ),
                    (( CONVERT (dec(15, 2), @logsize) ) * 8192 / 1048576 ),
                    ( (CASE WHEN @dbsize >= @reservedpages THEN ( CONVERT (dec(15, 2), @dbsize)- CONVERT (dec(15, 2), @reservedpages) )* 8192 / 1048576 ELSE 0 END )) ,
                    (( CONVERT (dec(15, 2), @reservedpages) ) * 8192 / 1048576 ),
                    (( CONVERT (dec(15, 2), @pages) ) * 8192 / 1048576) ,
                    (( CONVERT (dec(15, 2), @usedpages - @pages) ) * 8192/ 1048576) ,
                    (( CONVERT (dec(15, 2), @reservedpages - @usedpages) )* 8192 / 1048576),
                    (GETDATE()) '
SELECT * FROM #DBspace ORDER BY UnallocatedSize DESC
DROP TABLE #DBspace

create table #T
(
    [dbname] [nvarchar](100) NULL,
    [logsize] [decimal](30, 2) NULL,
    [logused] [decimal](30, 2) NULL,
    [status] [int] NULL
) 
INSERT INTO #T([dbname],[logsize],[logused],[status])
EXECUTE('dbcc sqlperf(logspace)') 
select a.*,b.log_reuse_wait_desc from #T a inner join master.sys.databases b 
on a.dbname = b.name order by a.logsize desc
drop table #T

USE [DBname]
GO
CREATE TABLE #Tablespace(
    [TableName] [nvarchar](100) NOT NULL,
    [Rows] [nvarchar](100) NULL,
    [ReservedSize] [nvarchar](100) NULL,
    [DataSize] [nvarchar](100) NULL,
    [IndexSize] [nvarchar](100) NULL,
    [UnusedSiz] [nvarchar](100) NULL ) 
INSERT INTO #Tablespace EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT * FROM #Tablespace 
order by convert(int,replace(DataSize,'KB','')) desc,2 desc
DROP TABLE #Tablespace