The SQL Server Data Row Size Question: Why is It Bigger?

This morning, I received the following question from a user:

Hello Madam,

Could you please clarify SQLServer “Data Row” size:

If I run the script below on SQL Server 2012, then Slot(row) Size is 710 bytes

if I run the same script against SQL Server 2016 and above, then Slot(row) Size is 724 bytes.

They then provided a script which creates and inserts a few rows into a sample table, runs the DBCC IND command to find a list of pages for the sample table, then uses the DBCC PAGE command to examine the page.

The first thing I looked for: how many bytes is the difference? Is it 14 bytes?

I love it when someone sends me a repro script, but in this case I didn’t need to run it. The first thing I did was to look at the two numbers given for row size, and to subtract the smaller one from the larger one: 724 – 710 = 14 bytes of difference.

That bit of information alone gave me an immediate guess of what was going on.

Row versioning in SQL Server has a 14-byte row overhead

You can reduce blocking in SQL Server by enabling “optimistic” locking. There are trade-offs for this approach, however: row versions are enabled in tempdb, and 14 bytes of overhead may be required on impacted rows. (For a quick overview of this row versioning process, check out this Simple Talk post by Kalen Delaney.)

My guess is that row versioning is enabled only on the SQL Server 2016 and above instances that are being tested in this case. This could be because of any of the following

  • Snapshot isolation is allowed on the database
  • Read committed snapshot isolation is enabled on the database
  • The database has a readable secondary in an availability group

You might wonder – why would having a readable secondary cause the row to grow?

Readable secondaries in SQL Server allow you to offload read workloads.

It’s important to minimize blocking against those readable secondaries, though: you don’t want data modifications flowing through to the readable secondary to be blocked by reporting queries that you are running against the secondary, after all. You also want reads to be as consistent as possible.

For this reason, queries that are run against readable secondaries are automatically escalated to snapshot isolation. And snapshot isolation requires row versioning — those 14 bytes of space are needed too help make it all work.

But readable secondaries are read-only, right? So how can it it write those 14 bytes? The answer is that it’s complicated. Here’s the documentation from in the “capacity planning” concerns in Microsoft’s docs:

When you configure read-access for one or more secondary replicas, the primary databases add 14 bytes of overhead on deleted, modified, or inserted data rows to store pointers to row versions on the secondary databases for disk-based tables. This 14-byte overhead is carried over to the secondary databases. As the 14-byte overhead is added to data rows, page splits might occur.

The row version data is not generated by the primary databases. Instead, the secondary databases generate the row versions. However, row versioning increases data storage in both the primary and secondary databases.

Offload read-only workload to secondary replica of an Always On availability group

To dig into the details of this more, click the link — there’s even more info on the full page.

What a Software Evangelist Does – and Where the Job is Going (Dear SQL DBA Episode 67)

I’ve been working as an Evangelist at Redgate for close to six months now, and one question keeps coming up: what exactly does an Evangelist do at a software company? In 21 minutes, I explain the core mission of Evangelists, and why I think being part of the sales process is an important part of the future of software evangelism.

A transcript is below the video if you’d rather read. You can also subscribe to the podcast to listen on the go.

Episode Transcript

Note: I generated the images below for this episode with’s free tool, Pablo.

Hello and welcome to Dear SQL DBA, a podcast, YouTube show, and now a twitch stream for database developers and database administrators.

I am Kendra little from Redgate software and from SQLWorkbooks. Today I want to chat about what it is it evangelists do. My job at Redgate— my job title is that I’m an Evangelist. I’ve gotten the question from multiple folks, including people at passport control, as to what exactly is it that you do as an evangelist? Because it really really isn’t always obvious, and I think it’s changing as well. I think the role of Evangelists who’ve been around in software companies for a while is evolving.

How I first encountered an ‘Evangelist’: a man yelling into a phone

I first heard of this job title way back when I first was working at Microsoft. I think I was still a contractor there. I got a job at Microsoft as a contractor, and I was very very excited to be working at Microsoft. It was an amazing job. I worked a lot and I learned a ton– of course I was an hourly employee so working a lot was actually to my benefit. I eventually did become a full-time employee at Microsoft and kept learning more and more there as well, and it was great. But at this time, I was still a contractor.

