切换到窄版

运维联盟俱乐部

 找回密码
 立即注册
运维联盟俱乐部»论坛 › 数据库 › sqlserver › sql server常用语句
返回列表 发新帖
查看: 8355|回复: 0

sql server常用语句

[复制链接]
admin
  • TA的每日心情
    开心
    2023-8-9 11:05
    		•
    发表于 2020-6-19 23:37:14 | 显示全部楼层 |阅读模式
    1. sqlserver查看实例级别的信息，使用SERVERPROPERTY函数
    1. select
    2.     SERVERPROPERTY ('propertyname')
    复制代码
    2. 查看实例级别的某个参数XX的配置
    1. select *
    2. from
    3.     sys.configurations
    4. where
    5.     name='XX'
    复制代码
    3. 更改实例级别的某个参数XX的值

    sp_configure 'XX' ,'0' RECONFIGURE WITH OVERRIDE

    sp_configure显示或更改当前服务器的全局配置设置。  

    RECONFIGURE表示SQL Server不用重新启动就立即生效 。

    使用sp_configure更改设置时，请使用RECONFIGURE语句使更改立即生效，否则更改将在SQL Server重新启动后生效。RECONFIGURE后面加WITH OVERRIDE表示不管这个值是不是符合要求都会生效，比如recovery interval的范围值是10--60对应sys.configurations.minimum是10、sys.configurations.maximum是60，如果sp_configure 'recovery interval', 75设置为75，超过了这个10--60规范，但是要让75生效，则必须加上WITH OVERRIDE。

    4. sqlserver没有系统表可以查询所有数据库下面对象，以下只能在当前数据库下面查
    select *
    from
        sys.all_objects

    --查询当前数据库的所有架构范围的对象
    select * from sys.sysobjects

    --查询当前数据库的所有对象
    --sys.all_objects、sys.sysobjects 这种的视图，在每个数据库的系统视图下面都有
    select *
    from
        sys.databases                                                                                                

    --在当前数据库下可以查询到所有数据库信息，包含是否on状态select * from sys.sysdatabases

    --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，这个系统视图会在后续的版本中删除
    --sys.databases、sys.sysdatabases这种的视图，在每个数据库的系统视图下面都有sys.processes

    --没有这个视图select * from sys.sysprocesses

    --在当前数据库下可以查询所有正在SQL Server 实例上运行的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的版本中删除
    5. 全局系统视图、单个数据库系统视图 sys.database_files                                                                       --每个存储在数据库本身中的数据库文件在表中占用一行。这是一个基于每个数据库的视图。sys.master_files --master 数据库中的每个文件对应一行。这是一个系统范围视图。--sys.database_files、sys.master_files这种的视图，在每个数据库的系统视图下面都有
    6. 一些只存在msdb的系统表，而非系统视图 dbo.backupsetdbo.log_shipping_secondarydbo.restorehistorydbo.sysjobsdbo.sysjobhistory--这些系统表只存在msdb数据库，使用的时候必须加上msdb前缀
    7. sp_lock、sp_who、sp_who2、sp_helptext等一些系统存储过程存在于每个数据库中
    8. 报告有关锁的信息，会显示实例里面的所有数据库的锁信息、堵塞信息 sp_lock
    9. 提供有关当前用户、 会话和进程的实例中的信息，可以看到会话的状态running、SUSPENDED、sleeping、rollback，sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很大  sp_who  sp_who2  sp_who2 active (可选参数LoginName, 或active代表活动会话数)  CPUTime (进程占用的总CPU时间)  DiskIO (进程对磁盘读的总次数)  LastBatch (客户最后一次调用存储过程或者执行查询的时间)  ProgramName (用来初始化连接的应用程序名称，或者主机名)
    10. 查看某个存储过程的内容 sp_helptext pro_name
    11.显示某个线程号发送到sqlserver数据库的最后一个语句 DBCC INPUTBUFFER
    12.假设查询到249被锁给堵塞了，查询被堵塞的SQL语句 DBCC INPUTBUFFER (249)
    13. 查看某个数据库中是否存在活动事务，有活动事务就一定会写日志 DBCC OPENTRAN (dbname)
    14. 监视日志空间 DBCC SQLPERF (LOGSPACE)
    15. 查找无法重用日志中的空间的原因(日志无法截断导致日志文件越来越大，但是可用空间很小，无法收缩)
    select
        name
      , log_reuse_wait_desc
    from
        sys.databases
    16. 查看虚拟日志文件信息 DBCC LOGINFO 结果有多少行，代表有多少虚拟日志文件，活动的虚拟日志文件的状态(status)为2
    17. 修复msdb数据库，比如ssms页面sql server agent丢失或看不了job view history等功能，说明msdb坏了，需要修复 dbcc checkdb (msdb);

    18. 在您当前连接到的 SQL Server 数据库中生成一个手动检查点 CHECKPOINT [ checkpoint_duration ]--checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间，一般不使用这个参数，让数据库自己控制
    19. 查看数据库各种设置
    select
        name
      , State
      , user_access
      , is_read_only
      , recovery_model
    from
        sys.databases
    20. 查看某个数据库中是否存在会话
    select
        DB_NAME(dbid)
      , *
    from
        sys.sysprocesses
    where
        dbid=db_id('dbname')
    21. 查询当前阻塞的所有请求
    select *
    from
        sys.sysprocesses
    where
        blocked>0或SELECT t1.resource_type
      , db_name(t1.resource_database_id)
      , t1.resource_associated_entity_id
      , t1.request_mode
      , t1.request_session_id
      , t2.blocking_session_id
      , t2.wait_duration_msFROM sys.dm_tran_locks as t1INNER
        JOIN
            sys.dm_os_waiting_tasks as t2ON t1.lock_owner_address = t2.resource_address
    ;

    select
        A.SPID as 被阻塞进程
      , a.CMD  AS 正在执行的操作
      , b.spid AS 阻塞进程号
      , b.cmd  AS 阻塞进程正在执行的操作from master..sysprocesses a
      , master..sysprocesses bwhere a.blocked<>0
        and a.blocked                         = b.spid或SELECT session_Id
      , spid
      , ecid
      , DB_NAME (sp.dbid)
      , nt_username
      , er.status
      , wait_type
      , [Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,( CASEWHEN er.statement_end_offset = -1THENLEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2ELSEer.statement_end_offsetEND- er.statement_start_offset)/ 2)
      , qt.text
      , program_name
      , Hostname
      , nt_domain
      , start_timeFROM sys.dm_exec_requests erINNER
        JOIN
            sys.sysprocesses sp
            ON
                er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qtWHERE session_Id > 50
                /* Ignore system spids.*/
                AND sp.blocked>0
                AND session_Id NOT IN (@@SPID)或SELECT session_id
              , status
              , blocking_session_id
              , wait_type
              , wait_time
              , wait_resource
              , transaction_idFROM sys.dm_exec_requestsWHERE status = N'suspended'
    ;
    --sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息
    22. 查看哪些表被锁了，以及这些表被哪个进程锁了
    select
        request_session_id                                       spid
      , OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks
    where
        resource_type='OBJECT'
    ORDER BY
        request_session_id ASC 23. 查询某个job是否被堵塞
    select *
    from
        msdb.dbo.sysjobs
    where
        name='jobname'
    select
        a.program_name
      , a.*
    from
        master..sysprocesses a
    where
        a.program_name like '%0D1CE57E8AC5%'--把第一个语句查询到的job_id代入第二个语句的program_name
    24. 检查SQL Agent是否开启 IF EXISTS
        (
            SELECT
                TOP 1 1FROM sys.sysprocessesWHERE program_name = 'SQLAgent - Generic Refresher'
        )
    SELECT
        'Running'ELSESELECT 'Not Running'
    25. 查看活动线程执行的sql语句
      , 并生成批量杀掉的语句
    select
        'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd
      , REPLACE(hostname,' ','')              as hostname
      , replace(program_name,' ','')          as program_name
      , REPLACE(loginame, ' ', '')            AS loginame
      , db_name(a.dbid)                       AS DBname
      , spid
      , blocked
      , waittime/1000 as waittime
      , a.status
      , Replace(b.text,'''','''') as sqlmessage
      , cpufrom sys.sysprocesses  as a with(nolock)cross apply sys.dm_exec_sql_text(sql_handle) as bwhere a.status<>'sleeping'
        AND a.spid                                                                                                <>@@SPID
    26. 查看备份进度
    SELECT
        DB_NAME(database_id) AS Exec_DB
      , percent_complete
      , CASE
            WHEN estimated_completion_time < 36000000THEN '0'
                ELSE ''
        END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
      , b.text                                                                                                                                                                                    as tsql
      , *
    FROM
        SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')--OR command LIKE 'RESTORE%'ORDER BY 2 DESC
    27. 查看恢复进度
    SELECT
        DB_NAME(database_id) AS Exec_DB
      , percent_complete
      , CASE
            WHEN estimated_completion_time < 36000000THEN '0'
                ELSE ''
        END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
      , b.text                                                                                                                                                                                    as tsql
      , *
    FROM
        SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder')--OR command LIKE 'RESTORE%'ORDER BY 2 DESC
    28. 查看数据库的最近备份信息
    SELECT
        database_name
      , type
      , MAX(backup_finish_date) AS backup_finish_date
    FROM
        msdb.dbo.backupset
    GROUP BY
        database_name
      , type
    ORDER BY
        database_name
      , type备注：D 表示全备份，i 表示差异备份，L 表示日志备份
    29. 查看数据库的历史备份记录，并生成restore语句 SELECTCONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server
      , bs.database_name
      , bs.backup_start_date
      , bs.backup_finish_date
      , bs.expiration_date
      , CASE bs.typeWHEN 'D'
                THEN 'Database'
            WHEN 'L'
                THEN 'Log'
        END AS backup_type
      , bs.backup_size
      , bmf.logical_device_name
      , bmf.physical_device_name
      , bs.name AS backupset_name
      , bs.description
      ,'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''+bmf.physical_device_name+ '''WITH NORECOVERY;'
    FROM
        msdb.dbo.backupmediafamily bmfINNER
        JOIN
            msdb.dbo.backupset bsON bmf.media_set_id=bs.media_set_idWHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())ORDER BY bs.backup_finish_date
    30. 查询XX库从YYYY-MM-DD日期开始的日志备份记录，并生成restore log的语句
    SELECT
        TOP 1000 S.database_name [Database]
      , CASE [S].[type]
            WHEN 'L'
                THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'
        END [LogRestore]
      , F.physical_device_name
      , S.[Type]
      , S.backup_start_date
      , S.backup_finish_date
    FROM
        msdb.dbo.backupmediafamily F
        INNER JOIN
            msdb.dbo.backupset S
            ON
                S.media_set_id = F.media_set_id
    WHERE
        S.database_name         = 'XX'
        AND S.type              = 'L'
        AND S.backup_start_date > 'YYYY-MM-DD'
    ORDER BY
        S.backup_start_date ASC  
    31. 查询always on状态是否正常
    select
        dc.database_name
      , d.synchronization_health_desc
      , d.synchronization_state_desc
      , d.database_state_desc
    from
        sys.dm_hadr_database_replica_states d
        join
            sys.availability_databases_cluster dc
            on
                d.group_database_id=dc.group_database_id
                and d.is_local     =1
    32. 查看mirror镜像信息 SELECTdb_name(database_id)
              , mirroring_state_desc
              , mirroring_role_desc
              , mirroring_partner_name
              , mirroring_partner_instanceFROM sys.database_mirroring
    33. 查询SSRS Report Subscriptions相关的job SELECTb.name AS JobName
              , e.name
              , e.path
              , d.description
              , a.SubscriptionID
              , laststatus
              , eventtype
              , LastRunTime
              , date_created
              , date_modifiedFROMReportServer.dbo.ReportSchedule aJOIN msdb.dbo.sysjobs b
                ON
                    CONVERT(SYSNAME,a.ScheduleID) = b.nameJOIN ReportServer.dbo.ReportSchedule c
                    ON
                        b.name = CONVERT(SYSNAME,c.ScheduleID)
                JOIN
                    ReportServer.dbo.Subscriptions d
                    ON
                        c.SubscriptionID = d.SubscriptionIDJOIN ReportServer.dbo.Catalog e
                        ON
                            d.report_oid = e.itemidWHEREe.name = 'Report Name Goes Here'
    34. 查看某个数据库的数据文件信息，就算是mirror从库的数据文件也可以查到，filestream目录也可以查到
    SELECT
        db_name(database_id)
      , *
    FROM
        master.sys.master_files
    WHERE
        database_id =DB_ID(N'DBA')
    ;

    35. 查看某个数据文件信息
    select
        b.name
      , a.type_desc
      , a.name
      , a.physical_name
      , a.size
      , a.max_size
      , a.is_percent_growth
      , a.growth
    from
        sys.master_files a
        join
            sys.databases b
            on
                a.database_id          =b.database_id
                and a.physical_name like '%DTSWonda_1%'
    36. 查询实例的数据文件总大小
    SELECT
        sum(size*8/1024/1024)
    FROM
        master.sys.master_files
    37. 查询某个目录中数据库使用的总大小
    SELECT
        a.size*8/1024/1024
      , a.*
    FROM
        master.sys.master_files a
    WHERE
        physical_name like 'G:\DEFAULT.DATA%'
    38. 查询某个目录中哪些数据库占用了8G以上容量
    SELECT
        b.name             dbname
      , a.size*8/1024/1024 sum_GB
      , a.type_desc
      , a.name datafilename
      , a.physical_name
    FROM
        master.sys.master_files a
        join
            sys.sysdatabases b
            on
                a.database_id          =b.dbid
                and a.physical_name like 'G:\DEFAULT.DATA%'
                and a.size*8/1024/1024 >8
    39. 查询实例上的每个数据库的大小 SELECTDB_NAME(db.database_id) DatabaseName
              ,(CAST(mfrows.RowSize AS       FLOAT)*8)/1024 RowSizeMB
              ,(CAST(mflog.LogSize AS        FLOAT)*8)/1024 LogSizeMB
              ,(CAST(mfstream.StreamSize AS  FLOAT)*8)/1024 StreamSizeMB
              ,(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMBFROM sys.databases dbLEFT
        JOIN
            (
                SELECT
                    database_id
                  , SUM(size) RowSize
                FROM
                    sys.master_files
                WHERE
                    type = 0
                GROUP BY
                    database_id
                  , type
            )
            mfrows
            ON
                mfrows.database_id = db.database_idLEFT
        JOIN
            (
                SELECT
                    database_id
                  , SUM(size) LogSize
                FROM
                    sys.master_files
                WHERE
                    type = 1
                GROUP BY
                    database_id
                  , type
            )
            mflog
            ON
                mflog.database_id = db.database_idLEFT
        JOIN
            (
                SELECT
                    database_id
                  , SUM(size) StreamSize
                FROM
                    sys.master_files
                WHERE
                    type = 2
                GROUP BY
                    database_id
                  , type
            )
            mfstream
            ON
                mfstream.database_id = db.database_idLEFT
        JOIN
            (
                SELECT
                    database_id
                  , SUM(size) TextIndexSize
                FROM
                    sys.master_files
                WHERE
                    type = 4
                GROUP BY
                    database_id
                  , type
            )
            mftext
            ON
                mftext.database_id = db.database_id
    40. 查询总耗CPU最多的前3个SQL，且最近5天出现过
    SELECT
        TOP 3total_worker_time/1000                  AS [总消耗CPU 时间(ms)]
      , execution_count [运行次数                           ]
      , qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)]
      , last_execution_time                          AS [最后一次执行时间]
      , max_worker_time /1000                        AS [最大执行时间(ms)]
      , SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (
            CASE
                WHEN qs.statement_end_offset = -1THEN DATALENGTH(qt.text)
                    ELSE qs.statement_end_offset
            END -qs.statement_start_offset
        )
        /2 + 1)      AS [使用CPU的语法]
      , qt.text [完整语法   ]
      , qt.dbid
      , dbname=db_name(qt.dbid)
      , qt.objectid
      , object_name(qt.objectid,qt.dbid) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1
        and last_execution_time                                                                                                                                          >dateadd(dd,-5,getdate())ORDER BY total_worker_time DESC
    41. 查询平均耗CPU最多的前3个SQL，且最近5小时出现过
    SELECT
        TOP 3total_worker_time/1000                  AS [总消耗CPU 时间(ms)]
      , execution_count [运行次数                           ]
      , qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)]
      , last_execution_time                          AS [最后一次执行时间]
      , min_worker_time /1000                        AS [最小执行时间(ms)]
      , max_worker_time /1000                        AS [最大执行时间(ms)]
      , SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (
            CASE
                WHEN qs.statement_end_offset = -1THEN DATALENGTH(qt.text)
                    ELSE qs.statement_end_offset
            END -qs.statement_start_offset
        )
        /2 + 1)      AS [使用CPU的语法]
      , qt.text [完整语法   ]
      , qt.dbid
      , dbname=db_name(qt.dbid)
      , qt.objectid
      , object_name(qt.objectid,qt.dbid) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1
        and last_execution_time                                                                                                                                          >dateadd(hh,-5,getdate())
    ORDER BY
        (qs.total_worker_time/qs.execution_count/1000) DESC
    42. 查看当前最耗资源的10个SQL及其spid
    SELECT
        TOP 10session_id
      , request_id
      , start_time           AS '开始时间'
      , status               AS '状态'
      , command              AS '命令'
      , d_sql.text           AS 'sql语句'
      , DB_NAME(database_id) AS '数据库名'
      , blocking_session_id  AS '正在阻塞其他会话的会话ID'
      , wait_type            AS '等待资源类型'
      , wait_time            AS '等待时间'
      , wait_resource        AS '等待的资源'
      , reads                AS '物理读次数'
      , writes               AS '写次数'
      , logical_reads        AS '逻辑读次数'
      , row_count            AS '返回结果行数'
    FROM
        sys.dm_exec_requests AS d_requestCROSS APPLYsys.dm_exec_sql_text(d_request.sql_handle) AS d_sqlWHERE session_id>50ORDER BY cpu_time DESC--前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为background
    43. 查询某个存储过程被哪些job调用了
    SELECT *
    FROM
        msdb.dbo.sysjobs JOB WITH( NOLOCK)
        INNER JOIN
            msdb. dbo.sysjobsteps STP WITH(NOLOCK )
            ON
                STP .job_id = JOB .job_idWHERE STP .command LIKE N'%sp_name%'--以上要查询某个job被哪个job调用了，把sp_name存储过程名字改成job_name作业名字即可
    44. 命令执行某个job EXECUTE msdb.dbo.sp_start_job N'job_name'
    45. 查询某表标识列的列名
    SELECT
        COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.columns
    WHERE
        TABLE_NAME                                                  ='表名'
        AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1
    46. 获取标识列的种子值
    SELECT
        IDENT_SEED ('表名')
    47. 获取标识列的递增量
    SELECT
        IDENT_INCR('表名')
    48. 获取指定表中最后生成的标识值
    SELECT
        IDENT_CURRENT('表名')
    49. 重新设置标识种子值为XX DBCC CHECKIDENT (表名, RESEED, XX)
    50. 升级前，查询服务器名、实例名、版本号
    select
        SERVERPROPERTY('machinename')
      , @@SERVERNAME
      , SERVERPROPERTY ('edition')
      , @@version
    51. 用户被grant这样操作赋予的权限  use dbname exec sp_helprotect @username = 'username'
    52. 授予某个用户执行某个数据库的sp的权限 use dbname grant execute to "username"
    53. always on
        -查看集群各节点的信息，包含节点成员的名称，类型，状态，拥有的投票仲裁数
    1. SELECT *
    2. FROM
    3.     sys.dm_hadr_cluster_members
    4. ;
    复制代码
    -查看集群各节点的信息，包含节点成员的名称，节点成员上的sql实例名称
    1. select *
    2. from
    3.     sys.dm_hadr_instance_node_map
    复制代码
    -查看WSFC(windows server故障转移群集)的信息，包含集群名称，仲裁类型，仲裁状态
    1. SELECT *
    2. FROM
    3.     SYS.dm_hadr_cluster
    4. ;
    复制代码
    -查看AG名称
    1. select *
    2. from
    3.     sys.dm_hadr_name_id_map
    复制代码
    -查看集群各节点的子网信息，包含节点成员的名称，子网段，子网掩码
    1. SELECT *
    2. FROM
    3.     sys.dm_hadr_cluster_networks
    4. ；
    复制代码
    -查看侦听ip
    1. select *
    2. from
    3.     sys.availability_group_listeners
    4. ;
    复制代码
    -查看主从各节点的状态
    1. select
    2.     d.is_local
    3.   , dc.database_name
    4.   , d.synchronization_health_desc
    5.   , d.synchronization_state_desc
    6.   , d.database_state_desc
    7. from
    8.     sys.dm_hadr_database_replica_states d
    9.     join
    10.         sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id
    11. ;
    复制代码
    -查看辅助副本(传说中的从库)延迟多少M日志量
    1. select
    2.     db_name(database_id)
    3.   , log_send_queue_size/1024 delay_M
    4.   ,                    *
    5. from
    6.     sys.dm_hadr_database_replica_states
    7. where
    8.     is_primary_replica=0
    9. ;

    11. select
    12.     ar.replica_server_name
    13.   , db_name(drs.database_id)
    14.   , drs.truncation_lsn
    15.   , drs.log_send_queue_size
    16.   , drs.redo_queue_size
    17. from
    18.     sys.dm_hadr_database_replica_states drs
    19.     join
    20.         sys.availability_replicas ar
    21.         on
    22.             drs.replica_id=ar.replica_id
    23. where
    24.     drs.is_local=0
    25. ;

    27. select
    28.     ar.replica_server_name
    29.   , db_name(drs.database_id)
    30.   , drs.truncation_lsn
    31.   , drs.log_send_queue_size
    32.   , drs.log_send_rate
    33.   , drs.redo_queue_size
    34.   , drs.redo_rate
    35. from
    36.     sys.dm_hadr_database_replica_states drs
    37.     join
    38.         sys.availability_replicas ar
    39.         on
    40.             drs.replica_id=ar.replica_id
    41. where
    42.     drs.is_local=0
    复制代码
    --log_send_queue_size

    主数据库中尚未发送到辅助数据库的日志记录量 (KB)
    --log_send_rate

    在最后一个活动期间，以千字节 (KB) 的平均主副本发送实例数据的速率/秒
    --redo_queue_size

    在最后一个活动期间，以千字节 (KB) 的平均主副本发送实例数据的速率/秒
    --redo_rate

    平均千字节 (KB) 中的给定辅助数据库做的日志记录速率 / 秒
        54. 查询实例的FILESTREAM 使用的DIRECTORY_NAME  SELECT  SERVERPROPERTY('FilestreamShareName')  55. 查询FILETABLE表的数据库对应的DIRECTORY_NAME，仅仅使用filestream功能时，数据库不需要对应的DIRECTORY_NAME  
    1. select
    2.     db_name(database_id)
    3.   , *
    4. from
    5.     sys.database_filestream_options
    复制代码
    56. 查询FILETABLE表对应的DIRECTORY_NAME
    1. select
    2.     object_name(object_id)
    3.   , *
    4. from
    5.     sys.filetables
    6. 57. 查询filetable表testdb.dbo.table1中的文件完整路径名称
    7. SELECT
    8.     FileTableRootPath()+[file_stream].GetFileNamespacePath()
    9.   , name
    10. FROM
    11.     testdb.dbo.table1
    复制代码
    58. 查询所有job的状态是否running
    1. SELECT
    2.     sj.Name
    3.   , CASE
    4.         WHEN sja.start_execution_date IS NULL
    5.             THEN 'Not running'
    6.         WHEN sja.start_execution_date IS NOT NULL
    7.             AND sja.stop_execution_date   IS NULL
    8.             THEN 'Running'
    9.         WHEN sja.start_execution_date   IS NOT NULL
    10.             AND sja.stop_execution_date IS NOT NULL
    11.             THEN 'Not running'
    12.     END AS 'RunStatus'
    13. FROM
    14.     msdb.dbo.sysjobs sj
    15.     JOIN
    16.         msdb.dbo.sysjobactivity sja
    17.         ON
    18.             sj.job_id = sja.job_id
    19. WHERE
    20.     session_id =
    21.     (
    22.         SELECT
    23.             MAX(session_id)
    24.         FROM
    25.             msdb.dbo.sysjobactivity
    26.     )
    27. order by
    28.     RunStatus desc
    29. ;
    复制代码
    59. 锁表的四种用法 TABLOCKX
    查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束  HOLDLOCK
    1. SELECT *
    2. FROM
    3.     table WITH (TABLOCKX) 查询过程中，其他会话无法查询、更新此表，直到查询过程结束 TABLOCK
    4. SELECT *
    5. FROM
    6.     table WITH (TABLOCK)
    复制代码

    查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束 NOLOCK
    1. SELECT *
    2. FROM
    3.     table WITH (HOLDLOCK)
    复制代码

    查询过程中，其他会话可以查询、更新此表

    1. SELECT *
    2. FROM
    3.     table WITH (NOLOCK)
    复制代码

    60. 查询某个发布XX，发布的数据库对象的2种方法 发布数据库上执行(数据来源这三张表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns)
    1. select
    2.     a.article
    3.   , a.source_object
    4.   , a.destination_object
    5.   , b.colid
    6. from
    7.     (
    8.         select
    9.             article
    10.           , article_id
    11.           , source_object
    12.           , destination_object
    13.         from
    14.             [distribution].[dbo].MSarticles
    15.         where
    16.             publication_id in
    17.             (
    18.                 select
    19.                     publication_id
    20.                 from
    21.                     [distribution].[dbo].MSpublications
    22.                 where
    23.                     publication='XX'
    24.             )
    25.     )
    26.     a
    27.     inner join
    28.         (
    29.             select *
    30.             from
    31.                 replicate1.dbo.sysarticlecolumns
    32.         )
    33.         b
    34.         on
    35.             a.article_id=b.artid
    36. order by
    37.     a.article 订阅数据库上执行
    38. select distinct
    39.     article
    40. from
    41.     MSreplication_objects
    42. where
    43.     publication='XX'
    复制代码

    61. 查询发布信息，发布名称，发布名称对应的发布序号
    1. Select *
    2. from
    3.     distribution.dbo.MSpublications
    复制代码
    62. 查询发布名里面的发布对象的信息，包含表、视图、存储过程等  

    1. Select * from  distribution.dbo.MSarticles
    复制代码

    63. 监控发布订阅是否有异常，执行以下5条语句即可
    1. select *
    2. from
    3.     [distribution].[dbo].[MSlogreader_history]
    4. WHERE
    5.     error_id   != 0
    6.     AND [time] >= DATEADD(HOUR, -1, GETDATE())
    7. select *
    8. from
    9.     [distribution].[dbo].[MSdistribution_history]
    10. WHERE
    11.     error_id   != 0
    12.     AND [time] >= DATEADD(HOUR, -1, GETDATE())
    13. select *
    14. from
    15.     [distribution].[dbo].[MSsnapshot_history]
    16. WHERE
    17.     error_id   != 0
    18.     AND [time] >= DATEADD(HOUR, -1, GETDATE())
    19. select *
    20. from
    21.     [distribution].[dbo].MSrepl_errors
    22. order by
    23.     2 descselect *
    24. from
    25.     msdb.dbo.sysreplicationalerts
    26. order by
    27.     7 desc
    复制代码
    64. 查询XX表的索引信息
    1. SELECT
    2.     a.name index_name
    3.   , c.name table_name
    4.   , d.name column_name
    5. FROM
    6.     sysindexes a
    7.     JOIN
    8.         sysindexkeys b
    9.         ON
    10.             a.id       =b.id
    11.             AND a.indid=b.indid
    12.     JOIN
    13.         sysobjects c
    14.         ON
    15.             b.id=c.id
    16.     JOIN
    17.         syscolumns d
    18.         ON
    19.             b.id       =d.id=
    20.             AND b.colid=d.colid
    21. WHERE
    22.     a.indid NOT IN(0, 255)
    23.     AND c.name  in ('XX')
    复制代码
    65. 生成sql语句的执行计划
    1.     (
    2.         select
    3.             XXX为例，当然select XXX也可以换成执行存储过程比如exec pro_XXX，都是只生成执行计划，不产生结果集，不会执行存储过程
    4.     )
    5.       SET SHOWPLAN_ALL
    6. ON
    7. ;

    9. GO
    10. select
    11.     XXX GO SET SHOWPLAN_ALL OFF
    12. ;

    14. GO 或 SET SHOWPLAN_XML ON;
    15. GO
    16. select
    17.     XXX GO SET SHOWPLAN_XML OFF
    18. ;

    20. GO
    复制代码
    66. 查询名称为XXX的job的最后一次运行成功的时间
    1. SELECT
    2. TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time %% 10000) / 100 * 60) + (run_time %% 10000) %% 100) / (86399.9964)
    3. FROM
    4. msdb.dbo.sysjobhistory jobhis
    5. inner join
    6. msdb.dbo.sysjobs jobs
    7. on
    8. jobhis.job_id = jobs.job_id
    9. AND jobhis.step_id = 0
    10. AND jobhis.run_status = 1
    11. and jobs.name ='XXX'
    12. ORDER BY
    13. 1 DESC
    复制代码

    67. 查询某张分区表的总行数和大小，比如表为crm.EmailLog
    1. exec sp_spaceused 'crm.EmailLog'
    2. ;
    复制代码

    68. 查询某张分区表的信息，每个分区有多少行，比如表为crm.EmailLog
    1. select
    2.     convert(varchar(50), ps.name ) as partition_scheme
    3.   , p.partition_number
    4.   , convert(varchar(10), ds2.name )                as filegroup
    5.   , convert(varchar(19), isnull(v.value, ''), 120) as range_boundary
    6.   , str(p.rows, 9)                                 as rows
    7. from
    8.     sys.indexes i
    9.     join
    10.         sys.partition_schemes ps
    11.         on
    12.             i.data_space_id = ps.data_space_id
    13.     join
    14.         sys.destination_data_spaces dds
    15.         on
    16.             ps.data_space_id = dds.partition_scheme_id
    17.     join
    18.         sys.data_spaces ds2
    19.         on
    20.             dds.data_space_id = ds2.data_space_id
    21.     join
    22.         sys.partitions p
    23.         on
    24.             dds.destination_id = p.partition_number
    25.             and p.object_id    = i.object_id
    26.             and p.index_id     = i.index_id
    27.     join
    28.         sys.partition_functions pf
    29.         on
    30.             ps.function_id = pf.function_id
    31.     LEFT JOIN
    32.         sys.Partition_Range_values v
    33.         on
    34.             pf.function_id    = v.function_id
    35.             and v.boundary_id = p.partition_number - pf.boundary_value_on_right
    36. WHERE
    37.     i.object_id = object_id('crm.EmailLog')
    38.     and i.index_id in (0
    39.                      , 1) order by p.partition_number
    复制代码
    69. 查询分区函数
    1. select *
    2. from
    3.     sys.partition_functions

    复制代码
    70. 查看分区架构
    1. select *
    2. from
    3. sys.partition_schemes
    复制代码
    71. 查询ssis包的信息
    1. select *
    2. from
    3. msdb.dbo.sysssispackages
    复制代码
    72. 查询某张表里的索引的大小, 如下示例表为dbo.table1
    1. SELECT
    2. i.name AS IndexName
    3. , SUM(page_count * 8) AS IndexSizeKB
    4. FROM
    5. sys.dm_db_index_physical_stats( db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s
    6. JOIN
    7. sys.indexes AS i
    8. ON
    9. s.[object_id] = i.[object_id]
    10. AND s.index_id = i.index_id
    11. GROUP BY
    12. i.name
    13. ORDER BY
    14. i.name
    复制代码

    73. 重建表上的所有索引
    1. alter index all
    2. on
    3.     table_name rebuild with (online=on) 重建表上的某个索引
    4. alter index index_name
    5. on
    6.     table_name rebuild with (online=on) 重新组织表上的所有索引
    7. alter index all
    8. on
    9.     table_name reorganize 重新组织表上的某个索引
    10. alter index index_name
    11. on
    12.     table_name reorganize
    复制代码
    74. 查看数据文件可收缩空间，结果见Availabesize_MB字段值
    1. select
    2.     name
    3.   , size                                       *8/1024 as Totalsize_MB
    4.   , CAST(FILEPROPERTY(name,'SpaceUsed') AS                                                       int)*8/1024 as Usedsize_MB
    5.   , size                                       *8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB
    6. from
    7.     sys.master_files
    8. where
    9.     database_id=db_id(N'DBNAME')
    复制代码
    75. 查询某个表中的全部索引的信息  declare @tableName varchar(50) = 'LbaListAlertDetail' declare @tableId int
    1. select
    2.     @tableId = object_id
    3. from
    4.     sys.objects
    5. where
    6.     name = @tableName
    7. SELECT
    8.     OBJECT_NAME(IX.OBJECT_ID)     Table_Name
    9.   , IX.name                       AS Index_Name
    10.   , IX.type_desc                     Index_Type
    11.   , SUM(PS.[used_page_count]) * 8    IndexSizeKB
    12.   , IXUS.user_seeks               AS NumOfSeeks
    13.   , IXUS.user_scans               AS NumOfScans
    14.   , IXUS.user_lookups             AS NumOfLookups
    15.   , IXUS.user_updates             AS NumOfUpdates
    16.   , IXUS.last_user_seek           AS LastSeek
    17.   , IXUS.last_user_scan           AS LastScan
    18.   , IXUS.last_user_lookup         AS LastLookup
    19.   , IXUS.last_user_update         AS LastUpdate
    20. FROM
    21.     sys.indexes IX
    22.     INNER JOIN
    23.         sys.dm_db_index_usage_stats IXUS
    24.         ON
    25.             IXUS.index_id      = IX.index_id
    26.             AND IXUS.OBJECT_ID = IX.OBJECT_IDINNER
    27.     JOIN
    28.         sys.dm_db_partition_stats PS
    29.         on
    30.             PS.object_id=IX.object_id
    31. WHERE
    32.     OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
    33.     and IX.OBJECT_ID                           = @tableId GROUP BY OBJECT_NAME(IX.OBJECT_ID)
    34.   , IX.name
    35.   , IX.type_desc
    36.   , IXUS.user_seeks
    37.   , IXUS.user_scans
    38.   , IXUS.user_lookups
    39.   , IXUS.user_updates
    40.   , IXUS.last_user_seek
    41.   , IXUS.last_user_scan
    42.   , IXUS.last_user_lookup
    43.   , IXUS.last_user_update sqlserver中类似oracle的dba_source的视图是sys.sql_modules
    复制代码


    76. 查询某个数据库下的表数据占用磁盘容量最大的10张表  use XX if exists
    1.     (
    2.         select
    3.             1
    4.         from
    5.             tempdb..sysobjects
    6.         where
    7.             id       =object_id('tempdb..#tabName')
    8.             and xtype='u'
    9.     )
    10. drop table #tabName go
    11. create table #tabName
    12.     (
    13.         table_name    varchar(100)
    14.       , rowsNum       varchar(100)
    15.       , reserved_size varchar(100)
    16.       , data_size     varchar(100)
    17.       , index_size    varchar(100)
    18.       , unused_size   varchar(100)
    19.     )
    20.     declare @name varchar
    21.     (
    22.         100
    23.     )
    24.     declare cur cursor for
    25. select
    26.     name
    27. from
    28.     sysobjects
    29. where
    30.     xtype='u'
    31. order by
    32.     name open cur
    33. fetch next
    34. from
    35.     cur
    36. into
    37.     @name while @@fetch_status=0 begin
    38. insert into #tabName exec sp_spaceused @name
    39. fetch next
    40. from
    41.     cur
    42. into
    43.     @name
    44. end close cur deallocate cur
    45. select
    46.     top 10 table_name
    47.   , data_size
    48.   , rowsNum
    49.   , index_size
    50.   , unused_size
    51.   , reserved_size
    52.   , convert(int,SUBSTRING(data_size,0,LEN(data_size)-2)) size
    53. from
    54.     #tabName
    55. ORDER BY
    56.     size desc
    复制代码
    或
    1. select
    2.     top 10 a.tablename
    3.   , a.SCHEMANAME
    4.   , sum(a.TotalSpaceMB) TotalSpaceMB
    5.   , sum(a.RowCounts)    RowCounts
    6. from
    7.     (
    8.         SELECT
    9.             t.NAME                                                                                     AS TableName
    10.           , s.Name                                                                                     AS SchemaName
    11.           , p.rows                                                                                     AS RowCounts
    12.           , SUM(a.total_pages) * 8                                                                     AS TotalSpaceKB
    13.           , CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))                     AS TotalSpaceMB
    14.           , SUM(a.used_pages) * 8                                                                      AS UsedSpaceKB
    15.           , CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))                      AS UsedSpaceMB
    16.           , (SUM(a.total_pages) - SUM(a.used_pages)) * 8                                               AS UnusedSpaceKB
    17.           , CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    18.         FROM
    19.             sys.tables t
    20.             INNER JOIN
    21.                 sys.indexes i
    22.                 ON
    23.                     t.OBJECT_ID = i.object_id
    24.             INNER JOIN
    25.                 sys.partitions p
    26.                 ON
    27.                     i.object_id    = p.OBJECT_ID
    28.                     AND i.index_id = p.index_id
    29.             INNER JOIN
    30.                 sys.allocation_units a
    31.                 ON
    32.                     p.partition_id = a.container_id
    33.             LEFT OUTER JOIN
    34.                 sys.schemas s
    35.                 ON
    36.                     t.schema_id = s.schema_id
    37.         WHERE
    38.             t.NAME       NOT LIKE 'dt%'
    39.             AND t.is_ms_shipped = 0
    40.             AND i.OBJECT_ID     > 255
    41.         GROUP BY
    42.             t.Name
    43.           , s.Name
    44.           , p.Rows
    45.     )
    46.     a
    47. GROUP BY
    48.     a.tablename
    49.   , a.SCHEMANAME
    50. order by
    51.     sum(a.TotalSpaceMB) desc
    复制代码
    --这个比上一个专业
    77. 查询某个数据库中是否有create index '+name+ CHAR(10)

    2. select
    3.     'use '+name+ CHAR(10) +'
    4. select
    5.     DB_NAME()
    6.   , OBJECT_NAME(OBJECT_ID)
    7.   , definition
    8. from
    9.     '+name+'.sys.sql_modulesWHERE objectproperty(OBJECT_ID, ''IsProcedure'') = 1 AND definition like ''%online%=%on%'' and definition like ''%index%''' from sys.databases
    10. ;
    复制代码
    78. 根据id号查询某个数据库名

    2. SELECT DB_NAME(18)
    3. 根据id号查询某个对象名

    5. SELECT OBJECT_NAME(1769220894)
    复制代码
    79. 查看收缩的进度100%，此语句要到指定的数据库下执行

    2. SELECT
    3.     DB_NAME(database_id) AS Exec_DB
    4.   , percent_complete
    5.   , CASE
    6.         WHEN estimated_completion_time < 36000000THEN '0'
    7.             ELSE ''
    8.     END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
    9.   , b.text                                                                                                                                                                                    as tsql
    10.   , *
    11. FROM
    12.     SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')ORDER BY 2 DESC
    复制代码
    80. 查看重新组织索引的100%进度
    1. SELECT DB_NAME(database_id) AS Exec_DB
    2.         ,percent_complete
    3.         ,CASE
    4.                 WHEN estimated_completion_time < 36000000
    5.                         THEN '0'
    6.                 ELSE ''
    7.                 END + RTRIM(estimated_completion_time / 1000 / 3600) + ':' + RIGHT('0' + RTRIM((estimated_completion_time / 1000) % 3600 / 60), 2) + ':' + RIGHT('0' + RTRIM((estimated_completion_time / 1000) % 60), 2) AS [Time Remaining]
    8.         ,b.TEXT AS tsql
    9.         ,*
    10. FROM SYS.DM_EXEC_REQUESTScross
    11. APPLY sys.dm_exec_sql_text(sql_handle) AS bWHERE command LIKE '%REORGANIZE%' --and database_id=db_id('cardorder')ORDER BY 2 DESC
    复制代码
    81. 查看存储过程的执行计划
    1. SELECT
    2.     d.object_id
    3.   , DB_NAME(d.database_id) DBName
    4.   , OBJECT_NAME(object_id, database_id) 'SPName'
    5.   , d.cached_time
    6.   , d.last_execution_time
    7.   , d.total_elapsed_time /1000000                    AS total_elapsed_time
    8.   , d.total_elapsed_time / d.execution_count/1000000 AS [avg_elapsed_time]
    9.   , d.last_elapsed_time  /1000000                    AS last_elapsed_time
    10.   , d.execution_count
    11.   , d.total_physical_reads
    12.   , d.last_physical_reads
    13.   , d.total_logical_writes
    14.   , d.last_logical_reads
    15.   , et.text        SQLText
    16.   , eqp.query_plan executionplan
    17. FROM
    18.     sys.dm_exec_procedure_stats AS d CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp
    19. WHERE
    20.     OBJECT_NAME(object_id, database_id) = 'xxxx' ORDER BY [total_worker_time] DESC
    21. ;
    复制代码
    82. 查看当前用户
    1. select system_user
    复制代码
    83. 查询ddl修改操作的记录
    -执行如下找到trace文件的目录和名称
    1. select * from Sys.traces
    复制代码
    回复

    使用道具 举报

    返回列表 发新帖
    高级模式
    B Color Image Link Quote Code Smilies
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    运维联盟俱乐部 ( 冀ICP备19036648号 )

    GMT+8, 2024-4-29 16:21 , Processed in 0.052411 second(s), 21 queries , Gzip On.

    Powered by Discuz! X3.4

    © 2001-2023 Discuz! Team.

    快速回复 返回顶部 返回列表