NuGet · #1714 most downloaded on NuGet

Microsoft.SqlServer.Assessment

1.1.1722M downloads/momicrosoft/sql-server-samples ↗New releases in your daily digest, changelog included.

The SQL Assessment API provides a mechanism to evaluate the configuration of the SQL server for best practices. The API is delivered with a ruleset containing best practice rules suggested by the SQL Server team.

Last release 4 years ago
no release in 18 months
Ships unpredictably
gaps range from 2 weeks to 120.8 years
Most releases are documented
notes for 6 of 9 stable releases
1 version withdrawn
withdrawn after publishing
127 years old
17 releases · first in 1900

Release timeline

17 releases since 1900

Releases

1.1.1720 Dec 2022Deprecated
Fix falsely flagging deprecated features in job steps (GitHub #1025)
Version: SqlServer PowerShell module, SqlManagementObjects (SMO) package: not updated

What's new

New rules:

98 recommendations based on SQL Server error log content

Availability database count

Availability replica recovery health

Columnstore indexes with large page memory model

High SQL compilations frequency

High cursor requests frequency

Backup files on a separate physical location

Database mirroring

High deadlock frequency

SQL Server on a domain controller

Error log retention settings

High free space scans rate

Full scans vs index searches

High latch wait time

Excessive lazy writes

Lock requests per batch

Excessive lock waits

Memory grants pending

Non-default value for 'min server memory (MB)' option

Non-default value for 'nested triggers' option

High forwarded records rate

Sleeping sessions with open transactions

Orphaned server audit specification

Orphaned database audit specification

Low page life expectancy

High page lookups frequency

High page split rate

High SQL recompilation rate

Non-default value for 'remote access' option

Non-default value for 'remote login timeout (s)' option

Frequent scan point revalidations

Non-default value for 'server trigger recursion' option

Option 'show advanced options' is enabled

Tempdb data files initial size

High Wait for the worker

High transaction log disk response time

High Network IO

High non-Page latch waits

High Page IO latch waits

High Page Latch waits

High Workfiles Created per second

Low Worktables from Cache ratio

High Worktables Created per second

New probes:

Performance probe type

BasicPerformance

AuditSpecs

ComputerSystem

DatabaseBackupsMedia

DatabaseMirroring

ErrorLog

ServerAudits

ServerDatabasesProperties

SysOpenTransactions

New transformations:

Performance counter data transformation

Bug fixes and improvements

Add join string aggregate function new parameters:

distinct specifies whether duplicates should be eliminated

comparison specifies StringComparison used by the aggregate function

Add ag_replica_recovery_health variable to the AGConfiguration probe

Add age_days to ErrorLogInfo probe

Add remote_access, remote_login_timeout, server_trigger_recursion, and default_language to SysConfiguration probe

Fix warning message on missing permissions and other requirements

Fix help link for DbSpaceAvailable check (GitHub #1035)

Fix falsely flagging deprecated features in job steps (GitHub #1025)

Fix configured to active value comparison for Max and Min server memory

Fix duplicate messages for Azure disk checks

Fix TF834 check triggered when no columnstore index is in use

Remove Server target type filter from probes which work for databases

From the same repository

microsoft/sql-server-samples

Microsoft.SqlServer.Assessment.AuthoringNuGetThe SQL Server Assessment API Authoring package provides a public interface that helps extend the default mechanisms for evaluation of the SQL Server configuration for best practices using the SQL Server Assessment API.

1.1.97 Jul 2022

Version: SqlServer PowerShell module: 22.0.30-preview, SqlManagementObjects (SMO) package: not updated

What's new

New rules:
- Access check cache options ratio
- Availability group listener network mode
- Availability group failure-condition level
- Availability group health check timeout
- Availability replica connection state
- Availability replica synchronization health
- Availability database automatic failover readiness
- Availability database joined state
- Azure data disk striping
- Database mirroring endpoint encryption
- Database with unavailable state
- Maximum number of Availability Groups
- Not supported value for 'default full-text language' option
- Non-default values for access check cache options
- Option 'disallow results from triggers' is disabled
- Setting 'Autogrow' for data files
- Tables with more indexes than columns
- TempDb and user databases should not share volumes
New Probes:
- AGConfiguration
- AGDatabases
- AGEndpoints
- AGListener
- DatabaseSharedVolumes
- DatabasesState
- SysFullTextLanguages
- TablesInformation
Added 'MaxCheckResults' configuration option to limit assessment output

Bug fixes and improvements

The following rules are not run for Linux targets now:
- Indexes keys with more than @{threshold} bytes
- MAXDOP set in accordance with CPU count
- 'STRelate' and 'STAsBinary' functions unexpected results due to TF 6533
- TF 4199 enables query optimizer fixes
- TF 6532 enables performance improvements for spatial data
- TF 8015 disables auto-detection and NUMA setup
- TF 8744 disables pre-fetching for Nested Loop operator
- TF 9347 disables batch mode for sort operator
- TF 9349 disables batch mode for top N sort operator
- TF 9389 enables dynamic memory grant for batch mode operators
- TF 9476 causes SQL Server to generate plan using Simple Containment assumption
- TF 9481 enables Legacy CE model
Improved performance of idex related probes and checks
Improved performance of other probes
Affinity 64 and Affinity 64 IO masks are checked on 64-bit platforms only
Instant File Initialization check is not run for SQL Server 2016 without service packs
Fixed requirements processing for checks
'Max allowed memory' and 'Max server memory exceeds system memory' checks are not run for SQL Server Managed Instance now
Updated 'Latest cumulative update' check
'Stored procedure naming' check severity changed to Low
'Uncompressed database tables and indexes' gives more details in the message
'SQL logins have weak passwords' now checks for empty passwords and password equal to login
Non-default value for 'common criteria compliance enabled' option
Option 'backup compression default' is disabled
Replaced DatabaseFileLocation probe with DatabaseMasterFiles in the following rules:
- Azure disk caching for data files
- Azure disk caching for transaction logs
- Data files on Azure data disks
- Storage spaces disk column count
- Tempdb files on Azure temp drive
- Use premium SSDs for SQL Server data files
Improved probes
- OsSysMemory
- ServerProperties
- TopUncompressedTables
- ServerProperties
- WeakPassword
Removed rules:
- LoginEqPassword
- LoginNoPassword

Open source →

1.1.030 Dec 2021Deprecated

Improved performance of the 'DeprecatedFeaturesInModules' check. Now it groups results by feature

Version: SqlServer PowerShell module, SqlManagementObjects (SMO) package: not updated

What's new

Added .NET 451 support
The SQL Assessment API engine accepts a 'DbConnection' object as a target specification
Added the 'CloneConnection' property to the engine configuration
New rules:
- Cache needs to be cleared of single-use plans
- Instant file initialization (IFI) is disabled
- Tempdb files on Azure temp drive
- Error log and default trace files on Azure data disk
- Data files on Azure data disks
- Storage spaces disk column count
- Azure disk caching for data files
- Azure disk caching for transaction logs
- Database default locations
- Lock pages in memory
- Use premium SSDs for SQL Server data files
New probes:
- ServiceInfo returns the state of selected Windows service (WMI)
- AzStorage returns information about disks and volumes on the Azure VM hosting the target object (WMI)
- AzDiskMetadata probe returns information about storage devices present on the Azure VM hosting the target object (IMDS)
- DatabaseFilesLocation lists volumes hosting database files (T-SQL)
- DbFilesDefaultLocation returns the default data file location for new databases (registry)
Added 'sql_memory_model' to the 'SysDmOsSysInfo' probe
Added 'expandData' transformation
Added support for direct WMI and registry access
Added support for Azure Instance Metadata
Added custom data sources to the assessment target specification
Added CIM namespace support to WMI probes
Added support for PowerShell scripts returning arrays
Added the 'timeout' property to SQL probes for long-running queries (default value is 30 seconds)

Bug fixes and improvements

Changed severity levels to Information, Low, Medium, and High
The SQL Assessment API engine can now detect target's version, platform, engine edition, machine type, and server name automatically
Improved performance of the 'DeprecatedFeaturesInModules' check. Now it groups results by feature
Fixed 'CpuUsage' returning 'null' for a short time after a restart of SQL Server
Fixed crash on missing 'rules' ruleset property
The 'ErrorLogInfo' probe now uses a temporary table instead of direct stored procedure call
Fixed missing warnings on inaccessible WMI, registry, or Azure data
'LoginNoPassword' and 'LoginEqPassword' checks are now merged with 'WeakPassword'
'PlansUseRatio' check now uses the 'optimize_for_ad_hoc_workloads' configuration option
Azure-related probes now use Azure Instance Metadata directly or emulated with Azure Resource Graph
'AgentSvcStopped', 'BrowserSvcStopped', 'DtcSvcStopped', 'FullTextSvcStopped', and 'RsSvcStopped' checks now use the WMI-based 'ServiceInfo' probe instead of the 'ServiceControl' PowerShell script
'UnusedIndexes' check now displays the affected table name
NTFS Block size check now uses the device ID instead of the volume name
'TempDBFilesMultiple4' message was updated with more details
A number of checks were made available for Linux
'OnPremises' engine edition group was renamed to 'SqlServer'
'AzureSQL' VM type was renamed to 'AzureVM'

Open source →

1.0.30426 Feb 2021

Version: SqlServer PowerShell module wasn't updated, SqlManagementObjects (SMO) package wasn't updated

What's new

Added the 'MachineType' property to Target that is used for making rules specific to SQL Server on Azure Virtual Machine
Added the ability to pass data among multiple probes and invoke probes one by one
Added rules:
- [SQL Server on Azure VM] VM size is not memory-optimized - checks the virtual machine size and recommends to use memory-optimized VM series for the best performance of SQL Server workloads
- 'SQL Server Agent' service uses non-recommended account
- 'SQL Server Agent' service uses not supported account
- 'SQL Server Agent' and 'SQL Server Database Engine' use same account
- 'SQL Server Agent' service is stopped
- 'SQL Server Browser' and 'SQL Server Database Engine' use same account
- 'SQL Server Browser' service is stopped
- 'Integration Services' and 'SQL Server Database Engine' use same account
- 'Integration Services' service is stopped
- 'Full-text search' service uses non-recommended account
- 'Full-text search' and 'SQL Server Database Engine' use same account
- 'Full-text search' service is stopped
- 'Analysis Services' and 'SQL Server Database Engine' use same account
- 'Analysis Services' service is stopped
- 'Reporting Services' and 'SQL Server Database Engine' use same account
- 'Reporting Services' service is stopped
- 'SQL Server Database Engine' service uses non-recommended account
- 'SQL Server Database Engine' service uses not supported account

Bug fixes and improvements

Updated the 'LatestCU' rule with the latest CU versions
Updated the 'ReplErrors24H' rule to collect information for all 'Publisher' databases
Fixed an issue with local variables used for displaying extended details in messages
Fixed an issue with the wrong Linux target type for the 'PriorityBoostOn' rule

Open source →

1.0.30230 Dec 2020

Version: SqlServer PowerShell module wasn't updated, SqlManagementObjects (SMO) package wasn't updated

What's new

Added rules:
- 'Max server memory' and 'Max server memory exceeds system memory' that check if the 'max server memory' setting falls within the recommended range
- 'Error log file is too big' that checks if there are any big error log files
'Disk fragmentation analysis' that checks if a disk needs to be defragmented
New features for custom configuration files:
- Added new 'defaultValue' data transformation functions
- Added new functions:
  - 'max' and 'min' functions that return maximum and minimum values of their arguments
  - 'ieq' and 'ine' case-insensitive functions that check if the given arguments are equal
  - 'iin' case-insensitive function that checks if a value is present in the given set
  - 'interval' function that finds an interval into which the given argument falls and returns an associated value.
  - 'startswith' and 'endswith' functions that check if one string starts or ends with another one, and their case-insensitive variants 'istartswith' and 'iendswith'
  - 'indexof' function that finds the first occurrence of a substring, and its case-insensitive variant 'iindexof'
- Added local variables to assessment rules which can be used to show more details in messages
- Added WMI method call support to custom WMI probes

Bug fixes and improvements

Removed non-informative errors thrown from recursive assessment of SQL Server Registered Groups
Improved query performance for probes used in the 'Replication errors in the last 24 hours' rule
Updated MaxDOP related rules to follow the best practice recommendations
Fixed an issue that may occur when the 'automatic_soft_NUMA_disabled' configuration setting value was not returned by some SQL Server instances
Fixed an issue that may occur when the 'sql_memory_model' value was not returned by some SQL Server instances

Open source →