--列出最近多少天内的备份记录
SELECT sd.name AS [Database] ,
CASE WHEN bs.type = 'D' THEN 'Full backup'
WHEN bs.type = 'I' THEN 'Differential'
WHEN bs.type = 'L' THEN 'Log'
WHEN bs.type = 'F' THEN 'File/Filegroup'
WHEN bs.type = 'G' THEN 'Differential file'
WHEN bs.type = 'P' THEN 'Partial'
WHEN bs.type = 'Q' THEN 'Differential partial'
ELSE 'Unknown (' + bs.type + ')'
END AS [Backup Type] ,
bs.backup_start_date AS [Date]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON RTRIM(bs.database_name) = RTRIM(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name = '[AdventureWorks]' --填入数据库名
AND bs.backup_start_date > GETDATE() - 10 --填入天数
ORDER BY sd.name ,
[Date]
-----------------------------------------------------
--列出sql实例下所有数据库的最后一次备份类型 和当前备份类型 排除tempdb数据库
SELECT sd.name AS [Database],
CASE WHEN bs.type = 'D' THEN 'Full backup'
WHEN bs.type = 'I' THEN 'Differential'
WHEN bs.type = 'L' THEN 'Log'
WHEN bs.type = 'F' THEN 'File/Filegroup'
WHEN bs.type = 'G' THEN 'Differential file'
WHEN bs.type = 'P' THEN 'Partial'
WHEN bs.type = 'Q' THEN 'Differential partial'
WHEN bs.type IS NULL THEN 'No backups'
ELSE 'Unknown (' + bs.type + ')'
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name <> 'tempdb'
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name, [Last Backup of Type]
----------------------------------------------------------
--数据库文件、大小和已经使用空间
USE [AdventureWorks] --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置
GO
set nocount on
create table #Data(
FileID int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] nvarchar(MAX) NOT NULL,
[FileGroup] varchar(MAX) NULL)
create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePath nvarchar(MAX) NULL,
FileID int null)
create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] nvarchar(MAX) NULL)
INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])
EXEC ('DBCC showfilestats WITH NO_INFOMSGS')
update #Data
set #Data.FileGroup = sysfilegroups.groupname
from #Data, sysfilegroups
where #Data.FileGroupId = sysfilegroups.groupid
INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
FileID
FROM #Data
order BY --1,2
DB_NAME(), [FileGroup]
insert #Log (db,LogSize,LogUsed,Status)
exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')
insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,
s.FileName FilePath,
s.FileID FileID
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.FileID = f.FileID
and l.db = DB_NAME()
SELECT r.db AS "Database",
r.FileType AS "File type",
CASE
WHEN r.FileGroup = 'Log' Then 'N/A'
ELSE r.FileGroup
END "File group",
r.FileName AS "Logical file name",
r.TotalMB AS "Total size (MB)",
r.UsedMB AS "Used (MB)",
r.PctUsed AS "Used (%)",
r.FilePath AS "File name",
r.FileID AS "File ID",
CASE WHEN s.maxsize = -1 THEN null
ELSE CONVERT(decimal(18,2), s.maxsize /128.)
END "Max. size (MB)",
CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5
DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log
----------------------------------------------------------
--查看当前sql实例下buffer cache的命中率
SELECT
CASE WHEN t2.cntr_value = 0
THEN 0
ELSE CONVERT(DECIMAL(38,2), CAST(t1.cntr_value AS FLOAT) / CAST(t2.cntr_value AS FLOAT) * 100.0)
END 'Buffer Cache Hit Ratio (%)'
FROM sys.dm_os_performance_counters t1,
sys.dm_os_performance_counters t2
WHERE
t1.object_name LIKE '%Buffer Manager%'
AND t1.object_name = t2.object_name
AND t1.counter_name='Buffer cache hit ratio'
AND t2.counter_name='Buffer cache hit ratio base'
----------------------------------------------------------------
--在SQL实例级别列出在plan cache中的SQL语句的内容和每个语句平均运行时间并排序
SELECT SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset)/2) + 1) AS "Statement Text",
total_worker_time/execution_count/1000 AS "Average Worker Time (ms)",
execution_count AS "Execution Count",
total_worker_time/1000 AS "Total Worker Time (ms)",
total_logical_reads AS "Total Logical Reads",
total_logical_reads/execution_count AS "Average Logical Reads",
total_elapsed_time/1000 AS "Total Elapsed Time (ms)",
total_elapsed_time/execution_count/1000 AS "Average Elapsed Time (ms)",
QP.query_plan AS "Query Plan (double click to open)"
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
ORDER BY total_elapsed_time/execution_count DESC
------------------------------------------------------------------------
--列出单个数据库里所有的索引碎片
USE [AdventureWorks]
GO
SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
DB_ID()) + '].['
+ OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [Object] ,
i.[name] AS [Index] ,
ddips.[index_type_desc] AS [Index Type],
ddips.[partition_number] AS [Partition Number],
ddips.[alloc_unit_type_desc] AS [Allocation Unit Type],
ddips.[index_depth] AS [Index Depth],
ddips.[index_level] AS [Index Level],
CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT)
AS [Average Fragmentation (%)] ,
CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT)
AS [Average Fragment Size (pages)] ,
ddips.[fragment_count] AS [Fragments],
ddips.[page_count] AS [Pages]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,
NULL, NULL, 'limited') ddips
INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
AND ddips.[index_id] = i.[index_id]
WHERE ddips.[avg_fragmentation_in_percent] > 50 --填入索引碎片度
AND ddips.[page_count] > 100 --填入
ORDER BY ddips.[avg_fragmentation_in_percent] ,
OBJECT_NAME(ddips.[object_id], DB_ID()) ,
i.[name]
----------------------------------------------------------------------
--列出单个实例下SQLSERVER性能计数器的各个比率值
DECLARE @PERF_LARGE_RAW_FRACTION INT ,
@PERF_LARGE_RAW_BASE INT
SELECT @PERF_LARGE_RAW_FRACTION = 537003264 ,
@PERF_LARGE_RAW_BASE = 1073939712
SELECT dopc_fraction.object_name AS [Performance object],
dopc_fraction.instance_name AS [Counter instance],
dopc_fraction.counter_name AS [Counter name],
--when divisor is 0, return I return NULL to indicate
--divide by 0/no values captured
CONVERT(DECIMAL(38,2), CAST(dopc_fraction.cntr_value AS FLOAT)
/ CAST(CASE dopc_base.cntr_value
WHEN 0 THEN NULL
ELSE dopc_base.cntr_value
END AS FLOAT)) AS [Value]
FROM sys.dm_os_performance_counters AS dopc_base
JOIN sys.dm_os_performance_counters AS dopc_fraction
ON dopc_base.cntr_type = @PERF_LARGE_RAW_BASE
AND dopc_fraction.cntr_type = @PERF_LARGE_RAW_FRACTION
AND dopc_base.object_name = dopc_fraction.object_name
AND dopc_base.instance_name = dopc_fraction.instance_name
AND ( REPLACE(UPPER(dopc_base.counter_name), 'BASE', '') =
UPPER(dopc_fraction.counter_name)
--Worktables From Cache has "odd" name where
--Ratio was left off
OR REPLACE(UPPER(dopc_base.counter_name), 'BASE', '') =
REPLACE(UPPER(dopc_fraction.counter_name), 'RATIO', '')
)
ORDER BY dopc_fraction.object_name ,
dopc_fraction.instance_name ,
dopc_fraction.counter_name
-----------------------------------------------------------------------
--列出单个数据库下所有没有聚集索引的用户表
USE [AdventureWorks] --要看出的数据库
GO
SELECT o.name
FROM sys.objects o
WHERE o.type='U'
AND NOT EXISTS(SELECT 1 FROM sys.indexes i
WHERE o.object_id = i.object_id
AND i.type_desc = 'CLUSTERED')
---------------------------------------------------------------------------
--列出实例级别下阻塞的事务和阻塞，包括SQL语句，登录名 会话ID
SELECT db_name(DTL.[resource_database_id]) AS [Database],
DTL.[resource_type] AS [Resource Type] ,
CASE WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]
)
ELSE 'Unidentified'
END AS [Parent Object] ,
DTL.[request_mode] AS [Lock Type] ,
DTL.[request_status] AS [Request Status] ,
DOWT.[wait_duration_ms] AS [Wait Duration (ms)] ,
DOWT.[wait_type] AS [Wait Type] ,
DOWT.[session_id] AS [Blocked Session ID] ,
DES_Blocked.[login_name] AS [Blocked Login] ,
SUBSTRING(DEST_Blocked.text, (DER.statement_start_offset / 2) + 1,
( CASE WHEN DER.statement_end_offset = -1
THEN DATALENGTH(DEST_Blocked.text)
ELSE DER.statement_end_offset
END - DER.statement_start_offset ) / 2)
AS [Blocked Command] ,
DOWT.[blocking_session_id] AS [Blocking Session ID] ,
DES_Blocking.[login_name] AS [Blocking Login] ,
DEST_Blocking.[text] AS [Blocking Command] ,
DOWT.resource_description AS [Blocking Resource Detail]
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.[dm_exec_requests] DER
ON DOWT.[session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES_Blocked
ON DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN sys.dm_exec_sessions DES_Blocking
ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DOWT.[blocking_session_id] = DEC.[most_recent_session_id]
CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle])
AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
------------------------------------------------------------------------------
--列出前十行占用CPU最长时间的SQL语句
SELECT TOP (10)
RANK() Over (ORDER BY deqs.total_worker_time DESC) As [Rank],
CONVERT(decimal(38,2), CONVERT(float, total_worker_time) / 1000) AS [Total CPU Time (ms)],
execution_count AS [Execution Count],
CONVERT(decimal(38,2), (CONVERT(float, total_worker_time) / execution_count) / 1000) AS [Average CPU Time (ms)] ,
SUBSTRING(execText.text,
-- starting value for substring
CASE WHEN deqs.statement_start_offset = 0
OR deqs.statement_start_offset IS NULL
THEN 1
ELSE deqs.statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN deqs.statement_end_offset = 0
OR deqs.statement_end_offset = -1
OR deqs.statement_end_offset IS NULL
THEN LEN(execText.text)
ELSE deqs.statement_end_offset/2 END -
CASE WHEN deqs.statement_start_offset = 0
OR deqs.statement_start_offset IS NULL
THEN 1
ELSE deqs.statement_start_offset/2 END + 1
) AS [Query Text],
execText.text AS [Object Text]
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;
------------------------------------------------------------------------------------
--列出单个数据库的每个数据库文件的读写和总的输入/输出统计信息
SELECT DB_NAME(database_id) AS [Database Name] ,
file_id AS [File ID],
io_stall_read_ms AS [Total Read Waits (ms)],
num_of_reads AS [Number of Reads],
CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1))
AS [Average Read Wait (ms)] ,
io_stall_write_ms AS [Total Write Waits (ms)],
num_of_writes AS [Number of Writes],
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1))
AS [Average Write Wait (ms)] ,
io_stall_read_ms + io_stall_write_ms AS [Total I/O Waits (ms)] ,
num_of_reads + num_of_writes AS [Number of I/O Operations] ,
CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
+ num_of_writes)
AS NUMERIC(10,1)) AS [Average I/O Wait (ms)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) --可以指定dbid 和fileid也可以不指定，如果不指定就列出当前实例下所有数据库的I/O状况
ORDER BY [Average I/O Wait (ms)] DESC ;
--FROM sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks'), NULL)
---------------------------------------------------------------------------
--列出单个数据库的所有表的读写统计
USE [AdventureWorks]
GO
SELECT OBJECT_SCHEMA_NAME(ddius.object_id) + '.' + OBJECT_NAME(ddius.object_id) AS [Object Name] ,
CASE
WHEN ( SUM(user_updates + user_seeks + user_scans + user_lookups) = 0 )
THEN NULL
ELSE CONVERT(DECIMAL(38,2), CAST(SUM(user_seeks + user_scans + user_lookups) AS DECIMAL)
/ CAST(SUM(user_updates + user_seeks + user_scans
+ user_lookups) AS DECIMAL) )
END AS [Proportion of Reads] ,
CASE
WHEN ( SUM(user_updates + user_seeks + user_scans + user_lookups) = 0 )
THEN NULL
ELSE CONVERT(DECIMAL(38,2), CAST(SUM(user_updates) AS DECIMAL)
/ CAST(SUM(user_updates + user_seeks + user_scans
+ user_lookups) AS DECIMAL) )
END AS [Proportion of Writes] ,
SUM(user_seeks + user_scans + user_lookups) AS [Total Read Operations] ,
SUM(user_updates) AS [Total Write Operations]
FROM sys.dm_db_index_usage_stats AS ddius
JOIN sys.indexes AS i ON ddius.object_id = i.object_id
AND ddius.index_id = i.index_id
WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' ) --only works in Current db
GROUP BY ddius.object_id
ORDER BY OBJECT_SCHEMA_NAME(ddius.object_id) + '.' + OBJECT_NAME(ddius.object_id)
------------------------------------------------------------------------------
--列出服务器实例级别下的最长的资源等待，帮你找出系统瓶颈
WITH Waits
AS ( SELECT wait_type ,
wait_time_ms / 1000. AS wait_time_sec ,
100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,
ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE' )
)
SELECT wait_type AS [Wait Type],
CAST(wait_time_sec AS DECIMAL(12, 2)) AS [Wait Time (s)] ,
CAST(pct AS DECIMAL(12, 2)) AS [Wait Time (%)]
FROM Waits
WHERE pct > 1 --wait time limit%
ORDER BY wait_time_sec DESC
------------------------------------------------------------------------
----列出实例下的每个用户会话（不是系统会话）运行的语句，包括登录，查询语句，状态信息
SELECT des.login_name AS [Login],
der.command AS [Command],
dest.text AS [Command Text] ,
des.login_time AS [Login Time],
des.[host_name] AS [Hostname],
des.[program_name] AS [Program],
der.session_id AS [Session ID],
dec.client_net_address [Client Net Address],
der.status AS [Status],
DB_NAME(der.database_id) AS [Database Name]
FROM sys.dm_exec_requests der
INNER JOIN sys.dm_exec_connections dec
ON der.session_id = dec.session_id
INNER JOIN sys.dm_exec_sessions des
ON des.session_id = der.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
WHERE des.is_user_process = 1
---------------------------------------------------------------------
--统计出每个数据库中有多少个表是做了表分区的
DECLARE @db VARCHAR(60)
DECLARE @vsql VARCHAR(1400)
DECLARE getdb CURSOR FOR
SELECT name from msdb.sys.sysdatabases
CREATE TABLE #parts (dbname VARCHAR(60), part int)
OPEN getdb
FETCH NEXT FROM getdb
INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #parts VALUES(@db, 0)
SET @vsql = 'UPDATE #parts SET part = 1 from ' + @db + '.sys.partitions sp , ' + @db + '.sys.sysobjects so
where sp.object_id = so.id and partition_number != 1 and #parts.dbname = ''' + @db + ''''
print @vsql
EXEC (@vsql)
FETCH NEXT FROM getdb INTO @db
END
CLOSE getdb
select dbname AS 'Database', part AS 'Number of Partitioned Tables' from #parts
drop table #parts
deallocate getdb
Database Number of Partitioned Tables
------------------------------------------------------------ ----------------------------
master 0
tempdb 0
model 0
msdb 0
DLGPOS 0
GPOSDB 0
Northwind 0
partionTest 1
---------------------------------------------------------------------------
----列出成功和失败的用户登录SQL服务器的信息 从SQL ERRORLOG文件里获取
DECLARE @TSQL NVARCHAR(2000)
DECLARE @lC INT
CREATE TABLE #TempLog (
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
[Text] NVARCHAR(MAX))
CREATE TABLE #logF (
ArchiveNumber INT,
LogDate DATETIME,
LogSize INT
)
INSERT INTO #logF
EXEC sp_enumerrorlogs
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHILE @lC IS NOT NULL
BEGIN
INSERT INTO #TempLog
EXEC sp_readerrorlog @lC
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHERE ArchiveNumber > @lC
END
--Failed login counts. Useful for security audits.
SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS Details
FROM #TempLog
where ProcessInfo = 'Logon'
and Text like '%failed%'
Group by Text
--Find Last Successful login. Useful to know before deleting "obsolete" accounts.
SELECT Distinct 'Successful - Last login at (' + CONVERT(nvarchar(64), MAX(LogDate)) + ')' AS [Login Attempt], Text AS Details
FROM #TempLog
where ProcessInfo = 'Logon' and Text like '%succeeded%'
and Text not like '%NT AUTHORITY%'
Group by Text
DROP TABLE #TempLog
DROP TABLE #logF
-------------------------------------------------------------------------
--列出当前数据库实例的版本和从开机到现在为止数据库实例已经运行了多少天
SELECT SUBSTRING(CONVERT(VARCHAR, SERVERPROPERTY('servername')), 1, 40) 'Instance' ,
CONVERT(SYSNAME, @@version) 'Version' ,
DATEDIFF(d, crdate, GETDATE()) 'Days Online'
FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
--crdate 数据库创建时间，因为tempdb是SQL启动的时候才创建的，所以过滤条件选择tempdb
SELECT * FROM master.dbo.sysdatabases
-------------------------------------------------------------------------------------------
--查看当前库有哪些表是分区的
SELECT so.[name]
FROM [数据库名].sys.partitions sp ,
[数据库名].sys.objects so
WHERE sp.object_id = so.[object_id]
AND partition_number != 1
GROUP BY [so].[name]
--sys.partitions
---http://technet.microsoft.com/zh-cn/library/ms175012(SQL.90).aspx