The first encounter I had with an evangelist was… we were in these buildings where everyone was in offices. They weren’t cube farms at all. Everybody was in offices. Lots of us did share offices with other people. My office mates were always good people to work with. It was nice, actually, to share an office, because you could learn a lot. The people you were sitting with were often on your team or on a related team, so you could figure out lots of stuff about what it was like to support different products. But our offices weren’t all together for the team, so you had neighbors who worked in completely different parts of the organization.

One of the neighbors had a little nameplate that mentioned this person was an Evangelist. I don’t even know what team this person was on. It was a man, and the only things I knew about this Evangelist were that their door to their office was usually shut, and they were often yelling angrily into a telephone.

I was just like: I don’t know exactly what this job is, I don’t know if that the job is for really angry people, or if this just happens to be a really angry person– but there seems to be a lot of yelling involved. To this day I’m surprised by that. Even now, I cannot reconcile what all of this anger and yelling had to do with this person’s job. In my job as an Evangelist there is not a lot of yelling, there is not a lot of anger, and I don’t even use the phone that much. So everything is really different from that first encounter with an Evangelist. But it did teach me that different Evangelists may have very very different jobs, because that is not at all the job that I’m in in now.

Rebranding: ‘Evangelist’ –> ‘Developer Advocate’

These days a lot of people who do the job of what was formerly known as Evangelists are changing their job title, because the the word ‘Evangelist’ has religious overtones. To the many people I think it brings up the Crusades, and it brings up violence. I don’t know if that’s related to the angry person in their office screaming into the phone or not. But at Microsoft, for example, I do know that many evangelists now have the job title Developer Advocate.

I appreciate why the name might change, but I don’t know if Developer Advocate is the perfect name, either. When I think of an advocate it brings up to me someone you might get if you were in a difficult legal proceeding or if you were in a tough healthcare situation and you needed someone to advocate for you. It represents being a little bit helpless.

Maybe that’s not the right understanding of Advocates. Maybe that’s a bias on my part. But naming things is about understanding cultural bias. Is Developer Advocate a better name than Evangelist? Maybe. But it isn’t a name I’m rushing out to adopt — not yet.

I feel like I kind of haven’t come up with the perfect job title yet.

So what does an Evangelist do?

Let’s talk about what Evangelists actually do. A lot of what I do has to do with communication between my company and our customers, and our potential customers out in the wild. This is communication that goes two ways.

One of my main tasks is figuring out: what are the difficult things that people are encountering, what are the problems that people really need to solve? How do our products or different techniques related to our products help with that? That is a big part of my job: Patterns and practices that can help people be more successful.

Now, the two-way communication involves both listening to people about what’s difficult and what’s hard (which our products could help with) AND finding where our products could improve. Identifying: what are those pain points that our existing products help with, AND what might we be able to do with them that could really help fill this need?

I’m tasked with channeling communication back to our development teams and saying, hey, here’s what I’m hearing from our customers. I get asked by our development teams a ton: what are you hearing from people in the marketplace? What are you hearing from consultants? What are you hearing from potential customers? What are you hearing from people at conferences? They really want to know, what are the opportunities we have, what’s working well with people, what could we do better?

When listening to a customer, the job of an Evangelist is not only to understand, okay there’s a product that could help you out. The job of an Evangelist is also to understand patterns about how people work. If we are going to apply a technology or a product or a process, what are the things that will actually make a team successful at that? What are the ways that people interact that need to change? What are the dependencies on really making this work well?

The idea isn’t just to sell the products. In my talks, I’m going to talk about things where, yeah, our products could make it easier. But there’s always a bunch of different ways to do things. When I design talks, I’m interested in thinking about: what are the ways to make people successful? Yes, if our products fit into this, that’s amazing. And I do have a bias, because I do have an employer, right? I want to be clear about that all the time. Evangelists aren’t unbiased and neutral. But part part of the job is also to be quite open, and to not only think about the products, but to also think about how people work and how people can be successful — even if parts of that are outside of the products. Or even if our products aren’t required to make you successful.

