I am working on reviewing the current partition information in one of our databases. I found it interesting that it was fairly difficult to get the information I wanted using the system information.

I wanted to…

1. Find all tables that are partitioned
2. See what partition function and scheme the table is using
3. See what column the table is partitioned on
4. Find out the record count of each partition

After doing some digging, here is what I came up with.

SELECT OBJECT_NAME(p.OBJECT_ID) TableName,
        c.name PartColumn,
        ps.name PartScheme,
        pf.name PartFunction
 FROM sys.data_spaces  d JOIN
      sys.indexes i JOIN
      (SELECT DISTINCT OBJECT_ID
       FROM sys.partitions
       WHERE partition_number > 1) p
       ON i.OBJECT_ID = p.OBJECT_ID
       ON d.data_space_id = i.data_space_id
      JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id
      JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
      JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID
      JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id
WHERE i.index_id < 2 AND ic.partition_ordinal > 0 

This query gives me some good information, but once I have the preceding information, I can also use it to dynamically create statements using the $PARTITION function to show me the number of records that exist in each partition.

 SELECT 'SELECT $PARTITION.' +  pf.name + '(' + c.name + ') AS ' +
        OBJECT_NAME(p.OBJECT_ID) + 'Partition, COUNT(*) AS [COUNT] FROM ' +
        OBJECT_NAME(p.OBJECT_ID) + ' GROUP BY $PARTITION.' +
        pf.name + '(' + c.name +
        ') ORDER BY [COUNT] DESC;'
      --  ') ORDER BY ' + OBJECT_NAME(p.object_id) + 'Partition;'
 FROM sys.data_spaces  d JOIN
      sys.indexes i JOIN
      (SELECT DISTINCT OBJECT_ID
       FROM sys.partitions
       WHERE partition_number > 1) p
       ON i.OBJECT_ID = p.OBJECT_ID
       ON d.data_space_id = i.data_space_id
      JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id
      JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
      JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID
      JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id
WHERE i.index_id < 2 AND ic.partition_ordinal > 0 

I found it useful to order by the Count and the Partition Number, so all you have to do is uncomment whichever line you would like to see the results ordered by.

I am working on reviewing the current partition information in one of our databases. I found it interesting that it was fairly difficult to get the information I wanted using the system information.

I wanted to…

1. Find all tables that are partitioned
2. See what partition function and scheme the table is using
3. See what column the table is partitioned on
4. Find out the record count of each partition

After doing some digging, here is what I came up with.

SELECT OBJECT_NAME(p.OBJECT_ID) TableName,
        c.name PartColumn,
        ps.name PartScheme,
        pf.name PartFunction
 FROM sys.data_spaces  d JOIN
      sys.indexes i JOIN
      (SELECT DISTINCT OBJECT_ID
       FROM sys.partitions
       WHERE partition_number > 1) p
       ON i.OBJECT_ID = p.OBJECT_ID
       ON d.data_space_id = i.data_space_id
      JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id
      JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
      JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID
      JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id
WHERE i.index_id < 2 AND ic.partition_ordinal > 0 

This query gives me some good information, but once I have the preceding information, I can also use it to dynamically create statements using the $PARTITION function to show me the number of records that exist in each partition.

 SELECT 'SELECT $PARTITION.' +  pf.name + '(' + c.name + ') AS ' +
        OBJECT_NAME(p.OBJECT_ID) + 'Partition, COUNT(*) AS [COUNT] FROM ' +
        OBJECT_NAME(p.OBJECT_ID) + ' GROUP BY $PARTITION.' +
        pf.name + '(' + c.name +
        ') ORDER BY [COUNT] DESC;'
      --  ') ORDER BY ' + OBJECT_NAME(p.object_id) + 'Partition;'
 FROM sys.data_spaces  d JOIN
      sys.indexes i JOIN
      (SELECT DISTINCT OBJECT_ID
       FROM sys.partitions
       WHERE partition_number > 1) p
       ON i.OBJECT_ID = p.OBJECT_ID
       ON d.data_space_id = i.data_space_id
      JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id
      JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
      JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID
      JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id
WHERE i.index_id < 2 AND ic.partition_ordinal > 0 

I found it useful to order by the Count and the Partition Number, so all you have to do is uncomment whichever line you would like to see the results ordered by.

I have been reviewing the number of Virtual Log Files (VLFs) in my environment and I didn't want to run DBCC LOGINFO on each individual database, so I wrote a script that would return the number of VLFs for all the databases on the server and thought I would share it.


For more information, Kimberly Tripp has a great post on VLFs including an acceptable number and how to fix them. 8 Steps to better Transaction Log throughput (look at step #8)

CREATE TABLE #LogInfo(
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50))
CREATE TABLE #LogInfo2(
                DatabaseName SYSNAME,
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50))
EXEC master.dbo.sp_MSFOREACHDB
    'USE ? INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO'');
           INSERT INTO #LogInfo2 SELECT ''?'', * FROM #LogInfo;
           DELETE FROM #LogInfo'
