Wednesday, March 24, 2010

Selecting Partition Information for All Tables in a Database

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_IDTableName,
        
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 1p
       
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 AND ic.partition_ordinal 

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 1p
       
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 AND ic.partition_ordinal 


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.

Tuesday, March 23, 2010

Find The Number of VLFs For All Databases

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 
#LogInfo

Thursday, March 18, 2010

Getting the Improvement Measure of Missing Indexes from the Plan Cache

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_handleAS eqp
        
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
        
AS qn )
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'

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.

Thursday, March 11, 2010

Retrieve Microsoft Best Practices Using Policy-Based Management

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.namesp.name

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


PolicyName

CategoryName

help_link

SQL Server Default Trace

Microsoft Best Practices: Audit


SQL Server System Tables Updatable

Microsoft Best Practices: Configuration


Backup and Data File Location

Microsoft Best Practices: Maintenance


Database Page Status

Microsoft Best Practices: Maintenance


Database Page Verification

Microsoft Best Practices: Maintenance


Last Successful Backup Date

Microsoft Best Practices: Maintenance


Read-only Database Recovery Model

Microsoft Best Practices: Maintenance


Data and Log File Location

Microsoft Best Practices: Performance


Database Auto Close

Microsoft Best Practices: Performance


Database Auto Shrink

Microsoft Best Practices: Performance


Database Collation

Microsoft Best Practices: Performance


File Growth for SQL Server 2000

Microsoft Best Practices: Performance


SQL Server 32-bit Affinity Mask Overlap

Microsoft Best Practices: Performance


SQL Server 64-bit Affinity Mask Overlap

Microsoft Best Practices: Performance


SQL Server Affinity Mask

Microsoft Best Practices: Performance


SQL Server Blocked Process Threshold

Microsoft Best Practices: Performance


SQL Server Dynamic Locks

Microsoft Best Practices: Performance


SQL Server I/O Affinity Mask For Non-enterprise SQL Servers

Microsoft Best Practices: Performance


SQL Server Lightweight Pooling

Microsoft Best Practices: Performance


SQL Server Max Degree of Parallelism

Microsoft Best Practices: Performance


SQL Server Max Worker Threads for 32-bit SQL Server 2000

Microsoft Best Practices: Performance


SQL Server Max Worker Threads for 64-bit SQL Server 2000

Microsoft Best Practices: Performance


SQL Server Max Worker Threads for SQL Server 2005 and above

Microsoft Best Practices: Performance


SQL Server Network Packet Size

Microsoft Best Practices: Performance


SQL Server Open Objects for SQL Server 2000

Microsoft Best Practices: Performance


Asymmetric Key Encryption Algorithm

Microsoft Best Practices: Security


CmdExec Rights Secured

Microsoft Best Practices: Security


Guest Permissions

Microsoft Best Practices: Security


Public Not Granted Server Permissions

Microsoft Best Practices: Security


SQL Server Login Mode

Microsoft Best Practices: Security


SQL Server Password Expiration

Microsoft Best Practices: Security


SQL Server Password Policy

Microsoft Best Practices: Security


Symmetric Key Encryption for User Databases

Microsoft Best Practices: Security


Symmetric Key for master Database

Microsoft Best Practices: Security


Symmetric Key for System Databases

Microsoft Best Practices: Security


Trustworthy Database

Microsoft Best Practices: Security


Windows Event Log Cluster Disk Resource Corruption Error

Microsoft Best Practices: Windows Log File


Windows Event Log Device Driver Control Error

Microsoft Best Practices: Windows Log File


Windows Event Log Device Not Ready Error

Microsoft Best Practices: Windows Log File


Windows Event Log Disk Defragmentation

Microsoft Best Practices: Windows Log File


Windows Event Log Failed I/O Request Error

Microsoft Best Practices: Windows Log File


Windows Event Log I/O Delay Warning

Microsoft Best Practices: Windows Log File


Windows Event Log I/O Error During Hard Page Fault Error

Microsoft Best Practices: Windows Log File


Windows Event Log Read Retry Error

Microsoft Best Practices: Windows Log File


Windows Event Log Storage System I/O Timeout Error

Microsoft Best Practices: Windows Log File


Windows Event Log System Failure Error

Microsoft Best Practices: Windows Log File


Surface Area Configuration for Database Engine 2005 and 2000 Features

Microsoft Off by Default: Surface Area Configuration


Surface Area Configuration for Database Engine 2008 Features

Microsoft Off by Default: Surface Area Configuration


Surface Area Configuration for Service Broker Endpoints

Microsoft Off by Default: Surface Area Configuration


Surface Area Configuration for SOAP Endpoints

Microsoft Off by Default: Surface Area Configuration