查看库的相关语句

 查看库

USE [Public_Data]
GO

/****** Object:  View [dbo].[V_查看库]    Script Date: 03/15/2012 10:26:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[V_查看库]  
as  
select * from ( 
select top 100 PERCENT db_name(dbid) '数据库', 
(select name from sys.master_files B where B.file_id=A.fileid and B.database_id=A.DBID) '逻辑文件名', 
NumberReads '读取次数', 
BytesRead '读取字节数', 
NumberWrites '写入次数', 
BytesWritten '写入字节数', 
BytesOnDisk/(1024)/(1024)/(1024) '物理字节数G' 
from Fn_virtualfilestats(-1,-1) as A 
order by 物理字节数G desc) as ghgt
GO

select * from [V_查看库]
USE [Public_Data]
GO

/****** Object:  View [dbo].[VV_查看当前正在执行的SQL语句]    Script Date: 03/15/2012 10:27:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[VV_查看当前正在执行的SQL语句]
as
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              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.

GO
USE [Public_Data]
GO

/****** Object:  View [dbo].[V_查看Sql执行效率]    Script Date: 03/15/2012 10:28:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create View [dbo].[V_查看Sql执行效率]
as

SELECT  top 100 percent DB_ID(DB.dbid) '数据库名'
      , OBJECT_ID(db.objectid) '对象'
      , QS.creation_time '编译计划的时间'
      , QS.last_execution_time '上次执行计划的时间'
      , QS.execution_count '执行的次数'
      , QS.total_elapsed_time / 1000 '占用的总时间（秒）'
      , QS.total_physical_reads '物理读取总次数'
      , QS.total_worker_time / 1000 'CPU 时间总量（秒）'
      , QS.total_logical_writes '逻辑写入总次数'
      , QS.total_logical_reads N'逻辑读取总次数'
      , QS.total_elapsed_time / 1000 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) AS '执行语句'
FROM    sys.dm_exec_query_stats AS QS CROSS APPLY
        sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
        ( SELECT    *
          FROM      sys.dm_exec_cached_plans cp CROSS APPLY
                    sys.dm_exec_query_plan(cp.plan_handle)
        ) DB
            ON QS.plan_handle = DB.plan_handle
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 QS.total_elapsed_time / 1000 DESC

 

GO