SELECT DatabaseName,
       COUNT(*) AS VLFs
FROM  #LogInfo2
GROUP BY DatabaseName
ORDER BY VLFs DESC

DROP TABLE #LogInfo
DROP TABLE #LogInfo2 

I have been reviewing the number of Virtual Log Files (VLFs) in my environment and I didn't want to run DBCC LOGINFO on each individual database, so I wrote a script that would return the number of VLFs for all the databases on the server and thought I would share it.


For more information, Kimberly Tripp has a great post on VLFs including an acceptable number and how to fix them. 8 Steps to better Transaction Log throughput (look at step #8)

CREATE TABLE #LogInfo(
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50))
CREATE TABLE #LogInfo2(
                DatabaseName SYSNAME,
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50))
EXEC master.dbo.sp_MSFOREACHDB
    'USE ? INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO'');
           INSERT INTO #LogInfo2 SELECT ''?'', * FROM #LogInfo;
           DELETE FROM #LogInfo'
SELECT DatabaseName,
       COUNT(*) AS VLFs
FROM  #LogInfo2
GROUP BY DatabaseName
ORDER BY VLFs DESC

DROP TABLE #LogInfo
DROP TABLE #LogInfo2 

I have been interrogating the plan cache a lot more every since I was tuning the cost threshold for parallelism on one of my servers and ran across the post Tuning ‘cost threshold for parallelism’ from the Plan Cache by Jonathan Kehayias.

You may be aware that in SQL Server 2008, missing indexes show up in the execution plan. This also works if you are using SQL Server 2008 Management Studio to view SQL Server 2005 plans. One thing I noticed though is that if there are multiple batches in the plan, the same missing index shows for each query in the plan. I also noticed that there could be multiple missing indexes in the actual XML, but the GUI only shows one. The way the XML is structured, you can even have multiple missing indexes per statement and all you will ever see is one missing index.

You may have also seen the Missing Index Report you can create using DMV's. This report generates an improvement measure based on the characteristics of the missing index. The problem with the DMV's is that you can't tell what query caused the index recommendation.

I figured since everything I wanted was in the Plan Cache, I could write a query that would calculate an improvement measure based on the number of times the plan was used, the cost of the statement in the plan, and the impact the index will have on the statement. In addition, I can also see the statement that would benefit from the index.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES  
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT TOP 100--ecp.plan_handle,
        DENSE_RANK() OVER ( ORDER BY ecp.plan_handle ) AS ArbitraryPlanNumber ,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')
        * ISNULL(n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float'), 0)
        * ecp.usecounts AS Improvement ,
        query_plan AS CompleteQueryPlan ,
        n.value('(@StatementId)[1]', 'float') AS StatementID ,
        n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost ,
        n.query('./QueryPlan/MissingIndexes') MissingIndex ,
        n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IndexImpact ,
        ecp.usecounts
FROM    sys.dm_exec_cached_plans AS ecp
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
        AS qn ( n )
WHERE   n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IS NOT NULL AND
        ecp.usecounts > 100 AND
        eqp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
         /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes') = 1 
ORDER BY Improvement DESC


I also included an arbitrary plan number, which is just an ID that gets assigned to the plan, so I can easily see if the statements are coming from the same plan. I could use the plan hash, but using an ID is easier for me to identify the same plans.

I have been interrogating the plan cache a lot more every since I was tuning the cost threshold for parallelism on one of my servers and ran across the post Tuning ‘cost threshold for parallelism’ from the Plan Cache by Jonathan Kehayias.

You may be aware that in SQL Server 2008, missing indexes show up in the execution plan. This also works if you are using SQL Server 2008 Management Studio to view SQL Server 2005 plans. One thing I noticed though is that if there are multiple batches in the plan, the same missing index shows for each query in the plan. I also noticed that there could be multiple missing indexes in the actual XML, but the GUI only shows one. The way the XML is structured, you can even have multiple missing indexes per statement and all you will ever see is one missing index.

You may have also seen the Missing Index Report you can create using DMV's. This report generates an improvement measure based on the characteristics of the missing index. The problem with the DMV's is that you can't tell what query caused the index recommendation.

I figured since everything I wanted was in the Plan Cache, I could write a query that would calculate an improvement measure based on the number of times the plan was used, the cost of the statement in the plan, and the impact the index will have on the statement. In addition, I can also see the statement that would benefit from the index.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES  
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT TOP 100--ecp.plan_handle,
        DENSE_RANK() OVER ( ORDER BY ecp.plan_handle ) AS ArbitraryPlanNumber ,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')
        * ISNULL(n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float'), 0)
        * ecp.usecounts AS Improvement ,
        query_plan AS CompleteQueryPlan ,
        n.value('(@StatementId)[1]', 'float') AS StatementID ,
        n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,
        n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost ,
        n.query('./QueryPlan/MissingIndexes') MissingIndex ,
        n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IndexImpact ,
        ecp.usecounts
FROM    sys.dm_exec_cached_plans AS ecp
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
        AS qn ( n )
WHERE   n.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','float') IS NOT NULL AND
        ecp.usecounts > 100 AND
        eqp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
         /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes') = 1 
ORDER BY Improvement DESC


I also included an arbitrary plan number, which is just an ID that gets assigned to the plan, so I can easily see if the statements are coming from the same plan. I could use the plan hash, but using an ID is easier for me to identify the same plans.

I wanted a list of Microsoft Best Practices the other day and I started to seach the web. Then I thought this would be a perfect use for Policy-Based Management. I had a new install of SQL Server 2008 so I navigated to the default directory "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033" and imported all the policies.

Then I just ran the following query to get a list of all the policies along with a help link for additional information.

SELECT sp.name PolicyName,
       spc.name CategoryName,
       sp.help_link
FROM msdb.dbo.syspolicy_policies_internal sp JOIN
     msdb.dbo.syspolicy_policy_categories_internal spc ON
       sp.policy_category_id = spc.policy_category_id
ORDER BY spc.name, sp.name

You can also add the sp.description column for a short description about why it is considered best practice. Here is the results.

I wanted a list of Microsoft Best Practices the other day and I started to seach the web. Then I thought this would be a perfect use for Policy-Based Management. I had a new install of SQL Server 2008 so I navigated to the default directory "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033" and imported all the policies.

Then I just ran the following query to get a list of all the policies along with a help link for additional information.

SELECT sp.name PolicyName,
       spc.name CategoryName,
       sp.help_link
FROM msdb.dbo.syspolicy_policies_internal sp JOIN
     msdb.dbo.syspolicy_policy_categories_internal spc ON
       sp.policy_category_id = spc.policy_category_id
ORDER BY spc.name, sp.name

You can also add the sp.description column for a short description about why it is considered best practice. Here is the results.

Many times if you look for missing indexes you will often find a lot of duplication due to all the Include colums. For example, you will find a lot of entries like this.

CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2)
CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2,Col3)

