Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

SQL Updates Newsletter – March 2017

Discussion in 'Official Microsoft News' started by Fany Carolina Vargas, Mar 31, 2017.

Thread Status:
Not open for further replies.
  1. Fany Carolina Vargas

    Fany Carolina Vargas Guest

    Blog Posts:
    Recent Releases and Announcements

    Issue Alert

    • Critical: Do NOT delete files from the Windows Installer folder. C:windowsInstaller is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed you may have to rebuild the operating system and reinstall SQL Server.
    • Critical: Please be aware of a critical Microsoft Visual C++ 2013 runtime pre-requisite update that may be required on machines where SQL Server 2016 will be, or has been, installed.
      • https://blogs.msdn.microsoft.com/sqlcat/2016/07/28/installing-sql-server-2016-rtm-you-must-do-this/
      • If KB3164398 or KB3138367 are installed, then no further action is necessary. To check, run the following from a command prompt:
        • powershell get-hotfix KB3164398
        • powershell get-hotfix KB3138367
      • If the version of %SystemRoot%system32msvcr120.dll is 12.0.40649.5 or later, then no further action is necessary. To check, run the following from a command prompt:
        • powershell “get-item %systemroot%system32msvcr120.dll | select versioninfo | fl”
    • Important: If the Update Cache folder or some patches are removed from this folder, you can no longer uninstall an update to your SQL Server instance and then revert to an earlier update build.
      • In that situation, Add/Remove Programs entries point to non-existing binaries, and therefore the uninstall process does not work. Therefore, Microsoft strongly encourages you to keep the folder and its contents intact.
      • https://support.microsoft.com/en-us/kb/3196535
    • Important: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
    • Important: Default auto statistics update threshold change for SQL Server 2016
    • Troubleshooting Internal Load Balancer Listener Connectivity in Azure
    • Why am I getting so many checkpoint files when I have In-Memory OLTP enabled?
    • A few new things in XML plan to help you troubleshoot query performance
      • Scenario: In this case, customer stated a merge query ran very slowly and wanted us to help improve the performance.
      • By examining ActualElapsedms which is displayed as “Actual Elapsed Time(ms)”, we quickly found out which operator was taking most of the time.
      • We also added top wait stats for the query. if your query runs slowly but consumes very little CPU, this will be a great help.
      • For both properties to show up in SSMS, you must be [on the] latest SSMS
      • https://blogs.msdn.microsoft.com/ps...n-to-help-you-troubleshoot-query-performance/
    • Replication Monitor could not insert the tracer token
      • We’re seeing an uptick in customers using combination of AlwaysOn and Transactional Replication.
      • Scenario: If a failover has occurred and Published database is now running on a node which was not Primary when Replication was setup, i.e. now on Secondary as Primary, inserting a Tracer Token via Replication Monitor will generate the following error: Replication Monitor could not insert the tracer token.
      • Solution: You can still insert Tracer Token via sys.sp_posttracertoken on the current Primary then show latency in Replication Monitor, you just can’t insert new Token via Replication Monitor after failover to secondary replica.
      • https://blogs.msdn.microsoft.com/re...on-monitor-could-not-insert-the-tracer-token/
    • Transaction Log won’t truncate when using Snapshot Replication to Azure SQLDB
    • Backing up a VLDB to Azure Blob Storage
      • If using Backup to URL to create striped backups of large databases (over 48 GB per stripe), specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536 in the BACKUP statement.
      • Scenario: The following error is raised during backup to blob storage: Write to backup block blob device <url> failed. Device has reached its limit of allowed blocks.
      • Solution: Make SQL Server use larger block sizes via the MAXTRANSFERSIZE parameter in the BACKUP DATABASE statement. For 4 MB blocks: BACKUP DATABASE … TO URL = ‘<>’, … URL = ‘<>’, WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 5;
      • https://blogs.msdn.microsoft.com/sqlcat/2017/03/10/backing-up-a-vldb-to-azure-blob-storage/

    Recent Blog Posts and Articles

    • Columnstore Index – How to Estimate Compression Savings
      • Customers can invoke sp_estimate_data_compression_savings stored procedure to estimate the storage savings for ROW and PAGE compression
      • This stored procedure has not been extended to estimate storage savings from columnstore index. This is something we could consider for the future.
      • To estimate compression savings for columnstore index, we recommend the following steps: (1)Create a staging table with identical schema (2)Load 2 million rows into the staging table. [We] have chosen 2 million arbitrarily but it needs to be at least 1 million. (3) Use sp_spaceused to find the size of the table (4) Now create columnstore index on the table (5) Measure the storage using sp_spaceused. Compare the numbers in (3) and (5)
      • https://blogs.msdn.microsoft.com/sq...re-index-how-to-estimate-compression-savings/
    • Moving databases to new storage within an Availability Group
    • Data Simulator For Machine Learning
    • Getting more statistics information programmatically
    • Five reasons to run SQL Server 2016 on Windows Server 2016 — No. 1: Security
      • The security functionality in Windows Server 2016 includes the following:
      • Device Guard helps lock down what runs on the server so that you are better protected from unauthorized software running on the same server as your SQL Server application.
      • Credential Guard to protect SQL Server admin credentials from being stolen by Pass-the-Hash and Pass-the-Ticket attacks. Using an entirely new isolated Local Security Authority (LSA) process, which is not accessible to the rest of the operating system, Credential Guard’s virtualization-based security isolates credential information to prevent interception of password hashes or Kerberos tickets.
      • Control Flow Guard and Windows Defender protect against known and unknown vulnerabilities that malware can otherwise exploit. Control Flow tightly restricts what application code can be executed — especially indirect call instructions. Lightweight security checks identify the set of functions in the application that are valid targets for indirect calls. When an application runs, it verifies that these indirect call targets are valid. Windows Defender works hand-in-hand with Device Guard and Control Flow Guard to prevent malicious code of any kind from being installed on your servers.
      • https://blogs.technet.microsoft.com...er-2016-on-windows-server-2016-no-1-security/
    • Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost
      • SQL Server professionals know that database transactions can be gated by log write speed. If the log is faster, more database updates are possible. Windows Server 2016 helps solve this with Persistent Memory (aka Storage Class Memory).
      • Storage Spaces Direct in Windows Server 2016 allows use of industry-standard servers with local storage as a highly available, scalable alternative to expensive storage area networks (SANs) — with read speeds that can exceed 25 GB/second.
      • Windows Server 2016 has built-in capability to provide in-memory with 24 terabytes of available server memory. Plus, new CPU maximums have been increased by three times so that you can run up to 640 CPU cores.
      • https://blogs.technet.microsoft.com...ql-server-2016-on-windows-server-2016-part-2/
    Recent Training and Technical Guides

    Monthly Script and Tool Tips

    Recommended KB’s To Review Regularly

    • Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
    • Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads
      • https://support.microsoft.com/en-gb/kb/2964518
      • Note for SQL Server 2016: You no longer have to enable these trace flags in SQL Server 2016 because the auto-detection of the associated logic for trace flags is already incorporated into the product.
    • Troubleshooting SQL Server backup and restore operations

    Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services

    Continue reading...
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software