SCRIPT: Basic Information About Indexes

I’m having a little fun with documenting basic information about indexes in my current project. I’m posting the scripts here mostly for me to come back when I need them in the future, but hopefully some of you might find them useful.

Find tables without any index:

This script gives you the list of tables that don’t have any index.

; WITH A
AS
(
SELECT SchemaName = OBJECT_SCHEMA_NAME(t.OBJECT_ID) ,
TableName = t.name
FROM   sys.tables t
WHERE  OBJECTPROPERTY(t.OBJECT_ID,'TableHasIndex') = 0
)
SELECT SchemaName ,
TableName ,
TwoPartName = SchemaName+'.'+TableName
FROM A

Find tables without a clustered index:

This script gives the list of tables without a clustered index.

; WITH B
AS
(
SELECT SchemaName = OBJECT_SCHEMA_NAME(OBJECT_ID) ,
TableName = OBJECT_NAME(OBJECT_ID)
FROM sys.indexes
WHERE index_id = 0
AND OBJECTPROPERTY(OBJECT_ID, 'IsUserTable') = 1
)
SELECT SchemaName ,
TableName ,
TwoPartName = SchemaName+'.'+TableName
FROM B
ORDER BY SchemaName

Find indexes on a table along with columns covered:

Use this script to get the index names and a comma separated list of columns included in the index.

; WITH C
AS
(
SELECT TableName = t.name
, IndexName = i.name
, ColumnName = c.name
FROM   sys.tables t
INNER JOIN
sys.indexes i
ON t.OBJECT_ID = i.OBJECT_ID
INNER JOIN
sys.index_columns ic
ON ic.OBJECT_ID = i.OBJECT_ID
AND ic.index_id = i.index_id
INNER JOIN
sys.columns c
ON c.OBJECT_ID = t.OBJECT_ID
AND c.column_id = ic.column_id
)
SELECT DISTINCT TableName, IndexName, ColumnNames
FROM cte A
CROSS APPLY
(
SELECT ColumnName + ', '
FROM cte B
WHERE A.TableName = B.TableName AND A.IndexName = B.IndexName
ORDER BY TableName, IndexName
FOR XML PATH('')
)
D (ColumnNames)

Notice that I generously used CTE’s here. I like to reuse column aliases instead of repeating length expressions everywhere. CTE allows you to do that.

@SamuelVanga

Advertisements

Parallel Execution in SSIS with MaxConcurrentExecutables

MaxConcurrentExecutables, a package level property in SSIS determines the number of control flow items that can be executed in parallel. The default value is -1. This is equivalent to number of processors (logical and physical) plus 2.

For example, in the below package running on my machine with 4 processors and MaxConcurrentExecutables = -1, you can see 6 tasks have completed execution and 6 are currently running. It’s executing 6 at a time because 4 processors + 2 = 6 threads.

maxconcurrentexecutables ssis

This applies to all versions of SSIS. Parallelism is powerful when your goal is to complete a process as quickly as possible, specially when the tasks in a control flow are independent of each other.

If you’re thinking of increasing this setting to an infinity hoping to achieve a Nobel prize in performance tuning… slow down. If the words throughput, threading, multi-tasking scares you, you should be careful with this property. In most cases, the default setting can get the job done just fine.

Follow me on Twitter.

@SamuelVanga