查看当前的 sql 语句 sqlserver

Question

查看当前的 sql 语句
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
             er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
                                er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2

Answer 1

ysprocesses 表中保存关于运行在 Microsoft® SQL Server™ 上的进程的信息。这些进程可以是客户端进程或系统进程。sysprocesses 只存储在 master 数据库中。

列名 数据类型 描述
spid smallint SQL Server 进程 ID。
kpid smallint Microsoft Windows NT 4.0® 线程 ID。
blocked smallint 分块进程的进程 ID (spid)。
waittype binary(2) 保留。
waittime int 当前等待时间（以毫秒为单位）。当进程不处于等待时，为 0。
lastwaittype nchar(32) 表示上次或当前等待类型名称的字符串。
waitresource nchar(32) 锁资源的文本化表示法。
dbid smallint 当前正由进程使用的数据库 ID。
uid smallint 执行命令的用户 ID。
cpu int 进程的累计 CPU 时间。无论 SET STATISTICS TIME ON 选项是 ON 还是 OFF，都为所有进程更新该条目。
physical_io int 进程的累计磁盘读取和写入。
memusage int 当前分配给该进程的过程高速缓存中的页数。一个负数，表示进程正在释放由另一个进程分配的内存。
login_time datetime 客户端进程登录到服务器的时间。对于系统进程，是存储 SQL Server 启动发生的时间。
last_batch datetime 客户端进程上次执行远程存储过程调用或 EXECUTE 语句的时间。对于系统进程，是存储 SQL Server 启动发生的时间。
ecid smallint 用于唯一标识代表单个进程进行操作的子线程的执行上下文 ID。
open_tran smallint 进程的打开事务数。
status nchar(30) 进程 ID 状态（如运行、休眠等）。
sid binary(85) 用户的全局唯一标识符 (GUID)。
hostname nchar(12 工作站的名称。
program_name nchar(12 应用程序的名称。
hostprocess nchar( 工作站进程 ID 号。
cmd nchar(16) 当前正在执行的命令。
nt_domain nchar(12 客户端的 Windows NT 4.0 域（如果使用 Windows 身份验证）或信任连接的 Windows NT 4.0 域。
nt_username nchar(12 进程的 Windows NT 4.0用户名（如果使用 Windows 身份验证）或信任连接的 Windows NT 4.0 用户名。
net_address nchar(12) 指派给每个用户工作站上的网络接口卡唯一标识符。当用户登录时，该标识符插入 net_address 列。
net_library nchar(12) 用于存储客户端网络库的列。每个客户端进程都在网络连接上进入。网络连接有一个与这些进程关联的网络库，该网络库使得这些进程可以建立连接。有关更多信息，请参见客户端和服务器 Net-Library。
loginame nchar(12 登录名。

上面的sql 似乎可以如下
  SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
  er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
  er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
,sp.waittime
  FROM sys.dm_exec_requests er
  INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
  WHERE session_Id > 50 -- Ignore system spids.
  AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
  ORDER BY 1, 2

mysql 的锁类型 这里是, [Wait] = wait_type
总体能看到锁了. sqlserver 按道理却是很强大 全面

这个 比 sp_who 要 好 ， 不仅仅看到所有的 sql 。而且 看到具体

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务： 回滚，而回滚会取消事务执行的所有工作。 由于死锁时回滚而由应用程序重新提交。 下列方法有助于最大限度地降低死锁： 按同一顺序访问对象。 避免事务中的用户交互。 保持事务简短并在一个批处理中。 使用低隔离级别。 使用绑定连接。 按同一顺序访问对象 如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务获得 Supplier 表上的锁，然后获得 Part 表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后，第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。 避免事务中的用户交互 避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如答复应用程序请求参数的提示。例如，如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。 保持事务简短并在一个批处理中 在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。 保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。 使用低隔离级别 确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺。 使用绑定连接 使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。次级连接所获得的任何锁可以象由主连接获得的锁那样持有，反之亦然，因此不会相互阻塞 检测死锁 如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？ 这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。 use master go create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int  create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 RETURN @@ERROR  insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0  IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数  select @intCountProperties = Count(*),@intCounter = 1  from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0  select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录  select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin  if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  else  select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'  DBCC INPUTBUFFER (@bl ) end -- 循环指针下移  set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end  杀死锁和进程 如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。 use master go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_killspid] GO create proc p_killspid @dbname varchar(200) --要关闭进程的数据库名 as declare @sql nvarchar(500) declare @spid nvarchar(20) declare #tb cursor for  select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #tb fetch next from #tb into @spid  while @@fetch_status=0  begin exec('kill '+@spid) fetch next from #tb into @spid  end close #tb deallocate #tb go --用法 exec p_killspid 'newdbpy'  查看锁信息 如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。 --查看锁信息 create table #t(req_spid int,obj_name sysname) declare @s nvarchar(4000) ,@rid int,@dbname sysname,@id int,@objname sysname declare tb cursor for select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid from master..syslockinfo where rsc_type in(4,5) open tb fetch next from tb into @rid,@dbname,@id while @@fetch_status=0 begin  set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'  exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id  insert into #t values(@rid,@objname) fetch next from tb into @rid,@dbname,@id end close tb deallocate tb select 进程id=a.req_spid ,数据库=db_name(rsc_dbid) ,类型=case rsc_type when 1 then 'NULL 资源（未使用）'  when 2 then '数据库'  when 3 then '文件'  when 4 then '索引'  when 5 then '表'  when 6 then '页'  when 7 then '键'  when 8 then '扩展盘区'  when 9 then 'RID（行 ID)'  when 10 then '应用程序'  end  ,对象id=rsc_objid ,对象名=b.obj_name ,rsc_indid from master..syslockinfo a left join #t b on a.req_spid=b.req_spid go drop table #t 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/12/24/5071970.aspx

Answer 2

ALTER TABLE [table_name] DROP COLUMN [column_name]
ALTER TABLE [table_name] ALTER COLUMN [column_name] IDENTITY (1,1)

--使用辅助字段col2保存id值，然后删除id,把col2改名为id create table tb(id int primary key, col varchar(1)) go insert tb select 1,'a' union all select 2,'b' select * into # from tb truncate table tb alter table tb add col2 int identity(1,1) go set identity_insert tb on insert tb(id,col,col2) select id,col,id from # set identity_insert tb off go --exec sp_helpconstraint tb --查看约束 alter table tb drop constraint PK__tb__53584DE9 go alter table tb drop column id go alter table tb add constraint PK__tb_id primary key(col2) go exec sp_rename 'tb.col2','id','COLUMN' go insert tb(col) select 'c' select * from tb drop table tb,#

declare @s nvarchar(max) select @s=isnull(@s+'','')+' alter table '+name+' add ID int identity primary key' from sysobjects where type='U' exec(@s)

EXEC SP_MSFOREACHTABLE N' ALTER TABLE ? ADD ID INT PRIMARY KEY IDENTITY(1,1) '

se master go if exists(select * from sysdatabases where name='mytest') drop database mytest go create database mytest go use mytest go create table user1 ( mid int , name varchar(25) ) go create table user2 ( mid int, name varchar(25) ) go select * from user1 select * from user2 --drop table user1 --drop table user2 --*****start*****-- DECLARE @table_name sysname --保存表名的全局变量 -- 获得所有User表 DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0 --(加入这个条件是因为前面的条件查询出来的结果带有"dtproperties"非用户所建的表,应该还有其他滤去该表的条件)  OPEN tables_cursor -- Perform the first fetch  FETCH NEXT FROM tables_cursor INTO @table_name  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  WHILE @@FETCH_STATUS = 0  BEGIN  -- 遍历表@table_name的每条记录  -- 动态生成执行语句  exec ( 'alter table '+ @table_name+' drop column mid ' ) exec ('alter table '+ @table_name +' add mid int primary key IDENTITY(1,1) ') FETCH NEXT FROM tables_cursor INTO @table_name  END  CLOSE tables_cursor --CLOSE语句关闭已打开的游标，之后不能对游标进行读取等操作，但可以使用OPEN语句再次打开该游标  DEALLOCATE tables_cursor --DEALLOCATE语句删除定义游标的数据结构，删除后不可再用

--没主键的写入表backup_table SELECT a.name,b.name into backup_table FROM sysobjects as a,syscolumns as b where b.status=128 and a.id=b.id and a.name in ( select name from sysobjects s where xtype = 'U' AND OBJECTPROPERTY(s.id , 'TableHasIdentity' ) = 1 AND OBJECTPROPERTY(s.id , 'TableHasPrimaryKey' ) = 0 ) --添加主键 declare @name varchar(50),@cloumm varchar(50),@sql varchar(200) while (select count(*) from backup_table)>0 begin  select top 1 @name=name,@cloumm=cloumn from backup_table exec('alter table ' +@name +' add CONSTRAINT ' +'PK_'+@name+ ' PRIMARY KEY CLUSTERED('+@cloumm+')') delete from backup_table where name=@name end

SQL codeuse mastergoifexists(select*from sysdatabaseswhere name='mytest')dropdatabase mytestgocreatedatabase mytestgouse mytestgocreatetable user1
(
midint ,
namevarchar(25)
)gocreatetable user2
(
¡­

如果自增ID和和表之间的字段没有关系，可以考虑这个use kisscard go --*****start*****-- DECLARE @table_name sysname --保存表名的全局变量 -- 获得所有User表 DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0 --(加入这个条件是因为前面的条件查询出来的结果带有"dtproperties"非用户所建的表,应该还有其他滤去该表的条件)  OPEN tables_cursor -- Perform the first fetch  FETCH NEXT FROM tables_cursor INTO @table_name  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  WHILE @@FETCH_STATUS = 0  BEGIN  -- 遍历表@table_name的每条记录  -- 动态生成执行语句  exec ('alter table '+ @table_name +' change id int primary key IDENTITY(1,1) ') FETCH NEXT FROM tables_cursor INTO @table_name  END  CLOSE tables_cursor --CLOSE语句关闭已打开的游标，之后不能对游标进行读取等操作，但可以使用OPEN语句再次打开该游标  DEALLOCATE tables_cursor --DEALLOCATE语句删除定义游标的数据结构，删除后不可再用

Answer 3

SELECT TOP 5 total_worker_time, last_worker_time,
max_worker_time, min_worker_time,
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) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC