--1.查看数据库版本信息select @@version--2.查看所有数据库名称及大小exec sp_helpdb--3.查看数据库所在机器的操作系统参数exec master..xp_msver--4.查看数据库启动的参数exec sp_configure--5.查看数据库启动时间select convert(varchar(30),login_time,120)from master..sysprocesses where spid=1--6.查看数据库服务器名select 'Server Name:'+ltrim(@@servername)--7.查看数据库实例名select 'Instance:'+ltrim(@@servicename) --8.数据库的磁盘空间呢使用信息exec sp_spaceused--9.日志文件大小及使用情况dbcc sqlperf(logspace)--10.表的磁盘空间使用信息exec sp_spaceused 'tablename'--11.获取磁盘读写情况select @@total_read [读取磁盘次数],@@total_write [写入磁盘次数],@@total_errors [磁盘写入错误数],getdate() [当前时间]--12.获取I/O工作情况select @@io_busy,@@timeticks [每个时钟周期对应的微秒数],@@io_busy*@@timeticks [I/O操作毫秒数],getdate() [当前时间]--13.查看CPU活动及工作情况select@@cpu_busy,@@timeticks [每个时钟周期对应的微秒数],@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],getdate() [当前时间]--14.检查锁与等待exec sp_lock--15.检查死锁exec sp_who_lock --自己写个存储过程即可/*create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint) IF @@ERROR<>0 RETURN @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sys.sysprocesses where blocked>0 ) a where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sys.sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end */--16.用户和进程信息exec sp_whoexec sp_who2--17.活动用户和进程的信息exec sp_who 'active'--18.查看进程中正在执行的SQLdbcc inputbuffer(进程号)exec sp_who3/*CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )AS BEGIN SELECT SPID = er.session_id , Status = ses.status , [Login] = ses.login_name , Host = ses.host_name , BlkBy = er.blocking_session_id , DBName = DB_NAME(er.database_id) , CommandType = er.command , SQLStatement = st.text , ObjectName = OBJECT_NAME(st.objectid) , ElapsedMS = er.total_elapsed_time , CPUTime = er.cpu_time , IOReads = er.logical_reads + er.reads , IOWrites = er.writes , LastWaitType = er.last_wait_type , StartTime = er.start_time , Protocol = con.net_transport , ConnectionWrites = con.num_writes , ConnectionReads = con.num_reads , ClientAddress = con.client_net_address , Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE er.session_id > 50 AND @SessionID IS NULL OR er.session_id = @SessionID ORDER BY er.blocking_session_id DESC , er.session_id END*/--19.查看所有数据库用户登录信息exec sp_helplogins --20.查看所有数据库用户所属的角色信息exec sp_helpsrvrolemember--21.查看链接服务器exec sp_helplinkedsrvlogin--22.查看远端数据库用户登录信息exec sp_helpremotelogin --23.获取网络数据包统计信息select @@pack_received [输入数据包数量],@@pack_sent [输出数据包数量],@@packet_errors [错误包数量],getdate() [当前时间]--24.检查数据库中的所有对象的分配和机构完整性是否存在错误dbcc checkdb--25.查询文件组和文件select df.[name],df.physical_name,df.[size],df.growth, f.[name][filegroup],f.is_default from sys.database_files df join sys.filegroups f on df.data_space_id = f.data_space_id --26.查看数据库中所有表的条数select b.name as tablename , a.rowcnt as datacount from sysindexes a , sysobjects b where a.id = b.id and a.indid < 2 and objectproperty(b.id, 'IsMSShipped') = 0 --27.得到最耗时的前10条T-SQL语句;with maco as ( select top 10 plan_handle, sum(total_worker_time) as total_worker_time , sum(execution_count) as execution_count , count(1) as sql_count from sys.dm_exec_query_stats group by plan_handle order by sum(total_worker_time) desc ) select t.text , a.total_worker_time , a.execution_count , a.sql_count from maco a cross apply sys.dm_exec_sql_text(plan_handle) t --28. 查看SQL Server的实际内存占用select * from sysperfinfo where counter_name like '%Memory%'--29.显示所有数据库的日志空间信息dbcc sqlperf(logspace)--30.收缩数据库dbcc shrinkdatabase(databaseName)
