Performance Killers in MSSQL

• Frequent recompilation of execution plans
• Inaccurate statistics
• Excessive use or improper configuration of tempdb
• Excessive blocking and deadlocks
• Excessive fragmentation
• Improper use of cursors
• Improper configuration of the database log
• Non-reusable execution plans
• Non-set-based operations, usually T-SQL cursors
• Poor database design
• Poor query design
• Poor indexing
• Poor execution plans, usually caused by parameter sniffing

Sp_rename fails : Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong

You can get below error if you have ‘DOT’ in between your constraint name. SP_rename fails because object_id(ContraintnamewithDOT) returns NULL for constraint objects with DOT (.) in their name.

Sp_rename fails with Msg 15248, Level 11, State 1, Procedure sp_rename, Line 357
Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

The solution to above problem is to drop and recreate the constraint without DOT

Unused Index

Here is a useful script to get the unused index in a Database. This may be inaccurate sometime. So you had to apply commonsense before running DROP index query:

SELECT DB_Name() AS [Database], S.Name AS [Schema], O.Name AS [Object], C.Name AS [Column], I.Name AS [Index],
        (CASE WHEN I.IS_Disabled = 1 THEN 'Yes' ELSE 'No' END) AS [Disabled],
        (CASE WHEN I.IS_Hypothetical = 1 THEN 'Yes' ELSE 'No' END) AS Hypothetical,
        Rows = (SELECT SUM(P.Rows) FROM sys.Partitions P WHERE P.INDEX_ID = I.INDEX_ID 
                        AND P.OBJECT_ID = I.OBJECT_ID GROUP BY P.INDEX_ID, P.OBJECT_ID),
        N'USE ' + DB_Name() + N'; DROP INDEX ' + QuoteName(I.Name) + ' ON ' + QuoteName(S.Name) + 
                    '.' + QuoteName(OBJECT_Name(I.OBJECT_ID)) AS 'Drop Statement'
FROM [sys].[Objects] O 
        INNER JOIN [sys].[Indexes] I ON O.[OBJECT_ID] = I.[OBJECT_ID] AND O.[Type] = 'U' AND O.IS_MS_Shipped = 0 AND O.Name <> 'sysDiagrams'
        INNER JOIN [sys].[Tables] T ON T.[OBJECT_ID] = I.[OBJECT_ID]
        INNER JOIN [sys].[Schemas] S ON S.[Schema_ID] = T.[Schema_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] = IC.[OBJECT_ID] AND C.COLUMN_ID = IC.COLUMN_ID
WHERE I.[Type] > 0 AND I.IS_Unique_Constraint = 0 AND I.IS_Primary_Key = 0 
        AND NOT EXISTS (SELECT * FROM [sys].[Foreign_Key_Columns] FKC INNER JOIN [sys].[Index_Columns] XIC ON FKC.Parent_COLUMN_ID = XIC.COLUMN_ID AND FKC.Parent_OBJECT_ID = XIC.[OBJECT_ID] WHERE XIC.[OBJECT_ID] = I.[OBJECT_ID] AND XIC.INDEX_ID = I.INDEX_ID)
        AND NOT EXISTS (SELECT * FROM [master].[sys].[DM_db_Index_Usage_Stats] IUS WHERE IUS.Database_ID = DB_ID(DB_Name()) AND IUS.[OBJECT_ID] = I.[OBJECT_ID] AND IUS.INDEX_ID = I.INDEX_ID)

SQL Agent DCOM error Event ID 10016

I got this error in my event log ID 10016 of my database server installed with Windows Server 2012, in which i have only installed SQL server 2012 database. It was running fine since last 15 days but since yesterday I have got a new problem in it is that i am unable to shut down this very server although i am doing the same with administrator account. It is in domain and none of the policies are implemented in it. I searched a lot on all blogs and even tried some nut none worked. The error is mentioned herein under.

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
{6DF8CB71-153B-4C66-8FC4-E59301B8011B}
 and APPID 
{961AD749-64E9-4BD5-BCC8-ECE8BA0E241F}
 to the user NT SERVICE\SQL Server Distributed Replay Client SID (S-1-5-80-3249811479-2167633679-2115734285-1138413726-166979568) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

After googling i found the below  solutions and that worked for me.

Component services –> computers –> my computer –>DCOM config->MSDTSServer100 
    Right click and select Properties 
    Security 
    Launch and Activate permissions – edit 
Add the local SQL AgentUser and enable local launch and Local activation.

Refresh Intellisense in SSMS(SQL Server Management Studio) 2008 & above

Intellisense is a new feature that comes with SSMS(SQL Server Management Studio) 2008 onwards. Intellisense makes coding quite easy. Sometimes I find that intellisense becomes stale and you start getting the wavy red lines when writing code. Even though object exists in database but Intellisense is not able to recognize it.

Refreshing the cache is quite easy but not necessarily strictly required.

Go to Edit -> IntelliSense -> Refresh Local Cache OR you can use shortcut Ctrl + Shift + R