Because the truth about the modern world and technology is you can absolutely write a bunch of code yourself to do a lot of things. Is it gonna take you a long time? Yes. But there’s always more than one way to do something. Buying products from different vendors can make your life easier, though.

So, to me, the point of being an Evangelist is really about helping people get set up to succeed. The parts I’m gonna be the best at, the parts I’m gonna look at the most are in the areas around where my company’s focus is. Because in fact, I am really passionate about that.

I took an Evangelist job at a place where I was passionate about what we are doing. I wouldn’t have taken an Evangelist job if I didn’t think these were important problems to solve, if I didn’t think they were good tools to solve the problems.

It’s really hard to say this in a nutshell, but I think an Evangelist is someone who helps people be successful with the technology from that company– and further helps set people up for making an informed decision about how they can be successful. What this ends up meaning is Evangelist may be useful to you even if you’re not a customer of that company’s products. Because when it comes to a good Evangelist, they’re giving you information that — yeah, it’s gonna really help you if you use that company’s products, but they’re also giving you really valuable information that’s going to have implications agnostic of what products you’re using.

For me, I want to deliver value and insights about DevOps however you’re doing DevOps. I want it to be valuable to you. I would love it if you would use our products, but even if you aren’t, in everything I do I want there to be takeaways that are valuable. Even if you’re not doing DevOps right now, the information can be useful if you’re keeping it in the possible things you might do. If you are doing DevOps now, I want to include concepts and patterns you can work in regardless of the products that you’re using.

My job at Redgate combines Evangelism with involvement in the sales process

An interesting thing about my job is — my job personally is not a “pure Evangelist job”. (I don’t know if there is such a thing as a pure Evangelist job.) But my job at Redgate is a mix. The job advertisement for my position said: up to 40% of your time is going to be spent working with the sales folks, as part of the sales process. But not as a salesperson, as an Evangelist.

This might sound kind of weird, because I have said hey, part of Evangelism is including things that are meaningful even if you aren’t using the products. That’s still true.

The interesting thing I think about sales — and I think this is an evolution of the sales process– is more and more companies need to tap into insights about the customer and really understand the customer’s problems. Companies are turning to different models of purchase, different subscription models. Organizations don’t want to sell you something you buy one time. They want to sell something that you renew.

Renewal is incredibly important to software companies. The points in sales is not just to sell a product once, you need your customers to use your products and to become successful with them, because you want them to continue happily using (and renewing) your product in the future. That is a huge important thing. So really understanding where the customer is coming from, what their problems are, and how to solve those problems is a very important part of the sales process.

Working with customers is the best part of my job

My absolute favorite part of my job is interacting with new and renewing customers as part of the sales process. Because when you interact with potential customers and existing customers, you learn about all these different real world scenarios. I think the value that I provide to Redgate as an Evangelist is that I’ve been a database person for twenty years. For a long time, I’ve been working with how to write code, how to optimize SQL Servers, how to optimize processes. I’m always learning more. So in terms of my experience, it helps me really understand where customers are coming from when they’re struggling with: how do we safely deliver database changes at a high rate? How do we do this without overworking our people so that they’re dragging in the next morning and aren’t even awake enough to do code review? How do we optimize this process and make happy teams that work well, that have stable systems, and we have everything humming perfectly– so we can really churn changes through?

That’s not easy, but my job as an Evangelist is to have insights into: how do we safely make these changes? Working with different customers, it resonates with so many different experiences I’ve had.

Every software development group is unique. Everyone has their own problems, and everybody has a certain amount of anti patterns that work for us. The job isn’t just, hey, there’s these best practices. It’s much more about figuring out: how do we come up with unique ways to solve these problems and get to the result we want. We’re gonna follow a lot of best practices, but probably we can’t just implement all these best practices. Because there’s always unique requirements and unique factors that mean not every best practice fits.

So a lot of what I do is listen for: what are the really important requirements, and how do we map best practices to this? How do we creatively come up with solutions to the places where the best practices don’t match? That helps us picture the future state. Then there’s the further problem of: how do we get from here to the desired future state? Often we can’t do it all in one go. Often we may have to take smaller steps to get there.

