暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

sqlserver 数据库常用脚本及静默安装

逆风飞翔 2024-01-25
410

sqlserver 数据库常用脚本

-- 修改数据库恢复模式为【简单模式】

USE [master]

GO

ALTER DATABASE [目标数据库名称] SET RECOVERY SIMPLE WITH NO_WAIT

GO

 

USE [目标数据库名称]

GO

DBCC SHRINKFILE (N'数据库日志逻辑名称', 200)  --10指压缩后的大小

GO

 

-- 修改数据库恢复模式为【完整模式】

USE [master]

GO

ALTER DATABASE [目标数据库名称] SET RECOVERY FULL WITH NO_WAIT

GO

 

把erp数据库的日志文件(逻辑名称叫Xgoss_log),收缩成50MB

 

USE [master]

ALTER DATABASE [erp] SET RECOVERY SIMPLE WITH NO_WAIT

 

USE [erp]

DBCC SHRINKFILE (N'Xgoss_log', 50)

 

USE [master]

ALTER DATABASE [erp] SET RECOVERY FULL WITH NO_WAIT

 

总空间和可用空间:

 

SELECT [File Name] = name,

[File Location] = physical_name,

[Total Size (MB)] = size/128.0,

[Available Free Space (MB)] = size/128.0

- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0,

[Type] = type_desc

FROM sys.database_files;

 

 

 

--查看数据库版本信息

select @@version

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

exec sp_helpdb

 

--所有数据库状态

select name,

user_access_desc,--用户访问模式

state_desc,--数据库状态

recovery_model_desc,--恢复模式

page_verify_option_desc,--页检测选项

log_reuse_wait_desc--日志重用等待

from sys.databases;

 

--某个数据库的大小

exec sp_spaceused

--刷新某个数据库统计信息

dbcc updateusage('CF_TBMPRO')

 

--某个数据库中的所有文件及大小

exec sp_helpfile

--查看所有文件所在数据库、路径、状态、大小

select db_name(database_id) dbname,

type_desc,

name,

physical_name,

state_desc,

size * 8.0/1024 as '文件大小(MB)'

from sys.master_files;

 

--某个数据库大小统计,TotalExtents*64/1024(MB),不统计日志文件

dbcc showfilestats

 

--统计某个数据库所有的表信息

 

--查询数据库设置的 Recovery Model

SELECT db.[name] AS [Database Name]

    ,db.recovery_model_desc AS [Recovery Model]

    ,db.state_desc

    ,db.log_reuse_wait_desc AS [Log Reuse Wait Description]

    ,CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)]

    ,CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)]

    ,CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %]

    ,db.[compatibility_level] AS [DB Compatibility Level]

    ,db.page_verify_option_desc AS [Page Verify Option]

    ,db.is_auto_create_stats_on

    ,db.is_auto_update_stats_on

    ,db.is_auto_update_stats_async_on

    ,db.is_parameterization_forced

    ,db.snapshot_isolation_state_desc

    ,db.is_read_committed_snapshot_on

    ,db.is_auto_close_on

    ,db.is_auto_shrink_on

    ,db.target_recovery_time_in_seconds

    ,db.is_cdc_enabled

FROM sys.databases AS db WITH (NOLOCK)

INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.NAME = lu.instance_name

INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.NAME = ls.instance_name

WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'

    AND ls.counter_name LIKE N'Log File(s) Size (KB)%'

    AND ls.cntr_value > 0

OPTION (RECOMPILE);

 

--查看最近的 Full Backup 信息

--use #DB_name

SELECT TOP (30) bs.machine_name

    ,bs.server_name

    ,bs.database_name AS [Database Name]

    ,bs.recovery_model

    ,CONVERT(BIGINT, bs.backup_size / 1048576) AS [Uncompressed Backup Size (MB)]

    ,CONVERT(BIGINT, bs.compressed_backup_size / 1048576) AS [Compressed Backup Size (MB)]

    ,CONVERT(NUMERIC(20, 2), (CONVERT(FLOAT, bs.backup_size) / CONVERT(FLOAT, bs.compressed_backup_size))) AS [Compression Ratio]

    ,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)]

    ,bs.backup_finish_date AS [Backup Finish Date]

FROM msdb.dbo.backupset AS bs WITH (NOLOCK)

WHERE DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0

    AND bs.backup_size > 0

    AND bs.type = 'D' -- Change to L if you want Log backups

    AND database_name = DB_NAME(DB_ID())

ORDER BY bs.backup_finish_date DESC

OPTION (RECOMPILE);

 

--获取所有数据库的 VLF 数量

