7 lipca 201715 kwietnia 2020 Michał PawlikowskiPrzydatne skrypty, SQL Server

Aktualizacja statystyk dla tabel używanych w widoku (wraz z zagnieżdżonymi)

Transact-SQL

DECLARE @ObjectName sysname = 'VIEW_NAME'; DECLARE @ObjectSchema sysname = 'dbo'; DECLARE @WithFullscan BIT = 1; DECLARE @ExecuteUpdateStatistics BIT = 1; ---------------CODE SET NOCOUNT ON; IF NOT EXISTS ( SELECT * FROM sys.objects WHERE name = @ObjectName AND schema_id = SCHEMA_ID(@ObjectSchema) AND type = 'V' ) THROW 50000, 'Obiekt nie istnieje lub nie jest widokiem!', 1; DECLARE @ObjectsToProcess TABLE ( id INT IDENTITY PRIMARY KEY , oschema sysname , oname sysname , otype CHAR(2) , AddedFrom NVARCHAR(MAX) NULL ); WITH ObjectsToProcess AS ( SELECT * FROM sys.dm_sql_referenced_entities( QUOTENAME(@ObjectSchema) + '.' + QUOTENAME(@ObjectName) , 'OBJECT' ) ) INSERT INTO @ObjectsToProcess ( oschema , oname , otype ) SELECT DISTINCT oschema = ISNULL(tp.referenced_schema_name, 'dbo') , oname = tp.referenced_entity_name , otype = o.type FROM ObjectsToProcess tp JOIN sys.objects o ON tp.referenced_id = o.object_id; DECLARE @ViewsToExpand TABLE ( id INT IDENTITY PRIMARY KEY , vschema sysname , vname sysname , Processed BIT DEFAULT 0 ); DELETE FROM @ObjectsToProcess OUTPUT Deleted.oschema , Deleted.oname INTO @ViewsToExpand ( vschema , vname ) WHERE otype = 'V'; DECLARE @CurrId INT , @CurrVFullName NVARCHAR(MAX); DECLARE @NestedObjectsToProcess TABLE ( id INT IDENTITY PRIMARY KEY , oschema sysname , oname sysname , otype CHAR(2) ); WHILE EXISTS ( SELECT TOP 1 1 FROM @ViewsToExpand WHERE Processed = 0 ) BEGIN SELECT TOP 1 @CurrId = id , @CurrVFullName = QUOTENAME(vschema) + '.' + QUOTENAME(vname) FROM @ViewsToExpand WHERE Processed = 0; WITH NestedObjectsToProcess AS ( SELECT * FROM sys.dm_sql_referenced_entities(@CurrVFullName, 'OBJECT') ) INSERT INTO @NestedObjectsToProcess ( oschema , oname , otype ) SELECT DISTINCT oschema = ISNULL( tp.referenced_schema_name , 'dbo' ) , oname = tp.referenced_entity_name , otype = o.type FROM NestedObjectsToProcess tp JOIN sys.objects o ON tp.referenced_id = o.object_id; INSERT INTO @ViewsToExpand ( vschema , vname ) SELECT oschema , oname FROM @NestedObjectsToProcess tp LEFT JOIN @ViewsToExpand vte ON tp.oschema = vte.vschema AND tp.oname = vte.vname WHERE tp.otype = 'V' AND vte.id IS NULL; INSERT INTO @ObjectsToProcess ( oschema , oname , otype , AddedFrom ) SELECT ntp.oschema , ntp.oname , ntp.otype , @CurrVFullName FROM @NestedObjectsToProcess ntp LEFT JOIN @ObjectsToProcess otp ON otp.oname = ntp.oname AND otp.oschema = ntp.oschema WHERE ntp.otype != 'V' AND otp.id IS NULL; UPDATE @ViewsToExpand SET Processed = 1 WHERE id = @CurrId; END; /* SEKCJA PODSUMOWANIA */ DECLARE @msg NVARCHAR(MAX) , @txt NVARCHAR(MAX); DECLARE @SCRollDog CURSOR; IF EXISTS ( SELECT TOP 1 1 FROM @ViewsToExpand ) BEGIN SET @msg = 'Znaleziono odniesienia do następujących widoków:'; RAISERROR(@msg, 0, 1) WITH NOWAIT; SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR SELECT txt = QUOTENAME(vschema) + '.' + QUOTENAME(vname) FROM @ViewsToExpand; OPEN @SCRollDog; FETCH NEXT FROM @SCRollDog INTO @txt; WHILE @@FETCH_STATUS = 0 BEGIN SET @msg = ' --> ' + @txt; RAISERROR(@msg, 0, 1) WITH NOWAIT; FETCH NEXT FROM @SCRollDog INTO @txt; END; CLOSE @SCRollDog; DEALLOCATE @SCRollDog; END; --odstep SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT; SET @msg = 'Tabele do aktualizacji statystyk:'; RAISERROR(@msg, 0, 1) WITH NOWAIT; SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR SELECT txt = QUOTENAME(oschema) + '.' + QUOTENAME(oname) + CASE WHEN AddedFrom IS NOT NULL THEN ' (wymagana przez widok: ' + AddedFrom + ')' ELSE '' END FROM @ObjectsToProcess; OPEN @SCRollDog; FETCH NEXT FROM @SCRollDog INTO @txt; WHILE @@FETCH_STATUS = 0 BEGIN SET @msg = ' --> ' + @txt; RAISERROR(@msg, 0, 1) WITH NOWAIT; FETCH NEXT FROM @SCRollDog INTO @txt; END; CLOSE @SCRollDog; DEALLOCATE @SCRollDog; SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT; IF @ExecuteUpdateStatistics = 0 BEGIN SET @msg = 'Skrypt aktualizujący statystyki:'; RAISERROR(@msg, 0, 1) WITH NOWAIT; SET @msg = CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT; SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR SELECT txt = 'UPDATE STATISTICS ' + QUOTENAME(oschema) + '.' + QUOTENAME(oname) + CASE WHEN @WithFullscan = 1 THEN ' WITH FULLSCAN' ELSE '' END FROM @ObjectsToProcess; OPEN @SCRollDog; FETCH NEXT FROM @SCRollDog INTO @txt; WHILE @@FETCH_STATUS = 0 BEGIN RAISERROR(@txt, 0, 1) WITH NOWAIT; FETCH NEXT FROM @SCRollDog INTO @txt; END; CLOSE @SCRollDog; DEALLOCATE @SCRollDog; SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT; END; ELSE --IF @ExecuteUpdateStatistics = 1 BEGIN DECLARE @ObjCount_s NVARCHAR(MAX) = ( SELECT CAST(COUNT(*) AS NVARCHAR(MAX)) FROM @ObjectsToProcess ) , @i INT = 1; DECLARE @DateTimeStart DATETIME2(0) , @DateTimeStop DATETIME2(0) , @TotalDateTimeStart DATETIME2(0) = GETDATE() , @TotalDateTimeStop DATETIME2(0); DECLARE @CurrObject NVARCHAR(MAX); SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR SELECT txt = 'UPDATE STATISTICS ' + QUOTENAME(oschema) + '.' + QUOTENAME(oname) + CASE WHEN @WithFullscan = 1 THEN ' WITH FULLSCAN' ELSE '' END , CurrObject = QUOTENAME(oschema) + '.' + QUOTENAME(oname) FROM @ObjectsToProcess; OPEN @SCRollDog; FETCH NEXT FROM @SCRollDog INTO @txt , @CurrObject; WHILE @@FETCH_STATUS = 0 BEGIN SET @DateTimeStart = GETDATE(); SET @msg = 'Processing: ' + @CurrObject + ' (' + CAST(@i AS NVARCHAR(MAX)) + '/' + @ObjCount_s + ') , start: ' + CAST(@DateTimeStart AS NVARCHAR(MAX)); RAISERROR(@msg, 0, 1) WITH NOWAIT; EXEC ( @txt ); SET @DateTimeStop = GETDATE(); SET @msg = 'Processing: ' + @CurrObject + ' DONE in: ' + CONVERT( NVARCHAR , DATEADD( ss , DATEDIFF( ss , @DateTimeStart , @DateTimeStop ) , 0 ) , 108 ) + ' , stop: ' + CAST(@DateTimeStop AS NVARCHAR(MAX)); RAISERROR(@msg, 0, 1) WITH NOWAIT; RAISERROR('', 0, 1) WITH NOWAIT; FETCH NEXT FROM @SCRollDog INTO @txt , @CurrObject; SET @i += 1; END; CLOSE @SCRollDog; DEALLOCATE @SCRollDog; SET @TotalDateTimeStop = GETDATE(); SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT; SET @msg = 'PROCESSING DONE! Total time: ' + CONVERT( NVARCHAR , DATEADD( ss , DATEDIFF( ss , @TotalDateTimeStart , @TotalDateTimeStop ) , 0 ) , 108 ); RAISERROR(@msg, 0, 1) WITH NOWAIT; END;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325

