Skip to content

What's new in Database Engine - SQL Server 2017

Geschrieben von Database Magician am

'This topic describes improvements made to the SQL Server Database Engine for SQL Server 2017. Click the links for more information on each item.

Enhancements

  • CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017, an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. CLR assemblies can be added to a whitelist, as a work-around for the clr strict security feature. sp_add_trusted_assembly, sp_drop_trusted_assembly, and sys.trusted_asssemblies are added to support the white list of trusted assemblies. For more information, see CLR strict security. 

  • A new, DMF sys.dm_db_log_stats, is introduced to expose summary level attributes and information on transaction log files; useful for monitoring the health of the transaction log. 

  • Resumable online index rebuild. Resumable online index rebuild allows you to resume an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space). You can also pause and later resume an online index rebuild operation. For example, you might need to temporarily free up systems resources in order to execute a high priority task or complete the index rebuild in another maintenance window if the available maintenance windows is too short for a large table. Finally, resumable online index rebuild does not require significant log space, which allows you to perform log truncation while the resumable rebuild operation is running. See ALTER INDEX and Guidelines for online index operations'...

https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2017

Guidelines for Online Index Operations

Geschrieben von Database Magician am

'When you perform online index operations, the following guidelines apply: 

  • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text. 

  • Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. 

  • Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

  • Indexes can be resumed from where it stopped after an unexpected failure, database failover, or a PAUSE command. See Alter Index. This feature is in public preview for SQL Server 2017 and Azure SQL Database.'...

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations

Crappy Missing Index Requests

Geschrieben von Database Magician am

'When you’re tuning queries

It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor.

But does adding missing indexes from requests always make things better?

The question goes for any tool, whether it’s DTA, or the missing index DMVs, or your own wild speculation. Testing is important.

Not all requests are helpful

In fact, some of them can be harmful. Let’s look at a recent example from the Orders database. After running for a while, I noticed the UpdateShipped stored procedure was asking for an index. And not just any index, but one that would reduce query costs by 98.5003%. That’s incredible. That’s amazing. Do you take DBA Express cards?'...

https://www.brentozar.com/archive/2017/02/crappy-missing-index-requests/