sqlsever2008常用SQL命令集合

浏览数：21 / 时间：2015年06月12日

1.物理的CPU个数

SELECT (cpu_count / hyperthread_ratio) AS PhysicalCPUs,
cpu_count AS logicalCPUs
FROM sys.dm_os_sys_info

2. 查询数据库恢复模式、日志重用等待描述、事务日志的大小、使用的日志空间、日志使用百分比、兼容级别和页面验证

-- Recovery model, log reuse wait description, log file size,
-- log usage size and compatibility level for all databases on instance


SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] 
, db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] 
,lu.cntr_value AS [Log Used (KB)] 
, CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] 
, db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db 
INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name 
INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_name
WHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%‘ AND 
ls.counter_name LIKE ‘Log File(s) Size (KB)%‘ ;

3.清除全局缓存使用下面的语句：

DBCC DROPCLEANBUFFERS;

4.从全局缓存中清除执行计划，使用下面的语句：

DBCC FREEPROCCACHE;

5.查看文件及文件组的语句

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

6.在SQL Server里查看当前连接的在线用户数

use master
select loginame,count(0) from sysprocesses
group by loginame
order by count(0) desc
select nt_username,count(0) from sysprocesses
group by nt_username
order by count(0) desc

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

sqlsever2008常用SQL命令集合