database, log, table, index size
카테고리 없음 / 2024. 2. 8. 13:46
-- database size, table size
SP_SPACEUSED
GO
-- log space
dbcc sqlperf(logspace)
go
dbcc loginfo
go
select * from sys.dm_db_log_info(6)
go
-- database size
SELECT
A.fileid,
A.groupid,
A.size,
A.maxsize,
A.growth,
A.status,
A.perf,
B.NAME DBNAME,
A.NAME DBNAMEDETAIL,
A.filename,
COMPUTEDSIZEMB = Cast(A.size / 128.AS INT),
COMPUTEDGROWTH = CASE WHEN A.status & 0X100000 = 0X100000 THEN A.growth ELSE Cast(A.growth / 128.AS INT) END,
COMPUTEDGROWTHUNIT = CASE WHEN A.status & 0X100000 = 0X100000 THEN 'PERCENT' ELSE 'MB' END
FROM
master.dbo.sysaltfiles A WITH (nolock)
JOIN master.dbo.sysdatabases B WITH (nolock) ON A.dbid = B.dbid
WHERE
B.NAME NOT IN (
'MASTER', 'MSDB', 'TEMPDB', 'MODEL',
'LAZYLOG'
)
-- database size
SELECT
Db_name() AS DBNAME,
NAME AS FILENAME,
type_desc,
size / 128.0 AS CURRENTSIZEMB,
size / 128.0 - Cast(
Fileproperty(NAME, 'SPACEUSED') AS INT
) / 128.0 AS FREESPACEMB
FROM
sys.database_files
WHERE
type IN (0, 1);
-- all database size
if object_id('tempdb..#filesize') is not null
drop table #filesize
create table #filesize
(
dbname nvarchar(128),
filename nvarchar(128),
type_desc nvarchar(128),
currentsizemb decimal(10, 2),
freespacemb decimal(10, 2)
);
go
insert into #filesize(dbname, filename, type_desc, currentsizemb, freespacemb)
exec sp_msforeachdb 'use [?];
select db_name() as dbname,
name as filename,
type_desc,
size/128.0 as currentsizemb,
size/128.0 - cast(fileproperty(name, ''spaceused'') as int)/128.0 as freespacemb
from sys.database_files
where type in (0,1);';
go
select * from #filesize where dbname not in ('distribution', 'master', 'model', 'msdb')
go
drop table #filesize;
go
-- fragmentation check takes a long time
SELECT
OBJECT_SCHEMA_NAME(IPS.OBJECT_ID) AS SCHEMA_NAME,
OBJECT_NAME(IPS.OBJECT_ID) AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
I.TYPE_DESC AS INDEX_TYPE,
IPS.AVG_FRAGMENTATION_IN_PERCENT,
IPS.AVG_PAGE_SPACE_USED_IN_PERCENT,
IPS.PAGE_COUNT,
IPS.ALLOC_UNIT_TYPE_DESC
FROM
SYS.DM_DB_INDEX_PHYSICAL_STATS(
DB_ID(),
DEFAULT,
DEFAULT,
DEFAULT,
'SAMPLED'
) AS IPS
INNER JOIN SYS.INDEXES AS I ON IPS.OBJECT_ID = I.OBJECT_ID
AND IPS.INDEX_ID = I.INDEX_ID
ORDER BY
PAGE_COUNT DESC;
GO
-- tempdb size
SELECT
CAST(
SUM(
(TOTAL_PAGE_COUNT) * 8
) / 1024.AS DECIMAL(25, 2)
) AS TOTAL_SIZE_MB,
--WORKS ON SQL 2012 AND FUTURE EDITIONS
CAST(
SUM(
(UNALLOCATED_EXTENT_PAGE_COUNT) * 8
) / 1024.AS DECIMAL(25, 2)
) AS FREE_SPACE_MB,
CAST(
SUM(
(
USER_OBJECT_RESERVED_PAGE_COUNT
) * 8
) / 1024.AS DECIMAL(25, 2)
) AS USER_OBJECTS_MB,
CAST(
SUM(
(
INTERNAL_OBJECT_RESERVED_PAGE_COUNT
) * 8
) / 1024.AS DECIMAL(25, 2)
) AS INTERNAL_OBJECTS_MB,
CAST(
SUM(
(
VERSION_STORE_RESERVED_PAGE_COUNT
) * 8
) / 1024.AS DECIMAL(25, 2)
) AS VERSION_STORE_MB,
CAST(
SUM(
(MIXED_EXTENT_PAGE_COUNT) * 8
) / 1024.AS DECIMAL(25, 2)
) AS MIXED_EXTENT_MB
FROM
TEMPDB.SYS.DM_DB_FILE_SPACE_USAGE
GO
-- tempdb capacity used by sessions
SELECT
GETDATE() AS RUNTIME,
A.*,
B.KPID,
B.BLOCKED,
B.LASTWAITTYPE,
B.WAITRESOURCE,
DB_NAME(B.DBID) AS DATABASE_NAME,
B.CPU,
B.PHYSICAL_IO,
B.MEMUSAGE,
B.LOGIN_TIME,
B.LAST_BATCH,
B.OPEN_TRAN,
B.STATUS,
B.HOSTNAME,
B.PROGRAM_NAME,
B.CMD,
B.LOGINAME,
REQUEST_ID
FROM
SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A
INNER JOIN SYS.SYSPROCESSES B ON A.SESSION_ID = B.SPID
-- table size
SELECT
T.NAME AS TABLENAME,
S.NAME AS SCHEMANAME,
P.ROWS,
SUM(A.TOTAL_PAGES) * 8 AS TOTALSPACEKB,
CAST(
ROUND(
(
(
SUM(A.TOTAL_PAGES) * 8
) / 1024.00
),
2
) AS NUMERIC(36, 2)
) AS TOTALSPACEMB,
SUM(A.USED_PAGES) * 8 AS USEDSPACEKB,
CAST(
ROUND(
(
(
SUM(A.USED_PAGES) * 8
) / 1024.00
),
2
) AS NUMERIC(36, 2)
) AS USEDSPACEMB,
(
SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)
) * 8 AS UNUSEDSPACEKB,
CAST(
ROUND(
(
(
SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)
) * 8
) / 1024.00,
2
) AS NUMERIC(36, 2)
) AS UNUSEDSPACEMB
FROM
SYS.TABLES T
INNER JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID
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
LEFT OUTER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE
T.NAME NOT LIKE 'DT%'
AND T.IS_MS_SHIPPED = 0
AND I.OBJECT_ID > 255
GROUP BY
T.NAME,
S.NAME,
P.ROWS
ORDER BY
TOTALSPACEMB DESC,
T.NAME
-- index stats takes a long time (commentout ,STAT3.*)
IF OBJECT_ID('SP_HELPINDEX2') IS NULL
EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO
ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- SCRIPT BY MINSOUK KIM
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
,PS.NAME AS PTNFUNCNAME
,D.PARTITIONCNT AS PTNCNT
,T1.TYPE_DESC
,FI.NAME AS FGNAME
,SCHEMA_NAME(SCHEMA_ID) AS SNAME
,T1.CREATE_DATE AS TBLCREDATE
,T1.NAME AS OBJNAME
,I.TYPE_DESC ISCLUSTERORHEAP
,I.NAME AS IXNAME
,INDEXSIZEKB AS IXSIZEKB
,SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
,SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
,STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
,I.IS_UNIQUE
,I.IS_UNIQUE_CONSTRAINT
,I.IS_PRIMARY_KEY
,I.HAS_FILTER
,I.FILTER_DEFINITION
,I.IS_DISABLED
,STAT1.*
,STAT2.*
,STAT3.*
-- , *
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
-- AND I.[TYPE] IN (0,1)
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE
WHEN IS_DESCENDING_KEY = 1
THEN ' DESC'
ELSE ' ASC'
END AS [text()]
FROM SYS.INDEX_COLUMNS IC
INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
AND IC.COLUMN_ID = AC.COLUMN_ID
WHERE IC.OBJECT_ID = I.OBJECT_ID
AND IC.INDEX_ID = I.INDEX_ID
AND IC.IS_INCLUDED_COLUMN = 0
ORDER BY IC.INDEX_COLUMN_ID
FOR XML PATH('')
) B(INDEX_COLUMN_DESC)
OUTER APPLY (
SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
FROM SYS.INDEX_COLUMNS IC
INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
AND IC.COLUMN_ID = AC.COLUMN_ID
WHERE IC.OBJECT_ID = I.OBJECT_ID
AND IC.INDEX_ID = I.INDEX_ID
AND IC.IS_INCLUDED_COLUMN = 1
ORDER BY IC.INDEX_COLUMN_ID
FOR XML PATH('')
) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
SELECT COUNT(*)
FROM SYS.TABLES AS T
INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
AND I.INDEX_ID = P.INDEX_ID
INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
AND R.BOUNDARY_ID = P.PARTITION_NUMBER
WHERE T.NAME = T1.NAME
AND I.TYPE <= 1
) D(PARTITIONCNT)
OUTER APPLY (
SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
FROM SYS.INDEXES AS III
INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
AND P.INDEX_ID = I.INDEX_ID
INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
WHERE III.OBJECT_ID = T1.OBJECT_ID
AND III.INDEX_ID = I.INDEX_ID
) E(INDEXSIZEKB)
OUTER APPLY (
SELECT TOP 1 *
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL)
WHERE 1 = CASE
WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
THEN 0
ELSE 1
END
) STAT2
OUTER APPLY (
SELECT TOP 1 *
FROM (
SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS
) A
WHERE OBJECT_ID = T1.OBJECT_ID
AND INDEX_ID = I.INDEX_ID AND DATABASE_ID = DB_ID()
) STAT1
OUTER APPLY (
SELECT TOP 1 *
FROM SYS.dm_db_index_physical_stats(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL, NULL)
) STAT3
WHERE IS_MS_SHIPPED = 0
AND T1.NAME = CASE
WHEN @NAME = ''
THEN T1.NAME
ELSE @NAME
END
ORDER BY 1
,2
,3
,4
,5
,6
,7
,I.INDEX_ID
GO
EXEC sp_helpindex2
GO