Tamany estimat de consum de memoria de les taules d’un esquema:
SELECT SCHEMA_NAME, TABLE_NAME, SUM(ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL) Tamany, ROUND(SUM(ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "SIZE" FROM M_CS_TABLES WHERE SCHEMA_NAME = 'A20174_ECOALF_PRODL' GROUP BY SCHEMA_NAME, TABLE_NAME ORDER BY Tamany DESC
Tamany de les taules totals per base de dades (Font)
DO BEGIN DECLARE V_DBNAME varchar(20); DECLARE V_RCT_SIZE float; DECLARE V_RRT_SIZE float; SELECT database_name into V_DBNAME from "SYS"."M_DATABASES"; SELECT round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024/1024, 3) into V_RCT_SIZE FROM M_CS_TABLES; SELECT round(SUM(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) /1024/1024/1024, 3) into V_RRT_SIZE FROM M_RS_TABLES; SELECT V_DBNAME as DB_NAME, V_RCT_SIZE as "Column table size", V_RRT_SIZE as "Row table size", (V_RCT_SIZE + V_RRT_SIZE) as "All Tables GB Size" FROM DUMMY; END
Espai de memoria utilitzat pels esquemes d’una base de dades
DO BEGIN DECLARE V_DBNAME varchar(20); SELECT database_name into V_DBNAME from "SYS"."M_DATABASES"; select V_DBNAME as DB_NAME, SCHEMA_NAME, sum("Column table main and delta GB Used") as "GB Used per column table" from ( SELECT SCHEMA_NAME, round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024/1024, 3) AS "Column table main and delta GB Used" FROM M_CS_TABLES GROUP BY SCHEMA_NAME ) GROUP BY SCHEMA_NAME ORDER BY "GB Used per column table" DESC; END
Espai de memoria utilitzat per les taules (RowStore) dels esquemes d’una base de dades
DO BEGIN DECLARE V_DBNAME varchar(20); SELECT database_name into V_DBNAME from "SYS"."M_DATABASES"; select V_DBNAME as DB_NAME, SCHEMA_NAME, "Row table GB Used" from ( SELECT SCHEMA_NAME, round(SUM(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024/1024,3) AS "Row table GB Used" FROM M_RS_TABLES GROUP BY SCHEMA_NAME ) ORDER BY SCHEMA_NAME, "Row table GB Used" DESC; END
Consum total de memoria de les taules (10 majors consumidors)
SELECT TOP 10 SCHEMA_NAME, "TABLE_NAME", round("MEMORY_SIZE_IN_TOTAL"/1024/1024,3) as Size_MB FROM "SYS"."M_CS_TABLES" ORDER BY "MEMORY_SIZE_IN_TOTAL" DESC;