Improving SQL Server 2016 Large Database Performance

This article aims to save you days or weeks of troubleshooting when using SQL Server 2016 for large databases

Flexible Statistics Update

Update the statistics starting from most accessed and modified ones, with a time limit. Procedure will exit when @MaxExecutionTime parameter is greater than the current execution time.

Improved Support for Parallel Statistics Rebuilds

Jonathan Kehayias takes a closer look at a new trace flag that allows you to process multiple concurrent statistics updates on the same table.

A Spectacular Case of 100% CPU Caused by Online Reindexing

An investigation of a serious performance issue during online re-indexing on a production server leads us to

interesting discoveries and solutions.

SQL Server's Auto Update Statistics Async Option

I have been trying to tune my SQL Server and I noticed a few database settings for statistics. I also read the previous tip on automatic statistics updates. Could you please describe the Auto Create Statistics Async option? How is this configured and when should I use it and when should I avoid using it?

Update statistics in parallel

This articles shows a technique that generates and launches an SSIS package to update statistics in parallel.

Turbocharge Your Database Maintenance With Service Broker: Part 1

Are your maintenance windows too wide? Find out how to shrink them with Service Broker.

Update Statistics

RE: Access Email

I think the way to do this is to open a recordset which contains the data to be placed in the message body. Then simply use DoCmd.SendObject to send the email. Sub sEmailNotification()Dim rst As New ADODB.Recordset Dim strMsg As String'get data for request (assumes only 1 record in table) rst.Open "SELECT REQUEST_DATE_FROM, REQUEST_DATE_TO, REQUEST_HOURS, USERNAME […]

RE: Attach DB and Procedure Cache

When you execute sp_detach_db, the procedure cache is flushed for that database. This is probably due to DBCC DETACHDB() [which sp_detach_db wraps], though this command is undocumented. Edit: You can see this by watching syscacheobjects. I did so with the pubs db on a development sql server. Run a stored procedure, verify it's in the […]