Working with customers in the sales process — I have found that it’s the secret to making Evangelism exciting for me. Because I get to hear from customers about real problems, and I get to help brainstorm creatively to come up with solutions for those– and help unstick people from getting blocked. To me that is just a ton of fun.

The culture of your sales team matters

I do think that the culture of the sales team you’re working with matters. I’ve worked with different sales teams throughout my career, and I’m not gonna name any names here — I’m not talking about now at Redgate– I have worked with sales teams who tended to over-promise. They promised vaporware. They were selling things that don’t quite exist.

Being an Evangelist in that context would be deeply uncomfortable, and probably wouldn’t work. Because a big part of being an Evangelist is being straightforward with people. Being honest and and having credibility.

I can’t sell vaporware. I’m not going to sell vaporware. We’re working in a culture where that would be a hard fit. I do think I think more and more with software, because of the importance of renewals, because of the importance of customer happiness, I don’t think those practices are gonna survive and thrive.

I think more and more sales processes are going to need to become more transparent to be successful. Otherwise, later on, people are going to be looking at those deals and they’re gonna say: why why didn’t this customer renew? Well, if we sell them something that doesn’t exist, if we sell them something that doesn’t work for them, that customer is not going to end up being super satisfied.

So I have been thriving as being part of the sales process, but if you’re if you’re looking at different Evangelists in other companies, their ability to be part of sales will depend on on the sales culture.

The future for Evangelists / Developer Advocates

I think Evangelist jobs are great. It’s definitely a specific personality type: you have to like to talk, you have to like to listen, you have to like to interact with people, as well as create presentations and things like that. But if you are a person who is interested in teaching and in learning, it can be a fantastic fit.

My job does technically sit within a marketing department. I think that’s great. In the past, I thought of marketing as being just a department that put out a good image. “We need to make things look good.” More and more, that is changing.

With the business changes that are happening inside companies — with the “digital transformations” that get talked about at the executive level — the role of marketing is about helping the company have insight into the customer.

What is the customer saying? What is the customer doing? What resonates with the customer? What excites the customer? What frustrates the customer? These insights are incredibly critical to helping a company survive both in terms of the messages you’re giving to people — because you want to help people understand when you can solve their biggest pains. But also in terms of shaping the product design. Customers can easily compare and easily switch service providers more quickly than ever using phones and computers. People have choice.

We have more and more interesting ways of delivering messages to people, too – and I think Evangelists are a really important part of that. That has to do both with reaching people through things like podcasts, through things like Twitch, through trying out new communication methods.

But also in terms of listening to the customers, I get insights from customers in Slack channels, for example the SQL Server community Slack channel. Sometimes I get them by email, I get them by messages on my website. I also get them through that contact with customers. The great thing about that [contact with customers] is that I can learn in-depth about: what are the problems that you’re facing? What are the real pains you have? What’s really important to you, and what is the best plan to get there?

Do our company’s products fit in that plan? Sometimes they will. Sometimes they won’t. The success of marketing and the success of sales is finding places where it really works, because those are going to be the customers that not only buy, but the customers that renew. The customers that tell other customers: hey we found a great way to solve these problems using those solutions.

So, have I found a short answer to what an evangelist that I could say at passport control? Well, to be honest, at passport control, I think it’s just easier to say that I am a software developer or sales engineer. We can all describe our jobs in different ways, right? My job is not a sales engineer, but a related job title is a little bit easier to explain to someone who doesn’t work in software. For better or for worse, the term ‘Evangelist’ is just very heavily loaded.

If you have ideas about the best possible job title for an Evangelist, I would love to hear it — because I still haven’t come up with that perfect term yet. I like the term Developer Advocate, but I’m still not 100% sold that it’s the best way to describe the job.

So, anyhow, this episode is for all of those have you been asking, “hey Kendra, what is it that you’ve been doing these days?” I’ve got some more episodes in the queue coming up, I’ve got some more questions to answer. Looking forward to talking to you folks again soon, and making Dear SQL DBA more regularly again. Thanks, folks!

DBA Jobs: Threats and Opportunities (Dear SQL DBA Episode 66)

