sql server常用查询语句

admin · 发表于 2020-1-13 22:33:00

SELECT o.name AS [table_name], i.name AS [index_name], ios.index_id, ios.partition_number,
SUM(ios.row_lock_wait_count) AS [total_row_lock_waits],
SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],
SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],
SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],
SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON ios.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ios.[object_id] = i.[object_id]
AND ios.index_id = i.index_id
WHERE o.[object_id] > 100
GROUP BY o.name, i.name, ios.index_id, ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);

复制代码

admin · 发表于 2020-1-13 22:33:42

WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

复制代码

admin · 发表于 2020-1-13 22:34:10

复制代码

admin · 发表于 2020-1-13 22:35:23

SELECT TOP 10 DB_NAME(qt.dbid) as DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count AS Avg_MultiCore_CPU_time_sec,
qs.total_worker_time / 1000000 As Total_MultiCore_CPU_time_sec,
qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
qs.total_elapsed_time / 1000000 As Total_Seconds,
(total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO,
total_logical_reads + total_logical_writes as Total_IO,
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.[text])
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
) as Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qs.execution_count > 5 --more than 5 occurrences
ORDER BY Total_MultiCore_CPU_time_sec DESC

复制代码

admin · 发表于 2020-1-13 22:36:08

SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count As Avg_MultiCore_CPU_time_sec,
qs.total_worker_time / 1000000 as Total_MultiCore_CPU_time_sec,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds,
qs.total_elapsed_time / 1000000.0 AS Total_Seconds,
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where last_execution_time > getdate()-1
ORDER BY average_seconds DESC

复制代码

admin · 发表于 2020-1-13 22:36:58

declare @count int
select @count = count(1) from msdb.dbo.sysjobs as sj
join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id
where sj.enabled != 0
and sjh.sql_message_id > 0
and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
and sjh.Step_id <= 1
if (@count >= 1)
begin
select distinct sj.name as SQLJobName
from msdb.dbo.sysjobs as sj
join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id
where sj.enabled != 0
and sjh.sql_message_id > 0
and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
and sjh.Step_id <= 1
order by name
end
else
begin
Select 'No Job Failed in Last 24 Hours' as SQLJobName
end

复制代码

admin · 发表于 2020-1-13 22:37:34

SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio],
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date]
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
AND bs.backup_size > 0
AND bs.[type] = 'D' -- Change to L if you want Log backups
AND database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);

复制代码

admin · 发表于 2020-1-13 22:38:12

复制代码

admin · 发表于 2020-1-13 22:38:52

exec master..xp_msver
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

复制代码

admin · 发表于 2020-6-19 23:05:09

复制代码

admin · 发表于 2020-6-19 23:07:48

复制代码

admin · 发表于 2020-6-19 23:10:30

declare @tableName varchar(50) = 'LbaListAlertDetail'
declare @tableId int
select @tableId = object_id
from sys.objects
where name = @tableName
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
,IX.name AS Index_Name
,IX.type_desc Index_Type
,SUM(PS.[used_page_count]) * 8 IndexSizeKB
,IXUS.user_seeks AS NumOfSeeks
,IXUS.user_scans AS NumOfScans
,IXUS.user_lookups AS NumOfLookups
,IXUS.user_updates AS NumOfUpdates
,IXUS.last_user_seek AS LastSeek
,IXUS.last_user_scan AS LastScan
,IXUS.last_user_lookup AS LastLookup
,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
and IX.OBJECT_ID = @tableId
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

复制代码

admin · 发表于 2020-6-19 23:13:09

admin 发表于 2020-6-19 23:10

select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 as Usedsize_MB,
size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB
from sys.master_files where database_id=db_id(N'DBNAME')

复制代码

admin · 发表于 2020-6-21 08:47:00

复制代码

		自动登录	找回密码
密码			立即注册