sql 笔记1

    技术2022-05-12  6

     

    --添加一个字段alter table TB_效率  add 抛盘率 int

    --修改一个字段EXEC sp_rename  'TB_效率.zhi' , '拦截率'

    --10大占cpu的SELECT TOP 10   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,   execution_count,   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,      (CASE WHEN statement_end_offset = -1         THEN LEN(CONVERT(nvarchar(max), text)) * 2         ELSE statement_end_offset      END - statement_start_offset)/2)   FROM sys.dm_exec_sql_text(sql_handle)) AS query_textFROM sys.dm_exec_query_statsORDER BY [avg_cpu_cost] DESC

    --修复会丢失数据DBCC CHECKDB('xxx')

    --列和列之间的比较create table tb_A(a int,  b int, c int)

    insert into TB_Aselect 1,2,3union allselect 4,5,6union allselect 7,8,9

    select * ,(select max(a) from (select a union all select b union all select c) as d) from TB_A

    --更新远程表update lianjie.stock_new.tb_Aset A=a.Afrom TB_b a,lianjie.stock_new.tb_A bwhere a.sid=b.sid

    --依次复制create table TB_B(sid int, ziduan int)

    insert into tB_Bselect 1,nullunion all select 1,nullunion all select -1,nullunion all select -1,nullunion all select -1,null

    declare @a int, @b intset @a = 0set @b = 0

    UPDATE tB_B SET @b = (case WHEN @a <> sid THEN @b +1       ELSE @b END)   ,@a = sid   ,ziduan = @b--数据库没有完全关闭,误删了日志文件--注意:数据库的物理位置变动一下USE MASTER GO SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE GO ALTER DATABASE stock_new SET EMERGENCY GO sp_dboption 'stock_new', 'single user', 'true' GO DBCC CHECKDB('stock_new','REPAIR_ALLOW_DATA_LOSS') GO ALTER DATABASE stock_new SET ONLINE GO sp_configure 'allow updates', 0 reconfigure with override GO sp_dboption 'stock_new', 'single user', 'false' GO

    --堵塞分析SELECTblocked_query.session_id AS blocked_session_id,blocking_query.session_id AS blocking_session_id,blocking_sql_text.text AS blocking_sql_text,blocked_sql_text.text AS blocked_sql_text,waits.wait_type AS blocking_resource,blocked_query.command AS blocked_command,blocking_query.command AS blocking_command, blocked_query.wait_type AS blocked_wait_type,blocked_query.wait_time AS blocked_wait_time, blocking_query.total_elapsed_time AS blocking_elapsed_time,GETDATE()FROM sys.dm_exec_requests blocked_queryJOIN sys.dm_exec_requests blocking_query ONblocked_query.blocking_session_id = blocking_query.session_idCROSS APPLY(SELECT *FROM sys.dm_exec_sql_text(blocking_query.sql_handle)) blocking_sql_textCROSS APPLY(SELECT *FROM sys.dm_exec_sql_text(blocked_query.sql_handle)) blocked_sql_textJOIN sys.dm_os_waiting_tasks waits ONwaits.session_id = blocking_query.session_id

    --查看死锁SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0


    最新回复(0)