Azure SQL Performance Testing on ESv5 v DSv2
In a previous blog I talked about the new Esv5 Azure VM Types that looked very promising for SQL Server and data warehousing , perhaps unseating the older DSv2 that we had been recommending. https://prodata.ie/2021/11/03/the-new-king-vm-type-for-sql-has-arrived/ Now that we have upgraded a couple of customers from the 8 or 16 core DS to the equivalent Esv5 … Continue reading Azure SQL Performance Testing on ESv5 v DSv2
EDW Automation Architecture
This blog explores a universal architecture for EDW automation. I’m, attempting to be technology agnostic, so little mention of specifics here. Albeit those who know me, know I am a SQL centric fan boy with a software engineering and Finance background. There are many different technologies for building data warehouses. Currently I see three emerging … Continue reading EDW Automation Architecture
The new King VM Type for SQL has arrived!
Hot of the announcements at Microsoft Ignite conference is the arrival of some new Memory Optimised VM Types. For those of you configuring SQL workloads on VMs, the DSv2 series has always been the “goto” VM Type for 8 and 16 core workloads, due to the higher RAM to core and IOPS to core ratios. … Continue reading The new King VM Type for SQL has arrived!
DW Automation demo for Synapse SQL Pools (3/3)
This is the third blog in a series looking at the need for DW Automation focusing on Synapse SQL Pools (dedicated). In the first blog we looked at why we need DW Automation tools and what they look like:https://prodata.ie/2021/09/14/the-need-for-dw-automation-1-3/ In the second blog we looked at a maturity model for DW automation with six levels … Continue reading DW Automation demo for Synapse SQL Pools (3/3)
Shrinking Azure Repos Bloat due to PowerBI
The world of source control for PowerBI is kinda in conflict On one side we need to use Source control and even Microsoft tools like Power BI deployment pipelines or your own CI/CD put PBIX files in GIT. On the orther side GIT has clear guidelines not to store large/binary files. If you have put … Continue reading Shrinking Azure Repos Bloat due to PowerBI
A DW Automation Maturity Model (2/3)
In a previous blog I talked about the need for DW Automation in order to achieve that holy grail of Agility, Extensibility and perhaps Robustness with our data architecture. This blog is now focusing on some different levels of maturity. What’s different about this maturity model and other ones is that its not necessary better … Continue reading A DW Automation Maturity Model (2/3)
The need for DW Automation (1/3)
Many folks build star schema data warehouses and the supporting ecosystem of Semantic Models, Business Intelligence and/or ML Analytics. This is a quick note on how to assess how you are with automation, regardless of what tools you are using: SSIS,ADF, SQL, data bricks, Synapse, or non Microsoft tools. I recently ran a twitter thread … Continue reading The need for DW Automation (1/3)
Do we need to maintain Columnstores in SQL Pools ?
TLDR The short answer is YES – if you have >= 60 million rows, then a poorly maintained column store was 100% slower due to trim fragmentation and 64% slower due to the delta row store with some updates in our tests. Obviously this is just a small test on only 60 million rows and … Continue reading Do we need to maintain Columnstores in SQL Pools ?
Finding Outdated and Missing Stats in Synapse SQL Pools
One recent realization for myself working with Synapse Dedicated SQL Pools is that while they have auto create stats, there is no auto update stats. So statistics will become stale faster than your bread left in the toaster. Rather than updating every 20% or so like traditional SQL Server. In additon, if you create an … Continue reading Finding Outdated and Missing Stats in Synapse SQL Pools
Top 10 SQL Partition Commandments
I wrote this in 2010, before Clustered Column Store Indexes, SQL Pools (so rule 3 looks a bit odd), and before date data type was mainstream, but the rest of these still look quite Thou Shalt use something that exists in your query as a partition key Thou shalt not make up a surrogate partition … Continue reading Top 10 SQL Partition Commandments
Top 10 SSIS/DW Commandments
Blast from the past. I wrote this is 2008 and at one point we had it made into a poster for the office rule whereby on code review we not say a word but just point to the poster. Not sure how well this stood the test of time now it is 2021 so many … Continue reading Top 10 SSIS/DW Commandments
Synpase SQL Tip 2 – Comments in Views/Procs
Synapse SQL Dedidcated Pools (aka SQLDW) does not support comments in views or procs in the same was as standalone SQL. This is annoying as comments can be very useful with tracking changes and lineage of objects, especially when used in conjunction with Schema Compare tools in Visual Studio. Everyone has seen that incident where … Continue reading Synpase SQL Tip 2 – Comments in Views/Procs
Loading…
Something went wrong. Please refresh the page and/or try again.
always on Azure backups Cloud consolidation Cube DW EDW ERP Events Featured Finance Financial Reporting Irish Economic Crisis Licencing Load Test Maestro Many to Many MasterClass MCM mdx Modelling MOLAP OLAP Partitioning Performance Personal Power BI PowerPivot Power View scom SQL SQLBits sqldw SQL IO SQL Saturday SQL Server SSAS SSIS storage Syanpse Synapse Tabular TSQL Virtualization
Subscribe to Prodata Blog
Get new content delivered directly to your inbox.