bedbmsguru

SELECT
SERVERPROPERTY('ServerName') AS sql_instance,
s.session_id
,r.STATUS
,CONVERT(varchar,r.start_time,20) start_time
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time 
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time 
,r.open_transaction_count
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID
ORDER BY start_time

  

alter  proc sp_list_server_property

DECLARE @props TABLE ( propertyname sysname PRIMARY KEY)

INSERT INTO @props( propertyname )

SELECT 'BuildClrVersion'

SELECT 'Collation'

SELECT 'CollationID'

SELECT 'ComparisonStyle'

SELECT 'ComputerNamePhysicalNetBIOS'

SELECT 'Edition'

SELECT 'HadrManagerStatus'

SELECT 'EngineEdition'

SELECT 'InstanceName'

SELECT 'IsClustered'

SELECT 'IsFullTextInstalled'

SELECT 'IsIntegratedSecurityOnly'

SELECT 'IsSingleUser'

SELECT 'LCID'

SELECT 'LicenseType'

SELECT 'MachineName'

SELECT 'NumLicenses'

SELECT 'ProcessID'

SELECT 'ProductVersion'

SELECT 'ProductLevel'

SELECT 'ResourceLastUpdateDateTime'

SELECT 'ResourceVersion'

SELECT 'ServerName'

SELECT 'SqlCharSet'

SELECT 'SqlCharSetName'

SELECT 'SqlSortOrder'

SELECT 'SqlSortOrderName'

SELECT 'FilestreamShareName'

SELECT 'FilestreamConfiguredLevel'

SELECT 'FilestreamEffectiveLevel'

SELECT propertyname , SERVERPROPERTY ( propertyname ) FROM @props

 UNION ALL

SELECT 'EditionID' AS  propertyname,

                                 CASE SERVERPROPERTY ( 'EditionID' )

                                                 WHEN 1804890536 THEN 'Enterprise'

                                                 WHEN 1872460670 THEN 'Enterprise With CORE Base Liserence'

                                                 WHEN 610778273 THEN 'Enterprise Evaluation'

                                                 WHEN 284895786 THEN 'Business Intelligence'

                                                 WHEN - 2117995310 THEN 'Developer'

                                                 WHEN - 1592396055 THEN 'Express'

                                                 WHEN - 133711905 THEN 'Express with Advanced Services'

                                                 WHEN - 1534726760 THEN 'Standard'

                                                 WHEN 1293598313 THEN 'WEB'

                                 END AS EditionId

UNION ALL                                                                                                           

SELECT 'IsHadrEnabled' AS propertyname,

                                 CASE SERVERPROPERTY ( 'IsHadrEnabled' )

                                                 WHEN 0 THEN 'AlwaysOn 가용성 그룹 기능을 사용하지 않습니다.'

                                                 WHEN 1 THEN 'AlwaysOn 가용성 그룹 기능을 사용합니다'

                                 END AS IsHadrEnabled

UNION ALL                                                                                                           

SELECT 'HadrManagerStatus' AS propertyname,

                                 CASE SERVERPROPERTY ( 'HadrManagerStatus' )

                                                 WHEN 0 THEN '시작되지 않았습니다. 통신 보류 중입니다.'

                                                 WHEN 1 THEN '시작되어 실행 중입니다.'

                                                 WHEN 2 THEN '시작되지 않고 실패했습니다.'

                                 END AS HadrManagerStatus

  

;WITH task_space_usage AS (

    -- SUM alloc/delloc pages

    SELECT session_id,

           request_id,

           SUM(internal_objects_alloc_page_count) AS alloc_pages,

           SUM(internal_objects_dealloc_page_count) AS dealloc_pages

    FROM sys.dm_db_task_space_usage WITH (NOLOCK)

    WHERE session_id <> @@SPID

    GROUP BY session_id, request_id

)SELECT TSU.session_id,

       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

       EST.text,

       -- Extract statement from sql text

       ISNULL(

           NULLIF(

               SUBSTRING(

                 EST.text, 

                 ERQ.statement_start_offset / 2, 

                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 

                  THEN 0 

                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END

               ), ''

           ), EST.text

       ) AS [statement text],

       EQP.query_plan

FROM task_space_usage AS TSU

INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)

    ON  TSU.session_id = ERQ.session_id

    AND TSU.request_id = ERQ.request_id

OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL ORDER BY 3 DESC;