sqlsever2008 Common SQL command Collection

1. Number of physical CPUs

SELECT (Cpu_count/hyperthread_ratio) as physicalcpus,cpu_count as Logicalcpusfrom sys.dm_os_sys_info

2. Querying the database recovery model, log reuse wait description, transaction log size, log space used, log usage percentage, compatibility level, and page validation

--Recovery model, log reuse wait description, log file size,--log usage size and compatibility level for all databases O n Instanceselect 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_val UE as FLOAT) as DECIMAL (18,2)) * [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_namewhere lu.counter_name like ' log file (s) used size (KB)% ' and ls.counter_name like ' log file (s) size (KB)% ' ;

3. Clear the global cache using the following statement:

DBCC dropcleanbuffers;

4. Clear the execution plan from the global cache, using the following statement:

DBCC Freeproccache;

5. View statements for files and filegroups

SELECT DF. [Name], Df.physical_name, DF. [Size], df.growth, F.[name][filegroup], f.is_defaultfrom sys.database_files dfjoin sys.filegroups FON df.data_space_id = f.data_space_id

6. View the number of online users currently connected in SQL Server

Use Masterselect loginame,count (0) from Sysprocessesgroup to Loginameorder by Count (0) Descselect nt_username,count (0) FR Om Sysprocessesgroup by Nt_usernameorder by count (0) desc

sqlsever2008 Common SQL command Collection