--添加一个字段
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_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC
--修复会丢失数据
DBCC CHECKDB('xxx')
--列和列之间的比较
create table tb_A
(a int,
b int,
c int)
insert into TB_A
select 1,2,3
union all
select 4,5,6
union all
select 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_A
set A=a.A
from TB_b a,lianjie.stock_new.tb_A b
where a.sid=b.sid
--依次复制
create table TB_B
(sid int,
ziduan int)
insert into tB_B
select 1,null
union all select 1,null
union all select -1,null
union all select -1,null
union all select -1,null
declare @a int, @b int
set @a = 0
set @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
--堵塞分析
SELECT
blocked_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_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) blocking_sql_text
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) blocked_sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id
--查看死锁
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0