DECLARE @ObjectName sysname = 'VIEW_NAME'; DECLARE @ObjectSchema sysname = 'dbo';   DECLARE @WithFullscan BIT = 1;   DECLARE @ExecuteUpdateStatistics BIT = 1;     ---------------CODE SET NOCOUNT ON;   IF NOT EXISTS (   SELECT *                   FROM   sys.objects                   WHERE  name = @ObjectName                          AND schema_id = SCHEMA_ID(@ObjectSchema)                          AND type = 'V'               )     THROW 50000, 'Obiekt nie istnieje lub nie jest widokiem!', 1;     DECLARE @ObjectsToProcess TABLE     (         id INT IDENTITY PRIMARY KEY ,         oschema sysname ,         oname sysname ,         otype CHAR(2) ,         AddedFrom NVARCHAR(MAX) NULL     );   WITH ObjectsToProcess AS ( SELECT *      FROM   sys.dm_sql_referenced_entities(                                               QUOTENAME(@ObjectSchema) + '.'                                               + QUOTENAME(@ObjectName) ,                                               'OBJECT'                                           )    ) INSERT INTO @ObjectsToProcess (   oschema ,                                   oname ,                                   otype                               )             SELECT DISTINCT oschema = ISNULL(tp.referenced_schema_name, 'dbo') ,                    oname = tp.referenced_entity_name ,                    otype = o.type             FROM   ObjectsToProcess tp                    JOIN sys.objects o ON tp.referenced_id = o.object_id;     DECLARE @ViewsToExpand TABLE     (         id INT IDENTITY PRIMARY KEY ,         vschema sysname ,         vname sysname ,         Processed BIT             DEFAULT 0     );   DELETE FROM @ObjectsToProcess OUTPUT Deleted.oschema ,        Deleted.oname INTO @ViewsToExpand (   vschema ,                         vname                     ) WHERE otype = 'V';   DECLARE @CurrId INT ,         @CurrVFullName NVARCHAR(MAX);     DECLARE @NestedObjectsToProcess TABLE     (         id INT IDENTITY PRIMARY KEY ,         oschema sysname ,         oname sysname ,         otype CHAR(2)     );     WHILE EXISTS (   SELECT TOP 1 1                  FROM   @ViewsToExpand                  WHERE  Processed = 0              )     BEGIN           SELECT TOP 1 @CurrId = id ,                @CurrVFullName = QUOTENAME(vschema) + '.' + QUOTENAME(vname)         FROM   @ViewsToExpand         WHERE  Processed = 0;               WITH NestedObjectsToProcess         AS ( SELECT *              FROM   sys.dm_sql_referenced_entities(@CurrVFullName, 'OBJECT')            )         INSERT INTO @NestedObjectsToProcess (   oschema ,                                                 oname ,                                                 otype                                             )                     SELECT DISTINCT oschema = ISNULL(                                                         tp.referenced_schema_name ,                                                         'dbo'                                                     ) ,                            oname = tp.referenced_entity_name ,                            otype = o.type                     FROM   NestedObjectsToProcess tp                            JOIN sys.objects o ON tp.referenced_id = o.object_id;           INSERT INTO @ViewsToExpand (   vschema ,                                        vname                                    )                     SELECT oschema ,                            oname                     FROM   @NestedObjectsToProcess tp                            LEFT JOIN @ViewsToExpand vte ON tp.oschema = vte.vschema                                                            AND tp.oname = vte.vname                     WHERE  tp.otype = 'V'                            AND vte.id IS NULL;           INSERT INTO @ObjectsToProcess (   oschema ,                                           oname ,                                           otype ,                                           AddedFrom                                       )                     SELECT ntp.oschema ,                            ntp.oname ,                            ntp.otype ,                            @CurrVFullName                     FROM   @NestedObjectsToProcess ntp                            LEFT JOIN @ObjectsToProcess otp ON otp.oname = ntp.oname                                                               AND otp.oschema = ntp.oschema                     WHERE  ntp.otype != 'V'                            AND otp.id IS NULL;               UPDATE @ViewsToExpand         SET    Processed = 1         WHERE  id = @CurrId;       END;     /* SEKCJA PODSUMOWANIA */ DECLARE @msg NVARCHAR(MAX) ,         @txt NVARCHAR(MAX); DECLARE @SCRollDog CURSOR;     IF EXISTS (   SELECT TOP 1 1               FROM   @ViewsToExpand           )     BEGIN           SET @msg = 'Znaleziono odniesienia do następujących widoków:';         RAISERROR(@msg, 0, 1) WITH NOWAIT;           SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR         SELECT txt = QUOTENAME(vschema) + '.' + QUOTENAME(vname)         FROM   @ViewsToExpand;         OPEN @SCRollDog;         FETCH NEXT FROM @SCRollDog         INTO @txt;         WHILE @@FETCH_STATUS = 0             BEGIN                 SET @msg = ' --> ' + @txt;                 RAISERROR(@msg, 0, 1) WITH NOWAIT;                 FETCH NEXT FROM @SCRollDog                 INTO @txt;             END;         CLOSE @SCRollDog;         DEALLOCATE @SCRollDog;       END;   --odstep SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT;   SET @msg = 'Tabele do aktualizacji statystyk:'; RAISERROR(@msg, 0, 1) WITH NOWAIT;   SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR SELECT txt = QUOTENAME(oschema) + '.' + QUOTENAME(oname)              + CASE WHEN AddedFrom IS NOT NULL THEN                         ' (wymagana przez widok: ' + AddedFrom + ')'                     ELSE ''                END FROM   @ObjectsToProcess; OPEN @SCRollDog; FETCH NEXT FROM @SCRollDog INTO @txt; WHILE @@FETCH_STATUS = 0     BEGIN         SET @msg = ' --> ' + @txt;         RAISERROR(@msg, 0, 1) WITH NOWAIT;         FETCH NEXT FROM @SCRollDog         INTO @txt;     END; CLOSE @SCRollDog; DEALLOCATE @SCRollDog;   SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); RAISERROR(@msg, 0, 1) WITH NOWAIT;     IF @ExecuteUpdateStatistics = 0     BEGIN         SET @msg = 'Skrypt aktualizujący statystyki:';         RAISERROR(@msg, 0, 1) WITH NOWAIT;         SET @msg = CHAR(13) + CHAR(10);         RAISERROR(@msg, 0, 1) WITH NOWAIT;           SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR         SELECT txt = 'UPDATE STATISTICS ' + QUOTENAME(oschema) + '.'                      + QUOTENAME(oname)                      + CASE WHEN @WithFullscan = 1 THEN ' WITH FULLSCAN'                             ELSE ''                        END         FROM   @ObjectsToProcess;         OPEN @SCRollDog;         FETCH NEXT FROM @SCRollDog         INTO @txt;         WHILE @@FETCH_STATUS = 0             BEGIN                   RAISERROR(@txt, 0, 1) WITH NOWAIT;                 FETCH NEXT FROM @SCRollDog                 INTO @txt;             END;         CLOSE @SCRollDog;         DEALLOCATE @SCRollDog;           SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);         RAISERROR(@msg, 0, 1) WITH NOWAIT;     END; ELSE --IF @ExecuteUpdateStatistics = 1     BEGIN         DECLARE @ObjCount_s NVARCHAR(MAX) = (   SELECT CAST(COUNT(*) AS NVARCHAR(MAX))                                                 FROM   @ObjectsToProcess                                             ) ,                 @i INT = 1;         DECLARE @DateTimeStart DATETIME2(0) ,                 @DateTimeStop DATETIME2(0) ,                 @TotalDateTimeStart DATETIME2(0) = GETDATE() ,                 @TotalDateTimeStop DATETIME2(0);           DECLARE @CurrObject NVARCHAR(MAX);               SET @SCRollDog = CURSOR LOCAL FAST_FORWARD FOR         SELECT txt = 'UPDATE STATISTICS ' + QUOTENAME(oschema) + '.'                      + QUOTENAME(oname)                      + CASE WHEN @WithFullscan = 1 THEN ' WITH FULLSCAN'                             ELSE ''                        END ,                CurrObject = QUOTENAME(oschema) + '.' + QUOTENAME(oname)         FROM   @ObjectsToProcess;         OPEN @SCRollDog;         FETCH NEXT FROM @SCRollDog         INTO @txt ,              @CurrObject;         WHILE @@FETCH_STATUS = 0             BEGIN                 SET @DateTimeStart = GETDATE();                 SET @msg = 'Processing: ' + @CurrObject + ' ('                            + CAST(@i AS NVARCHAR(MAX)) + '/' + @ObjCount_s                            + ') , start: '                            + CAST(@DateTimeStart AS NVARCHAR(MAX));                 RAISERROR(@msg, 0, 1) WITH NOWAIT;                   EXEC ( @txt );                   SET @DateTimeStop = GETDATE();                 SET @msg = 'Processing: ' + @CurrObject + ' DONE in: '                            + CONVERT(                                         NVARCHAR ,                                         DATEADD(                                                    ss ,                                                    DATEDIFF(                                                                ss ,                                                                @DateTimeStart ,                                                                @DateTimeStop                                                            ) ,                                                    0                                                ) ,                                         108                                     ) + ' , stop: '                            + CAST(@DateTimeStop AS NVARCHAR(MAX));                   RAISERROR(@msg, 0, 1) WITH NOWAIT;                   RAISERROR('', 0, 1) WITH NOWAIT;                   FETCH NEXT FROM @SCRollDog                 INTO @txt ,                      @CurrObject;                 SET @i += 1;             END;         CLOSE @SCRollDog;         DEALLOCATE @SCRollDog;           SET @TotalDateTimeStop = GETDATE();           SET @msg = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);         RAISERROR(@msg, 0, 1) WITH NOWAIT;           SET @msg = 'PROCESSING DONE! Total time: '                    + CONVERT(                                 NVARCHAR ,                                 DATEADD(                                            ss ,                                            DATEDIFF(                                                        ss ,                                                        @TotalDateTimeStart ,                                                        @TotalDateTimeStop                                                    ) ,                                            0                                        ) ,                                 108                             );         RAISERROR(@msg, 0, 1) WITH NOWAIT;       END;

 

