2018. 10. 26. 21:26
SQL SERVER
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
[Indexsize(MB)],
TB_SIZE.[Tablesize(MB)],
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor],
st.avg_fragmentation_in_percent,
case when st.avg_fragmentation_in_percent > 30 then
'alter index ' + i.name + ' on ' + OBJECT_NAME(s.[object_id]) + ' rebuild with (online = on)'
else
'alter index ' + i.name + ' on ' + OBJECT_NAME(s.[object_id]) + ' REORGANIZE'
end AS QUERY
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
cross apply sys.dm_db_index_physical_stats(db_id(),s.[object_id], i.index_id, null, null) ST
INNER JOIN
(
SELECT
i.OBJECT_ID,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a .used_pages) / 1024 AS 'Indexsize(MB)'
FROM sys .indexes AS i
JOIN sys .partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys .allocation_units AS a ON a.container_id = p.partition_id
--where OBJECT_NAME (i. OBJECT_ID) ='TLogState'
GROUP BY i. OBJECT_ID,i .index_id, i.name
)AS IND_SIZE
ON i.object_id = IND_SIZE.object_id
AND i.index_id = IND_SIZE.IndexID
INNER JOIN
(
SELECT
i.object_id,
SUM(a .total_pages) * 8 /1024 AS 'Tablesize(MB)'
FROM sys. indexes i
INNER JOIN sys. partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys. allocation_units a ON p.partition_id = a.container_id
WHERE i.OBJECT_ID > 255
GROUP BY i.object_id
)AS TB_SIZE
ON TB_SIZE.object_id = I.object_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;
'SQL SERVER' 카테고리의 다른 글
| 파티션(partition) 테이블 현황조회 (0) | 2018.10.26 |
|---|---|
| SSMS의 Tip (0) | 2018.10.26 |
| 알아봐요 할 DMV (0) | 2018.09.19 |
| Troubleshooting transactional replication latency issues (0) | 2018.08.10 |
| Replication Subscription Type 확인 (0) | 2018.05.09 |