Hekaton Part 5: Native Procedures

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

Now we come to one of the Hekaton components that is really quite cool but that I haven’t found a practical use for. Not even once.

Natively compiled stored procedures.

The cool part is that they can be really, really fast. They can be orders of magnitude faster than traditional interpreted procedures. The not so cool part is that they support only a very, very limited subset of T-SQL.

Let’s demonstrate. First, we’ll create a new table and add a couple of rows.

if exists (select * from sys.tables where name = 'Product')
	drop table dbo.Product;
create table dbo.Product
(
	ProductID int not null,
	Description nvarchar(500) not null,
	UnitPrice money not null,

	primary key nonclustered hash (ProductID) with (bucket_count = 1048576),
	index idx_Product__UnitPrice nonclustered (UnitPrice)
) with (memory_optimized = on, durability = schema_and_data);

-- Populate a few rows into the table

insert dbo.Product (ProductID, Description, UnitPrice)
values (1, 'Gizmo', 34.00),
	(2, 'Whatzit', 16.00);

Now can create a natively compiled stored procedure to update the table. This isn’t going to demonstrate speed, but it’s just to show the general idea (and the general awkwardness of the syntax).

if exists (select * from sys.objects where type = 'P' and name = 'usp_UpdateProduct')
	drop procedure usp_UpdateProduct
go
create procedure dbo.usp_UpdateProduct (@ProductID int, @NewUnitPrice money)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	update dbo.Product
	set UnitPrice = @NewUnitPrice
	where ProductID = @ProductID;
end
go

We can demonstrate that the procedure really works by running something like.

select ProductID, Description, UnitPrice from dbo.Product where ProductID = 1;
exec dbo.usp_UpdateProduct @ProductID = 1, @NewUnitPrice = 42.00;
select ProductID, Description, UnitPrice from dbo.Product where ProductID = 1;

Now let’s write a procedure to add a bunch of new rows. First, we will create and populate a tally table.

create table Tally
(
    Number int not null,
    constraint PK_Tally primary key nonclustered
        (Number)
) with (memory_optimized = on, durability = schema_and_data);

insert Tally (Number)
select top 1000000 row_number() over (order by v1.number)
from master.dbo.spt_values v1 cross join master.dbo.spt_values v2;

And then try this for a stored procedure:

if exists (select * from sys.objects where type = 'P' and name = 'usp_CreateNewRecords')
	drop procedure usp_CreateNewRecords
go
create procedure dbo.usp_CreateNewRecords (@RecordCount int)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	declare @firstId int;
	select @firstId = isnull(max(ProductID), 0) from dbo.Product;

	insert	dbo.Product (ProductID, Description, UnitPrice)
	select	t.Number + @firstId ProductID,
			'Product ' + cast(t.Number + @firstId as nvarchar(500)) Description,
			cast(t.Number + @firstId as money) UnitPrice
	from	dbo.Tally t
	where	t.Number <= @RecordCount;
end
go

Then we’ll run the procedure to add 1,000,000 records:

exec dbo.usp_CreateNewRecords @RecordCount = 1000000;

This goes pretty fast on my machine, consistently adding the 1M records in 5 to 6 seconds.

I wrote the procedure the way I did because I’ve always been taught (any experience has validated) that set-based logic is almost always superior to row-by-row processing. Hekaton definitely changes that paradigm. Let’s rewrite that procedure to do one-off inserts.

if exists (select * from sys.objects where type = 'P' and name = 'usp_CreateNewRecords')
	drop procedure usp_CreateNewRecords
go
create procedure dbo.usp_CreateNewRecords (@RecordCount int)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	declare @firstId int;
	select @firstId = isnull(max(ProductID), 0) from dbo.Product;

	declare @counter int = 1;
	while @counter <= @RecordCount
	begin
		insert dbo.Product (ProductID, Description, UnitPrice)
		values (@firstId + @counter,
			'Product ' + cast(@firstId + @counter as nvarchar(500)),
			cast(@firstId + @counter as money));
		select @counter = @counter + 1;
	end
