bedbmsguru

--DB별 버퍼풀 사용량

SELECT 

    DB_NAME(database_id) AS [Database Name]

    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

WHERE database_id not in (1,3,4) -- system databases

AND database_id <> 32767 -- ResourceDB

GROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] 

DESC OPTION (RECOMPILE);

--DB에서 테이블별 버퍼풀 사용량

;WITH src AS

SELECT object_name (sp. object_id) as object_name ,name as stats_name , sp .stats_id,

    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter

FROM sys .stats AS s

CROSS APPLY sys. dm_db_stats_properties(s .object_id, s. stats_id) AS sp

WHERE sp .object_id > 100;

SELECT schema_name (schema_id) AS SchemaName,  object_name(o .object_id) AS ObjectName,

    i.name AS IndexName, index_id, o.type ,

    STATS_DATE(o .object_id, index_id) AS statistics_update_date

FROM sys .indexes i join sys. objects o

       on i .object_id = o .object_id

WHERE o .object_id > 100 AND index_id > 0

  AND is_ms_shipped = 0;

 $ServerArray = "test-dev" , "test"    # place computername here for remote access

$username = '계정'

$password = '암호'

$desc = '백업용'

foreach ($server in $ServerArray)

    try

        $computer = [ADSI ]"WinNT://$server ,computer"

        $user = $computer. Create("user", $username)

        $user.SetPassword( $password)

        $user.Setinfo()

        $user.description = $desc

        #$user.UserFlags = 65536  #암호사용기간 제한없음

        $user.PasswordExpired = 1 #다음번 로그인시 암호변경해야함

        $user.SetInfo()

        $group = [ADSI ]("WinNT:// $server/administrators,group")

        $group.add( "WinNT://$username,user" )

        Write-Host $server + "\t" + "완료"

    catch

        Write-Host $server + "`t" + $_. Exception.Message;

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName

      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]

      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]

      ,sum( execution_count) AS Execution_Count

      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU

      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED

      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS

      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES

      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS

                  , GETDATE () AS write_time

FROM sys .dm_exec_procedure_stats

group by

CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END

      ,OBJECT_SCHEMA_NAME( object_id,database_id )

      ,OBJECT_NAME( object_id,database_id )

ORDER BY AVG_LOGICAL_READS DESC

  

CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT](

                 [DBName] [nvarchar] (128) NULL,

                 [SCHEMA_NAME] [nvarchar] (128) NULL,

                 [OBJECT_NAME] [nvarchar] (128) NULL,

                 [Execution_Count] [bigint] NULL,

                 [AVG_CPU] [bigint] NULL,

                 [AVG_ELAPSED] [bigint] NULL,

                 [AVG_LOGICAL_READS] [bigint] NULL,

                 [AVG_LOGICAL_WRITES] [bigint] NULL,

                 [AVG_PHYSICAL_READS] [bigint] NULL,

                 [write_time] [datetime] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT_TEMP] (

                 [DBName] [nvarchar] (128) NULL,

                 [SCHEMA_NAME] [nvarchar] (128) NULL,

                 [OBJECT_NAME] [nvarchar] (128) NULL,

                 [Execution_Count] [bigint] NULL,

                 [AVG_CPU] [bigint] NULL,

                 [AVG_ELAPSED] [bigint] NULL,

                 [AVG_LOGICAL_READS] [bigint] NULL,

                 [AVG_LOGICAL_WRITES] [bigint] NULL,

                 [AVG_PHYSICAL_READS] [bigint] NULL,

                 [write_time] [datetime] NOT NULL

) ON [PRIMARY]

  

create procedure usp_chk_proc_execute_count

SET NOCOUNT ON;

INSERT INTO TBL_CHECK_PROC_EXECUTION_COUNT (DBName, [SCHEMA_NAME], [OBJECT_NAME], Execution_Count,

AVG_CPU, AVG_ELAPSED, AVG_LOGICAL_READS, AVG_LOGICAL_WRITES, AVG_PHYSICAL_READS, write_time)

SELECT A .DBName, B. [SCHEMA_NAME], B.[OBJECT_NAME] , B .Execution_Count - A .Execution_Count AS Execution_Count

,B. AVG_CPU,B .AVG_ELAPSED, B. AVG_LOGICAL_READS, B.AVG_LOGICAL_WRITES ,B. AVG_PHYSICAL_READS

, getdate () as write_time

FROM TBL_CHECK_PROC_EXECUTION_COUNT_TEMP AS A

inner JOIN

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName

      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]

      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]

      ,sum( execution_count) AS Execution_Count

      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU

      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED

      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS

      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES

      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS

                  , GETDATE () AS write_time

FROM sys .dm_exec_procedure_stats

group by

CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END

      ,OBJECT_SCHEMA_NAME( object_id,database_id )

      ,OBJECT_NAME( object_id,database_id )

HAVING sum (execution_count) > 50000

)AS B

ON A .DBName = B .DBName

AND A .[OBJECT_NAME] = B .[OBJECT_NAME]

truncate table TBL_CHECK_PROC_EXECUTION_COUNT_TEMP

insert into TBL_CHECK_PROC_EXECUTION_COUNT_TEMP

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName

      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]

      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]

      ,sum( execution_count) AS Execution_Count

      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU

      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED

      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS

      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES

      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS

                  , GETDATE () AS write_time

FROM sys .dm_exec_procedure_stats

group by

CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END

      ,OBJECT_SCHEMA_NAME( object_id,database_id )

      ,OBJECT_NAME( object_id,database_id )

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;