Obviously, you don't need both of these indexes. The second one will work just fine. However, you will find reccomendations for both in the missing index DMV. What I have been doing lately is grouping by the base index and then reviewing the Include reccomendations seperately by using the following query.

SELECT  COUNT(*) CountBeforeInclude ,
        mid.statement ,
        SUM(migs.user_seeks) seeks ,
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' AS base_index_statement ,
        SUM(CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
            * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ))) AS improvement_measure
FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
GROUP BY mid.statement ,
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')'
ORDER BY improvement_measure DESC


This helps out with a copule of things.

1. You can get aggregated data for the improvement measures.
2. It prevents you from creating redundant indexes.

Then you can take a single table and plug it into the following query to come up with your own index that covers multiple missing indexes. (Make sure to change the DatabaseName and TableName parameters in the WHERE clause.)

SELECT  mid.statement ,
        migs.user_seeks ,
        equality_columns ,
        inequality_columns ,
        included_columns ,
        'CREATE INDEX missing_index_'
        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
        + CONVERT (VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' ('
        + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
                                                              + mid.included_columns
                                                              + ')', '') AS create_index_statement ,
        migs.* ,
        mid.database_id ,
        mid.[object_id] ,
        mig.index_group_handle ,
        mid.index_handle ,
        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) AS improvement_measure
FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE   DB_NAME(database_id) = 'DatabaseName'
        AND mid.statement LIKE '%TableName%'

Many times if you look for missing indexes you will often find a lot of duplication due to all the Include colums. For example, you will find a lot of entries like this.

CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2)
CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2,Col3)

Obviously, you don't need both of these indexes. The second one will work just fine. However, you will find reccomendations for both in the missing index DMV. What I have been doing lately is grouping by the base index and then reviewing the Include reccomendations seperately by using the following query.

SELECT  COUNT(*) CountBeforeInclude ,
        mid.statement ,
        SUM(migs.user_seeks) seeks ,
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' AS base_index_statement ,
        SUM(CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
            * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ))) AS improvement_measure
FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
GROUP BY mid.statement ,
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')'
ORDER BY improvement_measure DESC


This helps out with a copule of things.

1. You can get aggregated data for the improvement measures.
2. It prevents you from creating redundant indexes.

Then you can take a single table and plug it into the following query to come up with your own index that covers multiple missing indexes. (Make sure to change the DatabaseName and TableName parameters in the WHERE clause.)

SELECT  mid.statement ,
        migs.user_seeks ,
        equality_columns ,
        inequality_columns ,
        included_columns ,
        'CREATE INDEX missing_index_'
        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
        + CONVERT (VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' ('
        + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
                                                              + mid.included_columns
                                                              + ')', '') AS create_index_statement ,
        migs.* ,
        mid.database_id ,
        mid.[object_id] ,
        mig.index_group_handle ,
        mid.index_handle ,
        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) AS improvement_measure
FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE   DB_NAME(database_id) = 'DatabaseName'
        AND mid.statement LIKE '%TableName%'