SQL SERVER – Script to Get Partition Info Using DMV

I consider myself very fortunate as I get to see a new system every other day when I am working with a Comprehensive Database Performance Health Check. During the consultation, one of the questions was is there any way to see the information about the partition for any database. Of course yes, here is the script which you can run to get necessary information tables which are partitioned in SQL Server.

SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],
t.name AS [Table Name],
i.name AS [Index Name],
s.name AS [Partition Scheme], *
FROM sys.indexes i
INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id
INNER JOIN sys.tables t ON i.object_id = t.object_id 

Every table in SQL Server has a single partition. If you run above script it will list only those tables which are partitioned by partition scheme and have more than one partitions.

Here are a few additional blog posts which are discussing the same subject:

SQL SERVER – Disabling 15000 Partitions (15k)
The stored procedure sp_db_increased_partitions was introduced in SQL Server 2008 SP2 and SQL Server 2008 R2 versions. This procedure takes the first parameter as the database name and the second parameter is true or false. The following query will enable 15K partition support for database my DB.

SQL SERVER – Adding Values WITH OVER and PARTITION BY
How do I sum values for each group without using GROUP BY in my query? I want to sum based on the one column but I do not want a sum of entire results. See the following image for additional explanation.

Database Table Partitioning Tutorial – How to Horizontal Partition Database Table
I have written a tutorial using my C: Drive, however, to take advantage of partition it is recommended that different filegroups are created on separate hard disk to get maximum performance advantage of partitioning. Before running following script, make sure C: drive contains two folders – Primary and Secondary as the following example has used those two folders to store different filegroups.

Disk Partition Alignment Best Practices
Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science.

Reference: Pinal Dave (https://blog.sqlauthority.com)