SQL server 常用命令

1. 查看数据库版本号

        SELECT  SERVERPROPERTY('productversion') AS ProductVersion ,
        SERVERPROPERTY('productlevel') AS ProductLevel ,
        SERVERPROPERTY('edition') AS Edition;
        SELECT @@VERSION AS PRODUCT_VERSION;

•      查询当前数据库的磁盘使用情况

     EXEC sp_spaceused;
     -- 查看某个数据对象的大小
     EXEC sp_spaceused @objname;

•       查看服务器启动时间

     SELECT CONVERT(VARCHAR(30), LOGIN_TIME,120) AS StartDateTime
     FROM master..sysprocesses WHERE spid=1;

•     查看所有数据库名称与大小

    EXEC sp_helpdb

•     查看数据库服务器各数据库日志文件的大小及利用率/状态

     DBCC SQLPERF(LOGSPACE);
    或
     EXEC ('DBCC SQLPERF(LOGSPACE)');

•     查看数据库服务器磁盘分区剩余空间

    EXEC master.dbo.xp_fixeddrives

•     查看数据库服务器CPU/内存的信息

     SELECT  cpu_count                     AS [Logical CPU Count] ,
      hyperthread_ratio               AS [Hyperthread Ratio] ,
      cpu_count / hyperthread_ratio   AS [Physical CPU Count],
      physical_memory_kb / 1024       AS [Physical Memory (MB)] ,
      sqlserver_start_time
      FROM sys.dm_os_sys_info
      OPTION  ( RECOMPILE ) ;

 

• 赋予某用户关于某表的权限

grant select on EC_CLAIM_TB to dladmin    查

grant insert on EC_CLAIM_TB to dladmin    增

grant delete on EC_CLAIM_TB to dladmin    删

grant update on EC_CLAIM_TB to dladmin   改

grant select,update,delete,insert on UserTagPool to [app_webservice]
 

查看所有数据库用户登录信息 、所属的角色信息

EXEC sp_helplogins;

EXEC sp_helpsrvrolemember;

-- 查看客户端的用户和进程信息     EXEC sp_who;

-- 查看客户端活动用户和进程     EXEC sp_who 'active

• 查看数据库链接

select *
from master.dbo.sysprocesses
where db_name(dbid) in ('DBNAME')

kill 数据库连接

select 'kill '+ convert(varchar(10),spid)
from master.dbo.sysprocesses
where db_name(dbid) in ('NB-Workflow','NB-Middle')

select * from sys.dm_exec_sql_text (0x01001700EF369338B01A51760200000000000000)  --查看语句

• 查看死锁及被锁表：

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

spid   锁表进程  
tableName   被锁表名

解锁：

declare @spid  int 
Set @spid  = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

或者杀进程  kill spid

 

• 查询造成数据库死锁的语句

select er.session_id,CAST(csql.text AS varchar(255)) AS CallingSQL
from master.sys.dm_exec_requests er
WITH (NOLOCK)
CROSS APPLY MASTER.sys.fn_get_sql (er.sql_handle) csql
where er.session_id =68

查看-2锁 （系统锁）

SELECT DISTINCT req_transactionUOW from master.sys.syslockinfo where req_spid = -2 

杀进程
KILL 'req_transactionUOW'

SELECT DISTINCT 'kill ''' + convert(varchar(50),req_transactionUOW)+'''' from master.sys.syslockinfo where req_spid = -2 
 

查询sql执行时间

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
GO
SQL语句
GO 
SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF

 查询最消耗CPU的SQL Top10

1 2 3

select top(10) st.text as Query, qs.total_worker_time, qs.execution_count from sys.dm_exec_query_stats as qs CROSS Apply sys.dm_exec_sql_text(qs.sql_handle) AS st order by qs.total_worker_time desc

锁定表，其他事务可以读取表，但不能更新删除 

SELECT * FROM table WITH (HOLDLOCK) 

锁定表，其他事务不能读取表,更新和删除 

SELECT * FROM table WITH (TABLOCKX) 

注意：HOLDLOCK和TABLOCKX的区别。

###########################################################################################

SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果。 本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。 

功能说明：　 

NOLOCK（不加锁） 

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。 

HOLDLOCK（保持锁） 

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。 

UPDLOCK（修改锁） 

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。 

TABLOCK（表锁） 

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。 

PAGLOCK（页锁） 

此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。 

TABLOCKX（排它表锁） 

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。 

HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别 

　　NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别 

　　PAGLOCK 在使用一个表锁的地方用多个页锁 

　　READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁 

　　ROWLOCK 强制使用行锁 

　　TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表 

　　UPLOCK 强制在读表时使用更新而不用共享锁