INSYNCVA

Microsoft needs to put old tech like XML out to pasture

They recently (SQL Server 2022) added XML Compression after having the XML Data type around since 2005, and XML Indexes for those stubbing their toes on XPATH and XQuery Outer Apply fun. My testing yielded about 4:1 Compression Ratio on CCDA medical records,

If people are silly enough to store LOB Items in a database with its tepid indexing performance, instead of a file system where they belong (think Xrays, Audio, Video, IOT Data) and simultaneously allowing MS to deprecate SOAP/XML webservice support in .NET Core/7 and Powershell 7, they need to drop XML development efforts in SQL Server as well and focus on Indexing and Compressing of JSON in the database instead.

But they wont, because having the higher performing SQL Server Engine with its near unlimited Disk, CPU and RAM engine compete on JSON Data with the lackluster CosmosDB performing engine cant be allowed

We might just find that SQL Server outruns CosmosDB and that cant get out

What are your thoughts on using CStore under SSAS MD ROLAP for large fact tables/dimensions?

Ever do any benchmarks on a DW using CStore (relational tables) vs an SSAS MD Molap cube?

What are your thoughts on CStore vs Tabular/PowerBi/AzureAS xVelocity engine?
currently CStore has no number of rows, CPU or memory limitations like xVelocty engine does

Since CStore rowgroups can get fragmented due to deletes/updates
what is your recommended maintenance plan? Rebuild or Reload?

How do CStore indexes handle frequent updates compared to Rowstore and Statistics?
Cstore doesnt use statistics but meta data and delta stores

Do you use Cstore on In-Memory Hekaton databases or whats the point? (Diminishing returns, non-compatible tech)

What are your thoughts on CStore in PDW/APS vs Hadoop?

CStore issues: performance loss due to decompression
AKA, the time to first row slower than rowstore on large queries

Done any Benchmarks on Rowstore Batch Mode vs Columnstore Batch Mode?

Ever Used NCCS on OLTP tables with <1M rows?

Segment elimination: does it work for columns not next to each other?
remember all this data is in one physical MDF file
only when you do NCCs so that each column is addressed separately?

Benchmark:
Columnstore on OLTP/Relational tables
SSAS MD Molap
SSAS Tabular on-prem
Azure Analysis Services
Power BI Premium
Azure Synapse
Azure Data Warehouse
Hadoop/Databricks/Spark

Problem: coming up with a Query and dataset to compare all these

An Old I.T. Joke

re•cur•sion rĭ-kûr′zhən – See Link

Its 2020, do you know what your DBA does?

I started this blog in 2015 and did nothing with it, TLDR, life etc.

As a DBA, at my Employer, I do much more than a regular DBA

1. Because they let me
2. Because my Boss encourages it
3. Because I’m good at it
4. Because I.T. moves at the speed of light and so do I – Matt 5:15

Azure was the Old New Thing in 2015 ( Raymond Chen ) , and to certain degrees, still is

Its just the same thing on Microsoft’s servers.

Not anymore.

They have products and services neither Google or Book Boy (AWS) have because all they can do is host somebody else’s(MS) stuff.

Speaking for myself, these are just some of the techs that cross my consciousness on a regular:

• SQL DBA (duh)
• Data Security
• Data Cleaning
• Powershell
• Powershell
• Powershell
• Active Directory
• Workday Integrations
• .NET namespace junkie
• REST and SOAP Webservices
• XML
• JSON
• Azure Storage Queues
• Azure Storage Tables
• Azure Storage Blobs
• Azure Flow
• Azure Logic Apps
• Azure Function Apps
• Azure DevOps
• Azure RBAC
• Azure SAS Tokens
• Azure MS Graph
• SSIS – not so much –> Powershell
• Data Warehouses
• SSAS MD
• SSAS Tabular
• SSRS
• Power BI/DAX
• R/ggplot2
• Machine Learning
• Exchange REST API
• Parsing Data from HTML Emails
• Exotic (Moon-based) workflows
• Healthcare CCDA XML shredding
• And a few Standard Deviations thrown in for good measure

What will a DBA be tomorrow?