最適化の3つの側面（DBおよびソフトウェア）

USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoUpdateStatisticsCache] @DB_Name nvarchar(255)=null, @IsUpdateStatistics bit=0 AS BEGIN /*           */ SET NOCOUNT ON; declare @tbl table (Name nvarchar(255), [DB_ID] int); declare @db_id int; declare @name nvarchar(255); declare @str nvarchar(255); --  ,        insert into @tbl(Name, [DB_ID]) select name, database_id from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb', 'distribution') and is_read_only=0 --write and state=0 --online and user_access=0 --MULTI_USER and is_auto_close_on=0 and (name=@DB_Name or @DB_Name is null); while(exists(select top(1) 1 from @tbl)) begin --    select top(1) @db_id=[DB_ID] , @name=Name from @tbl; --   id  DBCC FLUSHPROCINDB(@db_id); if(@IsUpdateStatistics=1) begin --  set @str='USE'+' ['+@name+']; exec sp_updatestats;' exec(@str); end delete from @tbl where [DB_ID]=@db_id; end END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vSizeCache] as --         (https://club.directum.ru/post/1125) with tbl as ( select TotalCacheSize = SUM(CAST(size_in_bytes as bigint)) / 1048576, QueriesCacheSize = SUM(CAST((case when objtype in ('Adhoc', 'Prepared') then size_in_bytes else 0 end) as bigint)) / 1048576, QueriesUseMultiCountCacheSize = SUM(CAST((case when ((objtype in ('Adhoc', 'Prepared')) and (usecounts>1)) then size_in_bytes else 0 end) as bigint)) / 1048576, QueriesUseOneCountCacheSize = SUM(CAST((case when ((objtype in ('Adhoc', 'Prepared')) and (usecounts=1)) then size_in_bytes else 0 end) as bigint)) / 1048576 from sys.dm_exec_cached_plans ) select 'Queries' as 'Cache', (select top(1) QueriesCacheSize from tbl) as 'Cache Size (MB)', CAST((select top(1) QueriesCacheSize from tbl) * 100 / (select top(1) TotalCacheSize from tbl) as int) as 'Percent of Total/Queries' union all select 'Total' as 'Cache', (select top(1) TotalCacheSize from tbl) as 'Cache Size (MB)', 100 as 'Percent of Total/Queries' union all select 'Queries UseMultiCount' as 'Cache', (select top(1) QueriesUseMultiCountCacheSize from tbl) as 'Cache Size (MB)', CAST((select top(1) QueriesUseMultiCountCacheSize from tbl) * 100 / (select top(1) QueriesCacheSize from tbl) as int) as 'Percent of Queries/Queries' union all select 'Queries UseOneCount' as 'Cache', (select top(1) QueriesUseOneCountCacheSize from tbl) as 'Cache Size (MB)', CAST((select top(1) QueriesUseOneCountCacheSize from tbl) * 100 / (select top(1) QueriesCacheSize from tbl) as int) as 'Percent of Queries/Queries' --option(recompile) GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vDelIndexOptimize] as /*   ,          ,   .  master, model, msdb  tempdb   */ select DB_NAME(t.database_id) as [DBName] , SCHEMA_NAME(obj.schema_id) as [SchemaName] , OBJECT_NAME(t.object_id) as [ObjectName] , obj.Type as [ObjectType] , obj.Type_Desc as [ObjectTypeDesc] , ind.name as [IndexName] , ind.Type as IndexType , ind.Type_Desc as IndexTypeDesc , ind.Is_Unique as IndexIsUnique , ind.is_primary_key as IndexIsPK , ind.is_unique_constraint as IndexIsUniqueConstraint , (t.[USER_SEEKS]+t.[USER_SCANS]+t.[USER_LOOKUPS]+t.[SYSTEM_SEEKS]+t.[SYSTEM_SCANS]+t.[SYSTEM_LOOKUPS])-(t.[USER_UPDATES]+t.[System_Updates]) as [index_advantage] , t.[Database_ID] , t.[Object_ID] , t.[Index_ID] , t.USER_SEEKS , t.USER_SCANS , t.USER_LOOKUPS , t.USER_UPDATES , t.SYSTEM_SEEKS , t.SYSTEM_SCANS , t.SYSTEM_LOOKUPS , t.SYSTEM_UPDATES , t.Last_User_Seek , t.Last_User_Scan , t.Last_User_Lookup , t.Last_System_Seek , t.Last_System_Scan , t.Last_System_Lookup , ind.Filter_Definition, STUFF( ( SELECT N', [' + [name] +N'] '+case ic.[is_descending_key] when 0 then N'ASC' when 1 then N'DESC' end FROM sys.index_columns ic INNER JOIN sys.columns c on c.[object_id] = obj.[object_id] and ic.[column_id] = c.[column_id] WHERE ic.[object_id] = obj.[object_id] and ic.[index_id]=ind.[index_id] and ic.[is_included_column]=0 order by ic.[key_ordinal] asc FOR XML PATH(''),TYPE ).value('.','NVARCHAR(MAX)'),1,2,'' ) as [Columns], STUFF( ( SELECT N', [' + [name] +N']' FROM sys.index_columns ic INNER JOIN sys.columns c on c.[object_id] = obj.[object_id] and ic.[column_id] = c.[column_id] WHERE ic.[object_id] = obj.[object_id] and ic.[index_id]=ind.[index_id] and ic.[is_included_column]=1 order by ic.[key_ordinal] asc FOR XML PATH(''),TYPE ).value('.','NVARCHAR(MAX)'),1,2,'' ) as [IncludeColumns] from sys.dm_db_index_usage_stats as t inner join sys.objects as obj on t.[object_id]=obj.[object_id] inner join sys.indexes as ind on t.[object_id]=ind.[object_id] and t.index_id=ind.index_id where ((last_user_seek is null or last_user_seek <dateadd(year,-1,getdate())) and (last_user_scan is null or last_user_scan <dateadd(year,-1,getdate())) and (last_user_lookup is null or last_user_lookup <dateadd(year,-1,getdate())) and (last_system_seek is null or last_system_seek <dateadd(year,-1,getdate())) and (last_system_scan is null or last_system_scan <dateadd(year,-1,getdate())) and (last_system_lookup is null or last_system_lookup <dateadd(year,-1,getdate())) or (((t.[USER_UPDATES]+t.[System_Updates])>0) and (t.[SYSTEM_SEEKS]<=(t.[USER_UPDATES]+t.[System_Updates]-(t.[USER_SEEKS]+t.[USER_SCANS]+t.[USER_LOOKUPS]+t.[SYSTEM_SCANS]+t.[SYSTEM_LOOKUPS]))))) and t.database_id>4 and t.[object_id]>0 and ind.is_primary_key=0 --     and ind.is_unique_constraint=0 --    and t.database_id=DB_ID() GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vDelIndexInclude] as /*  ..  () .                 ,     ,        . http://www.sql.ru/blogs/andraptor/1218 */ WITH cte_index_info AS ( SELECT tSS.[name] AS [SchemaName] ,tSO.[name] AS [ObjectName] ,tSO.[type_desc] AS [ObjectType] ,tSO.[create_date] AS [ObjectCreateDate] ,tSI.[name] AS [IndexName] ,tSI.[is_primary_key] AS [IndexIsPrimaryKey] ,d.[index_type_desc] AS [IndexType] ,d.[avg_fragmentation_in_percent] AS [IndexFragmentation] ,d.[fragment_count] AS [IndexFragmentCount] ,d.[avg_fragment_size_in_pages] AS [IndexAvgFragmentSizeInPages] ,d.[page_count] AS [IndexPages] ,c.key_columns AS [IndexKeyColumns] ,COALESCE(ic.included_columns, '') AS [IndexIncludedColumns] ,tSI.is_unique_constraint FROM ( SELECT tSDDIPS.[object_id] AS [object_id] ,tSDDIPS.[index_id] AS [index_id] ,tSDDIPS.[index_type_desc] AS [index_type_desc] ,MAX(tSDDIPS.[avg_fragmentation_in_percent]) AS [avg_fragmentation_in_percent] ,MAX(tSDDIPS.[fragment_count]) AS [fragment_count] ,MAX(tSDDIPS.[avg_fragment_size_in_pages]) AS [avg_fragment_size_in_pages] ,MAX(tSDDIPS.[page_count]) AS [page_count] FROM [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, N'LIMITED') tSDDIPS GROUP BY tSDDIPS.[object_id] ,tSDDIPS.[index_id] ,tSDDIPS.[index_type_desc] ) d INNER JOIN [sys].[indexes] tSI ON tSI.[object_id] = d.[object_id] AND tSI.[index_id] = d.[index_id] INNER JOIN [sys].[objects] tSO ON tSO.[object_id] = d.[object_id] INNER JOIN [sys].[schemas] tSS ON tSS.[schema_id] = tSO.[schema_id] CROSS APPLY ( SELECT STUFF(( SELECT ', ' + c.[name] + CASE ic.[is_descending_key] WHEN 1 THEN '(-)' ELSE '' END FROM [sys].[index_columns] ic INNER JOIN [sys].[columns] c ON c.[object_id] = ic.[object_id] and c.[column_id] = ic.[column_id] WHERE ic.[index_id] = tSI.[index_id] AND ic.[object_id] = tSI.[object_id] AND ic.[is_included_column] = 0 ORDER BY ic.[key_ordinal] FOR XML PATH('') ) ,1, 2, '' ) AS [key_columns] ) c CROSS APPLY ( SELECT STUFF(( SELECT ', ' + c.[name] FROM [sys].[index_columns] ic INNER JOIN [sys].[columns] c ON c.[object_id] = ic.[object_id] AND c.[column_id] = ic.[column_id] WHERE ic.[index_id] = tSI.[index_id] AND ic.[object_id] = tSI.[object_id] AND ic.[is_included_column] = 1 FOR XML PATH('') ) ,1, 2, '' ) AS [included_columns] ) ic WHERE tSO.[type_desc] IN ( N'USER_TABLE' ) AND OBJECTPROPERTY(tSO.[object_id], N'IsMSShipped') = 0 AND d.[index_type_desc] NOT IN ( 'HEAP' ) ) SELECT t1.[SchemaName] ,t1.[ObjectName] ,t1.[ObjectType] ,t1.[ObjectCreateDate] ,t1.[IndexName] as [DelIndexName] ,t1.[IndexIsPrimaryKey] ,t1.[IndexType] ,t1.[IndexFragmentation] ,t1.[IndexFragmentCount] ,t1.[IndexAvgFragmentSizeInPages] ,t1.[IndexPages] ,t1.[IndexKeyColumns] ,t1.[IndexIncludedColumns] ,t2.[IndexName] as [ActualIndexName] FROM cte_index_info t1 INNER JOIN cte_index_info t2 ON t2.[SchemaName] = t1.[SchemaName] AND t2.[ObjectName] = t1.[ObjectName] AND t2.[IndexName] <> t1.[IndexName] AND PATINDEX(REPLACE(t1.[IndexKeyColumns], '_', '[_]') + ',%', t2.[IndexKeyColumns] + ',') > 0 WHERE t1.[IndexIncludedColumns] = '' -- don't check indexes with INCLUDE columns AND t1.[IndexIsPrimaryKey] = 0 -- don't check primary keys AND t1.is_unique_constraint=0 -- don't check unique constraint AND t1.[IndexType] NOT IN ( N'CLUSTERED INDEX' ,N'UNIQUE CLUSTERED INDEX' ) -- don't check clustered indexes GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vRecomendateIndex] as --    DMV SELECT @@ServerName AS ServerName , DB_Name(ddmid.[database_id]) as [DBName] , t.name AS 'Affected_table' , ddmigs.user_seeks * ddmigs.avg_total_user_cost * (ddmigs.avg_user_impact * 0.01) AS index_advantage, ddmigs.group_handle, ddmigs.unique_compiles, ddmigs.last_user_seek, ddmigs.last_user_scan, ddmigs.avg_total_user_cost, ddmigs.avg_user_impact, ddmigs.system_seeks, ddmigs.last_system_scan, ddmigs.last_system_seek, ddmigs.avg_total_system_cost, ddmigs.avg_system_impact, ddmig.index_group_handle, ddmig.index_handle, ddmid.database_id, ddmid.[object_id], ddmid.equality_columns, -- = ddmid.inequality_columns, ddmid.[statement], ( LEN(ISNULL(ddmid.equality_columns, N'') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END, ',', '')) ) + 1 AS K , COALESCE(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + COALESCE(ddmid.inequality_columns, '') AS Keys , ddmid.included_columns AS [include] , 'Create NonClustered Index IX_' + t.name + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(20)) + ' On ' + ddmid.[statement] COLLATE database_default + ' (' + ISNULL(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(ddmid.inequality_columns, '') + ')' + ISNULL(' Include (' + ddmid.included_columns + ');', ';') AS sql_statement , ddmigs.user_seeks , ddmigs.user_scans , CAST(( ddmigs.user_seeks + ddmigs.user_scans ) * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' , ( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds FROM sys.databases WHERE name = 'tempdb' ) SecondsUptime FROM sys.dm_db_missing_index_groups ddmig INNER JOIN sys.dm_db_missing_index_group_stats ddmigs ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details ddmid ON ddmig.index_handle = ddmid.index_handle INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID WHERE ddmid.database_id = DB_ID() --ORDER BY est_impact DESC; GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vNewIndexOptimize] as /*     index_advantage: >50 000 -     >10 000 -   ,       <=10000 -     */ SELECT @@ServerName AS ServerName, DB_Name(ddmid.[database_id]) as [DBName], OBJECT_SCHEMA_NAME(ddmid.[object_id], ddmid.[database_id]) as [Schema], OBJECT_NAME(ddmid.[object_id], ddmid.[database_id]) as [Name], ddmigs.user_seeks * ddmigs.avg_total_user_cost * (ddmigs.avg_user_impact * 0.01) AS index_advantage, ddmigs.group_handle, ddmigs.unique_compiles, ddmigs.last_user_seek, ddmigs.last_user_scan, ddmigs.avg_total_user_cost, ddmigs.avg_user_impact, ddmigs.system_seeks, ddmigs.last_system_scan, ddmigs.last_system_seek, ddmigs.avg_total_system_cost, ddmigs.avg_system_impact, ddmig.index_group_handle, ddmig.index_handle, ddmid.database_id, ddmid.[object_id], ddmid.equality_columns, -- = ddmid.inequality_columns, ddmid.[statement], ( LEN(ISNULL(ddmid.equality_columns, N'') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END, ',', '')) ) + 1 AS K , COALESCE(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + COALESCE(ddmid.inequality_columns, '') AS Keys , ddmid.included_columns AS [include] , 'Create NonClustered Index [IX_' + OBJECT_NAME(ddmid.[object_id], ddmid.[database_id]) + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(20)) + '] On ' + ddmid.[statement] COLLATE database_default + ' (' + ISNULL(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL AND ddmid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(ddmid.inequality_columns, '') + ')' + ISNULL(' Include (' + ddmid.included_columns + ');', ';') AS sql_statement , ddmigs.user_seeks , ddmigs.user_scans , CAST(( ddmigs.user_seeks + ddmigs.user_scans ) * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' , ( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds FROM sys.databases WHERE name = 'tempdb' ) SecondsUptime FROM sys.dm_db_missing_index_group_stats ddmigs INNER JOIN sys.dm_db_missing_index_groups AS ddmig ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS ddmid ON ddmig.index_handle = ddmid.index_handle --WHERE mid.database_id = DB_ID() --ORDER BY migs_adv.index_advantage GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vIndexDefrag] as with info as (SELECT ps.[object_id], ps.database_id, ps.index_id, ps.index_type_desc, ps.index_level, ps.fragment_count, ps.avg_fragmentation_in_percent, ps.avg_fragment_size_in_pages, ps.page_count, ps.record_count, ps.ghost_record_count FROM sys.dm_db_index_physical_stats (DB_ID() , NULL, NULL, NULL , N'LIMITED') as ps inner join sys.indexes as i on i.[object_id]=ps.[object_id] and i.[index_id]=ps.[index_id] where ps.index_level = 0 and ps.avg_fragmentation_in_percent >= 10 and ps.index_type_desc <> 'HEAP' and ps.page_count>=8 --1  and i.is_disabled=0 ) SELECT DB_NAME(i.database_id) as db, SCHEMA_NAME(t.[schema_id]) as shema, t.name as tb, i.index_id as idx, i.database_id, (select top(1) idx.[name] from [sys].[indexes] as idx where t.[object_id] = idx.[object_id] and idx.[index_id] = i.[index_id]) as index_name, i.index_type_desc,i.index_level as [level], i.[object_id], i.fragment_count as frag_num, round(i.avg_fragmentation_in_percent,2) as frag, round(i.avg_fragment_size_in_pages,2) as frag_page, i.page_count as [page], i.record_count as rec, i.ghost_record_count as ghost, round(i.avg_fragmentation_in_percent*i.page_count,0) as func FROM info as i inner join [sys].[all_objects] as t on i.[object_id] = t.[object_id]; GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[ListDefragIndex]( [db] [nvarchar](100) NOT NULL, [shema] [nvarchar](100) NOT NULL, [table] [nvarchar](100) NOT NULL, [IndexName] [nvarchar](100) NOT NULL, [object_id] [int] NOT NULL, [idx] [int] NOT NULL, [db_id] [int] NOT NULL, [frag] [decimal](6, 2) NOT NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_ListDefragIndex] PRIMARY KEY CLUSTERED ( [object_id] ASC, [idx] ASC, [db_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[ListDefragIndex] ADD CONSTRAINT [DF_ListDefragIndex_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Defrag]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [db] [nvarchar](100) NOT NULL, [shema] [nvarchar](100) NOT NULL, [table] [nvarchar](100) NOT NULL, [IndexName] [nvarchar](100) NOT NULL, [frag_num] [int] NOT NULL, [frag] [decimal](6, 2) NOT NULL, [page] [int] NOT NULL, [rec] [int] NULL, [ts] [datetime] NOT NULL, [tf] [datetime] NOT NULL, [frag_after] [decimal](6, 2) NOT NULL, [object_id] [int] NOT NULL, [idx] [int] NOT NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_Defrag] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[Defrag] ADD CONSTRAINT [DF_Defrag_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [srv].[AutoDefragIndex] @count int=null ---    ,@isrebuild bit=0 --    (   30%) AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --      ONLINE declare @isRebuildOnline bit=CASE WHEN (CAST (SERVERPROPERTY ('Edition') AS nvarchar (max)) LIKE '%Enterprise%' OR CAST (SERVERPROPERTY ('Edition') AS nvarchar (max)) LIKE '%Developer%' OR CAST (SERVERPROPERTY ('Edition') AS nvarchar (max)) LIKE '%Evaluation%') THEN 1 ELSE 0 END; declare @IndexName nvarchar(100) ,@db nvarchar(100) ,@db_id int ,@Shema nvarchar(100) ,@Table nvarchar(100) ,@SQL_Str nvarchar (max)=N'' ,@frag decimal(6,2) ,@frag_after decimal(6,2) ,@frag_num int ,@page int ,@ts datetime ,@tsg datetime ,@tf datetime ,@object_id int ,@idx int ,@rec int; --  declare @tbl table ( IndexName nvarchar(100) ,db nvarchar(100) ,[db_id] int ,Shema nvarchar(100) ,[Table] nvarchar(100) ,frag decimal(6,2) ,frag_num int ,[page] int ,[object_id] int ,idx int ,rec int ); --  declare @tbl_copy table ( IndexName nvarchar(100) ,db nvarchar(100) ,[db_id] int ,Shema nvarchar(100) ,[Table] nvarchar(100) ,frag decimal(6,2) ,frag_num int ,[page] int ,[object_id] int ,idx int ,rec int ); set @ts = getdate() set @tsg = @ts; -- ,    ,   10% --     if(@count is null) begin insert into @tbl ( IndexName ,db ,[db_id] ,Shema ,[Table] ,frag ,frag_num ,[page] ,[object_id] ,idx ,rec ) select ind.index_name, ind.db, ind.database_id, ind.shema, ind.tb, ind.frag, ind.frag_num, ind.[page], ind.[object_id], ind.idx , ind.rec from [inf].[vIndexDefrag] as ind where not exists( select top(1) 1 from [  ].[srv].[ListDefragIndex] as lind where lind.[db_id]=ind.database_id and lind.[idx]=ind.idx and lind.[object_id]=ind.[object_id] ) --order by ind.[page] desc, ind.[frag] desc end else begin insert into @tbl ( IndexName ,db ,[db_id] ,Shema ,[Table] ,frag ,frag_num ,[page] ,[object_id] ,idx ,rec ) select top (@count) ind.index_name, ind.db, ind.database_id, ind.shema, ind.tb, ind.frag, ind.frag_num, ind.[page], ind.[object_id], ind.idx , ind.rec from [inf].[vIndexDefrag] as ind where not exists( select top(1) 1 from [  ].[srv].[ListDefragIndex] as lind where lind.[db_id]=ind.database_id and lind.[idx]=ind.idx and lind.[object_id]=ind.[object_id] ) --order by ind.[page] desc, ind.[frag] desc end --    (   ) --     --   if(not exists(select top(1) 1 from @tbl)) begin delete from [  ].[srv].[ListDefragIndex] where [db_id]=DB_ID(); if(@count is null) begin insert into @tbl ( IndexName ,db ,[db_id] ,Shema ,[Table] ,frag ,frag_num ,[page] ,[object_id] ,idx ,rec ) select ind.index_name, ind.db, ind.database_id, ind.shema, ind.tb, ind.frag, ind.frag_num, ind.[page], ind.[object_id], ind.idx , ind.rec from [inf].[vIndexDefrag] as ind where not exists( select top(1) 1 from [  ].[srv].[ListDefragIndex] as lind where lind.[db_id]=ind.database_id and lind.[idx]=ind.idx and lind.[object_id]=ind.[object_id] ) --order by ind.[page] desc, ind.[frag] desc end else begin insert into @tbl ( IndexName ,db ,[db_id] ,Shema ,[Table] ,frag ,frag_num ,[page] ,[object_id] ,idx ,rec ) select top (@count) ind.index_name, ind.db, ind.database_id, ind.shema, ind.tb, ind.frag, ind.frag_num, ind.[page], ind.[object_id], ind.idx , ind.rec from [inf].[vIndexDefrag] as ind where not exists( select top(1) 1 from [  ].[srv].[ListDefragIndex] as lind where lind.[db_id]=ind.database_id and lind.[idx]=ind.idx and lind.[object_id]=ind.[object_id] ) --order by ind.[page] desc, ind.[frag] desc end end --    if(exists(select top(1) 1 from @tbl)) begin --   INSERT INTO [  ].[srv].[ListDefragIndex] ( [db] ,[shema] ,[table] ,[IndexName] ,[object_id] ,[idx] ,[db_id] ,[frag] ) select [db] ,[shema] ,[table] ,[IndexName] ,[object_id] ,[idx] ,[db_id] ,[frag] from @tbl; insert into @tbl_copy ( IndexName ,db ,[db_id] ,Shema ,[Table] ,frag ,frag_num ,[page] ,[object_id] ,idx ,rec ) select IndexName ,db ,[db_id] ,Shema ,[Table] ,frag ,frag_num ,[page] ,[object_id] ,idx ,rec from @tbl; --      (  -     ) while(exists(select top(1) 1 from @tbl)) begin select top(1) @IndexName=[IndexName], @Shema=[Shema], @Table=[Table], @frag=[frag] from @tbl; if(@frag>=30 and @isrebuild=1 and @isRebuildOnline=1) begin set @SQL_Str = @SQL_Str+'ALTER INDEX ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] REBUILD WITH(ONLINE=ON);' end else begin set @SQL_Str = @SQL_Str+'ALTER INDEX ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] REORGANIZE;' +'UPDATE STATISTICS ['+@Shema+'].['+@Table+'] ['+@IndexName+'];'; end delete from @tbl where [IndexName]=@IndexName and [Shema]=@Shema and [Table]=@Table; end --   execute sp_executesql @SQL_Str; --    insert into [  ].srv.Defrag( [db], [shema], [table], [IndexName], [frag_num], [frag], [page], ts, tf, frag_after, [object_id], idx, rec ) select [db], [shema], [table], [IndexName], [frag_num], [frag], [page], @ts, getdate(), (SELECT top(1) avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID([db]), [object_id], [idx], NULL , N'LIMITED') where index_level = 0) as frag_after, [object_id], [idx], [rec] from @tbl_copy; end END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoDefragIndexDB] @DB nvarchar(255)=NULL, --      @count int=NULL, ---       @IsTempdb bit=0 --   tempdb AS BEGIN /*       */ SET NOCOUNT ON; declare @db_name nvarchar(255); declare @sql nvarchar(max); declare @ParmDefinition nvarchar(255)= N'@count int'; if(@DB is null) begin select [name] into #tbls from sys.databases where [is_read_only]=0 and [state]=0 --ONLINE and [user_access]=0--MULTI_USER and (((@IsTempdb=0 or @IsTempdb is null) and [name]<>N'tempdb') or (@IsTempdb=1)); while(exists(select top(1) 1 from #tbls)) begin select top(1) @db_name=[name] from #tbls; set @sql=N'USE ['+@db_name+']; '+ N'IF(object_id('+N''''+N'[srv].[AutoDefragIndex]'+N''''+N') is not null) EXEC [srv].[AutoDefragIndex] @count=@count;'; exec sp_executesql @sql, @ParmDefinition, @count=@count; delete from #tbls where [name]=@db_name; end drop table #tbls; end else begin set @sql=N'USE ['+@DB+']; '+ N'IF(object_id('+N''''+N'[srv].[AutoDefragIndex]'+N''''+N') is not null) EXEC [srv].[AutoDefragIndex] @count=@count;'; exec sp_executesql @sql, @ParmDefinition, @count=@count; end END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO exec sp_updatestats; GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoUpdateStatistics] --       @ObjectSizeMB numeric (16,3) = NULL, -- -    @row_count numeric (16,3) = NULL AS BEGIN /*    */ SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @ObjectID int; declare @SchemaName nvarchar(255); declare @ObjectName nvarchar(255); declare @StatsID int; declare @StatName nvarchar(255); declare @SQL_Str nvarchar(max); ;with st AS( select DISTINCT obj.[object_id] , obj.[create_date] , OBJECT_SCHEMA_NAME(obj.[object_id]) as [SchemaName] , obj.[name] as [ObjectName] , CAST( ( --  ,    ( 8   1024 =  128) SELECT SUM(ps2.[reserved_page_count])/128. from sys.dm_db_partition_stats as ps2 where ps2.[object_id] = obj.[object_id] ) as numeric (38,2) ) as [ObjectSizeMB] --    , s.[stats_id] , s.[name] as [StatName] , sp.[last_updated] , i.[index_id] , i.[type_desc] , i.[name] as [IndexName] , ps.[row_count] , s.[has_filter] , s.[no_recompute] , sp.[rows] , sp.[rows_sampled] ---   : --  -           --   -                 , sp.[modification_counter]+ABS(ps.[row_count]-sp.[rows]) as [ModificationCounter] --%  ,    , --             , NULLIF(CAST( sp.[rows_sampled]*100./sp.[rows] as numeric(18,3)), 100.00) as [ProcSampled] --%  -           --      , CAST(sp.[modification_counter]*100./(case when (ps.[row_count]=0) then 1 else ps.[row_count] end) as numeric (18,3)) as [ProcModified] -- : --[ProcModified]*    -    , CAST(sp.[modification_counter]*100./(case when (ps.[row_count]=0) then 1 else ps.[row_count] end) as numeric (18,3)) * case when (ps.[row_count]<=10) THEN 1 ELSE LOG10 (ps.[row_count]) END as [Func] --  : --  ,    ,   --            , CASE WHEN sp.[rows_sampled]<>sp.[rows] THEN 0 ELSE 1 END as [IsScanned] , tbl.[name] as [ColumnType] , s.[auto_created] from sys.objects as obj inner join sys.stats as s on s.[object_id] = obj.[object_id] left outer join sys.indexes as i on i.[object_id] = obj.[object_id] and (i.[name] = s.[name] or i.[index_id] in (0,1) and not exists(select top(1) 1 from sys.indexes i2 where i2.[object_id] = obj.[object_id] and i2.[name] = s.[name])) left outer join sys.dm_db_partition_stats as ps on ps.[object_id] = obj.[object_id] and ps.[index_id] = i.[index_id] outer apply sys.dm_db_stats_properties (s.[object_id], s.[stats_id]) as sp left outer join sys.stats_columns as sc on s.[object_id] = sc.[object_id] and s.[stats_id] = sc.[stats_id] left outer join sys.columns as col on col.[object_id] = s.[object_id] and col.[column_id] = sc.[column_id] left outer join sys.types as tbl on col.[system_type_id] = tbl.[system_type_id] and col.[user_type_id] = tbl.[user_type_id] where obj.[type_desc] <> 'SYSTEM_TABLE' ) SELECT st.[object_id] , st.[SchemaName] , st.[ObjectName] , st.[stats_id] , st.[StatName] INTO #tbl FROM st WHERE NOT (st.[row_count] = 0 AND st.[last_updated] IS NULL)--       --   AND NOT (st.[row_count] = st.[rows] AND st.[row_count] = st.[rows_sampled] AND st.[ModificationCounter]=0) --    (   ) AND ((st.[ProcModified]>=10.0) OR (st.[Func]>=10.0) OR (st.[ProcSampled]<=50)) --,     AND ( ([ObjectSizeMB]<=@ObjectSizeMB OR @ObjectSizeMB IS NULL) AND (st.[row_count]<=@row_count OR @row_count IS NULL) ); WHILE (exists(select top(1) 1 from #tbl)) BEGIN select top(1) @ObjectID =[object_id] ,@SchemaName=[SchemaName] ,@ObjectName=[ObjectName] ,@StatsId =[stats_id] ,@StatName =[StatName] from #tbl; SET @SQL_Str = 'IF (EXISTS(SELECT TOP(1) 1 FROM sys.stats as s WHERE s.[object_id] = '+CAST(@ObjectID as nvarchar(32)) + ' AND s.[stats_id] = ' + CAST(@StatsId as nvarchar(32)) +')) UPDATE STATISTICS ' + QUOTENAME(@SchemaName) +'.' + QUOTENAME(@ObjectName) + ' ('+QUOTENAME(@StatName) + ') WITH FULLSCAN;'; execute sp_executesql @SQL_Str; delete from #tbl where [object_id]=@ObjectID and [stats_id]=@StatsId; END drop table #tbl; END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoUpdateStatisticsDB] @DB nvarchar(255)=NULL, --      @ObjectSizeMB numeric (16,3) = NULL, -- -    @row_count numeric (16,3) = NULL, @IsTempdb bit=0 --   tempdb AS BEGIN /*        */ SET NOCOUNT ON; declare @db_name nvarchar(255); declare @sql nvarchar(max); declare @ParmDefinition nvarchar(255)= N'@ObjectSizeMB numeric (16,3), @row_count numeric (16,3)'; if(@DB is null) begin select [name] into #tbls from sys.databases where [is_read_only]=0 and [state]=0 --ONLINE and [user_access]=0--MULTI_USER and (((@IsTempdb=0 or @IsTempdb is null) and [name]<>N'tempdb') or (@IsTempdb=1)); while(exists(select top(1) 1 from #tbls)) begin select top(1) @db_name=[name] from #tbls; set @sql=N'USE ['+@db_name+']; '+ N'IF(object_id('+N''''+N'[srv].[AutoUpdateStatistics]'+N''''+N') is not null) EXEC [srv].[AutoUpdateStatistics] @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count;'; exec sp_executesql @sql, @ParmDefinition, @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count; delete from #tbls where [name]=@db_name; end drop table #tbls; end else begin set @sql=N'USE ['+@DB+']; '+ N'IF(object_id('+N''''+N'[srv].[AutoUpdateStatistics]'+N''''+N') is not null) EXEC [srv].[AutoUpdateStatistics] @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count;'; exec sp_executesql @sql, @ParmDefinition, @ObjectSizeMB=@ObjectSizeMB, @row_count=@row_count; end END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 declare @dt datetime=DateAdd(day,-28,GetDate()); exec msdb.dbo.sp_delete_backuphistory @dt; exec msdb.dbo.sp_purge_jobhistory @oldest_date=@dt; exec msdb.dbo.sp_maintplan_delete_log null, null, @dt; exec msdb.dbo.sp_cycle_errorlog; exec msdb.dbo.sp_Cycle_Agent_ErrorLog; ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp2 { class Program { static void Main(string[] args) { OpenSqlConnection(); Console.ReadKey(); } private static void OpenSqlConnection() { string connectionString = GetConnectionString(); using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = connectionString; connection.Open(); Console.WriteLine("State: {0}", connection.State); Console.WriteLine("ConnectionString: {0}", connection.ConnectionString); } } static private string GetConnectionString() { SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder(); conn.ApplicationName = "MyProgram"; conn.DataSource = "SQLServerName"; conn.InitialCatalog = "DBName"; conn.IntegratedSecurity = true; return conn.ToString(); } } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 select [ID] into #tbl_del from [Personal] where [FirstName] like '%%'; delete from p from #tbl_del as t inner join [Personal] as p on t.[ID]=p.[ID]; drop table #tbl_del; select [ID] ,[FirstName] into #tbl_upd from [Personal] where [LastName] like '%'; update p set p.[Comment]=t.[FirstName] from #tbl_upd as t inner join [Personal] as p on t.[ID]=p.[ID]; drop table #tbl_upd;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 --      Personal     . --       ,  ,    : ;with DelDups as ( select [ID], --         row_number() over(partition by [FirstName], [LastName] order by [InsertDate] desc) as [rn] from [Personal] ) select [ID] into #tbl_del delete from DelDups where rn>1; delete from p from #tbl_del as t inner join [Personal] as p on t.[ID]=p.[ID]; drop table #tbl_del;