图片

一、基础大叫稽查面前数据库的版块

SELECT &VERSION;

稽查办事器部分特殊信息

select SERVERPROPERTY(N'edition') as Edition     --数据版块，如企业版、开拓版等
    ,SERVERPROPERTY(N'collation') as Collation   --数据库字符集
    ,SERVERPROPERTY(N'servername') as ServerName --办事名
    ,&VERSION as Version   --数据库版块号
    ,&LANGUAGE AS Language  --数据库使用的话语，如us_english等

赢得数据库面前时辰

SELECT GETDATE() AS CurrentDateTime;

稽查数据库启动的参数

sp_configure

稽查所畸形据库用户登录信息

sp_helplogins

稽查数据库启动时辰（最近一次）

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

稽查有些许个端口

SELECT * FROM sys.dm_tcp_listener_states;

稽查面前的长入数

SELECT COUNT(*) AS [Connection Count] FROM sys.dm_exec_connections;

稽查各个磁盘分区的剩余空间

Exec master.dbo.xp_fixeddrives

稽查数据库的磁盘使用情况

Exec sp_spaceused

稽查数据库办事器各数据库日记文献的大小及哄骗率

DBCC SQLPERF(LOGSPACE)

稽查面前占用 cpu 资源最高的会话和其中实行的语句

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc

稽查缓存中重用次数少，占用内存大的查询语句（面前缓存中未开释的）

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]

FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

ORDER BY usecounts,p.size_in_bytes desc

看BUFFER POOL中，皆缓存了哪些表(面前数据库)的数据

select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb                              
from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
       and b.container_id=c.hobt_id             
       and database_id=DB_ID()                              
app
group by OBJECT_NAME(object_id)                           
order by 2 desc

稽查用户的权限

EXEC sp_helprotect;

稽查面前数据库内存使用情况

select * from sys.dm_os_process_memory

查询面前数据库缓存的所畸形据页面，哪些数据表，缓存的数据页面数目

-- 查询面前数据库缓存的所畸形据页面，哪些数据表，缓存的数据页面数目

-- 从这些信息不错看出，系统凡俗要侦探的皆是哪些表，有多大？

select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)

from sys.allocation_units a,

上期龙头开出奇数球05，开发物联网软件需要多少钱呢近10期龙头开出07 04 06 04 05 02 08 01 01 05，奇偶比5:5，本期参考奇数球05。

sys.dm_os_buffer_descriptors b,

sys.partitions p

where a.allocation_unit_id=b.allocation_unit_id

and a.container_id=p.hobt_id

and b.database_id=db_id()

group by p.object_id,p.index_id

order by buffer_pages desc

查询缓存中具体的实行运筹帷幄，及对应的SQL

-- 查询缓存中具体的实行运筹帷幄，及对应的SQL
-- 将此成果按照数据表或SQL进行统计，不错行动基线，转机索引时探求
-- 查询成果会很大，沉静将成果集输出到表或文献中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO

稽查具体某个用户的权限

SELECT p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc, u.name AS user_nameFROM sys.database_permissions pJOIN sys.database_principals u ON p.grantee_principal_id = u.principal_idWHERE u.name = 'test’

稽查注册时的实例名

SELECT * FROM sys.servers;

查询用户扮装

select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid  
from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
order by 1, 2  
go

守护事器扮装

select 用户名 = u.name,处分员权限 = g.name,是否在用 = u.is_disabled,MemberSID = u.sid

from sys.server_principals u, sys.server_principals g, sys.server_role_members m

where g.principal_id = m.role_principal_id

and u.principal_id = m.member_principal_id

and g.name = 'sysadmin'

order by 1, 2

go

查询面前用户所灵验户表

select name from sysobjects where xtype='u' order by name

稽查扫数的数据库

Select Name FROM Master..SysDatabases orDER BY Name

稽查办事器扮装关连信息

SP_HELPSRVROLE 
SP_HELPSRVROLEMEMBER 办事器扮装 
SP_HELPSRVROLE 办事器扮装