/*VLF :Virtual Log File

SQL Server 将日志文件 LDF 划分为多个 VLF 以提高日志处理效率。VLF 的数量和大小不能通过配置指定,SQL Server 会按情况自行判断处理,而如果生成了过多的 VLF 则会产生性能问题。通过指定合理的日志文件初始大小和增长步长,可以有效的防止过多 VLF 的产生。

1M-64M   4

64M-1GB    8

>1GB       16

较高的 VLF 数量会影响写入性能,并且会使数据库的恢复过程变慢,通常需要保持 VLF Counts 在 200 以下。*/

CREATE TABLE #VLFInfo (

    RecoveryUnitID INT

    ,FileID INT

    ,FileSize BIGINT

    ,StartOffset BIGINT

    ,FSeqNo BIGINT

    ,[Status] BIGINT

    ,Parity BIGINT

    ,CreateLSN NUMERIC(38)

    );

CREATE TABLE #VLFCountResults (

    DatabaseName SYSNAME

    ,VLFCount INT

    );

EXEC sp_MSforeachdb N'Use [?];

                INSERT INTO #VLFInfo

                EXEC sp_executesql N''DBCC LOGINFO([?])'';      

                INSERT INTO #VLFCountResults

                SELECT DB_NAME(), COUNT(*)

                FROM #VLFInfo;

                TRUNCATE TABLE #VLFInfo;'

SELECT DatabaseName

    ,VLFCount

FROM #VLFCountResults

ORDER BY VLFCount DESC;

DROP TABLE #VLFInfo;

DROP TABLE #VLFCountResults;

 

 

 

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

exec master..xp_msver

--查看数据库启动的参数

exec sp_configure

--查看数据库启动时间

select convert(varchar(30),login_time,120)

from master..sysprocesses where spid=1

 

--查看数据库服务器名

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

 

--查看Windows 操作系统是什么版本

SELECT windows_release

    ,windows_service_pack_level

    ,windows_sku

    ,os_language_version

FROM sys.dm_os_windows_info WITH (NOLOCK)

OPTION (RECOMPILE);

--其中 windows_release 中的版本号代表着:

-- 6.3 Windows 8.1 or Windows Server 2012 R2   

-- 6.2 Windows 8 or Windows Server 2012

-- 6.1 Windows 7 or Windows Server 2008 R2

-- 6.0 Windows Vista or Windows Server 2008

-- 5.2 Windows XP or Windows Server 2003

--其中 windows_sku 代表着:

-- 4 Enterprise Edition  

-- 7 Standard Edition

-- 48 Professional Edition

 

--查看数据库实例名

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

--数据库的磁盘空间及使用信息

exec sp_spaceused

--日志文件大小及使用情况

dbcc sqlperf(logspace)

--表的磁盘空间使用信息

exec sp_spaceused 'tablename'

--获取磁盘读写情况

select

@@total_read [读取磁盘次数],

@@total_write [写入磁盘次数],

@@total_errors [磁盘写入错误数],

getdate() [当前时间]

 

--数据文件和日志文件位置和大小

SELECT DB_NAME([database_id]) AS [Database Name]

    ,[file_id]

    ,[name]

    ,physical_name

    ,type_desc

    ,state_desc

    ,is_percent_growth

    ,growth

    ,CONVERT(BIGINT, growth / 128.0) AS [Growth in MB]

    ,CONVERT(BIGINT, size / 128.0) AS [Total Size in MB]

FROM sys.master_files WITH (NOLOCK)

WHERE [database_id] > 4

    AND [database_id] <> 32767

    OR [database_id] = 2

ORDER BY DB_NAME([database_id])

OPTION (RECOMPILE);

 

--查看指定数据库文件的大小和可用空间

SELECT f.[name] AS [File Name]

    ,f.physical_name AS [Physical Name]

    ,CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [Total Size in MB]

    ,CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.[name], 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(15, 2)) AS [Available Space In MB]

    ,[file_id]

    ,fg.[name] AS [Filegroup Name]

FROM sys.database_files AS f WITH (NOLOCK)

LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id

OPTION (RECOMPILE);

 

SELECT db_name(vs.database_id) AS DatabaseName

    ,vs.file_id

    ,vs.volume_mount_point

    ,vs.volume_id

    ,vs.logical_volume_name

    ,vs.file_system_type

    ,(vs.total_bytes / 1024 / 1024 / 1024) AS [TotalSize(GB)]

    ,(vs.available_bytes / 1024 / 1024 / 1024) AS [AvailableSize(GB)]

    ,vs.supports_compression

    ,vs.supports_alternate_streams

    ,vs.supports_sparse_files

    ,vs.is_read_only

    ,vs.is_compressed

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs;

 