Jobs change over time, and database administrator jobs are no different. In this 35 minute recorded Twitch livestream (my first ever!) I talk about threats to DBA jobs and the related opportunities.

It’s been a few months since I published an episode of Dear SQL DBA. I like the idea of doing future episodes with livestreaming on my Twitch channel, so I’m hoping to make this a regular thing again. The audio in this episode isn’t perfect, but I reminded myself that perfect is the enemy of good, and that sometimes it’s OK to be imperfect: people will choose if they want to listen or not. But I’ll work on getting audio quality back up in the next episode for sure.

And with that, here’s the recording:

Fix My Functions: Speeding Up Scalar and Table Valued UDFs (video)

Last week, I was lucky enough to present on the topic of TSQL User Defined Functions (UDFs) in SQL Server at the PASS Summit.

My session was live-streamed, and the video, scripts, and slides are below.

Want to watch more Summit sessions for free? Check out PASS TV here.

Video (1 hour 10 minutes)

Note: if you’re just here to see the SQL Server 2019 (CTP2.1+) scalar UDF inlining, that starts at 54 minutes into the video. 


Fix My Functions demo script (zip)


Below the slides is an outline of the content, generated by SlideShare.

1. Speeding Up Scalar and Table Valued UDFs Kendra Little, Redgate Fix My Functions

2. Kendra Little Evangelist at Redgate Founder, SQL Workbooks Microsoft MVP, MCM [email protected] @Kendra_Little

3. This talk Level: 200 Audience: DBAs & Devs Goals: Speed up functions

4. “It is a known fact amongst practitioners that UDFs are “evil” when it comes to performance considerations [35, 28].”

5. Agenda

6. Why we use functions Modular meerkat

7. Why functions? Modularity Code reuse Simplify queries

8. Function types Scalar: returns a single value Multi-statement TVF: returns table Inline TVF: returns table

9. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS INT –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Scalar function syntax

10. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS @return_variable TABLE (/* table type definition */) –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Multi-statement TVF syntax

11. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS TABLE –WITH SCHEMABINDING, … AS RETURN ( ) GO Inline TVF syntax

12. SELECT TOP (10) qp.dbid, qp.query_plan, cp.size_in_bytes / 1024. / 1024. AS size_in_mb FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp ORDER BY size_in_mb DESC; CROSS/OUTER APPLY and TVFs

13. Demo: why so slow?

14. sp_WhoIsActive – free procedure from Adam Machanic:

15. sp_WhoIsActive – free procedure from Adam Machanic:

16. Lightweight Statistics Profiling SQL Server 2014 SP2 through 2017 • Trace Flag 7412 • Install KB 4078596 (2016 & 2017 only) SQL Server 2016 SP1+ MUCH lower overhead SQL Server 2019 no trace flag needed anytime-anywhere

17. sp_WhoIsActive Free procedure Written by @AdamMachanic

18. Find what functions hide Curious corg

19. Get tuning data for functions Estimated & actual execution plans Finding function calls in operators UdfCpuTime and UdfElapsedTime in actual execution plans

20. Demo: spy on function internals

21. Estimated plans help! Scalar functions and multi-statement TVFs: estimated plan shows the function logic • Does not appear in an actual execution plan • Plans for the calling query and the function are stored in sys.dm_exec_query_stats, but you must find them individually

22. UDFs and parallelism TSQL scalar UDFs – serial plan Multi-statement TVFs – serial zone Computed column with TSQL UDF – parallelism eradicator, BEWARE

23. MSTVFs and row estimates SQL Server 2005 – 2012 SQL Server 2014 – 2016 SQL Server 2017+ 100 1 ?

24. Interleaved execution Part of adaptive query processing, all Editions Introduced in SQL Server 2017… • MSTVFS only • Read only queries • Cannot be on the inside of an APPLY • Compatibility level 140+

25. Interleaved execution (continued) Diagram by Joe Sack @JoeSackMSFT ntroducing-interleaved-execution-for-multi-statement-table-valued- functions/

26. Scalar UDF Inlining Clever crow


28. Why are scalar UDFs slow? Executed row by agonizing row Scalar operators not ‘costed’ No cross-statement optimization No parallelism functions/scalar-udf-inlining

