---数据库当前版本
select @@version
---数据库实例
select @@SERVERNAME
---数据库字符集
sp_helpsort
---操作系统参数
exec master..xp_msver
---数据库参数
exec sp_configure
SELECT * FROM sys.configurations;
---查看数据库运行时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1;
---数据库用户的登录信息
exec sp_helplogins
---生产库数据库大小
select 'use ' +name from sysdatabases;
exec sp_spaceused;
---数据库状态
exec sp_helpdb;
---事务日志
dbcc sqlperf(logspace);
---活动进程
exec sp_who 'active'
---锁资源使用情况
select str(request_session_id, 4, 0) as spid,
resource_database_id as DB_Id,
case when resource_database_id = db_id() and resource_type = 'OBJECT'
then convert(char(20), object_name(resource_Associated_Entity_id))
else convert(char(20), resource_Associated_Entity_id)
end as object,
convert(varchar(12), resource_type) as resrc_type,
convert(varchar(12), request_type) as req_type,
convert(char(3), request_mode) as mode,
convert(varchar(8), request_status) as status
from sys.dm_tran_locks
order by request_session_id desc;
---检查数据库完整性
dbcc checkdb
SELECT [total_physical_memory_kb]/1024 as [Physical Memory_MB],
[available_physical_memory_kb]/1024 as [Available_Memory_MB]
FROM sys.dm_os_sys_memory;
select physical_io,cpu,* from sys.sysprocesses
---sqlserver I/O使用情况
select
@@total_read as '读取磁盘的次数',
-@@total_write as '写入磁盘的次数',
@@ERROR as '磁盘写入错误数',
getdate() as '当前时间'
---sqlserver cpu使用率
select
@@cpu_busy,--自上次启动以来的工作时间
@@timeticks as '每个时钟周期对应的微秒数', --每个时钟周期对应的微秒数
@@cpu_busy*cast(@@timeticks as float)/1000 as 'cpu工作时间(秒)',
-@@IDLE*cast(@@timeticks as float)/1000 as 'CPU空闲时间(秒)',
getdate() as '当前时间'
---查看sql server内存使用情况
SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]
FROM sys.dm_os_performance_counters
WHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')
select * FROM sys.dm_os_performance_counters
where counter_name in('Max memory (KB)','Used memory (KB)','Target memory (KB)')
++++++++++++++++++++++++++++++++++++++++++++++++++++
----查看数据库服务器各数据库日志文件的大小及利用率/状态
DBCC SQLPERF(LOGSPACE)
----查看日志：
exec xp_readerrorlog
