文章来源:https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw 作者:廖学强 1. sqlserver查看实例级别的信息,使用SERVERPROPERTY函数 select SERVERPROPERTY ('propertyname') 2. 查看实例级别的某个参数XX的配置
3. 更改实例级别的某个参数XX的值 sp_configure 'XX','0' RECONFIGURE WITH OVERRIDE sp_configure显示或更改当前服务器的全局配置设置。 RECONFIGURE表示SQL Server不用重新启动就立即生效 。 使用sp_configure更改设置时,请使用RECONFIGURE语句使更改立即生效,否则更改将在SQL Server重新启动后生效。RECONFIGURE后面加WITH OVERRIDE表示不管这个值是不是符合要求都会生效,比如recovery interval的范围值是10--60对应sys.configurations.minimum是10、sys.configurations.maximum是60,如果sp_configure 'recovery interval', 75设置为75,超过了这个10--60规范,但是要让75生效,则必须加上WITH OVERRIDE。 4. sqlserver没有系统表可以查询所有数据库下面对象,以下只能在当前数据库下面查
5. 全局系统视图、单个数据库系统视图 sys.database_files --每个存储在数据库本身中的数据库文件在表中占用一行。这是一个基于每个数据库的视图。sys.master_files --master 数据库中的每个文件对应一行。这是一个系统范围视图。--sys.database_files、sys.master_files这种的视图,在每个数据库的系统视图下面都有 6. 一些只存在msdb的系统表,而非系统视图
7. sp_lock、sp_who、sp_who2、sp_helptext等一些系统存储过程存在于每个数据库中 8. 报告有关锁的信息,会显示实例里面的所有数据库的锁信息、堵塞信息 sp_lock 9. 提供有关当前用户、 会话和进程的实例中的信息,可以看到会话的状态running、SUSPENDED、sleeping、rollback,sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很大
10. 查看某个存储过程的内容 sp_helptext pro_name 11.显示某个线程号发送到sqlserver数据库的最后一个语句
12.假设查询到249被锁给堵塞了,查询被堵塞的SQL语句 DBCC INPUTBUFFER (249) 13. 查看某个数据库中是否存在活动事务,有活动事务就一定会写日志
14. 监视日志空间 DBCC SQLPERF (LOGSPACE) 15. 查找无法重用日志中的空间的原因(日志无法截断导致日志文件越来越大,但是可用空间很小,无法收缩)
16. 查看虚拟日志文件信息 DBCC LOGINFO 结果有多少行,代表有多少虚拟日志文件,活动的虚拟日志文件的状态(status)为2 17. 修复msdb数据库,比如ssms页面sql server agent丢失或看不了job view history等功能,说明msdb坏了,需要修复
18. 在您当前连接到的 SQL Server 数据库中生成一个手动检查点 CHECKPOINT [ checkpoint_duration ]--checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间,一般不使用这个参数,让数据库自己控制 19. 查看数据库各种设置
20. 查看某个数据库中是否存在会话 select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname') 21. 查询当前阻塞的所有请求
22. 查看哪些表被锁了,以及这些表被哪个进程锁了 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC 23. 查询某个job是否被堵塞
24. 检查SQL Agent是否开启 IF EXISTS (SELECT TOP 1 1FROM sys.sysprocessesWHERE program_name = 'SQLAgent - Generic Refresher')SELECT 'Running'ELSESELECT 'Not Running' 25. 查看活动线程执行的sql语句,并生成批量杀掉的语句
26. 查看备份进度 SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')--OR command LIKE 'RESTORE%'ORDER BY 2 DESC 27. 查看恢复进度
28. 查看数据库的最近备份信息 SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type备注:D 表示全备份,i 表示差异备份,L 表示日志备份 29. 查看数据库的历史备份记录,并生成restore语句
30. 查询XX库从YYYY-MM-DD日期开始的日志备份记录,并生成restore log的语句 SELECT TOP 1000 S.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' AND S.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY S.backup_start_date ASC 31. 查询always on状态是否正常
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目录也可以查到
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. 查询实例的数据文件总大小
37. 查询某个目录中数据库使用的总大小 SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:\DEFAULT.DATA%' 38. 查询某个目录中哪些数据库占用了8G以上容量
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小时出现过
42. 查看当前最耗资源的10个SQL及其spid SELECT TOP 10session_id,request_id,start_time AS '开始时间',status AS '状态',command AS '命令',d_sql.text AS 'sql语句', DB_NAME(database_id) AS '数据库名',blocking_session_id AS '正在阻塞其他会话的会话ID',wait_type AS '等待资源类型',wait_time AS '等待时间',wait_resource AS '等待的资源',reads AS '物理读次数',writes AS '写次数',logical_reads AS '逻辑读次数',row_count AS '返回结果行数'FROM sys.dm_exec_requests AS d_requestCROSS APPLYsys.dm_exec_sql_text(d_request.sql_handle) AS d_sqlWHERE session_id>50ORDER BY cpu_time DESC--前50号session_id一般是系统后台进程,sys.dm_exec_requests的status显示为background 43. 查询某个存储过程被哪些job调用了
44. 命令执行某个job EXECUTE msdb.dbo.sp_start_job N'job_name' 45. 查询某表标识列的列名
46. 获取标识列的种子值 SELECT IDENT_SEED ('表名') 47. 获取标识列的递增量
48. 获取指定表中最后生成的标识值 SELECT IDENT_CURRENT('表名') 49. 重新设置标识种子值为XX
50. 升级前,查询服务器名、实例名、版本号 select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version 51. 用户被grant这样操作赋予的权限
52. 授予某个用户执行某个数据库的sp的权限 use dbname grant execute to 'username' 53. always on
54. 查询实例的FILESTREAM 使用的DIRECTORY_NAME SELECT SERVERPROPERTY('FilestreamShareName') 55. 查询FILETABLE表的数据库对应的DIRECTORY_NAME
56. 查询FILETABLE表对应的DIRECTORY_NAME select object_name(object_id),* from sys.filetables 57. 查询filetable表testdb.dbo.table1中的文件完整路径名称
59. 锁表的四种用法 TABLOCKX SELECT * FROM table WITH (TABLOCKX)查询过程中,其他会话无法查询、更新此表,直到查询过程结束TABLOCK SELECT * FROM table WITH (TABLOCK)查询过程中,其他会话可以查询,但是无法更新此表,直到查询过程结束 HOLDLOCK SELECT * FROM table WITH (HOLDLOCK)查询过程中,其他会话可以查询,但是无法更新此表,直到查询过程结束NOLOCK SELECT * FROM table WITH (NOLOCK)查询过程中,其他会话可以查询、更新此表 60. 查询某个发布XX,发布的数据库对象的2种方法
61. 查询发布信息,发布名称,发布名称对应的发布序号 Select * from distribution.dbo.MSpublications 62. 查询发布名里面的发布对象的信息,包含表、视图、存储过程等
63. 监控发布订阅是否有异常,执行以下5条语句即可 select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].MSrepl_errors order by 2 descselect * from msdb.dbo.sysreplicationalerts order by 7 desc 64. 查询XX表的索引信息
65. 生成sql语句的执行计划(select XXX为例,当然select XXX也可以换成执行存储过程比如exec pro_XXX,都是只生成执行计划,不产生结果集,不会执行存储过程) SET SHOWPLAN_ALL ON; GO select XXX GO SET SHOWPLAN_ALL OFF; GO 或 SET SHOWPLAN_XML ON; GO select XXX GO SET SHOWPLAN_XML OFF; GO 66. 查询名称为XXX的job的最后一次运行成功的时间
67. 查询某张分区表的总行数和大小,比如表为crm.EmailLog exec sp_spaceused 'crm.EmailLog'; 68. 查询某张分区表的信息,每个分区有多少行,比如表为crm.EmailLog
69. 查询分区函数 select * from sys.partition_functions 70. 查看分区架构
71. 查询ssis包的信息 select * from msdb.dbo.sysssispackages 72. 查询某张表里的索引的大小,如下示例表为dbo.table1
73. 重建表上的所有索引 alter index all on table_name rebuild with (online=on)重建表上的某个索引alter index index_name on table_name rebuild with (online=on)重新组织表上的所有索引 alter index all on table_name reorganize重新组织表上的某个索引 alter index index_name on table_name reorganize 74. 查看数据文件可收缩空间,结果见Availabesize_MB字段值
FROM sys.indexes IX INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1 and IX.OBJECT_ID = @tableId GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update sqlserver中类似oracle的dba_source的视图是sys.sql_modules 76. 查询某个数据库下的表数据占用磁盘容量最大的10张表 use XX if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u') drop table #tabName go create table #tabName( table_name varchar(100), rowsNum varchar(100), reserved_size varchar(100), data_size varchar(100), index_size varchar(100), unused_size varchar(100) ) declare @name varchar(100) declare cur cursor for select name from sysobjects where xtype='u' order by name open cur fetch next from cur into @name while @@fetch_status=0 begin insert into #tabName exec sp_spaceused @name fetch next from cur into @name end close cur deallocate cur select top 10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,convert(int,SUBSTRING(data_size,0,LEN(data_size)-2)) size from #tabName ORDER BY size desc或 select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts from ( SELECT 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 TotalSpaceMB, 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 > 255 GROUP BY t.Name, s.Name, p.Rows) a GROUP BY a.tablename,a.SCHEMANAME order by sum(a.TotalSpaceMB) desc --这个比上一个专业 77. 查询某个数据库中是否有create index '+name+ CHAR(10)
78. 根据id号查询某个数据库名 SELECT DB_NAME(18) 根据id号查询某个对象名 SELECT OBJECT_NAME(1769220894) 79. 查看收缩的进度100%,此语句要到指定的数据库下执行
80. 查看重新组织索引的100%进度 SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE '%REORGANIZE%' --and database_id=db_id('cardorder')ORDER BY 2 DESC 81. 查看存储过程的执行计划
82. 查看当前用户 select system_user 83. 查询ddl修改操作的记录
原文链接:http://blog./30126024/viewspace-2638523/ |
|