end
go

Now that insert runs in 3 to 4 seconds. Not a dramatic difference, but intriguing to me because it seems to be a game-changer.

Finally, the limitations. This is why I’ve not found natively compiled procedures to be terribly compelling, because I am constantly running up against some limitation. The Microsoft master In-Memory Limitations page lists them. Scroll down to the Natively Compiled Stored Procedures section, which occupies the majority of the page, and you’ll see the long list. I won’t repeat them all here, but the big ones for me are lack of support for CTEs and subqueries, CASE statements, temp tables, non-BIN2 limits, lots of unsupported join types, no IN or OR or LIKE, no DISTINCT and limitations on aggregations, no ROW_NUMBER(), and of course no references to disk-based tables.

Whew! Even my abbreviated list is pretty long!

Hekaton Part 4: Isolation Levels

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One thing to keep in mind about querying Hekaton tables is that the isolation level you use is quite important. Queries must be run at the snapshot, repeatable read or serializable isolation level. In the previous post, we executed a select query. Let’s add an explicit isolation level.

set transaction isolation level read committed;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

And this query runs just fine. We execute at the read committed level using snapshot isolation.

But change that “read committed” to just about anything else (read uncommitted, repeatable read) and the statement will produce an error.

Msg 10794, Level 16, State 80, Line 3
The transaction isolation level 'READ UNCOMMITTED' is not supported with memory optimized tables.

Msg 41333, Level 16, State 1, Line 3
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Msg 41333, Level 16, State 1, Line 3
Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

Things also start to break down when you use an explicit transaction (instead of the autocommit transaction above).

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Msg 41368, Level 16, State 0, Line 5
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

The error message give the solution: use the with (snapshot) table hint.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee with (snapshot)
order by LastName, FirstName;

commit;

There is a database-level setting to allow omitting the table hint.

alter database Hekaton
set memory_optimized_elevate_to_snapshot = on;

And now the table hint can be left out of the query.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Things get a bit more interesting when queries access both Hekaton tables and disk-based tables. Suppose we have an on-disk Customer table and we want to note which Employees have certain relationships to a given Customer.

create table CustomerEmployeeRelationship
(
	RelationshipId int not null identity(1,1) primary key clustered,
	CustomerId int not null,
	EmployeeId int not null,
	RelationshipTypeId int not null
);

Side note: Trying create a foreign key back to the Employee table won’t work, another limitation of Hekaton tables.

alter table dbo.CustomerEmployeeRelationship
add constraint fk_CustomerEmployeeRelationship_EmployeeId
foreign key (EmployeeId) references dbo.Employee (EmployeeID);


Msg 10794, Level 16, State 10, Line 57
The feature 'FOREIGN KEY' is not supported with memory optimized tables.
Msg 1750, Level 16, State 0, Line 57
Could not create constraint or index. See previous errors.

Let’s find customer-employee relationships of a certain type:

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;

That works fine and well, but some combinations of isolation levels don’t mix and match.

set transaction isolation level repeatable read;

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp with (repeatableread)
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;


Msg 41333, Level 16, State 1, Line 74
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Hekaton Part 3: Querying In-Memory Tables

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So now that we’ve created our Hekaton table, we should be able to query and modify it just like any other table, right?

Actually, yes. OK, so there are a few limitations, but by and large this is the part of Hekaton that is the most fully supported part of the product.

insert dbo.Employee (EmployeeID, FirstName, LastName, Salary)
values
(1, 'Marie', 'Salazar', 106779.90),
(2, 'Jason', 'Brown', 85553.93),
(3, 'Felicia', 'Normandin', 52278.50),
(4, 'Peter', 'Sabatino', 55018.27),
(5, 'Gregory', 'Mannan', 66715.94);

Selecting from the data works well.

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