6 lipca 201715 kwietnia 2020 Michał PawlikowskiPrzydatne skrypty, SQL Server

Statystyki do aktualizacji

Transact-SQL

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] , [ss].[name] AS [Statistic] , [sp].[last_updated] AS [StatsLastUpdated] , [sp].[rows] AS [RowsInTable] , [sp].[rows_sampled] AS [RowsSampled] , [sp].[modification_counter] AS [RowModifications] FROM [sys].[stats] [ss] JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id] JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id] OUTER APPLY [sys].[dm_db_stats_properties]( [so].[object_id] , [ss].[stats_id] ) sp WHERE [so].[type] = 'U' --AND [sp].[modification_counter] > 0 ORDER BY [sp].[last_updated] DESC;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT   [sch].[name] + '.' + [so].[name] AS [TableName] ,          [ss].[name] AS [Statistic] ,          [sp].[last_updated] AS [StatsLastUpdated] ,          [sp].[rows] AS [RowsInTable] ,          [sp].[rows_sampled] AS [RowsSampled] ,          [sp].[modification_counter] AS [RowModifications] FROM     [sys].[stats] [ss]          JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]          JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]          OUTER APPLY [sys].[dm_db_stats_properties](                                                        [so].[object_id] ,                                                        [ss].[stats_id]                                                    ) sp WHERE    [so].[type] = 'U' --AND [sp].[modification_counter] > 0 ORDER BY [sp].[last_updated] DESC;

 

