일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
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 |
31 |
- 윈도우즈 터미널
- rc-service
- go
- DotNet
- docker
- SQL Server
- ms sql server 2022
- elasticSearch
- option pattern
- User
- 무선 디버깅
- Flutter
- otel
- opentelemetry
- log
- sqlc
- Android
- version 2
- vscode
- AnimatedPositioned
- openrc
- 옵션 패턴
- 맥미니
- shared_preload_libraries
- user-secrets
- mysql
- golang
- pg_stat_statements
- module upgrade
- Linux
- 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 |