ALL About TempDB

For TempDB

--Determining the amount of free space in tempdb

USE tempdb; 

SELECT SUM(unallocated_extent_page_count) AS [free pages],  

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] 

FROM sys.dm_db_file_space_usage;

 

--Determining the Amount of Space Used by User Objects

USE tempdb; 

SELECT SUM(user_object_reserved_page_count) AS [user object pages used], 

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] 

FROM sys.dm_db_file_space_usage;

 

use tempdb 

SELECT GETDATE() AS runtime

    ,(SUM(user_object_reserved_page_count) * 8)/1024 AS usr_obj_mb

    ,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb

    ,(SUM(version_store_reserved_page_count) * 8)/1024 AS version_store_mb

    ,(SUM(unallocated_extent_page_count) * 8)/1024 AS freespace_mb

    ,(SUM(mixed_extent_page_count) * 8)/1024 AS mixedextent_mb

FROM sys.dm_db_file_space_usage

 

use tempdb  

SELECT session_id,

    SUM(internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask,

    SUM(internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask,

    SUM(user_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask,

    SUM(user_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask

FROM sys.dm_db_task_space_usage

GROUP BY session_id

ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC

 

select * from sys.dm_db_task_space_usage

select *,db_name(database_id) from sys.dm_db_session_space_usage

 

Alter TempDB File Size

use master

ALTER DATABASE tempdb

MODIFY FILE (Name=N'tempdev', size = 176080MB)

For further information, click on the following link.

https://clotat.blogspot.com/2020/05/all-about-tempdb.html

SQL Server Statistics

SQL Server Statistics

 

-- Turn on/off statistics IO
SET STATISTICS IO ON 

SET STATISTICS IO OFF 

-- Turn on/off statistics TIME
SET STATISTICS TIME ON 

SET STATISTICS TIME OFF 

-- Turn on/off profile information.

Note: It displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.

SET STATISTICS PROFILE ON

SET STATISTICS PROFILE OFF

--Find the statistics of all the objects where modify count is > 1000

SELECT obj.name 'table name', obj.object_id, stat.name 'stats name', schema_name(obj.schema_id)+'.'+obj.name as 'full table name', stat.stats_id, last_updated, modification_counter FROM sys.objects AS obj INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE modification_counter > 1000 order by modification_counter desc;

--Get the row count of the table.

DECLARE @TableName sysname SET @TableName = 'RCHOWN.7_SERVICE' SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(@TableName) AND index_id < 2 GROUP BY OBJECT_NAME(object_id);

--Update statistics with full scan.

UPDATE STATISTICS ARCHOWN.PRIME17_SERVICES WITH FULLSCAN;

--Count the number of rows in a table

Use DBName

DECLARE @TableName sysname SET @TableName = 'schema.TableName' SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(@TableName) AND index_id < 2 GROUP BY OBJECT_NAME(object_id);

--Size of all tables in DB

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

--DB file info in a Drive

select db_name(database_id) 'Db_Name', name 'Logical_File_Name', (cast(size as float)*8)/1024/1024 as FILE_SIZE_GB,physical_name from sys.master_files where physical_name like 'R%' order by 2 desc;

--Space used per DB file in MB

Use <DBName>

SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files order by 4 desc;

--Space used per DB file in GB with % of free space

Use <DBName>

SELECT DB_NAME() AS DbName, name AS FileName, size/(128.0*1024) AS CurrentSizeGB, (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/(128.0))/1024 as 'Free space in GB', ((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)*100)/(size/128.0) AS 'FreeSpace in %'

FROM sys.database_files order by 3 desc;

--Databases file with size and free space

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,physical_name FROM sys.database_files WHERE type IN (0,1);

--TO check Filegroups 

SELECT [databasefile].NAME      AS [FileName],

  [filegroup].NAME       AS [File_Group_Name],

  [filegroup].type_desc,

  physical_name [Data File Location],

  size / 128    AS [Size_in_MB],

  state_desc    [State of FILE],

  growth        [Data file growth]

FROM   sys.database_files [databasefile]

  INNER JOIN sys.filegroups [filegroup]

          ON [databasefile].data_space_id = [filegroup].data_space_id order by 2

--All Table Size in a DB 

SELECT 

    o.name AS ObjectName,

    s.name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    SUM(a.data_pages) * 8 AS DataSpaceKB

FROM 

    sys.objects o

JOIN 

    sys.schemas s ON o.schema_id = s.schema_id

JOIN 

    sys.partitions p ON o.object_id = p.object_id

JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

    o.type = 'U' -- U = User tables

GROUP BY 

    o.name, s.name, p.rows

ORDER BY 

    TotalSpaceKB DESC;

--All Indexes Size in a DB 

SELECT 

    o.name AS ObjectName,

    i.name AS IndexName,

    SUM(ps.used_page_count) * 8 AS UsedSpaceKB,

    SUM(ps.reserved_page_count) * 8 AS ReservedSpaceKB

FROM 

    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps

JOIN 

    sys.objects AS o ON ps.object_id = o.object_id

JOIN 

    sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id

GROUP BY 

    o.name, i.name

ORDER BY 

    UsedSpaceKB DESC;

Partition Details for a Table

SELECT 

    p.partition_number,

    p.rows,

    f.name AS filegroup_name

FROM sys.partitions p

JOIN sys.objects o ON p.object_id = o.object_id

JOIN sys.indexes i ON o.object_id = i.object_id AND i.index_id = p.index_id

JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id

JOIN sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id

JOIN sys.destination_data_spaces dds ON ps.partition_scheme_id = dds.partition_scheme_id

JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id

WHERE o.name = 'YourTableName'  -- Replace with your table name

ORDER BY p.partition_number;