The data can be modified just like normal.

update dbo.Employee
set Salary = 57219.00
where EmployeeID = 4;

The table can be joined to other tables, whether another Hekaton table or a good old-fashioned disk table, so long as the other table in in the same database. If you try to cross over to a different database, you get this error:

Msg 41317, Level 16, State 1, Line 20
A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user database or databases model and msdb, and it cannot write to master.

Some constructs aren’t supported. This MERGE statement is valid for a disk-based table.

merge dbo.Employee emp
using (values (5, 'Gregory', 'Mannan', 69384.58), (6, 'Michelle', 'Irvin', 80221.66) )
as src (EmployeeID, FirstName, LastName, Salary)
on emp.EmployeeID = src.EmployeeID
when matched then
	update set FirstName = src.FirstName,
		LastName = src.LastName,
		Salary = src.Salary
when not matched by target then
	insert (EmployeeID, FirstName, LastName, Salary)
	values (src.EmployeeID, src.FirstName, src.LastName, src.Salary);

But for a Hekaton table we get an error:

Msg 10794, Level 16, State 100, Line 25
The operation 'MERGE' is not supported with memory optimized tables.

(However, using a Hekaton table in a MERGE statement but not as the target is OK.)

truncate table dbo.Employee;

Msg 10794, Level 16, State 92, Line 37
The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

So yeah, a few limitations on querying, but most things work just fine.

Hekaton Part 2: Table Limitations

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One the big bummers about the first version of Hekaton is the limitations, which are legion. Microsoft has the complete list, but here are the big ones as far as I’m concerned.

Once you’ve created a table structure, it can’t be modified. No new columns, no new indexes. If you need to change the schema, you need to copy out the data somewhere else, drop and recreate the table with the modified structure, then copy the data back. This is tedious at best for small data sets, but a possible killer for larger tables.

A table cannot have more than 8 indexes. It need to have at least one index, a primary key, but otherwise none of the indexes can be unique. Also, you cannot update the primary key columns. Oh, and if a index is sorted ascending, SQL can only use that index in ascending mode. If queries need descending order, you’ll have to create a second index on the same keys, but in the reverse order.

There is a hard limit of 8,060 bytes per row, enforced at table create time. Disk-based tables had a similar limitation at one point, but I sure have liked things since that went away.

As a natural extension of the 8,060 byte limit, tables cannot have LOB columns.

No foreign keys. No check constraints.

Any string columns in an index must be in a BIN2 collation. This is a big one, in my experience. We’ve come to expect certain behavior from certain collations, and not having this behavior has been a deal-breaker in a number of circumstances when I’ve considered using an in-memory table.

A note about indexes that I didn’t cover before is that are two types of indexes. One is the hash index, and you would create one by simply declaring a nonclustered hash index, and include a bucket count based on how many items you expect the index will ultimately hold. Hash indexes are more useful if the query specifies an equality.

index idx_Product__UnitPrice nonclustered hash (UnitPrice)
with (bucket_count = 1048576)

Range indexes are useful for finding ranges of data, such as when the query uses an inequality or a range of values. To create a range index, omit the HASH keyword.

index idx_Product__UnitPrice nonclustered (UnitPrice)

Hekaton Part 1: Getting started

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So, back in SQL Server 2014, Microsoft introduced a new technology by the name of In-Memory OLTP.  It was code named Hekaton, which is a much cooler name.  And shorter.  And easier to say.  So I’m just going to keep calling it Hekaton.

This post is part one of what I expect will be a long series of brain dumps on how to use Hekaton as well as some of the internal that I have picked up over the past couple of years.  With the run-up to SQL Server 2016, we’ll also see some pretty good enhancements, so I’ll address these as well in due course.

Back when Hekaton was first announced (and once I started to wrap my head around it), I thought it was the coolest thing ever, and that it was going to revolutionize SQL Server.  Once it came out, it was quickly appparent that it was a “version 1” product and that there were just too many limitations around it to be truly useful from the get-go.

