블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

 « 2025.2  »
일 월 화 수 목 금 토
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28
  • total
  • today
  • yesterday

통계 업데이트 날짜 조회하기

2018. 10. 27. 22:37 SQL SERVER

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;
  

저작자표시

'SQL SERVER' 카테고리의 다른 글

SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
posted by bedbmsguru

로컬( local)계정 생성후 Administrator 그룹에 넣기

2018. 10. 27. 22:36 Powershell

 $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;
    }
}

저작자표시

'Powershell' 카테고리의 다른 글

AD계정 정보 가져오기  (0) 2018.12.26
Powershell 방화벽 관리하기  (0) 2018.10.27
TCP 소켓 통신 예제  (0) 2018.10.27
Powershell 외부서버의 스크립트 실행하기  (0) 2018.05.25
posted by bedbmsguru

Procedure 실행횟수 확인

2018. 10. 27. 22:34 SQL SERVER

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
  


--정기적 체크를 위해 Batch Job으로 만들경우의 소스
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
AS
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 )
--ORDER BY AVG_LOGICAL_READS DESC

저작자표시

'SQL SERVER' 카테고리의 다른 글

bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
posted by bedbmsguru

티스토리툴바