--添加一个字段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