29. Automatic inlining Rewrite scalar UDF Substitute rewrite into calling query Then optimize functions/scalar-udf-inlining

30. Demo: wizardry

31. Controlling behavior Database compatibility level 150 CREATE FUNCTION … WITH INLINE = OFF USE HINT (‘DISABLE_TSQL_SCALAR_UDF_INLINING’) functions/scalar-udf-inlining

32. No scalar UDF inlining if it… Uses GETDATE() Uses table variables or TVPs Is in computed column Is in a check constraint functions/scalar-udf-inlining

33. Takeaways

34. Tips for tuning functions Scalar UDFs and Multi-Statement TVFs inhibit parallelism Use SCHEMABINDING if your function doesn’t do data access Use inline TVFs (single statement) or persist data when possible

35. The future of scalar UDFs 2019 inlining is VERY compelling Edition has not been announced Releasing with “high coverage”

36. References & links Lightweight query profiling reference – Pedro Lopes progress-anytime-anywhere/ SQL Server Functions, the basics – Jeremiah Peschka programming/sql-server-functions-the-basics/ Froid: Optimization of Imperative Programs in a Relational Database – Karthik Ramachandra et al

37. References & links continued Interleaved execution for multi-statement TVFs – Joe Sack ucing-interleaved-execution-for-multi-statement-table-valued-functions/ Parallelism inhibitors – Paul White -a-parallel-query-execution-plan.aspx

38. Thank You Where to find me… @Kendra_Little [email protected]

Register for my upcoming session on digital transformation

I’m excited to have a session accepted to GroupBy, a free online conference targeting the Microsoft data platform community. The conference is sponsored by Brent Ozar Unlimited, and sessions are chosen by community votes. 

My session will be given on Fri, Dec 21, along with five other terrific looking sessions. You should  register for GroupBy here.

About my session

My session is called “What ‘Digital Transformation’ means, and how you can use it to advance your career (without being a robot).” Here’s the abstract:

Whether you love or hate buzzwords, the big ones signify critical cultural changes. In this session, Kendra Little will explain what executives mean when they describe a ‘digital transformation’, why this transformation is happening across all industries, and how understanding this gives developers and database administrators an advantage in building their careers.

You will learn what motivates CEOs to modify their business models in a digital transformation, and patterns and anti-patterns of companies that have attempted these transformations – with different results.

You’ll leave the session with an understanding of the core ideas and philosophies behind digital transformation that will help you prioritize what to learn, guide your interactions at work, and strategize your career path.

Group By session abstract

I wasn’t sure that this session would get votes

I’m particularly excited that the community of voters saw value in this topic because I haven’t seen any talks on this topic before in the data platform community. 

While we hear the phrase ‘digital transformation’ in conference keynotes frequently these days, I think many technologists don’t think much about what it means. Or worse, assume it doesn’t mean anything

‘Digital transformation’ is meaningful, and is absolutely worth understanding

For the last several months, I have been researching what worries and inspires CEOs and CIOs. I was surprised to find a large body of studies and predictions of massive trends in “digital transformation” across all industries.

What I once thought was “just a buzzword” is a shorthand that executives use to represent major changes in business models. It’s incredibly useful for practitioners, team leads, and managers to understand what the goals of a digital transformation are and how success is measured. This understanding helps you communicate better with executives, align your team’s work with business strategies, and ask for the right resources and headcount to execute on critical tasks for your organization.

I hope you join me in December at GroupBy!

DevOps Terms – and Why They Matter to Database Specialists

In this 20 minute session, I define scrum, continuous deployment, test driven development, DevOps, and related concepts. I close with a quick discussion of why Database Administrators and Developers should care about DevOps.

Prefer to listen on the go? Subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app:

If you enjoy Dear SQL DBA, reviews  on iTunes help me out a ton.

Slides from the presentation

Employee Agreements & Contracts: Best Practices (23 minute video)

So you’ve got an employee agreement in front of you: now what? In this  episode, I talk about practical steps you should take to make sure that you understand the terms of your contract, and how to potentially negotiate the terms.

Listen on the go

Subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app:


Slides from the talk