SQL Server查詢各資料庫耗用的記憶體大小

--記憶體次數統計
SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY 3 DESC
       
--記憶體耗用統計
SELECT
[DatabaseName],
ISNULL([Dirty],'0') AS [Dirty],
ISNULL([Clean],'0') AS [Clean],
ISNULL([Total],'0') AS [Total]
FROM(
SELECT
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database'
ELSE ISNULL(DB_NAME (database_id),'Total')
END) AS 'DatabaseName',
(CASE WHEN ([is_modified] = 1) THEN 'Dirty'
WHEN ([is_modified] = 0) THEN 'Clean'
ELSE 'Total'
END) AS 'State',
COUNT (*)/128 AS 'SizeInMB'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified] WITH CUBE
) AS SourceTable
PIVOT(SUM([SizeInMB]) FOR [State] IN (Clean, Dirty, Total)) AS PivotTable
ORDER BY 4

SQL Server查詢Tempdb使用的Process以及空間耗用

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,last_request_start_time
  ,cpu_time / 1000 AS [CPU TIME (sec)]
  ,total_scheduled_time / 1000 AS [Total Scheduled TIME (sec)]
  ,total_elapsed_time / 1000 AS   [Elapsed TIME (sec)]
  ,(memory_usage * 8 / 1024)      AS [Memory USAGE (MB)]
  ,(user_objects_alloc_page_count * 8 / 1024) AS [SPACE Allocated FOR USER Objects (MB)]
  ,(user_objects_dealloc_page_count * 8 / 1024) AS [SPACE Deallocated FOR USER Objects (MB)]
  ,(internal_objects_alloc_page_count * 8 / 1024) AS [SPACE Allocated FOR Internal Objects (MB)]
  ,(internal_objects_dealloc_page_count * 8 / 1024) AS [SPACE Deallocated FOR Internal Objects (MB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type]
  , row_count AS [ROW COUNT]
FROM       sys.dm_db_session_space_usage
INNER join sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
--order by 6 desc

SQL Server 查詢Table & Index的Size

查詢Table size
SELECT 
    t.NAME AS TableName,p.rows AS RowCounts,
    SUM(a.total_pages)*8/1024 AS TotalSpaceMB, 
    SUM(a.used_pages)*8/1024 AS UsedMB, 
    (SUM(a.total_pages) - SUM(a.used_pages))*8/1024 AS UnusedMB
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
WHERE 1=1
    --and t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    --AND i.OBJECT_ID > 255 
GROUP BY t.Name, p.Rows
ORDER BY 3 desc

查詢Index size
SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName,SUM(a.used_pages)*8 AS 'Indexsize(KB)'
FROM sys.indexes I
JOIN sys.partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_id = I.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
-- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
GROUP BY I.OBJECT_ID,I.index_id,I.name
ORDER BY SchemaName, ObjectName, IndexName

SQL Server列出未使用的Index

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName,SUM(a.used_pages)*8 AS 'Indexsize(KB)'
FROM sys.indexes I
JOIN sys.partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_id = I.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
-- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- ###find all indexes that exists but are NOT used
AND NOT EXISTS ( 
SELECT index_id 
     FROM sys.dm_db_index_usage_stats
     WHERE OBJECT_ID = I.OBJECT_ID 
     AND I.index_id = index_id 
     -- limit our query only for the current db
     AND database_id = DB_ID())
GROUP BY I.OBJECT_ID,I.index_id,I.name
ORDER BY SchemaName, ObjectName, IndexName


參考：
http://caryhsu.blogspot.tw/2012/12/blog-post.html

SQL Server的各資料庫CPU、Memory、I/O使用佔比

--總覽
select db_name(a.dbid) as 'Database',loginame,
a.spid 'PID',
a.cpu 'CPU Time',a.physical_io 'I/O',
a.memusage 'MEM usage',
--login_time,
last_batch,a.status,hostname,program_name,
nt_domain,nt_username,
sql.text
from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) AS sql
where 1=1
and   substring(convert(char(6),last_batch,12),3,4) = substring(convert(char(6),getdate(),12),3,4)
order by 5 desc


--CPU使用
DECLARE @total BIGINT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
    join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT top 5 sb.name 'Database', @total 'System CPU', SUM(cpu) 'Database CPU', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

--I/O使用
DECLARE @total2 INT
SELECT @total2=sum(physical_io) FROM sys.sysprocesses sp (NOLOCK)
    join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT top 5 sb.name 'Database', @total2 'Total Physical I/O', SUM(physical_io) 'Physical I/O', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(physical_io)) / CONVERT(DECIMAL(17,2),@total2)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(physical_io)) / CONVERT(DECIMAL(17,2),@total2)*100) desc

