Tag Archives: sys.partitions

Get row count for tables quickly in SQL Server

Get row count for tables quickly in SQL Server

RowCountAs a DBA, This is my prime responsibility to keep track of table size and number of rows it has. If I notice any unusual increase or decrease in size or number of rows in table, I must have to investigate this.

I have seen people used to execute COUNT() aggregate function on table to know the total number of rows in table. This operation may affect performance of server if table is having billions of row.

I don’t recommend COUNT() aggregate function on table to know total number of rows in table. I recommend to use sys.partition and sys.allocation_units catalog view along with sys.tables and sys.schemas catalog view.

I have created one user defined table valued function which do this job for me. It is much faster then COUNT() aggregate for sure.

CREATE FUNCTION GetRowCount()
RETURNS TABLE
RETURN
SELECT
sch.name AS SchemaName,
SysTab.Name AS TableName,
sch.name + '.' + SysTab.name AS QualifiedName,
SUM(
CASE
WHEN (Parti.index_id < 2) AND (alloUni.type = 1) THEN Parti.rows
ELSE 0
END
) AS Rows
FROM sys.partitions AS Parti
INNER JOIN sys.allocation_units AS alloUni ON Parti.partition_id = alloUni.container_id
INNER JOIN sys.tables AS SysTab ON SysTab.object_id = Parti.Object_ID
INNER JOIN sys.schemas AS sch ON sch.schema_id = SysTab.schema_id
GROUP BY SysTab.name, sch.name
GO

Once you create “GetRowCount()” table valued function in your database, you can query it like this:

SELECT * FROM GetRowCount()
--you can even filter this function
--to get value for specific table for eg:
--WHERE TableName='SalesOrderDetail'
ORDER BY Rows DESC
GO

This is one of fastest approach to know the total number of rows in table. I have already written two articles related to table size, if you are interested, have a look:

  • Calculate Table & Schema size in SQL Server (Click Here)
  • List of Table in Filegroup with table size in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

List of Table in Filegroup with table size in SQL Server

List of Table in Filegroup with table size in SQL Server

Size

Sometime back I have provided the script to calculate the size of each schema and table in SQL Server and move index efficiently to different filegroup in different drive but today I come up with little different script. I had requirement to know list of table under each file group along with size of that table and name of clustered index if any.

 

I have used sys.Allocation_Units, sys.Partitions and sys.indexes system table to find the detail I wanted to know.

 

As per MSDN:

 

“sys.Partitions” contains a row for each partition of all the tables and most types of indexes in the database. Special index types like Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

 

“sys.Allocation_Units” contains a row for each allocation unit in database.

 

“sys.indexes” contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

 

So, if we have to get list of filegroup and table name with size, we can utilize sys.partitions and sys.allocation_units. We have had JOIN with sys.indexes as we wanted to know name of clustered index on the table, if available.

 

Here is the small script I am using to cater my need:

 


SELECT

FILEGROUP_NAME(AU.data_space_id) AS FileGroupName,

OBJECT_NAME(Parti.object_id) AS TableName,

ind.name AS ClusteredIndexName,

AU.total_pages/128 AS TotalTableSizeInMB,

AU.used_pages/128 AS UsedSizeInMB,

AU.data_pages/128 AS DataSizeInMB

FROM sys.allocation_units AS AU

INNER JOIN sys.partitions AS Parti ON AU.container_id = CASE WHEN AU.type in(1,3) THEN Parti.hobt_id ELSE Parti.partition_id END

LEFT JOIN sys.indexes AS ind ON ind.object_id = Parti.object_id AND ind.index_id = Parti.index_id

ORDER BY TotalTableSizeInMB DESC

 

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Calculate Table & Schema size in SQL Server

Calculate Table & Schema size in SQL Server :

Calculate Table & Schema Size

Calculate Table & Schema Size

Keeping watch on database size, drive size, table size, index size etc. are few of the primary duty of DBA. I have already written few articles to cover this type of subject, for example:

Keep watch on your disk size

Keep watch on your database size

Calculate size of index

Today I am going to introduce two more TSQL Script which covers the size of each Table and size of each Schema in your SQL Server database. I used to have log of table size for each date so that I can compare the growth of table for future disk & other resource planning. It is really very mandatory for DBA to know the actual size of each table along with total number or rows available in that table, in order to make operation smooth and successful.

I have two TSQL Scripts which I use for getting an alert. You can set either of these scripts in SQL Server Job to get alert, or log data coming from these scripts, every day, in one table and use that table for future reference while taking any hardware related decision.

--SCRIPT NO 1

--Get total size of table and row count for each table

SELECT

SCHEMA_NAME(sysTab.SCHEMA_ID) as SchemaName,

sysTab.NAME AS TableName,

parti.rows AS RowCounts,

SUM(alloUni.total_pages) * 8 AS TotalSpaceKB,

SUM(alloUni.used_pages) * 8 AS UsedSpaceKB,

(SUM(alloUni.total_pages) - SUM(alloUni.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables sysTab

INNER JOIN

sys.indexes ind ON sysTab.OBJECT_ID = ind.OBJECT_ID and ind.Index_ID<=1

INNER JOIN

sys.partitions parti ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id

INNER JOIN

sys.allocation_units alloUni ON parti.partition_id = alloUni.container_id

WHERE

sysTab.is_ms_shipped = 0

AND ind.OBJECT_ID > 255

AND parti.rows>0

GROUP BY

sysTab.Name, parti.Rows,sysTab.SCHEMA_ID

Order BY parti.rows desc

--SCRIPT NO 2

--Get total size of each schema available in your SQL Server database

SELECT

SCHEMA_NAME(sysTab.SCHEMA_ID) as SchemaName,

SUM(alloUni.total_pages) * 8 AS TotalSpaceKB,

SUM(alloUni.used_pages) * 8 AS UsedSpaceKB,

(SUM(alloUni.total_pages) - SUM(alloUni.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables sysTab

INNER JOIN

sys.indexes ind ON sysTab.OBJECT_ID = ind.OBJECT_ID  and ind.Index_ID<=1

INNER JOIN

sys.partitions parti ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id

INNER JOIN

sys.allocation_units alloUni ON parti.partition_id = alloUni.container_id

WHERE

sysTab.is_ms_shipped = 0

AND ind.OBJECT_ID > 255

AND parti.rows>0

GROUP BY

sysTab.SCHEMA_ID

ORDER BY

TotalSpaceKB DESC

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find locked table name with duration and count in SQL Server

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

	CREATE VIEW WhatIsLocked
	AS
	SELECT
	Locks.request_session_id AS SessionID,
	Obj.Name AS LockedObjectName,
	DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
	ActTra.Transaction_begin_time,
	COUNT(*) AS Locks
	FROM sys.dm_tran_locks Locks
	JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
	JOIN sys.objects Obj ON Obj.object_id = Parti.object_id
	JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
	JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
	JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
	WHERE resource_database_id = db_id() AND Obj.Type = 'U';
	GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name

You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.

I have written one more article in past which displays lock table, table with  'SCH-M' lock and which command causing the lock. read article in detail, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations