-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatabase_tuner.sql
More file actions
13816 lines (12639 loc) · 553 KB
/
database_tuner.sql
File metadata and controls
13816 lines (12639 loc) · 553 KB
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
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
------------------------------------------------------------
-- Database Tuner v2.13
------------------------------------------------------------
-- Description:
-- Collects a read-only analytical snapshot of a database's performance signals and schema.
-- Stores results in temp tables and then exports to a single markdown file (*.md).
-- Markdown can then be uploaded to an LLM of your choice for review and deep analysis.
-- Requirements:
-- SQL Server 2017 minimum (compat 100+) (not tested with Azure SQL Database).
-- LLM such as ChatGPT, Claude, Gemini etc. The deeper thinking model, the better.
-- Usage:
-- 1) SSMS -> Query -> SQLCMD Mode (enable)
-- 2) Update "User Config" section below (ensure OutputDir exists)
-- 3) Run and monitor progress from "Messages" tab
-- 4) Collect generated file from OutputDir (filename: dt_report ({TargetDB} - {Version}).md)
-- 5) Upload to LLM (ChatGPT etc) for analysis (zip md if need)
-- Notes:
-- - Ensure OutputDir exists otherwise data will be printed to the SSMS console instead of to file.
-- - Toggle ExportSchema or SafeMode (which redacts sensitive information) when needed (1 or 0).
-- - Ignore "The join order has been enforced" and "Null value is eliminated by an aggregate" warnings.
-- - No database changes are made by this script aside from temp tables which are discarded when query closes.
-- - Author: Dean Lafferty (laffo16@hotmail.com)
------------------------------------------------------------
-- User Config
------------------------------------------------------------
:SETVAR TargetDB "DatabaseName"
:SETVAR OutputDir "C:\Temp\DatabaseTuner\"
:SETVAR ExportSchema "1"
:SETVAR SafeMode "1"
------------------------------------------------------------
-- Version
------------------------------------------------------------
:SETVAR Version "2.13"
------------------------------------------------------------
-- Prerequisites
------------------------------------------------------------
:ON ERROR EXIT
DECLARE @ProductMajorVersion int = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));
DECLARE @CompatLevel int = (SELECT compatibility_level FROM sys.databases WHERE name = '$(TargetDB)');
IF @ProductMajorVersion < 14
RAISERROR('Database Tuner requires SQL Server 2017 (14.x) or higher.', 16, 1);
ELSE IF @CompatLevel < 100
RAISERROR('Database Tuner requires Database Compatability Level 100 or higher.', 16, 1);
GO
------------------------------------------------------------
-- Initialisation
------------------------------------------------------------
USE $(TargetDB)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 15000
DECLARE @ProductMajorVersion int = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));
DECLARE @CompatLevel int = (SELECT compatibility_level FROM sys.databases WHERE name = '$(TargetDB)');
DECLARE @IsSysAdmin bit = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0);
DECLARE @ExportSchema int = IIF('$(ExportSchema)' = '1', 1, 0);
DECLARE @SafeMode int = IIF('$(SafeMode)' = '1', 1, 0);
EXEC sys.sp_set_session_context 'ProductMajorVersion', @ProductMajorVersion;
EXEC sys.sp_set_session_context 'CompatLevel', @CompatLevel;
EXEC sys.sp_set_session_context 'IsSysAdmin', @IsSysAdmin;
EXEC sys.sp_set_session_context 'ExportSchema', @ExportSchema;
EXEC sys.sp_set_session_context 'SafeMode', @SafeMode;
PRINT 'Database Tuner Report $(Version)'
GO
------------------------------------------------------------
-- 00a. Metadata
------------------------------------------------------------
PRINT N'▶ 00a. Metadata - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_Metadata') IS NOT NULL DROP TABLE #DTR_Metadata
GO
SELECT
IDENTITY(int) AS RowNumber,
SYSUTCDATETIME() AS CollectionTimeUtc,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('ServerName'), '[SafeMode]') AS ServerName,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('InstanceName'), '[SafeMode]') AS InstanceName,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('MachineName'), '[SafeMode]') AS MachineName,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), '[SafeMode]') AS ComputerNamePhysicalNetBIOS,
SERVERPROPERTY('Collation') AS ServerCollation,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductBuild') AS ProductBuild,
SERVERPROPERTY('ProductBuildType') AS ProductBuildType,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('InstanceDefaultBackupPath'), '[SafeMode]') AS InstanceDefaultBackupPath,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('InstanceDefaultDataPath'), '[SafeMode]') AS InstanceDefaultDataPath,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('InstanceDefaultLogPath'), '[SafeMode]') AS InstanceDefaultLogPath,
SERVERPROPERTY('IsClustered') AS IsClustered,
SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
SERVERPROPERTY('HadrManagerStatus') AS HadrManagerStatus,
SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, SERVERPROPERTY('FilestreamShareName'), '[SafeMode]') AS FilestreamShareName,
SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
DB_NAME() AS DatabaseName,
DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS DatabaseRecoveryModel,
DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation,
DATABASEPROPERTYEX(DB_NAME(), 'Status') AS DatabaseStatus,
DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS DatabaseUpdateability,
DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') AS DatabaseUserAccess,
DATABASEPROPERTYEX(DB_NAME(), 'IsReadCommittedSnapshotOn') AS IsReadCommittedSnapshotOn,
DATABASEPROPERTYEX(DB_NAME(), 'IsSnapshotIsolationOn') AS IsSnapshotIsolationOn,
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatisticsAsync') AS IsAutoUpdateStatisticsAsync,
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTYEX(DB_NAME(), 'IsParameterizationForced') AS IsParameterizationForced,
DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullDefault') AS IsAnsiNullDefaultOn,
DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiWarningsOn') AS IsAnsiWarningsOn,
DATABASEPROPERTYEX(DB_NAME(), 'IsArithAbortOn') AS IsArithAbortOn,
DATABASEPROPERTYEX(DB_NAME(), 'IsBrokerEnabled') AS IsBrokerEnabled,
DATABASEPROPERTYEX(DB_NAME(), 'IsSyncWithBackup') AS IsSyncWithBackup,
DATABASEPROPERTYEX(DB_NAME(), 'LastBackupTime') AS LastBackupTime,
DATABASEPROPERTYEX(DB_NAME(), 'LastLogBackupTime') AS LastLogBackupTime,
DATABASEPROPERTYEX(DB_NAME(), 'LastGoodCheckDbTime') AS LastGoodCheckDbTime
INTO #DTR_Metadata
GO
------------------------------------------------------------
-- 00b. Database Configurations
------------------------------------------------------------
PRINT N'▶ 00b. Database Configurations - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_DatabaseConfigurations') IS NOT NULL DROP TABLE #DTR_DatabaseConfigurations
GO
SELECT
IDENTITY(int) AS RowNumber,
compatibility_level,
recovery_model_desc,
page_verify_option_desc,
user_access_desc,
containment_desc,
is_read_only,
is_encrypted,
is_query_store_on,
JSON_VALUE((SELECT d.* FOR JSON PATH), '$[0].is_result_set_caching_on') AS is_result_set_caching_on,
JSON_VALUE((SELECT d.* FOR JSON PATH), '$[0].is_tempdb_spill_to_remote_store') AS is_tempdb_spill_to_remote_store,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on,
is_auto_close_on,
is_auto_shrink_on,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on,
is_auto_create_stats_incremental_on,
is_parameterization_forced,
delayed_durability_desc,
target_recovery_time_in_seconds,
is_cdc_enabled,
log_reuse_wait_desc,
log_reuse_wait
INTO #DTR_DatabaseConfigurations
FROM sys.databases AS d
WHERE d.database_id = DB_ID()
GO
------------------------------------------------------------
-- 00c. Database-Scoped Configurations
------------------------------------------------------------
PRINT N'▶ 00c. Database-Scoped Configurations - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_DatabaseScopedConfigurations') IS NOT NULL DROP TABLE #DTR_DatabaseScopedConfigurations
GO
SELECT
IDENTITY(int) AS RowNumber,
configuration_id,
name,
value,
value_for_secondary,
is_value_default
INTO #DTR_DatabaseScopedConfigurations
FROM sys.database_scoped_configurations
ORDER BY name
GO
------------------------------------------------------------
-- 00d. Instance-Level Configurations
------------------------------------------------------------
PRINT N'▶ 00d. Instance-Level Configurations - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_InstanceConfigs') IS NOT NULL DROP TABLE #DTR_InstanceConfigs
GO
SELECT
IDENTITY(int) AS RowNumber,
configuration_id,
name,
minimum,
maximum,
value,
value_in_use,
is_dynamic,
is_advanced,
description
INTO #DTR_InstanceConfigs
FROM sys.configurations
ORDER BY name
GO
------------------------------------------------------------
-- 00e. Server Environment
------------------------------------------------------------
PRINT N'▶ 00e. Server Environment - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_ServerInfo') IS NOT NULL DROP TABLE #DTR_ServerInfo
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
cpu_count,
scheduler_count,
hyperthread_ratio,
socket_count,
max_workers_count,
sqlserver_start_time,
physical_memory_kb / 1024 AS physical_memory_mb,
committed_target_kb / 1024 AS committed_target_mb,
committed_kb / 1024 AS committed_memory_mb,
virtual_machine_type_desc,
sql_memory_model_desc,
softnuma_configuration_desc,
host_platform,
host_distribution,
host_release,
host_service_pack_level,
host_sku,
os_language_version
INTO #DTR_ServerInfo
FROM sys.dm_os_sys_info
CROSS JOIN sys.dm_os_host_info
END
GO
------------------------------------------------------------
-- 00f. Automatic Tuning Options (2017+)
------------------------------------------------------------
PRINT N'▶ 00f. Automatic Tuning Options (2017+) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_AutoTuningOptions') IS NOT NULL DROP TABLE #DTR_AutoTuningOptions
GO
SELECT
IDENTITY(int) AS RowNumber,
name,
desired_state,
desired_state_desc,
actual_state,
actual_state_desc,
reason,
reason_desc
INTO #DTR_AutoTuningOptions
FROM sys.database_automatic_tuning_options
ORDER BY name
GO
------------------------------------------------------------
-- 00g. Host OS Info (2017+)
------------------------------------------------------------
PRINT N'▶ 00g. Host OS Info (2017+) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_HostInfo') IS NOT NULL DROP TABLE #DTR_HostInfo
GO
SELECT
IDENTITY(int) AS RowNumber,
host_platform,
host_distribution,
host_release,
host_service_pack_level,
host_sku,
os_language_version
INTO #DTR_HostInfo
FROM sys.dm_os_host_info
GO
------------------------------------------------------------
-- 00h. HADR Replica State (AG)
------------------------------------------------------------
PRINT N'▶ 00h. HADR Replica State (AG) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_HADR_DBReplicaState') IS NOT NULL DROP TABLE #DTR_HADR_DBReplicaState
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
DB_NAME(drs.database_id) AS database_name,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, ar.replica_server_name, '[SafeMode]') AS replica_server_name,
ars.role_desc,
drs.is_local,
drs.is_primary_replica,
drs.is_commit_participant,
drs.is_suspended,
drs.suspend_reason_desc,
drs.synchronization_state_desc,
drs.synchronization_health_desc,
ars.operational_state_desc,
ars.recovery_health_desc,
drs.database_state_desc,
drs.log_send_queue_size,
drs.log_send_rate,
drs.redo_queue_size,
drs.redo_rate,
drs.secondary_lag_seconds,
drs.last_sent_time,
drs.last_received_time,
drs.last_hardened_time,
drs.last_redone_time,
drs.last_commit_time,
ars.connected_state_desc,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.primary_role_allow_connections_desc,
ar.secondary_role_allow_connections_desc,
ar.session_timeout,
ag.required_synchronized_secondaries_to_commit,
ag.automated_backup_preference_desc,
ag.is_distributed,
ag.dtc_support,
ag.basic_features
INTO #DTR_HADR_DBReplicaState
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
WHERE drs.database_id = DB_ID()
END
GO
------------------------------------------------------------
-- 00i. Extended Events Sessions (Defined vs Running)
------------------------------------------------------------
PRINT N'▶ 00i. Extended Events Sessions (Defined vs Running) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_XESessions') IS NOT NULL DROP TABLE #DTR_XESessions
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
;WITH defn AS (
SELECT
s.event_session_id,
s.name,
s.startup_state,
s.event_retention_mode_desc,
s.memory_partition_mode_desc,
s.max_memory,
s.max_dispatch_latency,
s.track_causality
FROM sys.server_event_sessions AS s
),
run AS (
SELECT
xs.name,
1 AS is_running,
xs.create_time,
xs.buffer_policy_desc,
xs.pending_buffers,
xs.total_buffer_size,
xs.total_bytes_generated,
xs.buffer_processed_count,
xs.dropped_event_count,
xs.dropped_buffer_count
FROM sys.dm_xe_sessions AS xs
),
tgt AS (
SELECT
st.event_session_id,
st.name AS target_name
FROM sys.server_event_session_targets AS st
),
fdef AS (
SELECT
t.event_session_id,
f1.def_file_name,
f2.def_max_file_size_mb,
f3.def_max_rollover_files
FROM sys.server_event_session_targets AS t
LEFT JOIN (
SELECT
event_session_id,
object_id,
CONVERT(nvarchar(4000), value) AS def_file_name
FROM sys.server_event_session_fields
WHERE name = 'filename'
) AS f1 ON f1.event_session_id = t.event_session_id AND f1.object_id = t.target_id
LEFT JOIN (
SELECT
event_session_id,
object_id,
CONVERT(int, value) AS def_max_file_size_mb
FROM sys.server_event_session_fields
WHERE name IN ('max_file_size', 'maxFileSize')
) AS f2 ON f2.event_session_id = t.event_session_id AND f2.object_id = t.target_id
LEFT JOIN (
SELECT
event_session_id,
object_id,
CONVERT(int, value) AS def_max_rollover_files
FROM sys.server_event_session_fields
WHERE name IN ('max_rollover_files', 'maxRolloverFiles')
) AS f3 ON f3.event_session_id = t.event_session_id AND f3.object_id = t.target_id
WHERE t.name = 'event_file'
),
rfile AS (
SELECT
xs.name AS session_name,
CAST(xst.target_data AS xml) AS xdata,
xst.execution_count,
xst.execution_duration_ms,
xst.bytes_written
FROM sys.dm_xe_sessions AS xs
JOIN sys.dm_xe_session_targets AS xst ON xst.event_session_address = xs.address
WHERE xst.target_name = 'event_file'
)
SELECT
IDENTITY(int) AS RowNumber,
d.name AS session_name,
d.startup_state,
d.event_retention_mode_desc,
d.memory_partition_mode_desc,
d.max_memory,
d.max_dispatch_latency,
d.track_causality,
CASE WHEN run.is_running = 1 THEN 1 ELSE 0 END AS is_running,
run.create_time AS session_start_time,
run.buffer_policy_desc,
run.pending_buffers,
run.total_buffer_size,
run.total_bytes_generated,
run.buffer_processed_count,
run.dropped_event_count,
run.dropped_buffer_count,
CASE
WHEN d.event_retention_mode_desc IN ('ALLOW_SINGLE_EVENT_LOSS', 'ALLOW_MULTIPLE_EVENT_LOSS') THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS is_lossy_mode,
CASE
WHEN ISNULL(run.dropped_event_count, 0) > 0
OR ISNULL(run.dropped_buffer_count, 0) > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS is_dropping_events,
t.target_name,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, ISNULL(rf_runtime.file_name, fdef.def_file_name), '[SafeMode]') AS file_name,
ISNULL(rf_runtime.max_file_size_mb, fdef.def_max_file_size_mb) AS max_file_size_mb,
ISNULL(rf_runtime.max_rollover_files, fdef.def_max_rollover_files) AS max_rollover_files,
rfile.execution_count AS target_execution_count,
rfile.bytes_written AS target_bytes_written
INTO #DTR_XESessions
FROM defn AS d
LEFT JOIN run ON run.name = d.name
LEFT JOIN tgt AS t ON t.event_session_id = d.event_session_id
LEFT JOIN fdef ON fdef.event_session_id = d.event_session_id
LEFT JOIN rfile ON rfile.session_name = d.name
OUTER APPLY (
SELECT
CASE WHEN run.is_running = 1 THEN rfile.xdata.value('(//EventFileTarget/@name)[1]', 'nvarchar(4000)') END AS file_name,
CASE WHEN run.is_running = 1 THEN COALESCE(
rfile.xdata.value('(//EventFileTarget/@max_file_size)[1]', 'int'),
rfile.xdata.value('(//EventFileTarget/@maxFileSize)[1]', 'int')
) END AS max_file_size_mb,
CASE WHEN run.is_running = 1 THEN COALESCE(
rfile.xdata.value('(//EventFileTarget/@max_rollover_files)[1]', 'int'),
rfile.xdata.value('(//EventFileTarget/@maxRolloverFiles)[1]', 'int')
) END AS max_rollover_files
) AS rf_runtime
END
GO
------------------------------------------------------------
-- 00j. Availability Groups (Group Overview & Backup Preference)
------------------------------------------------------------
PRINT N'▶ 00j. Availability Groups (Group Overview & Backup Preference) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_AG_Overview') IS NOT NULL DROP TABLE #DTR_AG_Overview
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
ag.name,
ag.automated_backup_preference_desc,
ag.cluster_type_desc,
ag.failure_condition_level,
ag.health_check_timeout,
ag.version,
ags.primary_replica,
ags.primary_recovery_health_desc,
ags.secondary_recovery_health_desc,
ags.synchronization_health_desc,
ag.required_synchronized_secondaries_to_commit,
ag.is_distributed,
ag.dtc_support,
ag.basic_features,
agl.dns_name AS listener_dns_name,
agl.port AS listener_port,
agip.ip_address AS listener_ip_address,
agip.state_desc AS listener_ip_state,
agip.is_dhcp AS listener_ip_is_dhcp,
JSON_VALUE((SELECT ag.* FOR JSON PATH), '$[0].is_contained') AS is_contained,
JSON_VALUE((SELECT ags.* FOR JSON PATH), '$[0].write_lease_remaining_ticks') AS write_lease_remaining_ticks,
JSON_VALUE((SELECT ags.* FOR JSON PATH), '$[0].current_configuration_commit_start_time_utc') AS current_configuration_commit_start_time_utc,
JSON_VALUE((SELECT dbs.* FOR JSON PATH), '$[0].quorum_commit_lsn') AS quorum_commit_lsn,
JSON_VALUE((SELECT dbs.* FOR JSON PATH), '$[0].quorum_commit_time') AS quorum_commit_time,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, JSON_VALUE((SELECT ar.* FOR JSON PATH), '$[0].read_write_routing_url'), '[SafeMode]') AS read_write_routing_url
INTO #DTR_AG_Overview
FROM sys.availability_groups AS ag
LEFT JOIN sys.dm_hadr_availability_group_states AS ags ON ag.group_id = ags.group_id
LEFT JOIN sys.availability_group_listeners AS agl ON agl.group_id = ag.group_id
LEFT JOIN sys.availability_group_listener_ip_addresses AS agip ON agip.listener_id = agl.listener_id
LEFT JOIN sys.availability_replicas AS ar ON ar.group_id = ag.group_id
OUTER APPLY (
SELECT TOP (1) *
FROM sys.dm_hadr_database_replica_states AS d
WHERE d.group_id = ag.group_id
ORDER BY d.database_id
) AS dbs
END
GO
------------------------------------------------------------
-- 00k. Resource Governor (Configuration & State)
------------------------------------------------------------
PRINT N'▶ 00k. Resource Governor (Configuration & State) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_RG_Config') IS NOT NULL DROP TABLE #DTR_RG_Config
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
rgc.is_enabled,
d.is_reconfiguration_pending,
rgc.classifier_function_id AS stored_classifier_function_id,
CASE
WHEN rgc.classifier_function_id IS NULL THEN NULL
ELSE QUOTENAME(OBJECT_SCHEMA_NAME(rgc.classifier_function_id, DB_ID('master'))) + '.' + QUOTENAME(OBJECT_NAME(rgc.classifier_function_id, DB_ID('master')))
END AS stored_classifier_function_name,
d.classifier_function_id AS effective_classifier_function_id,
CASE
WHEN d.classifier_function_id IS NULL OR d.classifier_function_id = 0 THEN NULL
ELSE QUOTENAME(OBJECT_SCHEMA_NAME(d.classifier_function_id, DB_ID('master'))) + '.' + QUOTENAME(OBJECT_NAME(d.classifier_function_id, DB_ID('master')))
END AS effective_classifier_function_name,
rgc.max_outstanding_io_per_volume AS stored_max_outstanding_io_per_volume,
d.max_outstanding_io_per_volume AS effective_max_outstanding_io_per_volume
INTO #DTR_RG_Config
FROM sys.resource_governor_configuration AS rgc
CROSS JOIN sys.dm_resource_governor_configuration AS d
END
GO
------------------------------------------------------------
-- 00l. Linked Servers (Inventory)
------------------------------------------------------------
PRINT N'▶ 00l. Linked Servers (Inventory) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_LinkedServers') IS NOT NULL DROP TABLE #DTR_LinkedServers
GO
WITH logins AS (
SELECT
ls.server_id,
COUNT(*) AS login_mappings,
SUM(CASE WHEN ls.uses_self_credential = 1 THEN 1 ELSE 0 END) AS self_mapping_count,
SUM(CASE WHEN ls.uses_self_credential = 0 THEN 1 ELSE 0 END) AS explicit_mapping_count
FROM sys.linked_logins AS ls
GROUP BY ls.server_id
)
SELECT
IDENTITY(int) AS RowNumber,
s.server_id,
s.name,
s.product,
s.provider,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, s.data_source, '[SafeMode]') AS data_source,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, s.catalog, '[SafeMode]') AS catalog,
s.is_linked,
s.is_system,
s.is_publisher,
s.is_subscriber,
s.is_distributor,
s.is_rpc_out_enabled,
s.is_data_access_enabled,
s.is_remote_login_enabled,
ISNULL(l.login_mappings, 0) AS login_mappings,
ISNULL(l.self_mapping_count, 0) AS self_credential_mappings,
ISNULL(l.explicit_mapping_count, 0) AS explicit_mappings,
s.modify_date,
s.is_collation_compatible,
s.uses_remote_collation,
s.collation_name,
s.lazy_schema_validation,
s.is_remote_proc_transaction_promotion_enabled,
s.connect_timeout,
s.query_timeout
INTO #DTR_LinkedServers
FROM sys.servers AS s
LEFT JOIN logins AS l ON l.server_id = s.server_id
WHERE s.server_id <> 0 -- exclude only the local server
GO
------------------------------------------------------------
-- 00m. Active Trace Flags
------------------------------------------------------------
PRINT N'▶ 00m. Active Trace Flags - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_TraceFlags') IS NOT NULL DROP TABLE #DTR_TraceFlags
GO
CREATE TABLE #DTR_TraceFlags_src (TraceFlag int, Status int, [Global] int, [Session] int)
INSERT INTO #DTR_TraceFlags_src
EXEC('DBCC TRACESTATUS(-1) WITH NO_INFOMSGS')
SELECT
IDENTITY(int) AS RowNumber,
TraceFlag,
Status,
[Global],
[Session]
INTO #DTR_TraceFlags
FROM #DTR_TraceFlags_src
ORDER BY TraceFlag
DROP TABLE #DTR_TraceFlags_src
GO
------------------------------------------------------------
-- 00n. Connection Encryption & Protocol Mix (Server & Target DB)
------------------------------------------------------------
PRINT N'▶ 00n. Connection Encryption & Protocol Mix (Server & Target DB) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_ConnEncryptionMix') IS NOT NULL DROP TABLE #DTR_ConnEncryptionMix
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
;WITH c AS (
SELECT
ec.session_id,
ec.net_transport,
ec.encrypt_option,
ec.protocol_version,
ec.auth_scheme
FROM sys.dm_exec_connections AS ec
),
s AS (
SELECT
session_id,
database_id
FROM sys.dm_exec_sessions
)
SELECT
IDENTITY(int) AS RowNumber,
c.net_transport,
c.auth_scheme,
c.encrypt_option,
c.protocol_version,
SUM(CASE WHEN s.database_id = DB_ID() THEN 1 ELSE 0 END) AS sessions_in_targetdb,
COUNT(*) AS sessions_total
INTO #DTR_ConnEncryptionMix
FROM c
LEFT JOIN s ON s.session_id = c.session_id
GROUP BY c.net_transport, c.auth_scheme, c.encrypt_option, c.protocol_version
END
GO
------------------------------------------------------------
-- 00o. HADR Endpoint (Database Mirroring) - Encryption & Auth
------------------------------------------------------------
PRINT N'▶ 00o. HADR Endpoint (Database Mirroring) - Encryption & Auth - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_HadrEndpoint') IS NOT NULL DROP TABLE #DTR_HadrEndpoint
GO
SELECT
IDENTITY(int) AS RowNumber,
e.name AS endpoint_name,
e.state_desc,
e.protocol_desc,
e.is_admin_endpoint,
dme.role_desc,
dme.is_encryption_enabled,
dme.encryption_algorithm_desc,
dme.connection_auth_desc,
dme.certificate_id,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, c.name, '[SafeMode]') AS certificate_name,
c.expiry_date,
te.port,
te.is_dynamic_port,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, sp.name, '[SafeMode]') AS owner,
sp.type_desc AS owner_type_desc
INTO #DTR_HadrEndpoint
FROM sys.endpoints AS e
JOIN sys.database_mirroring_endpoints AS dme ON dme.endpoint_id = e.endpoint_id
LEFT JOIN sys.tcp_endpoints AS te ON te.endpoint_id = e.endpoint_id
LEFT JOIN sys.certificates AS c ON c.certificate_id = dme.certificate_id
LEFT JOIN sys.server_principals AS sp ON sp.principal_id = e.principal_id
WHERE e.type_desc = 'DATABASE_MIRRORING'
GO
------------------------------------------------------------
-- 00p. Server Configuration - Focused Risk Summary
------------------------------------------------------------
PRINT N'▶ 00p. Server Configuration - Focused Risk Summary - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_ServerConfigRisk') IS NOT NULL DROP TABLE #DTR_ServerConfigRisk
GO
SELECT
IDENTITY(int) AS RowNumber,
configuration_id,
name,
minimum,
maximum,
value AS configured_value,
value_in_use,
is_dynamic,
is_advanced,
description
INTO #DTR_ServerConfigRisk
FROM sys.configurations
ORDER BY name;
GO
------------------------------------------------------------
-- 00q. AG Replica & Database State (Local View for the Target DB)
------------------------------------------------------------
PRINT N'▶ 00q. AG Replica & Database State (Local View for the Target DB) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_AGDbReplicaState') IS NOT NULL DROP TABLE #DTR_AGDbReplicaState;
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
DB_NAME(drs.database_id) AS database_name,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, ar.replica_server_name, '[SafeMode]') AS replica_server_name,
drs.is_local,
drs.is_primary_replica,
drs.synchronization_state_desc,
drs.synchronization_health_desc,
drs.database_state_desc,
drs.is_commit_participant,
drs.log_send_queue_size,
drs.log_send_rate,
drs.redo_queue_size,
drs.redo_rate,
drs.last_sent_time,
drs.last_received_time,
drs.last_hardened_time,
drs.last_redone_time,
ars.role_desc,
ars.connected_state_desc,
ar.availability_mode_desc,
ar.failover_mode_desc
INTO #DTR_AGDbReplicaState
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.group_id = drs.group_id AND ars.replica_id = drs.replica_id
WHERE drs.database_id = DB_ID()
ORDER BY CASE WHEN drs.is_primary_replica = 1 THEN 0 ELSE 1 END, ar.replica_server_name, DB_NAME(drs.database_id);
END
GO
------------------------------------------------------------
-- 00r. Collation Posture (Server vs. Target DB)
------------------------------------------------------------
PRINT N'▶ 00r. Collation Posture (Server vs. Target DB) - ' + CONVERT(char(8), GETDATE(), 108)
GO
IF OBJECT_ID('tempdb..#DTR_CollationPosture') IS NOT NULL DROP TABLE #DTR_CollationPosture;
GO
SELECT
IDENTITY(int) AS RowNumber,
CONVERT(sysname, SERVERPROPERTY('Collation')) AS ServerCollation,
DB_NAME() AS DatabaseName,
CONVERT(sysname, d.collation_name) AS DatabaseCollation,
CONVERT(sysname, DATABASEPROPERTYEX('tempdb', 'Collation')) AS TempdbCollation,
CASE WHEN CONVERT(sysname, SERVERPROPERTY('Collation')) <> CONVERT(sysname, d.collation_name) THEN 1 ELSE 0 END AS is_mismatch_server_db,
CASE WHEN CONVERT(sysname, DATABASEPROPERTYEX('tempdb', 'Collation')) <> CONVERT(sysname, d.collation_name) THEN 1 ELSE 0 END AS is_mismatch_tempdb_db,
CASE WHEN d.collation_name LIKE '%_CS_%' THEN 'CS' ELSE 'CI' END AS CaseSensitivity,
CASE WHEN d.collation_name LIKE '%_AI%' THEN 'AI' ELSE 'AS' END AS AccentSensitivity
INTO #DTR_CollationPosture
FROM sys.databases AS d
WHERE d.database_id = DB_ID();
GO
------------------------------------------------------------
-- 00s. Endpoints Inventory (TLS/Port/Type)
------------------------------------------------------------
PRINT N'▶ 00s. Endpoints Inventory (TLS/Port/Type) - ' + CONVERT(nvarchar(8), SYSDATETIME(), 108);
GO
IF OBJECT_ID('tempdb..#DTR_Endpoints') IS NOT NULL DROP TABLE #DTR_Endpoints;
GO
SELECT
IDENTITY(int) AS RowNumber,
e.endpoint_id,
e.name,
e.type_desc,
e.state_desc,
e.protocol_desc,
te.port,
te.is_dynamic_port,
te.is_admin_endpoint,
IIF(SESSION_CONTEXT(N'SafeMode') = 0, sp.name, '[SafeMode]') AS owner,
COALESCE(dme.connection_auth_desc, sbe.connection_auth_desc) AS connection_auth_desc,
dme.is_encryption_enabled,
COALESCE(dme.encryption_algorithm_desc, sbe.encryption_algorithm_desc) AS encryption_algorithm_desc,
COALESCE(dme.certificate_id, sbe.certificate_id) AS certificate_id,
e.principal_id
INTO #DTR_Endpoints
FROM sys.endpoints AS e
LEFT JOIN sys.tcp_endpoints AS te ON te.endpoint_id = e.endpoint_id
LEFT JOIN sys.database_mirroring_endpoints AS dme ON dme.endpoint_id = e.endpoint_id
LEFT JOIN sys.service_broker_endpoints AS sbe ON sbe.endpoint_id = e.endpoint_id
LEFT JOIN sys.server_principals AS sp ON sp.principal_id = e.principal_id
ORDER BY e.type_desc, e.name;
GO
------------------------------------------------------------
-- 00t. Effective Parallelism Posture (Server/DB/RG)
------------------------------------------------------------
PRINT N'▶ 00t. Effective Parallelism Posture (Server/DB/RG) - ' + CONVERT(nvarchar(8), SYSDATETIME(), 108);
GO
IF OBJECT_ID('tempdb..#DTR_EffectiveParallelism') IS NOT NULL DROP TABLE #DTR_EffectiveParallelism;
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
;WITH cfg AS (
SELECT
SUM(CASE WHEN name = 'max degree of parallelism' THEN CAST(value_in_use AS int) ELSE 0 END) AS server_maxdop,
SUM(CASE WHEN name = 'cost threshold for parallelism' THEN CAST(value_in_use AS int) ELSE 0 END) AS server_cost_threshold
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism')
),
dsc AS (
SELECT
SUM(CASE WHEN name = 'MAXDOP' THEN CAST(value AS int) ELSE 0 END) AS db_maxdop,
SUM(CASE WHEN name = 'LEGACY_CARDINALITY_ESTIMATION' THEN CAST(value AS int) ELSE 0 END) AS DBSC_LegacyCE,
SUM(CASE WHEN name = 'QUERY_OPTIMIZER_HOTFIXES' THEN CAST(value AS int) ELSE 0 END) AS DBSC_QO_Hotfixes,
SUM(CASE WHEN name = 'PARAMETER_SNIFFING' THEN CAST(value AS int) ELSE 0 END) AS DBSC_ParameterSniffing
FROM sys.database_scoped_configurations
WHERE name IN ('MAXDOP', 'LEGACY_CARDINALITY_ESTIMATION', 'QUERY_OPTIMIZER_HOTFIXES', 'PARAMETER_SNIFFING')
),
dbp AS (
SELECT
compatibility_level AS db_compat_level
FROM sys.databases
WHERE database_id = DB_ID()
),
rgo AS (
SELECT
is_enabled AS rg_is_enabled
FROM sys.resource_governor_configuration
),
rg AS (
SELECT TOP (1)
wg.max_dop,
wg.effective_max_dop
FROM sys.dm_resource_governor_workload_groups AS wg
WHERE wg.name = 'default'
)
SELECT
IDENTITY(int) AS RowNumber,
cfg.server_maxdop AS Server_MAXDOP,
cfg.server_cost_threshold AS Server_CostThreshold,
dsc.db_maxdop AS DB_MAXDOP,
ISNULL(rg.max_dop, 0) AS RG_MAXDOP,
ISNULL(rg.effective_max_dop, 0) AS RG_Effective_MAXDOP,
dbp.db_compat_level AS DB_CompatLevel,
rgo.rg_is_enabled AS RG_IsEnabled,
dsc.DBSC_LegacyCE,
dsc.DBSC_QO_Hotfixes,
dsc.DBSC_ParameterSniffing
INTO #DTR_EffectiveParallelism
FROM cfg
CROSS JOIN dsc
CROSS JOIN dbp
CROSS JOIN rgo
CROSS JOIN rg;
END
GO
------------------------------------------------------------
-- 00u. TempDB Metadata (Memory-Optimized) Status (2019+)
------------------------------------------------------------
PRINT N'▶ 00u. TempDB Metadata (Memory-Optimized) Status (2019+) - ' + CONVERT(nvarchar(8), SYSDATETIME(), 108);
GO
IF OBJECT_ID('tempdb..#DTR_TempdbMetadataMO') IS NOT NULL DROP TABLE #DTR_TempdbMetadataMO;
GO
IF SESSION_CONTEXT(N'ProductMajorVersion') >= 15
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
CONVERT(int, SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')) AS is_tempdb_metadata_memory_optimized
INTO #DTR_TempdbMetadataMO;
END
GO
------------------------------------------------------------
-- 00v. Accelerated Database Recovery (ADR) & PVS (2019+)
------------------------------------------------------------
PRINT N'▶ 00v. Accelerated Database Recovery (ADR) & PVS (2019+) - ' + CONVERT(nvarchar(8), SYSDATETIME(), 108);
GO
IF OBJECT_ID('tempdb..#DTR_ADR_PVS') IS NOT NULL DROP TABLE #DTR_ADR_PVS;
GO
IF SESSION_CONTEXT(N'ProductMajorVersion') >= 15
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
DB_NAME() AS database_name,
dbs.is_accelerated_database_recovery_on,
pvs.persistent_version_store_size_kb,
pvs.online_index_version_store_size_kb,
pvs.current_aborted_transaction_count,
pvs.oldest_active_transaction_id,
pvs.oldest_aborted_transaction_id,
pvs.pvs_filegroup_id
INTO #DTR_ADR_PVS
FROM sys.databases AS dbs
LEFT JOIN sys.dm_tran_persistent_version_store_stats AS pvs ON pvs.database_id = dbs.database_id
WHERE dbs.database_id = DB_ID();
END
GO
------------------------------------------------------------
-- 00w. TempDB File Space Usage (DB)
------------------------------------------------------------
PRINT N'▶ 00w. TempDB File Space Usage (DB) - ' + CONVERT(nvarchar(8), SYSDATETIME(), 108);
GO
IF OBJECT_ID('tempdb..#DTR_TempdbFileSpace') IS NOT NULL DROP TABLE #DTR_TempdbFileSpace;
GO
IF SESSION_CONTEXT(N'IsSysAdmin') = 1
BEGIN
SELECT
IDENTITY(int) AS RowNumber,
SUM(unallocated_extent_page_count) * 8.0 / 1024 AS unallocated_mb,
SUM(version_store_reserved_page_count) * 8.0 / 1024 AS version_store_mb,
SUM(user_object_reserved_page_count) * 8.0 / 1024 AS user_objects_mb,
SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS internal_objects_mb,
SUM(mixed_extent_page_count) * 8.0 / 1024 AS mixed_extents_mb
INTO #DTR_TempdbFileSpace
FROM tempdb.sys.dm_db_file_space_usage;
END
GO
------------------------------------------------------------
-- 00x. TempDB Session Space Usage (Top 50)
------------------------------------------------------------
PRINT N'▶ 00x. TempDB Session Space Usage (Top 50) - ' + CONVERT(nvarchar(8), SYSDATETIME(), 108);