--Runing process I/O
SELECT
    db_name(req.database_id) AS 'Database',
    sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

--History Top 10

SELECT  top 10
        total_logical_reads  as 'Total Read',
        total_logical_writes as 'Total Writes',        
        total_logical_reads+total_logical_writes as 'Total I/O',
        --execution_count      as 'Counts',
        total_elapsed_time as 'Total Time',
        b.text AS 'SQL_TEXT',
        CASE WHEN b.dbid IS NULL THEN 'Ad Hoc Query'
             WHEN dbid = 32767 THEN 'Resource Database'
             ELSE DB_NAME(dbid)
        END AS 'Database'
FROM sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.sql_handle) b
WHERE total_logical_reads+total_logical_writes > 0
and   convert(char(8),last_execution_time, 112) > '20130526' --避免撈全部
order by 3 desc;

將SQL Server 資料庫的 LOG Size 縮小

需先將資料庫的記錄模式整為簡單模式(Simple mode)

--SQL 2005的方法

BACKUP LOG [資料庫名稱] WITH NO_LOG 
DBCC SHRINKDATABASE ([資料庫名稱], 10)

--SQL 2008的方法
手動將資料庫屬性改為"簡單模式"
DBCC SHRINKFILE (N'DBName_Log' , 11, TRUNCATEONLY)
再手動將資料庫屬性改為"完全模式"

--清tempdb log(已設為"Simple mode")
DBCC SHRINKFILE (N'templog' , 11, TRUNCATEONLY)

--指令Sample
USE [資料庫名稱]
GO
ALTER DATABASE [資料庫名稱] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(記錄檔邏輯名稱, 1)
ALTER DATABASE [資料庫名稱] SET RECOVERY FULL WITH NO_WAIT
GO

--查詢log檔名
select * from sys.database_files

SQL Server 查詢應Rebuild or Reorg的語法

設定值，大於15進行Rebuild，大於10進行Reorganize


SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE
              WHEN ps.avg_fragmentation_in_percent > 15
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
       END +
       CASE
              WHEN pc.partition_count > 1
              THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
              ELSE ''
       END,
       avg_fragmentation_in_percent
FROM   sys.indexes AS ix
       INNER JOIN sys.tables t
       ON     t.object_id = ix.object_id
       INNER JOIN sys.schemas s
       ON     t.schema_id = s.schema_id
       INNER JOIN
              (SELECT object_id                   ,
                      index_id                    ,
                      avg_fragmentation_in_percent,
                      partition_number
              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
              ) ps
       ON     t.object_id = ps.object_id
          AND ix.index_id = ps.index_id
       INNER JOIN
              (SELECT  object_id,
                       index_id ,
                       COUNT(DISTINCT partition_number) AS partition_count
              FROM     sys.partitions
              GROUP BY object_id,
                       index_id
              ) pc
       ON     t.object_id              = pc.object_id
          AND ix.index_id              = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10
   AND ix.name IS NOT NULL

SQL Server 2012 支援的功能

功能名稱	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
單一執行個體所使用的計算容量上限 (SQL Server Database Engine)1	作業系統最大值	限制為 4 個插槽或 16 個核心的較小者	限制為 4 個插槽或 16 個核心的較小者	限制為 4 個插槽或 16 個核心的較小者	限制為 1 個插槽或 4 個核心的較小者	限制為 1 個插槽或 4 個核心的較小者	限制為 1 個插槽或 4 個核心的較小者
單一執行個體所使用的計算容量上限 (Analysis Services、Reporting Services)1	作業系統最大值	作業系統最大值	限制為 4 個插槽或 16 個核心的較小者	限制為 4 個插槽或 16 個核心的較小者	限制為 1 個插槽或 4 個核心的較小者	限制為 1 個插槽或 4 個核心的較小者	限制為 1 個插槽或 4 個核心的較小者
使用的記憶體上限 (SQL Server Database Engine)	作業系統最大值	64 GB	64 GB	64 GB	1 GB	1 GB	1 GB
使用的記憶體上限 (Analysis Services)	作業系統最大值	作業系統最大值	64 GB	無	無	無	無
使用的記憶體上限 (Reporting Services)	作業系統最大值	作業系統最大值	64 GB	64 GB	4 GB	無	無
關聯式資料庫大小上限	524 PB	524 PB	524 PB	524 PB	10 GB	10 GB	10 GB

網址