My SQL Server friendo Mr. Erik C. Darling was recently telling me about some work he did getting batch mode on paging queries. This sounded a bit odd to me because paging queries make me think of small seeks of rows against finely curated nonclustered rowstore indexes, so I asked him for a link to his blog post about it. He grumpily refused and told me to find the link myself, which I eventually did.

High End Machine Performance

Erik’s first attempt used OFFSET/FETCH and resulted in a row mode query:

The clustered index scan makes this a bit of a sad paging query. In Erik’s defense, there’s a whole host of real world reasons as to why you wouldn’t be able to make the perfect nonclustered index for your paging query:

• End users may choose to sort on many different columns and you can’t index them all
• A key member of your Index Approval Committee is on vacation
• You already have more indexes on the table than your number of fingers
• You’re working with third party software which does not allow you to create custom indexes

Getting back to the query, it doesn’t look that offensive to me. The row mode sort is a parallel top N sort and the overall number of rows to return is low, so each thread can independently sort its rows and return 1000 locally sorted rows to the parent operator. This is about as good as it gets with parallel row mode sorting. This is a row mode only query so the operator times that you see are the sum of that operator’s work and its children. In terms of real work done by the query, the scan clocks in at 1.168 seconds and the sort clocks in at 0.84 seconds. The final accounting at the end by the parent Parallelism (Gather Streams) is misleading at best and an outright LIE at worst. There wasn’t 4 seconds of work done by this query. There was only 2 seconds. The red lines illustrate the problem perfectly and I won’t be elaborating further:

Erik’s second attempt uses ROW_NUMBER() and he achieves a plan with some batch mode operators using BMOR (batch mode on row store):

The parallel batch mode sort works just fine here in that the single thread output property isn’t an issue. The parent operator is a batch mode window aggregate, but even if it wasn’t, the grandparent is a gather streams operator so the rows would end up on one thread anyway. Actual time statistics accounting works differently for batch mode operators: each batch mode operator only tracks its own work. In terms of real work done by the query, the scan clocks in at 1.1022 seconds and the sort clocks in at 0.892 seconds. This is quite similar to the first attempt. It could be argued that the batch mode sort is more efficient than the row mode top N sort, but I’d call it a wash considering the unpredictable rowstore to batch mode conversion overhead (which does seem to be small for this table).

Low End Machine Performance

I tested on my local machine with 64 GB of RAM which is less than Erik’s laptop. My clustered index scans took significantly longer than his, but as usual, there’s a lot to learn from low end machine performance. Let’s go back to the first reason as to why the table might not be indexed well for this particular query:

End users may choose to sort on many different columns and you can’t index them all

Microsoft presents a standard solution for this scenario: the humble nonclustered columnstore index. This will be great for my low end machine because I’ll be able to fit the new NCCI in memory. For those following along at home on their own low end machines, I created the index on every column except the Body column in a very carefree fashion:

CREATE NONCLUSTERED COLUMNSTORE INDEX ncci ON posts (
  Id
, AcceptedAnswerId
, AnswerCount
, ClosedDate
, CommentCount
, CommunityOwnedDate
, CreationDate
, FavoriteCount
, LastActivityDate
, LastEditDate
, LastEditorDisplayName
, LastEditorUserId
, OwnerUserId
, ParentId
, PostTypeId
, Score
, Tags
, Title
, ViewCount
) WITH (MAXDOP = 1)

I ran the OFFSET/FETCH query on my low end machine with MAXDOP 4 and it only took 430 CPU ms and 130 ms of elapsed time:

That’s a huge improvement compared to the 15-20 second runtime I was experiencing earlier. Interestingly, the second query (the ROW_NUMBER() approach) sticks with the parallel batch mode sort and performs significantly worse in comparison:

The key difference here is the batch mode Top N sort in the first query. Remember that the query compile process for BMOR is different than what you get when compiling in the presence of a glorious columnstore index. You can get the improved batch mode top N sort by also doing a fake join to an empty CCI table. Serious batch mode connoisseurs should be mindful of the compile differences as they seek to gain the greatest performance benefit possible from batch mode.

Final Thoughts

Friends don’t let friends be lied to by SSMS. Thanks for reading!

The post Erik cries when SSMS lies appeared first on Darling Data.

Today’s list has some very cool things to click through and check out. Dig into a few of these!

[blog] Vertical Integration is the Only Thing That Matters. Developer tools don’t work in a silo. This post explains why integrations, or “glue code”, among these tools is so important, yet hard to achieve.

[blog] How to Train an LLM: Part 1. Long post, but educational if you want to follow someone’s journey to train an LLM and what was learned along the way.

[blog] Do you detect a pattern? BigQuery’s new MATCH_RECOGNIZE function can! Complex pattern matching, right inside your SQL queries? We keep adding functions to BigQuery that simplify your architectures.

[blog] What Developers Really Mean by “Bad Code.” Good question! This post calls out some criteria, and how to actively reduce the amount of bad code in your projects.

[blog] Introducing Code Wiki: Accelerating your code understanding. I really like this. The era of static (and often incomplete) documentation is over. Oh, a quality source of truth always matters. But how we consume it will never be the same. Check it out!

