SQL Performance Tips

SQL Performance

In-Memory Tables with Natively Compiled T-SQL: Blazing Speed for OLTP and More December 1, 2016 in-memory-tables-with-natively-compiled-t-sql-blazing-speed-for-oltp-and-more
The SQL Server 2014 and 2016 Hekaton project brings us In-Memory tables and Natively Compiled T-SQL objects that run at amazing speed.  What you can accomplish is astounding. This presentation will introduce you to the Hekaton technology and show how big the differences can be and where you can put it to best use starting … Read More

100 Times Faster: Experiences Making SQL Server Fly September 20, 2016 Download the presentation: 100-times-faster-experiences-making-sql-server-fly-sql-saturday-prov-2015-12-12
How do you go about making a program run a lot faster!  Like 100 times faster?   It’s difficult.  Microsoft tried in the Hekaton project, its in-memory tables feature of SQL 2014, and they got close.  Inspired by that effort, this presentation looks at some recent cases where I’ve been able to achieve … Read More

Natively Compiled T-SQL: The Fastest SQL Ever! September 18, 2016 Download the Presentation .Natively-Compiled-T-SQL-Andrew-Novck-presentation
Demo files: Natively-Compiled-T-SQL-Andrew-Novck-demo-sql
SQL Server’s In-Memory tables are accompanied by Natively Compiled Stored Procedures and Functions that run at amazing speed.  However, the speed boost comes with tradeoffs.
The complete T-SQL language is not implemented, only key parts and we’ll discuss these limitations and how to get around some of them.

Transaction handling is different … Read More

SQL Server In-Memory Tables April 21, 2015 SQL Server In-Memory Tables(Hekaton) Andy Novick April 2015
SQL Server 2014 aimed to improve OLTP performance by adding In-Memory tables and native compilation of the stored procedures that use them.  In-Memory tables bring incredible speed for the right workload so long as a series of limitations can be observed.  This presentation describes what In-Memory tables are … Read More

Video – Improving SQL Server Performance by writing faster T-SQL Code- Sept 11, 2014 August 31, 2014 This webinar is now available for viewing online at any time. (About 60 Minutes)
http://www.mssqltips.com/sql-server-video/332/proving-sql-server-performance-by-writing-faster-tsql-code/
Some of the concepts we will cover include:

Ways to find code that’s slow:

DMV’s like sys.dm_exec_procedure
SQL Profiler and Server Traces
Extended Events
Writing your own logging code. (My favorite but it’s a lot of work)

 Functions:

Why scalar and TVF functions are slow
Finding high use functions with … Read More

Scripting Traces for Performance Monitoring on SQL Server August 20, 2014 The SQL Profiler is a great tool for monitoring and analyzing SQL Server Performance. I use it all the time to watch the detailed actions of a stored procedure, trigger or user-defined function (UDF) that I am developing. It can also be used to monitor aggregate performance of an entire SQL Server instance, a single … Read More

Proposed Solution to the Performance Problem with SQL Server Scalar UDFs August 20, 2014 SQL Server scalar User-Defined Functions (UDFs) have a performance problem that could be solved with an enhancement to the SQL Server database engine.
Read More

Retrieving a Random Record in SQL Server using udf_Num_RanInt August 1, 2014 This UDFs week solves a problem posed to me last week by a friend Gary.  Gary wants to select a random row from one of his tables and was hoping that SQL Server would have some support for this task.
The idea of selecting a random row runs counter to the ideas that are the basis of the … Read More