So it didn’t exactly set the world on fire, and even with many improvements coming in “version 2,” it still has a ways to go.  I am still firmly of the belief that the day will come when in-memory technology will become the de facto standard for new tables.

So, with that background and that hope for the future, let’s start with how to enable Hekaton at the database and how to create a new in-memory table.

create database Hekaton
on primary (name = 'Hekaton',
	filename = 'D:\sql\data\Hekaton.mdf',
	size = 25600KB, filegrowth = 5120KB),
filegroup InMemoryFileGroup contains memory_optimized_data
	(name = 'InMemoryFiles',
	filename = 'D:\sql\HekatonInMemoryFileGroup')
log on (name = 'Hekaton_log',
	filename = 'D:\sql\log\Hekaton_log.ldf',
	size = 10240KB, filegrowth = 10240KB);
use Hekaton;

create table Employee
(
	EmployeeID int not null,
	FirstName varchar(50) not null,
	LastName varchar(50) not null,
	Salary money not null,
	constraint PK_Employee primary key nonclustered
		(EmployeeID)
) with (memory_optimized = on, durability = schema_and_data);

SQLintersection session – Queries Gone Wild 2: Statistics and Cardinality Estimation, Versions 2008, 2008R2, 2012 and 2014

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

 

Session Title:  Queries Gone Wild 2: Statistics and Cardinality Estimation, Versions 2008, 2008R2, 2012 and 2014
Instructor: Kimberly Tripp, Joe Sack

 

Part I: Statistics and Data Distribution (Tripp)

 

  • Query execution
    • Standardization/normalization/algebrization creates a parse tree; this includes things such as expanding select * and views
    • Heuristics-based optimization (basically a guess) vs cost-based optimization
    • See Kimberly’s SP talk at this conference for limitations on SP performance
  • Query rewrites sometimes help
    • Joins written as subqueries (and vice versa)
    • Derived tables
    • Intermediate materialization
    • Check SARGability of prefixes
  • Selectivity
    • Tipping point is a very small percentage to drive SQL to use NCI with bookmark
    • This is why lots of narrow NCIs aren’t very helpful
    • Tipping point calculations – number of pages in CI * (between 1/4 and 1/3) = number of rows to do bookmark lookups
  • What to look for in statistics
    • Header: Updated date, rows vs rows sampled
    • Density vector
    • Histogram
  • “Bitmasks are evil”
  • When stats are build, SQL starts to “compress” the histogram, meaning there may be fewer than 200 steps. This can result in a bit of skew in the stats where “earlier” row get compressed out earlier and more often, resulting more “lossiness” for those rows
  • Leave auto-create stats on (except for very limited high-throughput environments that require very consistent timing)
  • DBCC SHOW_STATISTICS (tablename, statname)
    • STAT_HEADER
    • DENSITY_VECTOR
      • Output STAT_HEADER and DENSITY_VECTOR to temp table to join
    • HISTOGRAM
    • STAT_HEADER JOIN DENSITY_VECTOR (undocumented)
  • sp_autostats tablename
    • Not as useful except to quickly see if auto update disabled and last updated time
  • stats
    • See Kimberly’s query to get an at-a-glance view of all stats on a DB
  • STATS_DATE() function
  • dm_db_stats_properties
    • Rows vs rows sampled
    • modification_counter
  • Stats types
    • Auto-created
    • User-created
    • Hypothetical (what-if analysis (autopilot))
      • Ignored by query optimizer
      • DBCC AUTOPILOT (blog)
    • sp_update_stats (don’t use; it’s a sledge hammer)
      • Only one row modified triggers update
    • sp_create_stats (OK to use)
      • sp_create_stats ‘indexonly’, ‘fullscan’
        • (whether to user fullscan depends on the table size)
      • Updating
        • Leave autoupdate on, but manage yourself off-hours
        • Often based on sysrscols.rcmodified vs. entire table (3 to 8 percent)
      • Catch-all update statements, i.e.: SET col = ISNULL(@newvalue, col)
        • Causes counters to increase even if no actual change
        • Can greatly skew autoupdate thresholds
      • Auto updating
        • Can be done async (not enabled by default)