[blog] Ordering food using Zomato’s MCP server with Google Gemini CLI. i’m not emotionally prepared to order food via the CLI. Maybe next year. But MCP is opening up so many possibilities.

[article] Choosing the right format for your AI model: A comprehensive guide to AI inference formats. What are model formats, and why are there so many of them? It might feel too late to ask this question, but it’s not. Ivan taught me a few things here.

[blog] Nano Banana can be prompt engineered for extremely nuanced AI image generation. I often just one-shot silly stuff with our models, but this post does inspire me to invest more in refinement.

[blog] Spec-Driven Development: The Waterfall Strikes Back. It’s a legit concern, especially if people take the wrong approach with it. You can use specs to drive the next hour of work; don’t spend months writing specs!

[blog] Building Trustworthy Charity Agents with Google ADK and AP2. Great example of agentic commerce in action. Learn about the way it all works in this detailed post.

[blog] Waze keeps traffic flowing with 1M+ real-time reads per second on Memorystore. Some apps can tolerate a lag. Traffic and direction navigation data? Nope.

Want to get this update sent to you every day? Subscribe to my RSS feed or subscribe via email below:

Next week we’ll be in Orlando, Florida for Visual Studio Live!, and if you work with .NET, we’d like to meet you.

You’ll find the JetBrains booth (#118) in the conference expo area throughout the week. Stop by to:

• Watch live demos of ReSharper, Rider, and our other .NET tools.
  
• See how we’re supporting .NET 10 and Visual Studio 2026.
  
• Ask questions about your own projects and get tips from our team.
  
• Talk through what’s coming next on the product roadmaps.
  
• Pick up some JetBrains swag.
  

We’ll also have special offers available for those who visit the booth.

Getting Started: The Foundation

Q: "We're a SaaS platform with multiple customers—how do we keep their data separate?"

A: The most successful multi-tenant software companies use the owning and consuming tenant approach.  Once you have developed your application and it’s ready to be deployed to a customer, customers want to maintain control of their data, use the security configuration they have configured and compliance controls across their entire data estate.  What they don’t want to do is store data outside of their tenant.  When using SharePoint Embedded, you control the application, your customer controls their data.

Software companies create their application in the owning tenant. When you’re ready to bring on a new customer, you simply register SharePoint Embedded on the consuming tenant (your customer).  As documents are added through your application, they reside in the consuming tenant and all the security boundaries they have configured.

Scale reality check: You can create millions of containers per tenant, with each container holding up to 30 million documents. That's serious enterprise scale.

Q: Our customers demand specific geographic data storage and compliance. Can SharePoint Embedded handle this?

A: SharePoint Embedded inherits Office 365's data residency capabilities, which often exceeds what most software companies can provide on their own:

• Geographic controls: Data stays within customer-specified regions
• Government clouds: FedRAMP support for government tenants or contractors
• Compliance inheritance: Customers leverage their existing Microsoft compliance investments

Game-changer example: Customers need FIPS compliance and ITAR support for government contractors. Rather than building this infrastructure themselves, they leverage Microsoft's existing certifications.

Q: What's the real story on costs? How do software companies handle SharePoint Embedded billing?

A: When a software company is ready to deploy their app there are two primary billing models:

Pass-through model: Customer pays Microsoft directly through an Azure subscription they choose.

• The software company handles integration, customer handles billing
• Customers maintain control over their data and costs
• Works well for enterprise customers with existing Microsoft relationships

Software company-standard model: Software companies include SPE costs in their pricing and then invoices the customer

• Easier for customers, but software companies must manage costs closely.
• Storage: ~$0.0067/GB/day + API transaction costs + egress costs
• Works well for SMB customers or all-inclusive service models

Success Pattern: Legal sector software companies typically use pass-through, while financial management apps include costs in their SaaS pricing.

Document collaboration: it’s all about the user experience

Q: Our customers hate our current document editing experience. What changes with SharePoint Embedded?

A: The collaboration transformation is usually immediate and dramatic:

Before SharePoint Embedded:

• Web-only editing with limited functionality
• Version conflicts and manual merging
• External users need full software licenses
• Downloading a document to edit it and then re-uploading it increases risk

After SharePoint Embedded:

• Native desktop Office applications with full feature sets
• Real-time co-authoring with automatic conflict resolution
• External users collaborate without Office licenses
• Zero custom integration maintenance

Customer impact story: A Construction Cloud customer was frustrated with web-only Office editing. With SPE, their construction teams can collaborate on specifications in desktop Word, cost sheets in Excel, and project presentations in PowerPoint—all simultaneously. Customer satisfaction scores improved immediately.

Q: How do we handle external users—clients, contractors, reviewers—who aren't employees?

A: This is where SharePoint Embedded really shines for software companies:

• Guest user support: External users can collaborate using their existing email addresses 
• No license requirements: Guests don't need Office licenses to edit documents 
• Time-bound access: You can grant temporary access for specific projects 
• Granular permissions: External users see only what they need to see, and you control this through existing Entra ID security practices.

Real-world scenario: A pharmaceutical customer needs external regulatory reviewers to collaborate on drug approval documents. These reviewers (often using Gmail accounts) can access specific documents, make comments, and track changes—all while maintaining strict security controls and audit trails.

Q: What about industries with specialized document formats? Will SharePoint Embedded work?

A: SharePoint Embedded handles standard Office formats natively, and provides extensibility for specialized formats:

• Native support: Word, Excel, PowerPoint can be viewed or edited using the browser or full desktop experience. 
• Custom formats: Through Power Platform connectors and custom viewers depending on how you built the user interface. 
• Industry-specific: Many software companies build viewers for CAD files, proprietary image documents or medical records.  This is the real value add for your app.

Specialized example: Engineering companies use CAD files and Office documents, relying on custom viewers for technical drawings as well as built-in Office collaboration tools for handling specifications and project documentation.

AI and intelligence: The future-forward 

Q: Everyone talks about AI, but what are software development company customers really using?

A: Based on real implementations, customers are getting value from three AI capabilities:

1. In app experience

• • Customers can find information across documents using natural language
  • "Show me all contracts with renewal clauses" instead of keyword searches
  • Provides a robust alternative to the custom search solutions that many find challenging to develop effectively.

2.  Document summarization

• • Automatic summaries of meeting notes, reports, contracts
  • Executives get briefings without reading full documents
  • Particularly valuable for legal and consulting software companies

3.  Content extraction

• • Automatic metadata extraction from uploaded documents
  • Classification and tagging without manual effort
  • Useful for compliance and organization

AI success story: A market research platform uses AI to mine insights from massive survey result repositories. They can identify patterns across client studies and provide competitive intelligence that drives premium service offerings—capabilities that would have required a dedicated AI team to build.

Q: How does SharePoint Embedded content fit into the Microsoft AI story?

A: Content is king and when it’s stored in SharePoint Embedded you can use the Microsoft AI stack to reason over it.

• Copilot Studio – Build custom agents that can access and reason over your SharePoint Embedded content, enabling tailored workflows and conversational experiences for your business scenarios.
• Azure AI Foundry – Use advanced AI models and orchestration tools to analyze documents, extract insights, and apply generative reasoning directly on your embedded content (coming soon).
• M365 agents – Empower Microsoft 365 Copilot and domain-specific agents to leverage your SharePoint Embedded data securely, delivering contextual answers and automation across apps like Teams, Outlook, and Word.

Control flexibility: You can disable Copilot at the container level, so customers only pay for what they use. This lets you offer different service tiers based on AI capabilities.

Q: How much prep work do our documents need for AI to be effective?

A: The software companies seeing best AI results focus on structured metadata and information architecture fundamentals.  

Document organization:

• Clear document types (contracts, reports, specifications)
• Consistent metadata fields across document types
• Permissions, sensitivity labels and container architecture reduce the accidental data leakage risk.

Users simply add the document to a container through your application and SharePoint Embedded does the rest by automatically indexing the content, which adds it to the semantic index, so you get all the reasoning power in the LLM's.

Preparation benefit: Software companies find that organizing their document metadata and permissions before enabling Copilot improved AI accuracy.  Customers get better results and more relevant document summaries.

Security and compliance: Enterprise requirements

Q: Our customers are in highly regulated industries. How do we handle their compliance requirements without becoming compliance experts ourselves?

A: This is SharePoint Embedded's biggest advantage for software companies—compliance inheritance: 

Your customer's compliance = Your application's compliance

Following the owning/consuming deployment model provides:

• Customer's DLP policies automatically apply to containers
• Their retention policies govern document lifecycles
• Their audit requirements are automatically met
• Their security controls protect your application's data

Compliance success: Financial services software companies don't need to become SOX compliance experts—they inherit their customers' existing Microsoft Purview policies. Legal software companies get automatic GDPR compliance without building privacy infrastructure.

Q: What about audit trails and eDiscovery? Do we need to build this ourselves?

A: SharePoint Embedded provides enterprise-grade audit capabilities automatically:

• Complete audit trails: Every document access, modification, and sharing event is logged 
• eDiscovery Integration: Native integration with Microsoft eDiscovery tools 
• Retention policies: Automatic retention based on customer's existing policies 
• Legal hold: Built-in legal hold capabilities for litigation scenarios

Audit reality: Legal sector software companies can provide their clients with comprehensive audit trails for regulatory compliance without building any custom audit infrastructure. Everything is handled through Microsoft's existing compliance tools.

Q: How do we handle customers who want to keep control of their data?

A: SharePoint Embedded gives customers more control than most software company solutions:

• Customer tenant: Data stays in the customer's Microsoft tenant, not yours 
• Customer policies: Their security and compliance policies govern the data 
• Software company access: You only access data through APIs with customer-granted permissions

Control example: SaaS platform software companies explain to customers that their documents live in the customer's tenant with customer-controlled governance. This made enterprise sales easier because customers maintain complete control over their data.

Have more questions or want to talk to the team, contact us: SharePointEmbedded@microsoft.com

_____________________________________________________________________________________________________________________________________________________________

Resources

SharePoint Embedded overview: SharePoint Embedded Overview | Microsoft Learn