1 stycznia 190015 kwietnia 2020 Michał PawlikowskiPrzydatne skrypty

Statystyki filestats – io na serwerze

Transact-SQL

USE master go SET NOCOUNT ON DECLARE @crDate DateTime; DECLARE @hours DECIMAL(18,3), @Days int; DECLARE @FinalHours int, @FinalMinutes int, @FinalSeconds int, @total_seconds int; -- Determine uptime by checking Tempdb creation datetime: SELECT top 1 @crdate=create_date FROM sys.databases WHERE NAME='tempdb' SELECT @hours = DATEDIFF(ss,@crDate,GETDATE())/CAST(60 AS Decimal)/CAST(60 AS Decimal); PRINT 'SQL Server instance '+ @@SERVERNAME + '\' + @@SERVICENAME + ' is Up since: ' + CAST (@crdate as varchar) ; -- From hours to days: SELECT @Days = @hours/CAST(24 AS Decimal); -- Determine the remaining part of the hours: SELECT @FinalHours = @hours - (@Days*CAST(24 AS Decimal)) -- Remaining minutes: SELECT @FinalMinutes = (@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60; -- Remaining seconds: SELECT @FinalSeconds = (((@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60) - @Finalminutes) * 60; PRINT 'Or: '+ CAST(@Days as varchar) + ' Days, ' + CAST(@FinalHours as varchar) + ' Hours,' + CAST(@FinalMinutes as varchar) + ' Minutes and ' + CAST(@FinalSeconds as varchar) + ' Seconds.' SELECT @total_seconds = (CAST(@Days AS decimal(12,2))*24*3600 + CAST(@Finalhours AS decimal(12,2))*3600 + CAST(@Finalminutes AS decimal(12,2))*60 ) + CAST(@Finalseconds AS decimal(12,2)) PRINT 'Total uptime in seconds: '+ CONVERT(VARCHAR(20) ,@total_seconds ) SELECT @@SERVERNAME as Hostname, @@SERVICENAME as Instancename, @crdate AS SQL_Start_Date_Time , @total_seconds as TotalSeconds_Up SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , (num_of_bytes_read / 1024 / 1024 /1024) as GB_Read_Total, num_of_bytes_read / @total_seconds * 3600 * 24 /1024/1024/1024 as AVG_GB_read_per_day_ESTIMATE, CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , num_of_bytes_written / 1024 / 1024/1024 as GB_Written_Total, num_of_bytes_written /@total_seconds * 3600 * 24 /1024/1024/1024 as AVG_GB_Written_per_day_ESTIMATE, CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [IO_Stalls] , num_of_reads + num_of_writes AS [Total_IO] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [AVG_IO_stall_ms] FROM sys.dm_io_virtual_file_stats(NULL, NULL) --ORDER BY avg_io_stall_ms DESC ; order by GB_read_total DESC

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

