Level 3 of the Stairway to Synapse Analytics: Analyze Data Using a Spark Pool (2024-09-18)

SQLServerCentral Newsletter for September 18, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Level 3 of the Stairway to Synapse Analytics: Analyze Data Using a Spark Pool]( - [Auditing SQL Server – Part 4 – Database Configuration Audit]( - [Microsoft return to PASS Summit 2024 as Sapphire sponsor]( - [From the SQL Server Central Blogs - Missing Columns in Extended Events Live Data Explorer]( - [From the SQL Server Central Blogs - Using T-SQL Snapshot Backup - Point in Time Recovery]( - [The Phoenix Project]( Question of the Day - [Concat Addition]( The Voice of the DBA  When Do We Worry About Scalability? "We need to get the code written for feature X. Can you finish this query today?" We've all heard some variation of that request. We have a request or demand, and we need to get it done. We need to get code out so our business can advance, sell more things, get more customers, etc. There's always some reason to get new code pushed to production quickly. However, many technical people want to ensure their code works well. At least, I believe most do. While most people can write code that works and meets the requirement, some don't know how to write code that performs well or don't know how to test their code to check. Often there isn't a large workload in dev or test environments to verify things. There may not be a large workload in production either, at least not at first. So, what do you worry about first: your code being used or performing well? That's a similar question to this one: [Worry about Scalability or Popularity First?]( While most of us don't work for a startup and our organizations have some sort of financial stability, does popularity matter? I'd say that for any feature you build, whether a startup mobile app or a legacy ERP system, you're still looking at this type of question. You want to know if it's used, and how often. That might determine if you spend more time on this feature or area. Maybe you have some idea of popularity, or just plan old use of the feature. In that case, certainly make sure it will scale to not only meet your data size now, but plan for some level of growth across the next 6-12 months. If it's a new area of functionality for your application, then maybe you have no idea. In that case, the DevOps approach is get something working, a minimally viable version of your code or query, and then tune it later if it becomes a problem. Many technical people approach the endless number of tickets and requests they get like this. The problem is management often doesn't budget in time to clean up the technical debt (to care about scalability). My view for database code is that we should always be leveling up our database code knowledge. If we deploy bad code in production, and can't fix it, then at least we can avoid adding to the problem by writing the same poorly performing code again. Learn a better way to write that type of query. Whether you're [splitting strings]( [finding islands and gaps]( [calculating running totals]( or anything else. Learn what works well and write that code next time. That helps your team balance the scalability and popularity-chase by producing good code the first time. Or at least, the next time. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Synapse Analytics]( [Level 3 of the Stairway to Synapse Analytics: Analyze Data Using a Spark Pool]( Sucharita Das from SQLServerCentral Introduction In Level 1 of this series, I discussed Synapse Analytics basics and the steps for creation of the Synapse Workspace. In Level 2, data analysis was done on Data Lake files using Serverless SQL Pool. In Level 3, I will analyze data from the files uploaded in Data Lake container using a Spark Pool. I will […] [External Article]( [Auditing SQL Server – Part 4 – Database Configuration Audit]( Additional Articles from SimpleTalk This continues my series on auditing SQL Server. The fist parts covered discovery and documentation, server level hardware audits and SQL Server engine level audits. This section examines database configuration audits. [Technical Article]( [Microsoft return to PASS Summit 2024 as Sapphire sponsor]( Additional Articles from SQLServerCentral Get ready to meet Microsoft's experts on-site in the exhibit hall and in multiple educational sessions (including a pre-con, keynote and learning pathway). Watch a sneak peek of Microsoft's learning pathway 'Becoming an Azure SQL DBA – Advancing the Role of the On-Premises SQL Server DBA' [Blog Post]( From the SQL Server Central Blogs - [Missing Columns in Extended Events Live Data Explorer]( Grant Fritchey from The Scary DBA Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work... [Blog Post]( From the SQL Server Central Blogs - [Using T-SQL Snapshot Backup - Point in Time Recovery]( aen from Anthony Nocentino Blog Introduction In this post, the second in our series, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot... [The Phoenix Project]( [The Phoenix Project]( Additional Articles from SQLServerCentral In this newly updated and expanded edition of the bestselling The Phoenix Project, co-author Gene Kim includes a new afterword and a deeper delve into the Three Ways as described in The DevOps Handbook.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Concat Addition What is the result of this code? SELECT CONCAT(1, 2, 3) + 4 Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Trigger Order III I have created these triggers in SQL Server 2022: CREATE TRIGGER triggertest_tri_1 ON dbo.triggertest FOR INSERT AS PRINT 'one' GO CREATE TRIGGER triggertest_tri_2 ON dbo.triggertest FOR INSERT AS PRINT 'two' GO I want to be sure that the trigger with "1" runs first. I decide to run this: EXEC sp_settriggerorder @triggername = 'triggertest_tri_1', @order = 'first' What happens? Answer: I get an error since I didn't specify the type of trigger with a third parameter Explanation: There is a third parameter for sp_settriggerorder, which specifies the type of trigger (insert, update, or delete). Ref: sp_settriggerorder - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Development and T-SQL [Tablock to avoid deadlock]( - Hi, We have 4 stps. the flow of the stored procedure (stp) is as below stp A is main stp that is called by the application. Inside A, stp B C and D are called within Begin Tran. Also in each stp B C D explicit transaction is included. Below is the flow of stp […] Development - SQL Server 2014 [How to load AML files SSIS or any other option]( - Team, Please suggest or if you have an example please share the link for loading AML (anti moneylaundering)files into SQL Server. Meaning file having multiple recs in the .txt/.cvs file. Loaded to multiple tables .. Basically it is AML files if anyone who has done that do suggest Thanks in advance  SQL Server 2019 - Administration [Error Login from an untrusted domain]( - Hi All, I found something a little strange in my SQL Server Error Log, There is an error message: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: XXX.XXX.XXX.XXX], but I tried to access / login from the server using Management Studio and the same user has […] [SPID is suspended but the wait type is NULL]( - Hello experts, I have always assumed that if there is no wait going on for a SPID, that its status would be runnable. I have seen just now with a large insert query that my assumption is wrong. Could someone explain how a SPID remains suspended if the wait type is NULL? Thanks for any […] [backups failure]( - Hello All, We have a domain account running sqlserver service.this account has r/w access to a network share, but backups are still failing. what are we missing here ? Even the the said computer$ has r/w to the network share I can access the network share from the said server. But when I run the […] [Unable to begin a distributed transaction.]( -   create table #failover_logs   (     id int identity,     logdate datetime,     processinfo nvarchar(100),     logging nvarchar(max)   ) --DROP TABLE #failover_logs       insert into #failover_logs       ( […] [Capture long transact query using SQL Server performance condition alert feature]( - I created a SQL Alert trying to capture long query transaction under the SQL Server Agent\Agent with the options below. However, it does not have the feature to capture what query is causing the long transaction. I was thinking of adding the sp_WhoisActive to capture the long transaction query but it did not have SQL […] SQL Server 2019 - Development [divided by zero error in SQL please provide solution]( - divided by zero error in SQL please provide solution SQL Azure - Development [Azure Purview Scanner Requires Database Master Key to produce Lineage, but why?]( - I am studying for the Azure Data Engineer certification and the material covers Purview linage scanning of SQL Server databases. It says there must be a master key setup in the database for lineage to be produced, but I cannot find anything that explains why. SQL Azure - Administration [Statistics Update being mis-triggered by OlaHallenGren]( - Hi Guys, I have a very problem with Azure SQL DB taking more time to update stats than index rebuild. I am trying to filter by using on update stats if modifications are 40% but I think it getting triggered even if the modifications are less than 1%. I am taking about column stats here […] Suggestions [Gateway timeout when posting reply but...]( - I posted a reply to a topic today and, after a minute or so, received a 504: Gateway Timeout error. Seeing that error, I thought my post had failed and was going to redo it but luckily, I checked first and to my surprise my reply was posted successfully. So, this was an "error: process […] SQL Server 2022 - Development [Beginner questions]( - Hello everyone and thank you for joining the forum. I am new to SQL and have a few beginner questions. 1. i have installed Microsoft SQL Server and SSMS as well. I just did it and got it to work and I can write queries in SSMS. Unfortunately I don't know if I forgot something […] [talend "template" feature vs ssis]( - Hi we will be using a talend template shortly to abstract the schema definition of spreadsheets such that we can plug and play different excel layouts into a general talend etl "program" that lands that data into different configurable sql destinations. i believe the template is a flat file with one entry for every column. […] [executing a proc on another server from a proc on th e1st server]( - Hi, this is a second post trying to help a friend on a slightly different subject. as shown at he is forced into splitting up some db's to multiple servers...without changing code.  So on db Q (now on server x) he still wants to execute a stored proc that executes a stored proc on […] [simple linked server plumbing]( - Hi, as a favor to a friend who needs to split databases between servers, and doesnt want to change code, he has a select on a.dbo.view that used to run from db Q easily because db A was on the same server.  I havent done one of these in a long time but shouldnt he […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -