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:

 

[sourcecode language=”sql”]

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

[/sourcecode]

 

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.

Move clustered index to different filegroup in SQL Server

Move clustered index to different filegroup in SQL Server

Disk IO system always play vital role in performance of SQL Server. No matter how powerful processor you have and how much RAM you have, if your disk doesn’t support transaction as per your need, you won’t get benefit of processor and RAM.

Recently, we have attached very fast SAN to our production server to replace RAID-5 array which had four hard disk of 10,000RPM. I wanted to transfer all clustered index to our new SAN system for few of our highly transactional table in the first phase.

I want to share how we can efficiently move index to a different filegroup in different drive.

Let us create one sample database for demonstration and create one table with sample data and index in newly created database.

[sourcecode language=”sql”]

CREATE DATABASE ExtremeAdvice
GO

USE ExtremeAdvice
GO

IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
 DROP TABLE orders
END
GO

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

–inserting 100000 fack rows into table
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
 DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
 ABS(a.object_id % 10),
 CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

–creating clustered index
CREATE CLUSTERED INDEX idx_Orders_refno ON orders(refno)
GO
[/sourcecode]

When you create database, by default there would be one default filegroup which is PRIMARY and default data file comes under PRIMARY filegroup.

Let us see where our index is located.

[sourcecode language=”sql”]
SELECT
obj.name AS TableName
, obj.type
, ind.name AS IndexName
, ind.index_id AS Index_ID
, files.name AS FileGroupName
FROM sys.indexes ind
INNER JOIN sys.filegroups files
ON ind.data_space_id = files.data_space_id
INNER JOIN sys.all_objects obj
ON ind.object_id = obj.object_id
WHERE ind.data_space_id = files.data_space_id
AND obj.type = ‘U’ — User Created Tables
GO
[/sourcecode]

Here is the screen capture for my database:

1FileGroup

Now, we shall create one more filegroup for our database and shall create one new datafile (.ndf) in new filegroup.

[sourcecode language=”sql”]
–add new filegroup
USE [master]
GO

ALTER DATABASE [ExtremeAdvice] ADD FILEGROUP [ExtremeAdviceIndex]
GO

–add new secondary data file to filegroup
USE [master]
GO

ALTER DATABASE [ExtremeAdvice]
ADD FILE (
NAME = N’ExtremeAdvice_Index’,
FILENAME = N’D:\DATA\ExtremeAdvice_Index.ndf’ ,
SIZE = 6144KB ,
FILEGROWTH = 1024KB )

TO FILEGROUP [ExtremeAdviceIndex]
GO
[/sourcecode]

Once you are done with new filegroup and data file. We shall CREATE INDEX with DROP_EXISTING = ON and we shall assign new filegroup while re-creating an index. Once you transfer clustered to different filegroup, all data will be transferred to new location with clustered index.

[sourcecode language=”sql”]
USE [ExtremeAdvice]
GO

CREATE CLUSTERED INDEX [idx_Orders_refno] ON [Orders]
(
[refno] ASC
)
WITH (
PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON,
ONLINE = ON,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON
) ON [ExtremeAdviceIndex]
GO
[/sourcecode]

Once, you are done with creating an index, you can confirm whether your index is moved to new location or not with following query.

[sourcecode language=”sql”]
SELECT
obj.name AS TableName
, obj.type
, ind.name AS IndexName
, ind.index_id AS Index_ID
, files.name AS FileGroupName
FROM sys.indexes ind
INNER JOIN sys.filegroups files
ON ind.data_space_id = files.data_space_id
INNER JOIN sys.all_objects obj
ON ind.object_id = obj.object_id
WHERE ind.data_space_id = files.data_space_id
AND obj.type = ‘U’ — User Created Tables
GO
[/sourcecode]

Here is the screen capture for my database.

2Filegroup

 

You can see that “idx_Orders_Refno” is  on “ExtremeAdviceIndex” FileGroup.

I have written some more articles about Index, if you want to refer, follow the links given:

  • Some basics about Index in SQL Server (Click Here)
  • Clustered Index in SQL Server (Click Here)
  • Nonclustered Index in SQL Server (Click Here)
  • Included Column Index with non clustered index in SQL Server 2005/2008/Denali (Click Here)
  • Filtered Index in SQL Server 2008/Denali (Click Here)
  • Cannot create index on view Msg 1940, Level 16, State 1, Line 1 (Click Here)
  • Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005 (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • Do you know Index Statistics in SQL Server (Click Here)
  • Index Rebuild or Reorganize in SQL Server (Click Here)
  • Index Fillfactor in SQL Server (Click Here)
  • Find missing Index with DMVs in SQL Server 2005/2008/Denali (Click Here)
  • Find unused index in SQL Server 2005/2008/Denali (Click Here)
  • sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005 (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.

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.

[sourcecode language=”sql”]
–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
[/sourcecode]

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