How to identify duplicate indexes along with columns in SQL Server?

To get the key column list from indexes that are duplicates in SQL Server, you can use the following query:

use databasename
go

WITH DuplicateIndexes AS (
    SELECT 
        i.OBJECT_ID,
        i.index_id
    FROM 
        sys.index_columns ic
    JOIN 
        sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                     AND i.index_id = ic.index_id
    WHERE 
        i.type_desc <> 'HEAP' 
		AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%' --excluding system tables
    GROUP BY 
        i.OBJECT_ID, i.index_id
    HAVING 
        COUNT(*) > 1 -- to check duplicates 
)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS IndexedColumns
FROM 
    sys.index_columns ic
JOIN 
    sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                 AND i.index_id = ic.index_id
JOIN 
    sys.objects o ON o.OBJECT_ID = ic.OBJECT_ID
JOIN 
    sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID 
                 AND ic.column_id = c.column_id
JOIN 
    DuplicateIndexes di ON di.OBJECT_ID = ic.OBJECT_ID 
                        AND di.index_id = ic.index_id
GROUP BY 
    o.schema_id, ic.OBJECT_ID, i.name;
  

This query first identifies the indexes that are duplicates, and then retrieves the table name, index name, and the key column list for each duplicate index.

Execute this query in your SQL Server management tool to get the key column list from indexes that are duplicates in your database.

How to add a ServiceThrottlingBehavior to a WCF Service?

When working with WCF especially when middle-tier client applications uses Windows Communication Foundation, you should always think about performance and take some major design decisions and tuning parameters.

By adding ServiceThrottlingbehavior in web.config we can achieve high performance using WCF. Below is the sample serivceThrottleconfiguration settings in web.config in .NET 4.0 Framework.

 <behaviors>
      <serviceBehaviors>
        <behavior name="CommonService">
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="false" />
          <dataContractSerializer maxItemsInObjectGraph="2147483647" />
          <serviceThrottling maxConcurrentCalls="16" 
                             maxConcurrentInstances="116"   
                             maxConcurrentSessions="100"   />
        </behavior>
      </serviceBehaviors>
    </behaviors>

The main purpose for the throttling settings can be classified into the following two aspects:

1. Controlled resource usage: With the throttling of concurrent execution, the usage of resources such as memory or threads can be limited to a reasonable level so that the system works well without hitting reliability issues.


2. Balanced performance load: Systems always work in a balanced way when the load is controlled. If there are too much concurrent execution happening, a lot of contention and bookkeeping would happen and thus it would hurt the performance of the system.

In WCF 4, the default values of these settings are revised so that people don’t have to change the defaults in most cases. Here are the main changes:

• MaxConcurrentSessions: default is 100 * ProcessorCount


• MaxConcurrentCalls: default is 16 * ProcessorCount


• MaxConcurrentInstances: default is the total of the above two, which follows the same pattern as before.

“ProcessorCount” is used as multiplier for the settings. So on a 4-proc server, you would get the default of MaxConcurrentCalls as 16 * 4 = 64. Thus the consideration is that, when you write a WCF service and you use the default settings, the service can be deployed to any system from low-end one-proc server to high-end such as 24-way server without having to change the settings. So CPU uses count as the multiplier.

Please note, these changes are for the default settings only. If you explicitly set these settings in either configuration or in code, the system would use the settings that you provided. No “ProcessCount” multiplier would be applied.