auto_update_stats auto_update_stats_async Behavior
Off n/a No update
On Off Wait during updates
On Off Stats updated async
  • Good for OLTP environments generally
  • TF 2371 uses dynamic auto update threshold
  • Leave on as a safety net
  • Best to roll-your-own updating (possibly using Ola Hallengren scripts as a basis)
  • Incremental stats (2014)
    • Partitioned tables, if 20% of records change stats recomputed for that partition and then rolled into overall table stats
    • Very lossy
    • Despite some information out there to the contrary, this is not partition level stats
  • If for some reason auto_update_stats is off (on 2008R2 and earlier), updating stats does not invalidate plans
    • Recommend running sp_recompile to rebuild scripts
  • Watch for cases where stats are bad and stats have been sampled; use full scan instead
    • Test out, then use full scan in scripts off hours
    • Additional benefit is that sampled scans are serial but full scan can be parallelized (subject to maxdop)
  • Filtered stats
    • Session settings not required
    • Create stats over a range of values
    • Add WHERE clause to CREATE STATISTICS statement
    • Beware of using sampling (but weigh against full scan costs)
  • Analyzing data skew
    • See sp_SQLSkills_AnalyzeColumnSkew and sp_SQLSkills_AnalyzeAllLeadingIndexColumnSkew
    • sp_SQLSkills_CreateFilteredStats
      • Splits histogram into 10 to 20 chunks
      • Drop and recreate every week or so
    • Filtered stats only autoupdate on the same schedule as the entire table – not nearly often enough
  • No good way to track stats usage to see how often a stat is used or not used
  • TF 9481 – Use legacy CE
  • TF 3604 – Output to client
  • TF 9204 – Which stat is used
  • New CE does not use
    • Filtered stats
    • Multi-column column stats
    • Autopilot
  • Limitations to filtered stats
    • Need to recompile or use plan guide to take advantage (OPTION(RECOMPILE))
    • Better to use partitioned views
    • Subject to interval subsumption problem when query predicate spans filter interval
    • But these are really designed for point queries
    • Same problem using IN clause where values span ranges. Workaround by using UNION ALL (even using dynamic SQL)
  • See the whitepaper on When to Break Down Complex Queries
  • Unevenly distributed data through the table
    • Can really only solve through indexing

 

