Hi,
For considerations of storage utilization, as well as performance optimization of disk IO, it would be preferable if all the nvarchar(max) columns in the SQLSentry database were to be compressed and saved as a varbinary(max) instead of nvarchar(max), utilizing the built-in COMPRESS and DECOMPRESS functions available since SQL Server 2016.
Or, if you don't want to give up on backward compatibility with SQL Server version pre-2016, you could simply use equivalent GZip functions available in the C# language.
Example columns that could benefit from this:
- [dbo].[PerformanceAdvisorSqlQuery].[QueryText]
- [dbo].[PerformanceAnalysisDbeRequests].[StatementText]
- [dbo].[PerformanceAnalysisQuickTraceProcessRow].[TextData]
- [dbo].[PerformanceAnalysisQuickTraceRow].[TextData]
- [dbo].[PerformanceAnalysisSsasTraceData].[TextData]
- [dbo].[PerformanceAnalysisSsasTraceDataDetail].[TextData]
- [dbo].[PerformanceAnalysisTraceData].[TextData]
- [dbo].[PerformanceAnalysisTraceDeadlock].[DeadlockXml]
- [dbo].[PerformanceAnalysisTraceHash].[NormalizedTextData]
- [dbo].[PerformanceAnalysisTraceRecompile].[TextData]
And many more.
Unfortunately, this cannot be solved using table or index level Data Compression, since LOB pages cannot be compressed in SQL Server. So, the solution would have to come from the application by changing the way these columns are saved.
