30. 查询XX库从YYYY-MM-DD日期开始的日志备份记录，并生成restore log的语句
SELECT TOP 1000S.database_name [Database],CASE [S].[type]WHEN 'L'THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'END [LogRestore],F.physical_device_name,S.[Type],S.backup_start_date,S.backup_finish_date FROM msdb.dbo.backupmediafamily F INNER JOIN msdb.dbo.backupset S ON S.media_set_id = F.media_set_id WHERE S.database_name = 'XX' ANDS.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY S.backup_start_date ASC 
31. 查询always on状态是否正常
select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1 
32. 查看mirror镜像信息
SELECTdb_name(database_id),mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instanceFROM sys.database_mirroring33. 查询SSRS Report Subscriptions相关的jobSELECTb.name AS JobName, e.name, e.path, d.description, a.SubscriptionID, laststatus, eventtype, LastRunTime, date_created, date_modifiedFROMReportServer.dbo.ReportSchedule aJOIN msdb.dbo.sysjobs b ON CONVERT(SYSNAME,a.ScheduleID) = b.nameJOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(SYSNAME,c.ScheduleID)JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionIDJOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemidWHEREe.name = 'Report Name Goes Here' 
34. 查看某个数据库的数据文件信息，就算是mirror从库的数据文件也可以查到，filestream目录也可以查到
SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA'); 
35. 查看某个数据文件信息
select b.name,a.type_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_growth,a.growth from sys.master_files a join sys.databases b on a.database_id=b.database_id and a.physical_name like '%DTSWonda_1%' 
36. 查询实例的数据文件总大小
SELECT sum(size*8/1024/1024) FROM master.sys.master_files 
37. 查询某个目录中数据库使用的总大小
SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:DEFAULT.DATA%' 
38. 查询某个目录中哪些数据库占用了8G以上容量
SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like 'G:DEFAULT.DATA%' and a.size*8/1024/1024>8 
39. 查询实例上的每个数据库的大小
SELECTDB_NAME(db.database_id) DatabaseName,(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMBFROM sys.databases dbLEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_idLEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_idLEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_idLEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id40. 查询总耗CPU最多的前3个SQL，且最近5天出现过SELECT TOP 3total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],SUBSTRING(qt.text,qs.statement_start_offset/2+1,(CASE WHEN qs.statement_end_offset = -1THEN DATALENGTH(qt.text)ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)AS [使用CPU的语法], qt.text [完整语法],qt.dbid, dbname=db_name(qt.dbid),qt.objectid,object_name(qt.objectid,qt.dbid) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())ORDER BY total_worker_time DESC 
41. 查询平均耗CPU最多的前3个SQL，且最近5小时出现过
SELECT TOP 3total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],max_worker_time /1000 AS [最大执行时间(ms)],SUBSTRING(qt.text,qs.statement_start_offset/2+1,(CASE WHEN qs.statement_end_offset = -1THEN DATALENGTH(qt.text)ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)AS [使用CPU的语法], qt.text [完整语法],qt.dbid, dbname=db_name(qt.dbid),qt.objectid,object_name(qt.objectid,qt.dbid) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC