Test network packet size on a specific SQL Server connection, verify the negotiated value, and compare large-result performance carefully.
SQL SERVER 2016 – Creating Clustered ColumnStore with InMemory OLTP Tables
While preparing my SQLPASS session on SQL Server 2016, I explored creating clustered ColumnStore with InMemory OLTP tables. Here is what I learned.
SQL SERVER – Set AUTO_CLOSE Database Option to OFF for Better Performance
How I helped a customer get better performance by setting the AUTO_CLOSE Database Option to OFF, and why this setting hurts busy databases.
SET NOCOUNT ON in SQL Server: How to Hide Rows Affected Messages
SET NOCOUNT ON stops SQL Server from sending a rows affected message after every statement. This post tests what it changes and what it doesn’t: @@ROWCOUNT, scope inside a procedure, timing and the text that sp_updatestats prints. It also covers client programs that depend on the count.
Resource Bottlenecks in SQL Server: A Wait Stats Script That Groups Them
Resource bottlenecks hide in the wait statistics, under a pile of idle waits and totals since startup. This post builds a script that removes the idle waits, measures an interval and groups the rest by resource. It also explains how to read each group and the signal wait.
SQL SERVER – Columnstore Index Cannot be Created When Computed Columns Exist
A developer found a columnstore index cannot be created on a large history table in SQL Server 2014. The surprise cause: computed columns in the table.
Deleting Millions of Rows in Batches Without Filling the Log
Plan deleting millions of rows with bounded commits, an indexed cutoff, log reuse checks, and a clear choice of retention method.