USE master go   SET NOCOUNT ON DECLARE @crDate DateTime; DECLARE @hours DECIMAL(18,3), @Days int; DECLARE @FinalHours int, @FinalMinutes int, @FinalSeconds int, @total_seconds  int;     -- Determine uptime by checking Tempdb creation datetime: SELECT top 1 @crdate=create_date FROM sys.databases WHERE NAME='tempdb' SELECT @hours = DATEDIFF(ss,@crDate,GETDATE())/CAST(60 AS Decimal)/CAST(60 AS Decimal); PRINT 'SQL Server instance '+ @@SERVERNAME + '\' + @@SERVICENAME + ' is Up since: ' + CAST (@crdate as varchar)  ;   -- From hours to days: SELECT @Days = @hours/CAST(24 AS Decimal);   -- Determine the remaining part of the hours: SELECT @FinalHours = @hours - (@Days*CAST(24 AS Decimal))   -- Remaining minutes: SELECT @FinalMinutes = (@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60;   -- Remaining seconds: SELECT @FinalSeconds = (((@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60) - @Finalminutes) * 60;   PRINT 'Or: '+ CAST(@Days as varchar) + ' Days, ' + CAST(@FinalHours as varchar) + ' Hours,' + CAST(@FinalMinutes as varchar) + ' Minutes and ' +  CAST(@FinalSeconds as varchar) + ' Seconds.'   SELECT  @total_seconds = (CAST(@Days AS decimal(12,2))*24*3600 + CAST(@Finalhours AS decimal(12,2))*3600 + CAST(@Finalminutes AS decimal(12,2))*60 ) + CAST(@Finalseconds AS decimal(12,2)) PRINT 'Total uptime in seconds: '+ CONVERT(VARCHAR(20) ,@total_seconds )   SELECT @@SERVERNAME as Hostname, @@SERVICENAME as Instancename, @crdate AS SQL_Start_Date_Time ,  @total_seconds as TotalSeconds_Up   SELECT  DB_NAME(database_id) AS [Database Name] ,         file_id ,         io_stall_read_ms ,         num_of_reads ,             (num_of_bytes_read / 1024 / 1024 /1024) as GB_Read_Total,             num_of_bytes_read / @total_seconds * 3600 * 24 /1024/1024/1024  as AVG_GB_read_per_day_ESTIMATE,         CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1))             AS [avg_read_stall_ms] ,         io_stall_write_ms ,         num_of_writes ,             num_of_bytes_written / 1024 / 1024/1024 as GB_Written_Total,             num_of_bytes_written /@total_seconds * 3600 * 24 /1024/1024/1024  as AVG_GB_Written_per_day_ESTIMATE,         CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1))             AS [avg_write_stall_ms] ,         io_stall_read_ms + io_stall_write_ms AS [IO_Stalls] ,         num_of_reads + num_of_writes AS [Total_IO] ,         CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads                                                           + num_of_writes)            AS NUMERIC(10,1)) AS [AVG_IO_stall_ms] FROM    sys.dm_io_virtual_file_stats(NULL, NULL) --ORDER BY avg_io_stall_ms DESC ; order by GB_read_total DESC