Press "Enter" to skip to content

Author: Kevin Feasel

Memory-Optimized Objects In The Transaction Log

Published 2016-07-21 by Kevin Feasel

Frank Gill looks at the fn_dblog_xtp function:

Transactions against an in-memory table will return a single row in the result set from fn_dblog, while the result set from fn_dblog_xtp will contain a row for all activity. For example, if you insert 100 rows into an in-memory table, fn_dblog will contain 3 records: a BEGIN TRANSACTION, a single row for the INSERT, and a COMMIT TRANSACTION. fn_dblog_xtp will contain 102 records: a BEGIN TRANSACTION, and a row for each row inserted, and a COMMIT TRANSACTION.

One of the new columns in fn_dblog_xtp is xtp_object_id. I tried to join this to the sys.objects table to return the object name, but the values didn’t match. After banging my head against my monitor for a while, I posed the question to #sqlhelp on Twitter. Andreas Wolter (b|t) responded that a correlation can be made using DMV sys.memory_optimized_tables_internal_attributes.  Excited, I tried to join fn_dblog_xtp to the DMV, but was disappointed.

Read the whole thing.

Comments closed

Multi-Tenant Database Architectures

Published 2016-07-21 by Kevin Feasel

James Serra describes a few architectures for multi-tenant databases in the cloud:

Separate Servers\VMs

You create VMs for each tenant, essentially doing a “lift and shift” of the current on-premise solution.  This provides the best isolation possible and it’s regularly done on-premises, but it’s also the one that doesn’t enable cutting costs, since each tenant has it’s own server, sql, license and so on.  Sometimes this is the only allowable option if you have in your client contract that their data will be hardware-isolated from other clients.  Some cons: table updates must be replicated across all the servers (i.e. updating reference tables), there is no resource sharing, and you need multiple backup strategies across all the servers.

Read on for a few other strategies.  There aren’t any cloud-only details here; you could implement the same strategies on-premises.

Comments closed

Thinking Functionally With Scala

Published 2016-07-20 by Kevin Feasel

Kevin Jacobs solves a simple problem using Scala in a few ways and explains functional programming concepts along the way:

Why is this code better than the functional approach? Note that it saves an enormous amount of time since this approach does not need to scan through all the integers! It are simply a few calculations (at which a computer is good at). All the code (the naive approach and the better approach) can be found on GitHub.

Having a solid understanding of mathematics and logic can help you come up with superior algorithms, but make sure you comment them in detail so that the next dev (who might not understand the underpinnings of your code) doesn’t replace it with a brute-force method because it’s “easier.”

Comments closed

Job Hunting

Published 2016-07-20 by Kevin Feasel

Andy Mallon hunts the most dangerous of creatures:  man job.

Finding a new job is hard. At every turn, it’s a lot of work: finding a job, interviewing, negotiating. I don’t know anyone who likes doing it. I do know lots of people who struggle with various parts of the process.

I want to share my experience, my thoughts, and my opinions. Hopefully, you can learn from my experience (good and bad). By sharing my experience, hopefully I can make life a little bit easier on you the next time you’re job hunting.

Definitely looking forward to this series.

Comments closed

Finding Failed Backups And Jobs

Published 2016-07-20 by Kevin Feasel

Thomas Rushton builds a nasty query to answer an important question:

Assumptions

  1. Backup jobs that do full backups don’t overlap
  2. There’s nothing else doing full backups
  3. erm…
  4. that’s it

The reason I was thinking about this is that we have occasional-but-annoyingly-frequent backup job failures, wherein most of the databases back up just fine, but the odd one fails. (SharePoint box, I’m looking at you…) Rather than trawling through the error logs to find out which particular database didn’t back up successfully, I wanted a query to do the heavy lifting. Yes, I’m a lazy lone DBA…

The end result is a pivoted query showing days in which full backups fail.  There’s a lot of information in there, so that might be something I’d want to visualize in Excel or R, changing cell colors for failed jobs so they stand out better.  Nevertheless, check this out, especially if you don’t have a solution in place to monitor backups.

Comments closed

Parallel Loading Columnstore Indexes

Published 2016-07-20 by Kevin Feasel

Sunil Agarwal shows how to bulk load with parallelism into a clustered columnstore index from a staging table:

SQL Server 2016 requires following conditions to be met for parallel insert on CCI

  • Must specify TABLOCK
  • No NCI on the clustered columnstore index
  • No identity column
  • Database compatibility is set to 130

While these restrictions are enforced in SQL Server 2016 but they represent important scenarios. We are looking into relaxing these in subsequent releases. Another interesting point is that you can also load into ‘rowstore HEAP’ in parallel as well.

The restriction I’d most like to see reduced would be the “no non-clustered indexes” part.  The rest seem forgivable for most clustered columnstore setups (i.e., fact tables).

Comments closed

SSIS Firewall Rules

Published 2016-07-20 by Kevin Feasel

Slava Murygin shows how to create a firewall rule to allow SSIS connections:

Recently tried to connect to Remote SQL Server Integration Service directly from SSMS and got following error:

TITLE: Connect to Server
——————————
Cannot connect to 10.1.32.66.
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
——————————
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
——————————
BUTTONS:
OK
——————————

Slava then shows how to work around this.

Comments closed

Missing Values In R

Published 2016-07-20 by Kevin Feasel

David Smith explains NA values in R:

Here’s a little puzzle that might shed some light on some apparently confusing behaviour by missing values (NAs) in R:

What is NA^0 in R?

You can get the answer easily by typing at the R command line:

> NA^0
[1] 1

But the interesting question that arises is: why is it 1? Most people might expect that the answer would be NA, like most expressions that include NA. But here’s the trick to understanding this outcome: think of NA not as a number, but as a placeholder for a number that exists, but whose value we don’t know.

Definitely read the comments on this one.

Comments closed

Diagnosing Memory Grant Issues

Published 2016-07-20 by Kevin Feasel

Pedro Lopes looks at Extended Events around memory grants:

Three conditions can trigger this warning to show up in showplan:

  1. Excessive Grant: when max used memory is too small compared to the granted memory. This scenario can cause blocking and less efficient usage when large grants exist and a fraction of that memory was used.

  2. Used More Than Granted: when the max used memory exceeds the granted memory. This scenario can cause OOM conditions on the server.

  3. Grant Increase: when the dynamic grant starts to increase too much, based on the ratio between the max used memory and initial request memory. This scenario can cause server instability and unpredictable workload performance.

I like that this information also shows up when you view an execution plan using SSMS 2014 SP2.

Comments closed