--服务器 Disk 容量和挂载信息

SELECT DISTINCT vs.volume_mount_point

    ,vs.file_system_type

    ,vs.logical_volume_name

    ,CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)]

    ,CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)]

    ,CAST(CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Space Free %]

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

OPTION (RECOMPILE);

 

--查看 Disk 剩余空间

EXEC master.dbo.xp_fixeddrives

 

SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS Volume_mount_point

    ,total_bytes / 1024 / 1024 AS Total_MB

    ,available_bytes / 1024 / 1024 AS Available_MB

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

 

 

 

 

--获取I/O工作情况

select @@io_busy,

@@timeticks [每个时钟周期对应的微秒数],

@@io_busy*@@timeticks [I/O操作毫秒数],

getdate() [当前时间]

--查看CPU活动及工作情况

select

@@cpu_busy,

--@@timeticks [每个时钟周期对应的微秒数],

@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],

@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],

getdate() [当前时间]

--检查锁与等待

exec sp_lock

--检查死锁

exec sp_who_lock --自己写个存储过程即可

/*

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 sys.sysprocesses where blocked>0 ) a

    where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b

    where a.blocked=spid)

    union select spid,blocked from sys.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

*/

 

--用户和进程信息

exec sp_who

exec sp_who2

 

--活动用户和进程的信息

exec sp_who 'active'

 

--查看进程中正在执行的SQL

dbcc inputbuffer(进程号)

exec sp_who3

/*

CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )

AS

    BEGIN

 

 

        SELECT  SPID = er.session_id ,

                Status = ses.status ,

                [Login] = ses.login_name ,

                Host = ses.host_name ,

                BlkBy = er.blocking_session_id ,

                DBName = DB_NAME(er.database_id) ,

                CommandType = er.command ,

                SQLStatement = st.text ,

                ObjectName = OBJECT_NAME(st.objectid) ,

                ElapsedMS = er.total_elapsed_time ,

                CPUTime = er.cpu_time ,

                IOReads = er.logical_reads + er.reads ,

                IOWrites = er.writes ,

                LastWaitType = er.last_wait_type ,

                StartTime = er.start_time ,

                Protocol = con.net_transport ,

                ConnectionWrites = con.num_writes ,

                ConnectionReads = con.num_reads ,

                ClientAddress = con.client_net_address ,

                Authentication = con.auth_scheme

        FROM    sys.dm_exec_requests er

                OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

                LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id

                LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id

        WHERE   er.session_id > 50

                AND @SessionID IS NULL

                OR er.session_id = @SessionID

        ORDER BY er.blocking_session_id DESC ,

                er.session_id

 

 

    END

*/

 

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

exec sp_helplogins

 

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

exec sp_helpsrvrolemember

 

--查看链接服务器

exec sp_helplinkedsrvlogin

 

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

exec sp_helpremotelogin

 

--获取网络数据包统计信息

select

@@pack_received [输入数据包数量],

@@pack_sent [输出数据包数量],

@@packet_errors [错误包数量],

getdate() [当前时间]

 

--查看逻辑CPU情况,任务调度器(Scheduler)

SELECT is_online

    ,[status]

    ,COUNT(*) AS [count]

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

GROUP BY is_online

    ,[status];

 

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

dbcc checkdb

 

--查询文件组和文件

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

 

 

--得到最耗时的前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

 

--查看SQL Server的实际内存占用

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

 

 

--显示所有数据库的日志空间信息

dbcc sqlperf(logspace)

--查看日志文件所在数据库、路径、状态、大小

select db_name(database_id) dbname,

type_desc,--文件类型

name,

physical_name,--文件位置

state_desc,--文件状态

size * 8.0/1024 as '文件大小(MB)'

from sys.master_files

where type_desc = 'LOG';

 

--收缩数据库

dbcc shrinkdatabase(CF_HIDB)

 

 

 

--查看某个表的结构

use CF_TBMPRO

exec sp_help 'dbo.PRO_TBM_UIDataPlaceholder'

--查看视图的定义

SELECT object_definition (object_id('sys.tables'));

EXEC sp_helptext 'sys.tables';

 

--查看数据库中所有表的条数

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

order by datacount desc;

 

select  sum(a.rowcnt) as '总条数'

from    sysindexes a ,

        sysobjects b

where   a.id = b.id

        and a.indid < 2

        and objectproperty(b.id, 'IsMSShipped') = 0;

 

 

--清除 sql server错误日志文件

exec sp_cycle_errorlog

 

--SQL Server 的错误日志位置

SELECT is_enabled

    ,[path]

    ,max_size

    ,max_files

FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK)

OPTION (RECOMPILE);

 

--查询近期的 Error Log 信息

DECLARE @Time_Start DATETIME;

DECLARE @Time_End DATETIME;

SET @Time_Start = getdate() - 2;

SET @Time_End = getdate();

-- Create the temporary table

CREATE TABLE #ErrorLog (

    logdate DATETIME

    ,processinfo VARCHAR(255)

    ,Message VARCHAR(500)

    )

-- Populate the temporary table

INSERT #ErrorLog (

    logdate

    ,processinfo

    ,Message

    )

EXEC master.dbo.xp_readerrorlog 0

    ,1

    ,NULL

    ,NULL

    ,@Time_Start

    ,@Time_End

    ,N'desc';

-- Filter the temporary table

SELECT LogDate

    ,Message

FROM #ErrorLog

WHERE (

        Message LIKE '%error%'

        OR Message LIKE '%failed%'

        )

    AND processinfo NOT LIKE 'logon'

ORDER BY logdate DESC

-- Drop the temporary table

DROP TABLE #ErrorLog

 

--在错误日志中查询 I/O 超过 15s 的请求

--如果能够查询出结果,可以说明 I/O 性能存在问题,但是哪里引起的还需进一步探索。

CREATE TABLE #IOWarningResults (

    LogDate DATETIME

    ,ProcessInfo SYSNAME

    ,LogText NVARCHAR(1000)

    );

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 0

    ,1

    ,N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 1

    ,1

    ,N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 2

    ,1

    ,N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 3

    ,1

    ,N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 4

    ,1

    ,N'taking longer than 15 seconds';

SELECT LogDate

    ,ProcessInfo

    ,LogText

FROM #IOWarningResults

ORDER BY LogDate DESC;

DROP TABLE #IOWarningResults;

 

 

--查询 Disk 的性能指标

--通常 Latency 的值大于 20-25 ms 时可考虑有性能问题

SELECT [Drive]

    ,CASE

        WHEN num_of_reads = 0

            THEN 0

        ELSE (io_stall_read_ms / num_of_reads)

        END AS [Read Latency (ms)]

    ,CASE

        WHEN io_stall_write_ms = 0

            THEN 0

        ELSE (io_stall_write_ms / num_of_writes)

        END AS [Write Latency (ms)]

    ,CASE

        WHEN (

                num_of_reads = 0

                AND num_of_writes = 0

                )

            THEN 0

        ELSE (io_stall / (num_of_reads + num_of_writes))

        END AS [Overall Latency (ms)]

    ,CASE

        WHEN num_of_reads = 0

            THEN 0

        ELSE (num_of_bytes_read / num_of_reads)

        END AS [Avg Bytes/Read]

    ,CASE

        WHEN io_stall_write_ms = 0

            THEN 0

        ELSE (num_of_bytes_written / num_of_writes)

        END AS [Avg Bytes/Write]

    ,CASE

        WHEN (

                num_of_reads = 0

                AND num_of_writes = 0

                )

            THEN 0

        ELSE ((num_of_bytes_read + num_of_bytes_written) / (num_of_reads + num_of_writes))

        END AS [Avg Bytes/Transfer]

FROM (

    SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive

        ,SUM(num_of_reads) AS num_of_reads

        ,SUM(io_stall_read_ms) AS io_stall_read_ms

        ,SUM(num_of_writes) AS num_of_writes

        ,SUM(io_stall_write_ms) AS io_stall_write_ms

        ,SUM(num_of_bytes_read) AS num_of_bytes_read

        ,SUM(num_of_bytes_written) AS num_of_bytes_written

        ,SUM(io_stall) AS io_stall

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

    INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON vfs.database_id = mf.database_id

        AND vfs.file_id = mf.file_id

    GROUP BY LEFT(UPPER(mf.physical_name), 2)

    ) AS tab

ORDER BY [Overall Latency (ms)]

OPTION (RECOMPILE);

 

 

--查看哪个数据库文件 I/O 瓶颈最严重

--考虑将数据库文件移动到不同的磁盘上,或更快的磁盘阵列上以改进性能

SELECT DB_NAME(fs.database_id) AS [Database Name]

    ,CAST(fs.io_stall_read_ms / (1.0 + fs.num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]

    ,CAST(fs.io_stall_write_ms / (1.0 + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]

    ,CAST((fs.io_stall_read_ms + fs.io_stall_write_ms) / (1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]

    ,CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)]

    ,mf.physical_name

    ,mf.type_desc

    ,fs.io_stall_read_ms

    ,fs.num_of_reads

    ,fs.io_stall_write_ms

    ,fs.num_of_writes

    ,fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]

    ,fs.num_of_reads + fs.num_of_writes AS [total_io]

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs

INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id

    AND fs.[file_id] = mf.[file_id]

ORDER BY avg_io_stall_ms DESC

OPTION (RECOMPILE);

 

--按照 Write I/O 进行排名

SELECT [ReadLatency] = CASE

        WHEN [num_of_reads] = 0

            THEN 0

        ELSE ([io_stall_read_ms] / [num_of_reads])

        END

    ,[WriteLatency] = CASE

        WHEN [num_of_writes] = 0

            THEN 0

        ELSE ([io_stall_write_ms] / [num_of_writes])

        END

    ,[Latency] = CASE

        WHEN (

                [num_of_reads] = 0

                AND [num_of_writes] = 0

                )

            THEN 0

        ELSE ([io_stall] / ([num_of_reads] + [num_of_writes]))

        END

    ,[AvgBytesPerRead] = CASE

        WHEN [num_of_reads] = 0

            THEN 0

        ELSE ([num_of_bytes_read] / [num_of_reads])

        END

    ,[AvgBytesPerWrite] = CASE

        WHEN [num_of_writes] = 0

            THEN 0

        ELSE ([num_of_bytes_written] / [num_of_writes])

        END

    ,[AvgBytesPerTransfer] = CASE

        WHEN (

                [num_of_reads] = 0

                AND [num_of_writes] = 0

                )

            THEN 0

        ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes]))

        END

    ,LEFT([mf].[physical_name], 2) AS [Drive]

    ,DB_NAME([vfs].[database_id]) AS [DB]

    ,[mf].[physical_name]

    ,[mf].file_id

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]

JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id]

    AND [vfs].[file_id] = [mf].[file_id]

ORDER BY [WriteLatency] DESC;

 

 

--获取数据库的 I/O 使用率

WITH Aggregate_IO_Statistics

AS (

    SELECT DB_NAME(database_id) AS [Database Name]

        ,CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS io_in_mb

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]

    GROUP BY database_id

    )

SELECT ROW_NUMBER() OVER (

        ORDER BY io_in_mb DESC

        ) AS [I/O Rank]

    ,[Database Name]

    ,io_in_mb AS [Total I/O (MB)]

    ,CAST(io_in_mb / SUM(io_in_mb) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Percent]

FROM Aggregate_IO_Statistics

ORDER BY [I/O Rank]

OPTION (RECOMPILE);

 

--查看指定数据库文件的 I/O 状况,需要指定数据库

