20230325-SQLServer常用命令-优快云博客

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/148653855

在这里插入图片描述

1.查看数据库版本信息

select @@version

2.查看所有数据库名称及大小

exec sp_helpdb

3.数据库的磁盘空间使用信息

exec sp_spaceused

4.日志文件大小及使用情况

dbcc sqlperf(logspace)

5.表的磁盘空间使用信息

exec sp_spaceused 't1'

6.用户和进程信息

exec sp_who
exec sp_who2

7.活动用户和进程的信息

exec sp_who 'active'

8.查看进程中正在执行的SQL

exec sp_who2
dbcc inputbuffer(52)

9.日志收缩

---dbcc sqlperf(logspace)
select * from sys.databases
SELECT file_id,name from sys.master_files WHERE database_id=25; 
SELECT file_id, name FROM sys.database_files;
---
USE master
GO
ALTER DATABASE dbtest_0613 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE dbtest_0613 SET RECOVERY SIMPLE   
GO
USE dbtest_0613 
GO
DBCC SHRINKFILE (N'dbtest_0613_log' , 1, TRUNCATEONLY)
GO
USE master
GO
ALTER DATABASE dbtest_0613 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE dbtest_0613 SET RECOVERY FULL   
GO

10.temp问题

select name,log_reuse_wait_desc from sys.databases
dbcc loginfo
exec sp_spaceused

SELECT Name,
      physical_name,
      Size / 128.0 / 1024 AS [ Size(GB) ],
      FILEPROPERTY(Name, 'SpaceUsed') / 128.0 / 1024 AS [ SpaceUsed(GB) ],
      STR(FILEPROPERTY(Name, 'SpaceUsed') * 1.0 / Size * 100, 6, 3) AS [ SpaceUsed(%) ]
 FROM sys.database_files

查看tempdb的使用分配情况,及spid信息

use tempdb
go
SELECT top 10 t1.session_id,
      t1.internal_objects_alloc_page_count,
      t1.user_objects_alloc_page_count,
      t1.internal_objects_dealloc_page_count,
      t1.user_objects_dealloc_page_count,
      t3.login_name,
      t3.status,
      t3.total_elapsed_time
 from sys.dm_db_session_space_usage t1
inner join sys.dm_exec_sessions as t3
   on t1.session_id = t3.session_id
where (t1.internal_objects_alloc_page_count > 0 or
      t1.user_objects_alloc_page_count > 0 or
      t1.internal_objects_dealloc_page_count > 0 or
      t1.user_objects_dealloc_page_count > 0)
order by t1.internal_objects_alloc_page_count desc

—如果SQL还在执行，可以查到消耗tempdp较大的SQL

select p.*, s.text
 from master.dbo.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid = 53
---dbcc shrinkdatabase (tempdb, 'target percent')

11.查看数据库所在机器的操作系统参数

exec master..xp_msver

12.查看数据库启动的参数

exec sp_configure

13.查看数据库启动时间

select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1

14.查看数据库服务器名

select 'Server Name:'+ltrim(@@servername)

15.查看数据库实例名

select 'Instance:'+ltrim(@@servicename)

16.查看所有数据库用户登录信息

exec sp_helplogins

17.查看所有数据库用户所属的角色信息

exec sp_helpsrvrolemember

18.查看链接服务器

exec sp_helplinkedsrvlogin

19.查看远端数据库用户登录信息

exec sp_helpremotelogin

20.检查数据库中的所有对象的分配和机构完整性是否存在错误

dbcc checkdb

21.查询文件组和文件

select
   df.[name],df.physical_name,df.[size],df.growth,
   f.[name][filegroup],f.is_default
from sys.database_files df join sys.filegroups f
on df.data_space_id = f.data_space_id

26.查看数据库中所有表的条数

select  b.name as tablename ,
       a.rowcnt as datacount
from    sysindexes a ,
       sysobjects b
where   a.id = b.id
       and a.indid < 2
       and objectproperty(b.id, 'IsMSShipped') = 0

27.得到最耗时的前10条T-SQL语句

;with maco as
(
   select top 10
       plan_handle,
       sum(total_worker_time) as total_worker_time ,
       sum(execution_count) as execution_count ,
       count(1) as sql_count
   from sys.dm_exec_query_stats group by plan_handle
   order by sum(total_worker_time) desc
)
select  t.text ,
       a.total_worker_time ,
       a.execution_count ,
       a.sql_count
from    maco a
       cross apply sys.dm_exec_sql_text(plan_handle) t

28. 查看SQL Server的实际内存占用

select * from sys.sysperfinfo where counter_name like '%Memory%'

—查询TOP_SQL