Part IIA: Cardinality Estimation (Sack)

 

  • Problem with underestimating
    • Memory grants too small, leads to spills
  • Problem with overestimating
    • Memory grants too large, causes concurrency problems on other threads having to wait
    • Check sys.dm_exec_query_memory_grants for granted_memory_kb = null
  • General problems
    • Serial vs. parallelism
    • Seek vs. scan
    • Bad table join order
    • Bad join algorithm
  • Ways the CE comes up with estimates
    • Direct hit: Specific value in stats, uses EQ_ROWS
    • Intrastep hit: Uses AVG_RANGE_ROWS
    • Unknown runtime value: Uses density vector (all_density * row_count)
    • Distinct values: uses 1.0 / all_density
  • XEvent inaccurate_cardiniality_estimate
  • dm_exec_query_stats has actual row counts for last time executed
    • Can hide leaf-level skews
  • Warnings about troubleshooting CE issues
    • Be sure to check number of executions (need to multiply by number of rows)
    • Some cardinality issues cannot be readily resolved (example: Entity-Value table that contains many data types) and require fundamental redesign
  • Common CE-related problems
    • Missing or stale statistics
      • Verify auto_stats on
      • Check if stats have no_compute
      • Check stats date; manually update if needed
      • TF 2371 – decreasing dynamic threshold for auto updates
    • Sampling issues
      • Compare rows vs rows scanned
      • Will full scan work? Don’t know until you try it but can work in about 20% of cases
    • Hidden column correlation
      • Create multi-column column stats or indexes
      • Optimizer will only create single-column stats automatically
      • If value goes off stats histogram, reverts to density vector which can be worse
    • Intra-table column comparisons
      • e., where col1 < col2
      • Optimizer does not know how to correlate
      • Create computed column (i.e., col1 – col2) and create stats on it
        • Only works for pretty simple comparisons
      • Use self-joins, CTEs, or normalize data
    • Table variables
      • Generally not an issue for very small number of rows (less than about 100 rows)
      • Temp tables are better
    • Scalar or multi-statement table value functions
      • Convert to inline
      • Eliminate functions
      • Ask if the function is appropriate being in the data tier?
    • Parameter sniffing
      • SQL Server optimizing Stored Procedure (class on Pluralsight)
      • Check for ParameterCompiledValue vs ParameterRuntimeValue from actual plan
      • Standard parameter sniffing fixes
    • Implicit data type conversion
      • See Keyhias blog post on what causes conversions
      • Use correct datatypes
      • Use consistent naming conventions
      • Use catalog views to monitor for inconsistencies
    • Complex predicates
      • e., where left(firstname, 15) + ‘ ‘ + left(lastname, 15) = ‘xxxx’
      • Simplify when possible
    • Query complexity
      • Many objects references (implicitly or explicitly)
      • Break into smaller materialized sets
        • IO penalty, but can greatly make it worth it overall
      • Gatekeeper rows (DW scenarios)
        • See this blog post
        • Covering nonclustered index using FORCESEEK (usually a bad thing, but works here)
        • Dynamic SQL, columnstore index (still get skew, but speed of columnstore overcomes problems)
        • No really good resolution as of 2014
      • Linked servers
        • Prior to SQL 2012 SP1 cannot get permissions to get stats on remote server (with nonadmin accounts)
        • Can grant ddladmin role
        • Consider ETL or replication
      • Optimizer bugs
        • Very rare
        • Check Connect (either add to existing or create new with detailed reproduction)
      • See Joe’s list of troubleshooting questions

 

Part IIB: Cardinality Estimator Changes (Sack)

 

  • Many trace flags (disabled by default)
  • 0 to 2012 used essentially the same estimator
  • New CE in 2014
    • Workloads may or may not benefit from it
    • Context DB compat level must be 120+
    • See this article about using trace flags
    • TF 2312 – Use new CE
    • TF 9481 – Use legacy CE
    • Root node of plan has CardinalityEstimationModelVersion
      • 120 = new; 70 = old
    • To override per query: OPTION (QUERYTRACEON 9481)
      • But this requires ‘sa’ rights
      • Use plan guides to work around
    • New behavior
      • Multi-predicate correlation
        • Old: p0 * p1 * p2 * p3
        • New: p0 * p1 ^ (1/2) * p2 ^ (1/4) * p3 ^ (1/8)
        • Exponential backoff, ordered by selectivity
        • Does not use multi-column stats
        • Only to the first 4 predicates (?)
      • Out-of-range value estimation
        • Old: Ascending key problem underestimates recently-added rows; estimates 1 row above top step in histogram
          • TF 2389, 2390 enable auto generation of stats on ascending keys
        • New: Assumes existence of out-of-range rows; estimate based on density vector
      • Join estimates / containment
        • Old: Correlation assumed if non-join filter specified
        • New: No correlation assumed
      • Join estimation
        • New makes assumptions that are best associated with uniformity of data
        • Data skews may result in estimation worse than legacy CE
      • Distinct value count estimation (ambient cardinality)
        • Geared at ORM-generated code that includes extraneous joins or query elements
        • Tends to decrease estimates
      • TF 2363 – shows internal optimizer steps
      • XEvent query_optimizer_estimate_cardinality – indicates what stats are used
        • Usually for edge cases / MS Support
      • Options for regressions
        • Use compat level < 120, TF 2312 for selected queries
        • Use compat level 120, TF 9481 for queries with regressions
        • Use compat level 120, server trace flag 9481 globally
      • Be sure to test! If cannot test, use legacy

