Enable and disable all indexes in a database

Disabling indexes is a good idea when it comes to loading large quantities of data, but… the big problem is clustered indexes. If you disable a clustered index, you’ve disabled the entire table.

Several options suggest themselves, and none of them are simple.

1) Loop through the system views (sys.indexes), extract the table and index name, generate and execute dynamic SQL to disable the index. Have an “undo” routine to re-enable them. (Be wary–was it a unique index or a unique constraint?) This, alas, only works if you do not use clustered indexes. Good luck with that.

2) As for 1, but skip any clustered indexes. When you load data, make sure it gets loaded in (clustered index) sequential order, otherwise you’ll have poor load times and fragmented tables. (If you data providers are like mine, good luck with that one, too.)

3) Create tables in your database containing definitions of the indexes on your “loading” tables. Build a routine that loops through them and drops all the indexes (clustered indexes last). This will be fast if you truncate the tables first. Load your data, then loop through and recreate the indexes from scratch (clustered first). Use table partitioning to make less horrible on the rest of the system (e.g. do all the above on the “loading” tables, then use partition switching to move the loaded data into your “live” tables). It took me no little time to build such a system, but it can and will work.

Disable script:

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 0

 

Enable script:

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1
Advertisements

How to install .NET Framework 3.5 on Windows Server 2012 and Windows Server 2012 R2

If you have an application that you want to run on Windows Server 2012 that requires the .NET Framework 3.5, you will most likely run in to a problem when trying to install it. If you are trying to install .NET Framework 3.5 from the Server Manager GUI, you will see this when installing the feature:

“Do you want to specify an alternate source path? One or more installation selections are missing source files…”

To solve this, you can either:

Powershell (As Admin)

Install-WindowsFeature Net-Framework-Core -source \\network\share\sxs

Old Fashion Command Line (As Admin)

DISM /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:d:\sources\sxs

Using Powershell you can verify the install by running Get-WindowsFeature from within PS, you will notice something similar to this;

[X] .NET Framework 3.5 Features NET-Framework-Features   Installed
[X] .NET Framework 3.5 (includes .NET 2.0 and 3.0)  NET-Framework-Core Installed

Note: Source should be the Windows installation disc. In my case, this was located on D:

Bug when adding .net framework 3.5 in Server 2012

2. Go down to “Specify an alternate source path” and enter “d:\sources\sxs” as the path.

Now you should see this under your Features list:

.NET Framework 3.5 feature installed on Windows Server 2012

See currently running queries in SQL Server 2012

SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID — don’t show this query
AND session_id > 50 — don’t show system queries
ORDER BY r.start_time

This query is great at finding locking queries like the one below:

To fix this, either add “(NOLOCK)” table hint to allow the select to run while the update is happening or kill the offending process using “KILL” command followed by the SPID

Script all indexes for all tables in a database (SQL Script)

The need often arises to create or recreate the indexes for all tables in a database, especially in development and testing scenarios. This article presents a script to generate Index Creation Scripts for all tables in a database usingTransact-SQL (T-SQL).

SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +    
    I.name  + ' ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    ' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] '  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , ' + C.name  
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id     
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
--AND I.Object_id = object_id('Person.Address') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes  

 

How to rebuild all indexes in all databases

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command 
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
           EXEC (@cmd) 
       END
       ELSE
       BEGIN
          -- SQL 2000 command 
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

Identity column jumping from 6 to 1000+ in SQL Server 2012

I was surprised to find out that overnight, a unique identifier had jumped from 6 to 1012 and upon investigation, it looks like SQL Server, when restarted, messes up the seed of the unique identifier to a new value (this is why you should never use ints for primary keys!)
Don’t expect identity values to be dense. For example, a rollback when inserting a row will cause an identity value to be “consumed” leaving a gap in the numbers.

I wrote a small SP which can be used to re-seeed the identity of a table in a all DBs on a server:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]
AS
BEGIN
begin TRAN
SET NOCOUNT ON;

DECLARE @db_name varchar(280), @SQL nvarchar(500);

PRINT '-------- DB Reseet Report --------';

DECLARE db_name_cursor CURSOR FOR
select DatabaseName + '.dbo.table' from tblClients WHERE ACTIVE = 1

OPEN db_name_cursor

FETCH NEXT FROM db_name_cursor
INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
print '----- Resetting count: ' + @db_name

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

SET @SQL = '
declare @id int = 0
SELECT @id = MAX(ID) FROM ' + @db_name + '

DBCC CHECKIDENT (''' + @db_name + ''', reseed, @id)
'

exec sp_executeSQL @SQL

FETCH NEXT FROM db_name_cursor
INTO @db_name
END
CLOSE db_name_cursor;
DEALLOCATE db_name_cursor;

Commit

END

Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';