SELECT top 10  
  (total_elapsed_time / execution_count)/1000 N'平均时间ms'  
  ,total_elapsed_time/1000 N'总花费时间ms'  
  ,total_worker_time/1000 N'所用的CPU总时间ms'  
  ,total_physical_reads N'物理读取总次数'  
  ,total_logical_reads/execution_count N'每次逻辑读次数'  
  ,total_logical_reads N'逻辑读取总次数'  
  ,total_logical_writes N'逻辑写入总次数'  
  ,execution_count N'执行次数'  
  ,creation_time N'语句编译时间'  
  ,last_execution_time N'上次执行时间'  
  ,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  
  ) N'执行语句'  
  ,qp.query_plan  
FROM  sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
WHERE  
  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  
  ) not like '%fetch%'  
ORDER BY  total_elapsed_time / execution_count DESC;

—执行效率低的SQL

SELECT creation_time N'语句编译时间',
     last_execution_time N'上次执行时间',
     total_physical_reads N'物理读取总次数',
     total_logical_reads / execution_count N'每次逻辑读次数',
     total_logical_reads N'逻辑读取总次数',
     total_logical_writes N'逻辑写入总次数',
     execution_count N'执行次数',
     total_worker_time / 1000 N'所用的CPU总时间ms',
     total_elapsed_time / 1000 N'总花费时间ms',
     (total_elapsed_time / execution_count) / 1000 N'平均时间ms',
     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) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where 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) not like
     '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

—查询正在执行的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
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

select r.session_id, r.status, r.start_time, r.command, s.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s
where r.status = 'running';

查看物理文件位置

select database_id,
      name,
      physical_name AS CurrentLocation,
      state_desc,
      size
 from sys.master_files
where database_id = db_id(N'necology20180625');

---sp_helpfile 
select * from sys.databases;
select * from sys.database_files;
select DATABASEPROPERTYEX('CJC20180625','status') ---ONLINE

select * from sys.databases
GO
select * from sys.database_files
GO 
select * from sys.filegroups 
GO 
select DATABASEPROPERTYEX('cjc','status')

use cjc
GO 
sp_spaceused 
GO 
sp_helpdb 
GO 
sp_helpdb cjc
GO

分离和附加

分离数据库

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/detach-a-database?view=sql-server-2017

—此示例将分离 cjc 数据库，同时将 skipchecks 设置为 true。

---select * from sys.database_files; 
EXEC sp_detach_db 'cjc', 'true';
https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/attach-a-database?view=sql-server-2017

附加数据库
—此示例附加 cjc数据库的文件并将该数据库重命名为 chen。

CREATE DATABASE chen
    ON (FILENAME = 'D:\oadata\cjc.mdf'),   
    (FILENAME = 'D:\oadata\cjc_0.ldf')   
    FOR ATTACH;

查看当前session_id

SELECT @@SPID

其他

select * from sys.dm_tran_session_transactions;
select * from sys.dm_exec_connections;
select * from sys.dm_exec_sql_text;
select * from sys.dm_tran_active_transactions;

查询表行数和大小

1 创建临时表#tabName
---if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u')

---drop table #tabName

---go

create table #tabName(
tabname varchar(100),
rowsNum varchar(100),
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused_size varchar(100)
)

2 表信息插入到临时表

declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
    insert into #tabName
    exec sp_spaceused @name
    --print @name
  
    fetch next from cur into @name
end
close cur
deallocate cur

3 查询临时表

select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tabName
--where tabName not like 't%'
order by cast(rowsNum as int) desc

事物提交和回滚

DML语句默认自动提交和Oracle不同

BEGIN TRAN  
update test0706 set a = cast('100' as text) where id=2;
COMMIT TRAN 
---ROLLBACK TRAN

重命名

---select * into t_0709 from sysindexes;
---select * from t_0709;
exec sp_rename 't_0709','test_0709';
---select * from test_0709;

外键约束

select oSub.name    AS 子表名称,
       fk.name      AS 外键名称,
       SubCol.name  AS 子表列名,
       oMain.name   AS 主表名称,
       MainCol.name AS 主表列名
  from sys.foreign_keys fk
  JOIN sys.all_objects oSub
    ON (fk.parent_object_id = oSub.object_id)
  JOIN sys.all_objects oMain
    ON (fk.referenced_object_id = oMain.object_id)
  JOIN sys.foreign_key_columns fkCols
    ON (fk.object_id = fkCols.constraint_object_id)
  JOIN sys.columns SubCol
    ON (oSub.object_id = SubCol.object_id AND
       fkCols.parent_column_id = SubCol.column_id)
  JOIN sys.columns MainCol
    ON (oMain.object_id = MainCol.object_id AND
       fkCols.referenced_column_id = MainCol.column_id);

SA修改密码

如果忘记了sa的登录密码，可以先用windows身份认证登录进去，然后新建查询，输入命令：

EXECUTE sp_password NULL,'输入新密码','sa'

查询

select * from 数据库名.dbo.表名

###chenjuchao 20180724###
欢迎关注我的公众号《IT小Chen》