SQLintersection session – SQLintersection Closing Panel and RunAs Radio Recording

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  SQLintersection Closing Panel and RunAs Radio Recording

SQL Server Compliance Guide (for 2008 security, mostly still applies)

    • 2014 improvements include separation of duties, etc.
  • Multi-master replication guide
  • Google “call me maybe” + another platform (especially NoSQL) (blog about trying to break systems)
  • Filestream whitepaper; also blog on filestream
  • Worst SQL Server advice?
    • If you see CXPACKET waits, set maxdop = 1
    • PLE 300 is bad
    • Put a non-clustered idx on every column

 

SQLintersection session – Troubleshooting SQL Server Memory, Part 2

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Troubleshooting SQL Server Memory, Part 2
Instructor: Bob Ward

  • DMVs if something is going wrong
    • dm_os_memory_nodes
      • Non-NUMA = two nodes (0 and 64 [DAC])
      • NUMA = (0, 1, 2, …)
    • dm_os_memory_clerks
    • dm_os_memory_objects
  • Permon
    • SQLServer:BufferNode – per NUMA node
      • DB pages, PLE per node
    • DBCC MEMORYSTATUS
      • Away committed, Taken Away Committed – only available on this command, other columns found elsewhere
    • On 64-bit systems, ignore VirtualMemoryReserved
    • Memory clerks
      • SOSNODE – startup fixed
      • SQLSTOENG
      • SQLGENL – startup fixed
      • SQLCP – ad-hoc queries
      • SQLLOGPOOL
    • Memory standard report – add AllocatedMemory, VMCommitted, AWEAllocated to get actual memory
    • NUMA
      • Local memory favored; execution favored over “out of memory”
        • In conventional memory model set thread affninity
        • In locked memory model uses AllocateUserPhysicalPagesNuma()
        • Does not guarantee local node access
      • Taken Away – when physical node is not the SQL node
        • Not actually used at the time allocated, but reserved for local use
        • Retries a certain number of times to try to get local memory
      • Foreign Memory – when Windows doesn’t ultimately give local memory
      • Only shown in DBCC MEMORYSTATUS
    • Memory leaks
      • Out of memory error (message 701 in error log)
      • Not a Windows out-of-memory condition
      • Causes (can be SQL or can be user)
        • KB 2803065 (SQL)
        • Not closing cursors (user) (CACHESTORE_SQLCP)
        • xml_prepare_document without unprepared (user)
        • Other user-caused errors (usually heap)
          • External stored procedures
          • Unsafe CLR assemply with native calls
          • Other DLLs
        • Error log dumps
          • Look for system physical memory low and process physical memory low
          • Error 701
        • Plan cache eviction
          • Memory pressure – external clock hand
          • Cache cap limit – internal clock hand
          • Clock hand rolls through cache and decrements a cost-based counter; when zero, plan gets evicted
          • DMV to monitor external vs internal
          • DBCC FREEPROCCACHE
          • Some sp_configure changes cause eviction
        • Hard limit for all cache/user stores
          • TF 8032 uses pre-2005 SP2 behavior
          • Each cache gets a soft limit of the hard limit
          • MEMORY_CLERK_SQLLOGPOOL can be a factor; fixes to this problem available
        • RMLUtilities Suite
          • Includes ostress
          • Recommended download
        • Aggressive memory grants (workspace reservation)
          • Sorts, hash joins, index builds
          • Reserved memory for query operators
          • Typically capped at 25% of target per query
          • If grant vastly exceeds actual needs other parts of SQL become memory starved
          • Particularly bad for
            • Large data types
            • XML queries
            • Batched sorts
            • Linked servers
          • Can use Resource Governor to decrease that 25% number, but then beware of spills
        • Blocking problems
          • Large number of queries requesting grants
          • Concurrent query compilations
          • Thread-safe scalability
        • Buffer Pool Extensions (BPE)
          • Intended for SSDs, but this is not validated
        • In-Memory OLTP
          • Not evicted, cannot respond to pressure
          • Still has to honor target
        • Many SQL Server memory architecture decisions in old MS blog posts
        • See Bob’s 2013 PASS session for detailed info about SQL memory

