sql server常用查询语句-sqlserver-运维联盟俱乐部

admin 发表于 2020-1-13 22:18:35

sql server常用查询语句

admin 发表于 2020-1-13 22:18:58

select CONVERT(varchar,GETDATE(),120) as

admin 发表于 2020-1-13 22:19:52

select @@servername as ,
CAST( SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS ,
CAST( SERVERPROPERTY('ServerName')AS NVARCHAR(128)) AS ,
CAST( SERVERPROPERTY('IsClustered') AS NVARCHAR(128)) AS ,
CAST( SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS NVARCHAR(128)) AS ,
serverproperty('edition') as ,
serverproperty('productlevel') as ,
CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) AS ,
SERVERPROPERTY('Productversion') AS ,@@version as

admin 发表于 2020-1-13 22:21:02

select 'Instance:'+ltrim(@@servicename) as

admin 发表于 2020-1-13 22:21:42

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

admin 发表于 2020-1-13 22:22:57

exec sys.sp_readerrorlog 0, 1, 'listening'

admin 发表于 2020-1-13 22:23:34

declare @query2008r2_cpu nvarchar(max)
declare @query2012_cpu nvarchar(max)
set @query2008r2_cpu = 'SELECT cpu_count AS Logical_CPU_Count,
hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_in_bytes/1024/1024 AS Physical_Memory_in_MB
FROM sys.dm_os_sys_info'
set @query2012_cpu = 'SELECT cpu_count AS Logical_CPU_Count,
hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_kb/1024/1024 AS Physical_Memory_in_MB
FROM sys.dm_os_sys_info'
/*SQL Object Memory Allocation*/
declare @version nvarchar(128)
select @version = cast(SERVERPROPERTY('Productversion') as nvarchar(128))
if (@version like '11%')
begin
EXECUTE sp_executesql @query2012_cpu
end
else
begin
EXECUTE sp_executesql @query2008r2_cpu
end

admin 发表于 2020-1-13 22:24:11

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

admin 发表于 2020-1-13 22:24:41

exec sp_helpdb

admin 发表于 2020-1-13 22:25:06

dbcc sqlperf(logspace)

admin 发表于 2020-1-13 22:25:42

SELECT DB_NAME() AS ,
   , name, physical_name, type_desc, state_desc,
   is_percent_growth, growth,
   CONVERT(bigint, growth/128.0) AS ,
   CONVERT(bigint, size/128.0) AS
FROM sys.master_files WITH (NOLOCK)
WHERE > 4
AND <> 32767
OR = 2
ORDER BY DB_NAME() OPTION (RECOMPILE);

admin 发表于 2020-1-13 22:26:20

select name,physical_name,state_desc from sys.master_files;

admin 发表于 2020-1-13 22:27:31

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

admin 发表于 2020-1-13 22:28:08

select count(*) as from sys.master_files

admin 发表于 2020-1-13 22:28:50

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

admin 发表于 2020-1-13 22:29:20

SELECT j.name AS job_name
,js.step_name
,h.run_date
,h.run_time
,h.run_duration
,h.SERVER
,CASE run_status
   WHEN 0
         THEN '失败'
   WHEN 1
         THEN '成功'
   WHEN 2
         THEN '重试'
   WHEN 3
         THEN '取消'
   WHEN 4
         THEN '正在进行'
   END AS run_status
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps js ON js.job_id = h.job_id
AND js.step_id = h.step_id

admin 发表于 2020-1-13 22:30:18

select
@@total_read [读取磁盘次数],
@@total_write [写入磁盘次数],
@@total_errors [磁盘写入错误数],
getdate() [当前时间]

-- I/O Statistics by file for the current database(Query 46) (IO Stats By File)
SELECT DB_NAME(DB_ID()) AS , df.name AS , vfs.,
df.physical_name AS , vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,
CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS ,
CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS ,
(vfs.num_of_reads + vfs.num_of_writes) AS ,
CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS ,
CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS ,
CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS ,
CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.= df. OPTION (RECOMPILE);

admin 发表于 2020-1-13 22:30:49

SELECT login_name,Count(0) user_count
FROM Sys.dm_exec_requests dr WITH(nolock)
RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
ON dr.session_id = ds.session_id
RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
ON ds.session_id = dc.session_id
WHERE ds.session_id > 50
GROUP BY login_name
ORDER BY user_count DESC;

admin 发表于 2020-1-13 22:31:37

SELECT login_name, , COUNT(session_id) AS
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name,
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

SELECT ec.client_net_address, es., es., es.login_name,
COUNT(ec.session_id) AS
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es., es., es.login_name
ORDER BY ec.client_net_address, es. OPTION (RECOMPILE);

admin 发表于 2020-1-13 22:32:22

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

页: [1] 2

运维联盟俱乐部's Archiver

sql server常用查询语句