SQLServer常用sql记录

查看备份恢复任务进度

SELECT req.session_id, 
	database_name = db_name(req.database_id),
	req.status,
	req.blocking_session_id, 
	req.command,
	[sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (
				(
					CASE req.statement_end_offset
						WHEN - 1 THEN Datalength(txt.TEXT)
						ELSE req.statement_end_offset
					END - req.statement_start_offset
					) / 2
				) + 1),
	req.percent_complete,
	req.start_time,
	cpu_time_sec = req.cpu_time / 1000,
	granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),
	req.reads,
	req.logical_reads,
	req.writes,
	eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),
	elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),
	remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),
	eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),
	wait_type,
	wait_time_sec = wait_time/1000, 
	wait_resource
FROM sys.dm_exec_requests as req WITH(NOLOCK)
	CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt 
WHERE req.session_id>50
	AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG');

慢sql查询

declare @sKssj varchar(23),
        @sJssj varchar(23)
set @sKssj='2023-08-02 09:30:00.000'
set @sJssj='2023-08-02 10:30:00.000'
SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
and creation_time between @sKssj and @sJssj
ORDER BY 
total_elapsed_time / execution_count DESC;

重建索引

查看索引

SELECT B.NAME,A.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(db_id(),object_id('dbo.CTP_AFFAIR'),null,null,null) A ,sys.indexes B WHERE A.INDEX_ID=B.INDEX_ID AND A.OBJECT_ID=B.OBJECT_ID ORDER BY 2 DESC

SELECT * FROM sys.dm_db_index_physical_stats(db_id(),object_id('dbo.CTP_AFFAIR'),null,null,null) 

declare @table_id int  
set @table_id=object_id('tablename')  
dbcc showcontig(@table_id)

exec sp_helpindex 'CTP_AFFAIR'

SELECT name AS IndexName, type_desc AS IndexType
FROM sys.indexes
WHERE object_id = OBJECT_ID('TableName')

重建索引

dbcc dbreindex(tablename,'',90)