New DMF for retrieving input buffer in SQL Server

A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available in SQL Server 2016 RC0. This is functionally equivalent to DBCC INPUTBUFFER. However, this has a few advantages over DBCC INPUTBUFFER. Directly returns a rowset. Can be conveniently used with sys.dm_exec_sessions or sys.dm_exec_requests by doing a CROSS APPLY. Can use…

2

SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases

Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications. The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2012 and SQL Server 2014 for optimal scalability and performance. A SQL Server 2016 primary goal was ‘It Just Works.’ Out of the box a customer should not have to engage…

0

Nonclustered Columnstore Index on Base Table Partitioned by Computed Column

If you have SQL Server 2012 and created a NCCI on a base table partitioned by a computed column, you might have noticed that it produces a serial plan instead of a parallel plan. This, naturally, slows down index creation. How slow depends on your database size and number of partitions. You can use sys.dm_exec_query_memory_grants…

0

Best practices for maximum performance and scalability gains for SQL Server workloads

We have worked with our colleagues in CSS to publish a KB article that lists recommended configuration and tuning options as well as necessary updates for SQL Server if you are running one of the newer versions of SQL Server on modern hardware. A typical configuration of this high performance workload environment comprises SQL Server…

0