Dropping columns statistics after migrating to SQL Server 2014/2016/2017

While migrating databases to new versions of SQL Server, there are some tasks that need to be carried out in order to warrant strong consistent data and physical integrity of databases in tandem with their performance. For instance, it is common to run DBCC CHECKDB, rebuild all indexes, and update columns and index statistics. In some scenarios, we might consider dropping columns statistics and let SQL Server create them again according to new algorithms especially when the new cardinality estimator (CE) is going to be used after upgrading to SQL Server 2014/2016/2017. I personally do that, after restoring databases on the new version of SQL Server I proceed to drop all columns statistics (always having AUTO_CREATE_STATISTICS option enabled) and then SQL Server will definitely create them again based on the nature of database queries and the logic of THE new CE. In a long-term perspective this technique is the most recommendable from my point of view as not only will we have new versions of statistics but also purge old and unused statistics.
Here I will share a script to delete columns statistics. Be caution and only drop them if you have AUTO_CREATE_STATISTICS option enabled, otherwise no statistics will be created and the database performance will be affected tremendously. That is all for now. Let me know any remarks you may have.

SET NOCOUNT ON     
  IF db_name() NOT IN ('model','master','distribution','msdb','tempdb')
    BEGIN 
        DECLARE @schema_name varchar(max)      
        DECLARE @table_name varchar(max)     
        DECLARE @stat_name varchar(max)        
        DECLARE @update_stat_cmd varchar(max)        
        DECLARE @update_stat_msg_header   varchar(max)      
        DECLARE update_stat_cursor CURSOR FOR    
              select  schema_name(o.[schema_id]),  object_name(s1.[object_id]) , s1.name   
              from (  
                select s.[object_id], s.name from sys.stats s  
                left join sys.indexes i on s.name=i.name  
                where i.name is null) s1  
              inner join sys.objects o on o.[object_id]=s1.[object_id]  
              where  o.type='U'  
              order by schema_name(o.[schema_id]),  object_name(s1.[object_id]) , s1.name   
             
         OPEN update_stat_cursor        
         FETCH NEXT FROM update_stat_cursor INTO  @schema_name, @table_name,  @stat_name         
         WHILE (@@fetch_status = 0)        
         BEGIN    
           DECLARE @ini DATETIME, @fin DATETIME     
           SET @update_stat_msg_header =  '->Dropping ['+ RTRIM(@schema_name)  +'].[' + RTRIM(@table_name) + '].[' +@stat_name + ']'      
           PRINT @update_stat_msg_header    
           SET @update_stat_cmd ='DROP STATISTICS ['+ RTRIM(@schema_name)  +'].[' + RTRIM(@table_name) + '].[' +@stat_name + ']'    
           SET @ini=GETDATE()    
           EXEC (@update_stat_cmd)      
           SET @fin=GETDATE()    
          
           FETCH NEXT FROM update_stat_cursor INTO @schema_name, @table_name,  @stat_name            
         END        
     
         PRINT ' '       
         PRINT '----------------------------------------------------------------------------- '        
         SET @update_stat_msg_header = '*************  THERE ARE NO MORE STATISTICS TO BE UPDATED **************'         
         PRINT @update_stat_msg_header        
         PRINT ' '        
         PRINT 'All statistics not linked to any index were rebuilt!'        
          
        CLOSE update_stat_cursor        
        DEALLOCATE update_stat_cursor    
    
   END  
SET NOCOUNT OFF   

How to migrate SQL Server aliases easily

Definitely, in my daily DBA life many times I had to complete the migration of hundreds of SQL Server aliases without wasting much time. The DBAs always have the need of carrying out administrative tasks quickly and easily, and in this sense today I am going to share with you a technique of how to migrate SQL Server aliases.

To begin with, think of having three aliases in the database server. You can see them using SQL Server Manager Configuration tool.

The technique is to use Export/Import option of the system registry. Be cautious and do not try to modify other things. All the keys of the SQL Server aliases can be found for a x64 system in the following path:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

And whether you have SQL Server 32-bit on x64 then the keys are found here:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServe‌​r\Client\ConnectTo

Now, once the path was found we need to navigate to it and right click on it to select Export option to export the branch of aliases to a regedit file (.reg). Finally, copy the file to the new server and then using File->Import option you can import them into the registry of the new database server. That is all for now. Let me know any remarks you may have.

AUTO_CLOSE database option and its impact on the performance

The AUTO_CLOSE database option is only one of the many options related to performance and availability of the database in SQL Server.  When AUTO_CLOSE is set to ON for a database, SQL Server closes all its files and releases the resources used for it shortly after the last connection is closed. This action will reduce the usage of memory, nevertheless, it is barely insignificant (12KB or 20KB). Furthermore, having this option turned on, the first connection will have to open the database again, as a result, it will experience a delay. I highly recommend having disabled this option all the time. Here is the code to turn this option off:
  

       ALTER DATABASE [UserDBInProduction] SET AUTO_CLOSE OFF WITH NO_WAIT

This option is turned off by default, but I have found many databases with this option turned on which also impacts on it performance. The other disadvantage of having enabled it is that when the last established connection to the database is closed, its files are accessible to be manipulated directly via Windows by some user, which means that someone is completely able to delete them while the database engine is running. So, we need to work with lots of cautiousness when it comes to changing not only this database option but also others.

That is all for now, let me know any remarks you may have. Thanks for reading again. Stay tuned.