sql server常用查询语句

admin

admin

1. select CONVERT(varchar,GETDATE(),120) as [check_time]

复制代码

admin

1. select @@servername as [SQLNetworkName],
   
2. CAST( SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS [MachineName],
   
3. CAST( SERVERPROPERTY('ServerName')AS NVARCHAR(128)) AS [SQLServerName],
   
4. CAST( SERVERPROPERTY('IsClustered') AS NVARCHAR(128)) AS [IsClustered],
   
5. CAST( SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS NVARCHAR(128)) AS [SQLService_Current_Node],
   
6. serverproperty('edition') as [Edition],
   
7. serverproperty('productlevel') as [Servicepack],
   
8. CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) AS [InstanceName],
   
9. SERVERPROPERTY('Productversion') AS [ProductVersion],@@version as [Serverversion]

复制代码

admin

1. select 'Instance:'+ltrim(@@servicename) as [instance_ame]

复制代码

admin

1. select convert(varchar(30),login_time,120) as [startup_time] from master..sysprocesses where spid=1

复制代码

admin

1. exec sys.sp_readerrorlog 0, 1, 'listening'

复制代码

admin

1. declare @query2008r2_cpu nvarchar(max)
   
2. declare @query2012_cpu nvarchar(max)
   
3. set @query2008r2_cpu = 'SELECT cpu_count AS Logical_CPU_Count,
   
4. hyperthread_ratio AS Hyperthread_Ratio,
   
5. cpu_count/hyperthread_ratio AS Physical_CPU_Count,
   
6. physical_memory_in_bytes/1024/1024 AS Physical_Memory_in_MB
   
7. FROM sys.dm_os_sys_info'
   
8. set @query2012_cpu = 'SELECT cpu_count AS Logical_CPU_Count,
   
9. hyperthread_ratio AS Hyperthread_Ratio,
   
10. cpu_count/hyperthread_ratio AS Physical_CPU_Count,
    
11. physical_memory_kb/1024/1024 AS Physical_Memory_in_MB
    
12. FROM sys.dm_os_sys_info'
    
13. /*SQL Object Memory Allocation*/
    
14. declare @version nvarchar(128)
    
15. select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
    
16. if (@version like '11%')
    
17. begin
    
18. EXECUTE sp_executesql @query2012_cpu
    
19. end
    
20. else
    
21. begin
    
22. EXECUTE sp_executesql @query2008r2_cpu
    
23. end

复制代码

admin

1. SELECT --object_name,
   
2. counter_name as Counter, cntr_value/1024 as MemoryLimitSet_inMB
   
3. FROM sys.dm_os_performance_counters
   
4. WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

复制代码

admin

1. exec sp_helpdb

复制代码

admin

1. dbcc sqlperf(logspace)

复制代码

admin

1. SELECT DB_NAME([database_id]) AS [Database Name],
   
2.        [file_id], name, physical_name, type_desc, state_desc,
   
3.        is_percent_growth, growth,
   
4.        CONVERT(bigint, growth/128.0) AS [Growth in MB],
   
5.        CONVERT(bigint, size/128.0) AS [Total Size in MB]
   
6. FROM sys.master_files WITH (NOLOCK)
   
7. WHERE [database_id] > 4
   
8. AND [database_id] <> 32767
   
9. OR [database_id] = 2
   
10. ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

复制代码

admin

1. select name,physical_name,state_desc from sys.master_files;

复制代码

admin

1. with fs
   
2. as
   
3. (
   
4.     select database_id, type, size * 8.0 / 1024 size
   
5.     from sys.master_files
   
6. )
   
7. select
   
8.     name,
   
9.     (select   cast(round(sum(size),2)   as   numeric(15,2))  from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
   
10.     (select cast(round(sum(size),2)   as   numeric(15,2))  from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
    
11. from sys.databases db order by 2 desc

复制代码

admin

1. select count(*) as [dbf_count] from sys.master_files

复制代码

admin

1. select name,growth,max_size /1024/1024*8 as 'GB' from sys.master_files

复制代码

admin

1. SELECT j.name AS job_name
   
2.     ,js.step_name
   
3.     ,h.run_date
   
4.     ,h.run_time
   
5.     ,h.run_duration
   
6.     ,h.SERVER
   
7.     ,CASE run_status
   
8.         WHEN 0
   
9.             THEN '失败'
   
10.         WHEN 1
    
11.             THEN '成功'
    
12.         WHEN 2
    
13.             THEN '重试'
    
14.         WHEN 3
    
15.             THEN '取消'
    
16.         WHEN 4
    
17.             THEN '正在进行'
    
18.         END AS run_status
    
19. FROM msdb.dbo.sysjobhistory h
    
20. INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
    
21. INNER JOIN msdb.dbo.sysjobsteps js ON js.job_id = h.job_id
    
22.     AND js.step_id = h.step_id

复制代码

admin

1. select
   
2. @@total_read [读取磁盘次数],
   
3. @@total_write [写入磁盘次数],
   
4. @@total_errors [磁盘写入错误数],
   
5. getdate() [当前时间]
   
6. 
   
7. -- I/O Statistics by file for the current database  (Query 46) (IO Stats By File)
   
8. SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id],
   
9. df.physical_name AS [Physical Name], vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,
   
10. CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
    
11. CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
    
12. (vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads],
    
13. CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read],
    
14. CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written],
    
15. CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
    
16. CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
    
17. CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],
    
18. CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
    
19. FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
    
20. INNER JOIN sys.database_files AS df WITH (NOLOCK)
    
21. ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);

复制代码

admin

1. SELECT login_name,Count(0) user_count
   
2. FROM Sys.dm_exec_requests dr WITH(nolock)
   
3. RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
   
4. ON dr.session_id = ds.session_id
   
5. RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
   
6. ON ds.session_id = dc.session_id
   
7. WHERE ds.session_id > 50
   
8. GROUP BY login_name
   
9. ORDER BY user_count DESC;

复制代码

admin

1. SELECT login_name, [program_name], COUNT(session_id) AS [session_count]
   
2. FROM sys.dm_exec_sessions WITH (NOLOCK)
   
3. GROUP BY login_name, [program_name]
   
4. ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);
   
5. 
   
6. SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name,
   
7. COUNT(ec.session_id) AS [connection count]
   
8. FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
   
9. INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
   
10. ON es.session_id = ec.session_id
    
11. GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name  
    
12. ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);

复制代码

admin

1. select blocking_session_id, wait_duration_ms, session_id from
   
2. sys.dm_os_waiting_tasks
   
3. where blocking_session_id is not null

复制代码