SQLintersection session – Troubleshooting SQL Server Memory, Part 1

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Troubleshooting SQL Server Memory, Part 1
Instructor: Bob Ward

Primary memory consumers in SQL Server

    • Buffer pool (BPool) – number 1 consumer
    • Plan cache – traditionally number 2 consumer
    • Other (log pool, token perm)
    • Hosted memory (CLR)
    • Fixed memory (fundamental operations of SQL Server, such as connections)
    • In-Memory OLTP
    • Columnstore
  • As of 2012, max server memory = ALL server memory, not just the BPool
  • Locked pages – must be enabled on the service account at the OS level group policy
  • max server memory: 0 = min( 95% of physical RAM, 50% available RAM )
  • min server memory
  • Not coordinated between instances
  • Paging – only the BPool is pagable to disk
  • Windows paging
    • Working set trim operations
  • Three memory models
    • Conventional
    • Locked pages
    • Large pages
  • Locked pages memory model
    • Locked pages in memory privilege
    • Only Standard and Enterprise Editions
    • Memory is no longer part of the process’ working set
    • Recommended to use this feature especially if it is a dedicated box, including VM
    • Also take a hard look at max server memory
    • SQL will trim its own memory as requested by the OS down to min server memory
    • Recommend don’t set min unless running multiple instances
    • Private bytes include locked memory
  • DMVs
    • dm_os_sys_info
    • dm_os_sys_memory (Win counters)
    • dm_os_process_memory (very valuable for seeing what SQL thinks it has)
      • physical_memory_in_use_kb
    • Useful perfmon counters
      • Target server memory (KB)
      • Total server memory (KB)
    • Private bytes include process-based memory (stack space, etc) so will be somewhat higher than total server memory
      • If it does not stay at about a fixed amount over total server memory, there is probably a memory leak (most likely in a DLL)
    • Memory consumption standard report in SSMS
    • Buffer pool disfavoring – pages from large scans get discarded earlier
    • dm_os_buffer_descriptors – advanced diagnostics
      • group by to see the number of hashed buffer pages
    • Plan cache troubleshooting
      • Formula to determine cache cap size
      • Watch perfmon compilations/sec – may indicate a high rate of eviction
      • dm_exec_cached_plans
      • Bloat can be caused by non-parameterized plans
      • TF 8032 – increase size of all caches (uses pre-2012 formula) – use with caution
    • Known issues with memory
      • NUMA with large memory (256+ GB), see KB article
      • Large memory model (TF 834)
      • Standard Edition size limits only apply BPool
      • Known memory leak bugs
      • Read recommended articles at end of resources

SQLintersection session – Conceptualizing, Navigating and Reasoning about Roslyn Trees

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

This is one of two development session I attended.

Session Title:  Conceptualizing, Navigating and Reasoning about Roslyn Trees
Instructor: Kathleen Dollard

  • roslyn.codeplex.com
  • Ways Roslyn will change your life
    • Enhance compilers (allows new language features, open source release)
  • Source code
    • Parser -> Syntax Tree –> Symbol loader -> symbols -> binder -> semantic tree -> IL emitter
  • Analyzing code
    • Diagnostics (SyntaxKind)
    • Code fix
    • Code action
    • Code refactoring
  • Demo for adding rule to C# to require braces around if statements
  • Kathleen’s open source wrapper around Roslyn