Indexes that are not used

One of the main task of a database administrator is proper handling of Indexes. He will have check for the right indexes from time to time. Below query checks the database and returns the unused indexes.

SELECT o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'    
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) 
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, o.name, i.name

The above query returns the indexes which are never used.

Truncate and Delete

Generally Delete command removes the rows from a table based on some where condition. whereas Truncate removes all the rows from a table.

Truncate

• Truncate is faster and uses fewer system and transaction log resources than Delete. 
• You cannot use  Truncate Table on a table referenced by a FOREIGN KEY constraint.
• Truncate is a DDL Command.
• Truncate resets the identity of the table.
• Truncate removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
• Truncate cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
• Truncate removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

Delete

• Delete removes rows one at a time and records an entry in the transaction log for each deleted row.
• Delete does not reset the identity of the table.
• Delete is DML Command.
• Delete can be rolled back.
• Delete does not reset Identity property of the table.
• Delete can be used with or without a WHERE clause.
• Delete activates Triggers if defined on table.

Index was outside the bound of the array

Today I was trying to connect to SQL Server 2012 using SQL Server 2008 Management studio and management studio throws the below error. 

Then I tried the same with SQL Server 2005 Management studio to connect to SQL Server 2008 and also SQL Server 2012 and it returned the same error. It did not show any useful information under Technical details.

The main reason of above errors is SSMS is only backward compatible, it is not forward compatible. It means we can connect to lower version of SQL Server from higher version of SSMS but we cannot connect higher versoin SQL Server with lower version of SSMS.

The best way to upgrade all client tools to higher version and now you can connect to all version of SQL Server.

Add or Remove a node in SQLServer 2005 Failover Cluster

You need to follow some steps in case of adding or removing a node in a cluster or recover a failed node in a failover cluster environment. In case of node1 failure, the SQL Server failover cluster fails over to node2. Use Cluster administrator to evict node from cluster.

You may receive following error while adding or removing a node to the existing cluster.

Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.

Error message from event log:-
"SQL Server Remote Setup .job" (setup.exe) 5/20/2011 10:15:00 PM ** ERROR ** 
Unable to start task.
The specific error is:
0×80070005: Access is denied. 

You might be connected via Remote Desktop and you may have some active remote desktop session. Please note that you must close all your remote desktop session sessions before proceeding to installation.

To add or remove a node you must be a local administration on all the nodes of the failover cluster.

Step 1. Add a node to the Cluster
• Assign the quorum
• Run Add node wizard in Custer administrator from new node
• Provide Cluster service account password to add the node
• On the New Node assign the clustered drives used by SQL Server and verify the node recognizes the cluster drives
• The drive where you install SQL Server should have same driver letter as other nodes

Step 2. Add or remove a node in SQL Server 2005 Cluster
• Login to the surviving Node or active node of the SQL Server Cluster
• Click Add or Remove Programs in Control Panel
• Select the Microsoft SQL Server 2005 and click change
• The SQL Server 2005 Installation Wizard starts
• On the Component Selection page, select the SQL Server 2005 instance name to change and click next
• On Feature Maintenance page select a Database Engine component to change and click next
• SQL Server 2005 System Configuration Checker starts
• On the Microsoft SQL 2005 Welcome screen click next
• The System Configuration Checker scans the existing configuration on the computer
• Review the warnings and error messages, correct the errors as needed and click next
• On the Change or Remove Instance page, click Maintain the Virtual Server.
• On the Cluster Node Configuration page select the node from the available nodes list and click add . To remove a node from the failover cluster, select the node and click remove.
• In Remote Account Information, provide the password of the user currently logged in(This user should be a local admin on all the nodes) and click next
• Enter the service account username/password for each listed service and click next.
• On Error and Usage Report page, do not select anything and click next
• On the Ready to Install page, click install
• On the Completing the Microsoft SQL Server Installation Wizard page, you can review the summary log by clicking on the link provided. To exit from Installation wizard click Finish.

Step 3.Install the Workstation Components and Service packs 
• Run the setup.exe from Installation CD or software dump
• Install only workstation components (note that these are not part of the cluster)
• All nodes in a failover cluster must be at same service pack version, install the service packs as same as servicing node or active node.

Ref:http://www.sqlserver-expert.com/2011/07/add-or-remove-node-in-sqlserver-2005.html

Calculate Indexes Size via dm_db_index_physical_stats

Sometimes we may want to know the space used by a index. We can use the sp_spaceused system stored procedure to measure how much space used by different objects.

This code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are.


SELECT [name],type_desc,space_used_in_kb = (page_count * 8.0),space_used_in_mb = (page_count * 8.0 / 1024.0) 
  FROM sys.indexes I JOIN sys.dm_db_index_physical_stats(db_id(),object_id('.'),null,null,null) P 
    ON I.[object_id] = P.[object_id] 
   AND I.[index_id] = P.[index_id]