SELECT DB_NAME(DB_ID()) AS [Database Name]

    ,df.[name] AS [Logical Name]

    ,vfs.[file_id]

    ,df.physical_name AS [Physical Name]

    ,vfs.num_of_reads

    ,vfs.num_of_writes

    ,vfs.io_stall_read_ms

    ,vfs.io_stall_write_ms

    ,CAST(100. * vfs.io_stall_read_ms / (vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]

    ,CAST(100. * vfs.io_stall_write_ms / (vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]

    ,(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads]

    ,CAST(vfs.num_of_bytes_read / 1048576.0 AS DECIMAL(10, 2)) AS [MB Read]

    ,CAST(vfs.num_of_bytes_written / 1048576.0 AS DECIMAL(10, 2)) AS [MB Written]

    ,CAST(100. * vfs.num_of_reads / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct]

    ,CAST(100. * vfs.num_of_writes / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct]

    ,CAST(100. * vfs.num_of_bytes_read / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct]

    ,CAST(100. * vfs.num_of_bytes_written / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]

FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs

INNER JOIN sys.database_files AS df WITH (NOLOCK) ON vfs.[file_id] = df.[file_id]

OPTION (RECOMPILE);

 

--找出 I/O 平均使用最多的语句

SELECT TOP (50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name]

    ,(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [Avg IO]

    ,qs.execution_count AS [Execution Count]

    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (

            CASE

                WHEN qs.statement_end_offset = - 1

                    THEN LEN(CONVERT(NVARCHAR(max), qt.[text])) * 2

                ELSE qs.statement_end_offset

                END - qs.statement_start_offset

            ) / 2) AS [Query Text]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.[dbid] = DB_ID()

ORDER BY [Avg IO] DESC

OPTION (RECOMPILE);

 

--查询正在等待 I/O 的请求等待时间

SELECT DB_NAME(database_id) AS [DBNAME]

    ,file_id

    ,io_stall

    ,io_pending_ms_ticks

    ,scheduler_address

FROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs

    ,sys.dm_io_pending_io_requests AS iopior

WHERE iovfs.file_handle = iopior.io_handle


安装命令
# 注:以下命令中 password替换为你的数据库密码
mkdir "C:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir"
mkdir "C:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir"
.\setup.exe /SQLSVCPASSWORD="password" /AGTSVCPASSWORD="password" /ASSVCPASSWORD="password" /ISSVCPASSWORD="password"
/RSSVCPASSWORD="password" /CONFIGURATIONFILE="%CD%\ConfigurationFile.ini"
相关文件

ConfigurationFile.ini
注:内含sa用户密码,默认123456

;SQL Server 2012 Configuration File
[OPTIONS]; 必需,用于确认接受许可条款,静默安装用。IACCEPTSQLSERVERLICENSETERMS; 指定安装程序的工作流,
如 INSTALL、UNINSTALL 或 UPGRADE。这是必需的参数。 ACTION="Install"; 尚未定义命令行参数 ENU 的详细帮助。
ENU="False"; 用于控制用户界面行为的参数。有效值对于完整 UI 为 Normal,对于简化的 UI 为 AutoAdvance,
为 EnableUIOnServerCore 则跳过 Server Core 安装程序 GUI 块。 ;UIMODE="Normal";
安装程序将不会显示任何用户界面。 ;QUIET="false";
安装程序将只显示进度,而不需要任何用户交互。 QUIETSIMPLE="true";
指定 SQL Server 安装程序是否应发现和包括产品更新。有效值是 True 和 False 或者 1 和 0。默认情况下,
SQL Server 安装程序将包括找到的更新。 UpdateEnabled="False"; 指定要安装、卸载或升级的功能。
顶级功能列表包括 SQL、AS、RS、IS、MDS 和工具。SQL 功能将安装数据库引擎、复制、全文和 Data Quality Services (DQS)服务器。
工具功能将安装管理工具、联机丛书组件、SQL Server Data Tools 和其他共享组件。 FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS,RS_SHP,RS_SHPWFE,DQC,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK; 指定 SQL Server 安装程序将获取产品更新的位置。有效值为 "MU" (以便搜索产品更新)、有效文件夹路径以及 .\MyUpdates 或 UNC 共享目录之类的相对路径。默认情况下,SQL Server 安装程序将通过 Window Server Update Services 搜索 Microsoft Update 或 Windows Update 服务。 UpdateSource="MU"; 显示命令行参数用法 HELP="False"; 指定应将详细的安装程序日志传送到控制台。 INDICATEPROGRESS="False"; 指定安装程序应该安装到 WOW64 中。IA64 或 32 位系统不支持此命令行参数。 X86="False"; 指定共享组件的安装根目录。在已安装共享组件后,此目录保持不变。 INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"; 指定 WOW64 共享组件的安装根目录。在已安装 WOW64 共享组件后,此目录保持不变。 INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"; 指定默认实例或命名实例。MSSQLSERVER 是非 Express 版本的默认实例,SQLExpress 则是 Express 版本的默认实例。在安装 SQL Server 数据库引擎(SQL)、Analysis Services (AS)或 Reporting Services (RS)时,此参数是必需的。 INSTANCENAME="MSSQLSERVER"; 为您已指定的 SQL Server 功能指定实例 ID。SQL Server 目录结构、注册表结构和服务名称将包含 SQL Server 实例的实例 ID。 INSTANCEID="MSSQLSERVER"; 指定可以收集 SQL Server 功能使用情况数据,并将数据发送到 Microsoft。指定 1 或 True 将启用此功能,指定 0 或 False 将禁用此功能。 SQMREPORTING="False"; 客户端与之通信的分布式重播控制器服务的计算机名称。 CLTCTLRNAME="SQL Server"; 用于授予分布式重播控制器服务权限的 Windows 帐户。 CTLRUSERS="Administrator"; 分布式重播控制器服务使用的帐户。 CTLRSVCACCOUNT="NT Service\SQL Server Distributed Replay Controller"; 分布式重播控制器服务的启动类型。 CTLRSTARTUPTYPE="Manual"; 分布式重播客户端服务使用的帐户。 CLTSVCACCOUNT="NT Service\SQL Server Distributed Replay Client"; 分布式重播客户端服务的启动类型。 CLTSTARTUPTYPE="Manual"; 分布式重播客户端服务的结果目录。 CLTRESULTDIR="C:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir"; 分布式重播客户端服务的工作目录。 CLTWORKINGDIR="C:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir"; RSInputSettings_RSInstallMode_Description RSINSTALLMODE="DefaultNativeMode"; RSInputSettings_RSInstallMode_Description RSSHPINSTALLMODE="SharePointFilesOnlyMode"; 指定是否可将错误报告给 Microsoft 以便改进以后的 SQL Server 版本。指定 1 或 True 将启用此功能,指定 0 或 False 将禁用此功能。 ERRORREPORTING="False"; 指定安装目录。 INSTANCEDIR="C:\Program Files\Microsoft SQL Server"; 代理帐户名 AGTSVCACCOUNT="NT Service\SQLSERVERAGENT"; 安装后自动启动服务。 AGTSVCSTARTUPTYPE="Manual"; Integration Services 的启动类型。 ISSVCSTARTUPTYPE="Automatic"; Integration Services 的帐户: 域\用户或系统帐户。 ISSVCACCOUNT="NT Service\MsDtsServer110"; 运行 Analysis Services 服务所使用的帐户的名称。 ASSVCACCOUNT="NT Service\MSSQLServerOLAPService"; 在创建服务后控制服务启动类型设置。 ASSVCSTARTUPTYPE="Automatic"; Analysis Services 要使用的排序规则。 ASCOLLATION="Chinese_PRC_CI_AS"; Analysis Services 数据文件的位置。 ASDATADIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data"; Analysis Services 日志文件的位置。 ASLOGDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log"; Analysis Services 备份文件的位置。 ASBACKUPDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Backup"; Analysis Services 临时文件的位置。 ASTEMPDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Temp"; Analysis Services 配置文件的位置。 ASCONFIGDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config"; 指定是否允许 MSOLAP 访问接口在进程中运行。 ASPROVIDERMSOLAP="1"; 指定需要设置的管理员帐户的列表。 ASSYSADMINACCOUNTS="Administrator"; 指定 Analysis Services 实例的服务器模式。有效值为 MULTIDIMENSIONAL 和 TABULAR。默认值为 MULTIDIMENSIONAL。 ASSERVERMODE="MULTIDIMENSIONAL"; CM 程序块 TCP 通信端口 COMMFABRICPORT="0"; 矩阵如何使用专用网络 COMMFABRICNETWORKLEVEL="0"; 如何保护程序块间的通信 COMMFABRICENCRYPTION="0"; CM 程序块使用的 TCP 端口 MATRIXCMBRICKCOMMPORT="0"; SQL Server 服务的启动类型。 SQLSVCSTARTUPTYPE="Automatic"; 启用 FILESTREAM 功能的级别(0、1、2 或 3)。 FILESTREAMLEVEL="0"; 设置为 "1" 可为 SQL Server Express 启用 RANU。 ENABLERANU="False"; 指定要用于数据库引擎的 Windows 排序规则或 SQL 排序规则。 SQLCOLLATION="Chinese_PRC_CI_AS"; SQL Server 服务的帐户: 域\用户或系统帐户。 SQLSVCACCOUNT="NT Service\MSSQLSERVER"; 要设置为 SQL Server 系统管理员的 Windows 帐户。 SQLSYSADMINACCOUNTS="Administrator"; 默认值为 Windows 身份验证。使用 "SQL" 表示采用混合模式身份验证。 SECURITYMODE="SQL";sa用户密码 记得改成自己的SAPWD="123456"; 将当前用户设置为 SQL Server 2012 Express 的数据库引擎系统管理员。 ADDCURRENTUSERASSQLADMIN="False"; 指定 0 禁用 TCP/IP 协议,指定 1 则启用该协议。 TCPENABLED="1"; 指定 0 禁用 Named Pipes 协议,指定 1 则启用该协议。 NPENABLED="0"; Browser 服务的启动类型。 BROWSERSVCSTARTUPTYPE="Disabled"; 指定报表服务器 NT 服务在执行时应当使用的帐户。如果省略此值或者值为空字符串,则将使用当前操作系统的默认内置帐户。
; RSSVCACCOUNT 的用户名部分最大长度为 20 个字符,
; RSSVCACCOUNT 的域部分最大长度为 254 个字符。 RSSVCACCOUNT="NT Service\ReportServer"; 指定报表服务器 NT 服务的启动模式。
; 手动 - 在手动模式(默认值)下启动服务。
; 自动 - 在自动模式下启动服务。
; 已禁用 - 服务处于禁用状态 RSSVCSTARTUPTYPE="Automatic"; 添加输入参数 FTSVCACCOUNT 的描述 FTSVCACCOUNT="NT Service\MSSQLFDLauncher"


; SQL Server 2012 Configuration File
; 参考:https://msdn.microsoft.com/zh-cn/library/ms144259.aspx
[OPTIONS]

; ACTION 可选为 INSTALL(安装), UNINSTALL(卸载), UPGRADE(升级)

ACTION="Install"

; 【新增】必需,用于确认接受许可条款,静默安装用。

IACCEPTSQLSERVERLICENSETERMS

; 【新增】产品密钥。 如果未指定此参数,则使用 Evaluation。

PID="XXXXX-XXXXX-XXXXX-XXXXX-XXXXX"

; 在已本地化的操作系统上安装英文版的 SQL Server。

ENU="True"

; 【注释】UIMODE 可选为Normal,AutoAdvance (UIMode 设置不能与 /Q (QUIET)或 /QS(QUIETSIMPLE)参数结合使用)
; Normal : 非 Express 版本,所选的功能提供其所有安装程序对话框。
; AutoAdvance : 对于 Express 版本是默认值,它跳过不重要的对话框

; UIMODE="Normal"

; 【注释】是否静默安装,静默安装不显示用户交互界面.QUIET 与 QUIETSIMPLE 二选一

; QUIET="True"

; 是否只显示进度(最后一步安装进度),不显示用户交互界面。静默安装还是显示进度吧。

QUIETSIMPLE="True"

; 是否应发现和包含产品更新,不更新。

UpdateEnabled="False"

; 安装的功能,此为 MSSQL存储引擎 和 SSMS管理工具

FEATURES=SQLENGINE,SSMS,ADV_SSMS

; SQL Server 安装程序将获取产品更新的位置( .\MyUpdates)

UpdateSource="MU"

; 显示安装参数的用法选项,不显示

HELP="False"

; 指定是否将详细的安装日志文件传送到控制台。(此禁用或只看进度即可 QUIETSIMPLE="True")
; 安装过程日志信息:C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

INDICATEPROGRESS="True"

; 是否允许32位程序安装到64位系统上(WOW64)

X86="False"

; 为 64 位共享组件指定一个非默认安装目录。

INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; 为 32 位共享组件指定一个非默认安装目录。 仅在 64 位系统上受支持。

INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; 实例名称

INSTANCENAME="MSSQLSERVER"

; 实例ID

INSTANCEID="MSSQLSERVER"

; 是否收集使用数据发送到 Microsoft(是:1 or True;否:0 or False )

SQMREPORTING="False"

; 是否将错误反馈发送到 Microsoft(是:1 or True;否:0 or False )

ERRORREPORTING="False"

; 安装目录

INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; 【新增】代理服务账户(新增密码项)

AGTSVCACCOUNT="KK\dcadmin"

AGTSVCPASSWORD="dcadmin"

; 代理启动模式,设置自动

AGTSVCSTARTUPTYPE="Automatic"

; CM 程序块 TCP 通信端口(未知)

COMMFABRICPORT="0"

; 矩阵如何使用专用网络(未知)

COMMFABRICNETWORKLEVEL="0"

; 如何保护程序块间的通信(未知)

COMMFABRICENCRYPTION="0"

; CM 程序块使用的 TCP 端口(未知)

MATRIXCMBRICKCOMMPORT="0"

; SQL Server 引擎服务的启动模式

SQLSVCSTARTUPTYPE="Automatic"

; FILESTREAM 是否启用,值为 (0, 1, 2 or 3).

FILESTREAMLEVEL="0"

; 为 SQL Server Express 安装启用运行身份凭据。

ENABLERANU="False"

; SQL Server 的排序规则设置

SQLCOLLATION="Chinese_PRC_CI_AS"

; 【新增】SQL Server 服务的启动帐户(新增密码项)

SQLSVCACCOUNT="KK\dcadmin"

SQLSVCPASSWORD="dcadmin"

; 指定 Windows 账户作为数据库管理员

SQLSYSADMINACCOUNTS="KK\dcadmin" "Administrator"

; 【新增】SQL授权模式,"SQL"为混合授权,需设置密码(新增密码项)

SECURITYMODE="SQL"

SAPWD="dcadmin"

; 【注释】当前用户作为数据库管理员(对于 SQL Server Express 版本为 True,其他未true)

; ADDCURRENTUSERASSQLADMIN="True"

; 指定 SQL Server 服务的 TCP 协议的状态(0:禁用;1:启用)

TCPENABLED="1"

; 指定 SQL Server 服务的 Named Pipes 协议的状态(0:禁用;1:启用)

NPENABLED="0"

; SQL Server Browser 服务的启动模式,禁用。

BROWSERSVCSTARTUPTYPE="Disabled"

.\setup.exe /CONFIGURATIONFILE="C:\PerfLogs\ConfigurationFile.ini"

等待完成即可,若出现中断,查看安装日志什么错误。

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log



此外,还可以单独设置数据文件的数据目录(INSTALLSQLDATADIR)等

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论