일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
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 | 29 | 30 |
- otel
- log
- openrc
- go
- vscode
- SQL Server
- pg_stat_statements
- IDX Project
- Flutter
- 옵션 패턴
- sqlc
- Android
- golang
- rc-service
- 윈도우즈 터미널
- Linux
- mysql
- version 2
- 맥미니
- opentelemetry
- shared_preload_libraries
- User
- docker
- elasticSearch
- DotNet
- 무선 디버깅
- option pattern
- module upgrade
- user-secrets
- AnimatedPositioned
- Today
- Total
CATCHV Blog
통계 확인 쿼리( DB 전체 5% 이상) 본문
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DBNAME
, ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS [Statistics Last Updated]
, s.rowcnt AS [Row Count]
, s.rowmodctr AS [Number Of Changes]
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS [% Rows Changed]
INTO #Statistics_Change
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
AND CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) > 5
AND 1= 2
ORDER BY [% Rows Changed] DESC
EXEC sp_MSForEachDB 'USE [?];
IF DB_NAME() NOT IN ( ''master'', ''msdb'', ''model'', ''tempdb'', ''distribution'' )
BEGIN
INSERT INTO #Statistics_Change
SELECT
DB_NAME() AS DBNAME
, ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS [Statistics Last Updated]
, s.rowcnt AS [Row Count]
, s.rowmodctr AS [Number Of Changes]
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS [% Rows Changed]
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
AND CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) > 5
ORDER BY [% Rows Changed] DESC
END
'
SELECT * FROM #Statistics_Change
ORDER BY [% Rows Changed] DESC
DROP TABLE #Statistics_Change
'Database > MS SQL' 카테고리의 다른 글
DBMIRROR_DBM_EVENT (0) | 2014.06.12 |
---|---|
MS SQL Server 서비스 계정 정책 권한 확인 (0) | 2014.05.30 |
MS SQL Replciation Red Mark 문제 (0) | 2014.02.11 |
trigger 및 Procedure 에서 텍스트 찾기 (0) | 2014.01.24 |
MS SQL User Mapping 정보 확인 (0) | 2014.01.06 |