稽查数据库扮装关连信息

SP_HELPROLE

SP_HELPROLEMEMBER 数据库扮装

SP_HELPROLE 数据库扮装

稽查用户关连信息

SP_HELPUSER 
SP_HELPUSER 数据库用户名

稽查前次启动以来尝试的长入数

select &connections //复返 SQL Server 自前次启动以来尝试的长入数，无论长入是告捷还是失败

面前实例允许同期进行的最大用户长入数

select &max_connections 
//复返 SQL Server 实例允许同期进行的最大用户长入数。复返的数值不一定是面前配置的数值

查询面前最大的长入数

SELECT value_in_use

FROM sys.configurations c

WHERE c.name = 'user connections'; #0示意无死心

竖立修改长入数

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'user connections', 300
RECONFIGURE WITH OVERRIDE

查询面前会话超经常期

select &lock_timeout //复返面前会话确面前锁定超时竖立（毫秒）。

查询每个用户的长入数

select loginame,count(1) as Nums
from sys.sysprocesses
group by loginame
order by 2 desc
select spid,ecid,status,loginame,hostname,cmd,request_id 
from sys.sysprocesses where loginame='' and hostname=''

稽查面前行动的实例

SELECT CURRENT_USER AS [Current User], SESSION_USER AS [Session User];

稽查面前行动程度

SELECT * FROM sys.dm_exec_requests;

稽查所畸形据库的大小

SELECT

DB_NAME(database_id) AS DatabaseName,

SUM(size/128.0) AS SizeInMB,

SUM(size/128.0)/1024 AS SizeInGB

FROM

sys.master_files

GROUP BY

database_id

ORDER BY

SizeInMB DESC;

稽查某个数据库的大小

SELECT sys.databases.name AS [Database Name], 
    CAST(SUM(size * 8 / 1024.0) AS DECIMAL(10,2)) AS [Size (MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = 'master'
GROUP BY sys.databases.name;
#也不错用EXEC sp_spaceused @updateusage = N'TRUE';

稽查面前数据库的日记大小

SELECT sys.databases.name AS [Database Name],

CAST(size * 8 / 1024.0 AS DECIMAL(10,2)) AS [Log File Size (MB)]

FROM sys.master_files

INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id

WHERE sys.databases.name = 'master'

AND sys.master_files.type = 1;

查询面前数据库的表和视图

SELECT TABLE_NAME AS [Table/View Name], TABLE_TYPE AS [Type]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW');

查询表结构信息

sp_help 'test';

二、运维小妙技一次性拔除数据库扫数表的数据（高危操作，严慎）

CREATE PROCEDURE sp_DeleteAllData  
AS  
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSForEachTable 'DELETE FROM ?'  
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'  
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'  
GO

数据备份与规复备份

BACKUP DATABASE test TO DISK = 'C:\backup\MyDatabase.bak';

规复

RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';

完好数据库备份

BACKUP DATABASE test

TO DISK = 'C:\Backup\MyDatabase.bak'

WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';

相反备份

BACKUP DATABASE test
TO DISK = 'C:\Backup\MyDatabase_diff.bak'
WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';

事务日记备份

BACKUP LOG test

TO DISK = 'C:\Backup\MyDatabase_log.trn'

WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

还原数据库

RESTORE DATABASE test
FROM DISK = 'C:\Backup\MyDatabase.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;

创建账户及数据库用户

#创建账户

CREATE LOGIN test WITH PASSWORD = '123123';

#创建数据库用户并映射到登录名

CREATE USER test FOR LOGIN test;

ALTER ROLE db_datareader ADD MEMBER test; -- 赐与读权限

ALTER ROLE db_datawriter ADD MEMBER test; -- 赐与写权限

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 '




            

                
                
物联网软件开发公司
                
Sql
                
Server
                
泛泛
                
韵味
                

            
 
      

        
上一篇：物联网软件开发公司 作文熟练的秩序 
        
下一篇：没有了