How to identify partitioned tables within a database

List of partitioned tables, indexes in database

select distinct
p.[object_id],
OBJECT_NAME(p.[object_id]) as table_name,
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i
on p.[object_id] = i.[object_id]
and p.index_id = i.index_id
inner join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
on ps.function_id = pf.function_id
order by table_name, index_name ;

List of partitioned tables along with partition column

SELECT distinct object_name (c.object_id) AS table_name,
c.[name] AS partitioned_column
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_ID=c.object_ID
AND ic.Column_ID=c.Column_ID
WHERE ic.Partition_ordinal=1

Guidelines for selecting indexes

• Frequency that data is retrieved from a table based upon the values of a specific column. The more often data is retrieved based on the values of a column within a table, the greater the need for an index on that column. The data may be specifically required, such as a social security number, or found in a range of values, such as order date.
• Whether the column is used to build joins with other tables. Joins are almost always guaranteed better performance when the join columns in both tables are indexed. For example, foreign key columns should have an index associated with them.
• When the data in the column is usually needed in the same order every time.
• When the values in the index leaf node can answer the query without going to the data node
• When columns have a large number of distinct values. Do not index columns that contain relatively few distinct values

Top 10 executed SP’s by logical reads (memory pressure)

SELECT TOP 10 qt.text AS ‘SP Name’, total_logical_reads,
qs.execution_count AS ‘Execution Count’,
total_logical_reads/qs.execution_count AS ‘AvgLogicalReads’,
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS
‘Calls/Second’,
qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,
qs.total_worker_time AS ‘TotalWorkerTime’,
qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’,
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() — Filter by current database
ORDER BY total_logical_reads DESC

Identifying fragmented tables, bad indexes in a database

Fragmented tables

USE database
GO

SELECT OBJECT_NAME(object_id) ObjectName,
index_id,
index_type_desc,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(),NULL, NULL, NULL, ‘LIMITED’)
WHERE avg_fragmentation_in_percent > 30
ORDER BY OBJECT_NAME(object_id)

Bad indexes

Indexes that have many more writes than reads are possible candidates for elimination.

SELECT object_name(s.object_id) AS ‘Table Name’,
i.name AS ‘Index Name’, i.index_id,
user_updates AS ‘Total Writes’,
user_seeks + user_scans + user_lookups AS ‘Total Reads’,
user_updates – (user_seeks + user_scans + user_lookups) AS ‘Difference’
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,’IsUserTable’) = 1
AND s.database_id = db_id() — Filter for current database
AND user_updates > (user_seeks + user_scans + user_lookups )
ORDER BY ‘Difference’ DESC, ‘Total Writes’ DESC, ‘Total Reads’ ASC;