How Big Data Supports Gen AI (2024-05-20)

SQLServerCentral Newsletter for May 20, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How Big Data Supports Gen AI]( - [Recursive CTEs: Transforming and Analyzing Data in PostgreSQL, Part 3]( - [From the SQL Server Central Blogs - 3rd Party Applications Have Issues]( - [From the SQL Server Central Blogs - Get row counts of all tables in a Microsoft Fabric warehouse]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Multiple Query Trace Flags]( The Voice of the DBA  Kubernetes is Cool, But ... [Kubernetes]( is cool, and I think it's really useful in helping us scale and manage multiple systems easily in a fault-tolerant way. Actually, I don't think Kubernetes per se is important itself; more it seems that the idea of some orchestration engine to manage containers and systems is what really matters. As a side note, there are other orchestrators such as [Mesos]( [OpenShift]( and [Nomad](. However, do we need to know Kubernetes to use it for databases? This is a data platform newsletter, and most of us work with databases in some way. I do see more databases moving to the cloud, and a few moving to containers. I was thinking about this when I saw a Simple Talk article on [Kubernetes for Complete Beginners](. It's a basic article that looks at what the platform consists of, how it works, and how to set up a mini Kubernetes platform on your system. It's well written and interesting, but ... Do we need to know anything about it? Are we running databases in containers, or will we? I think it's possible that we might run any of our databases in containers. They are like lightweight VMs and there isn't a reason why we wouldn't run a database in a container. With external storage, of course, which gives you a cluster-like environment where your storage moves to a new node if the first one fails. That's a good use case. Deploying consistent environments quickly is a good use case. Using Kubernetes to manage the containers is great, but ... I don't think we need to know much about Kubernetes. I don't think most of us should run it and should outsource any container orchestration to the cloud if we decide to implement database containers. These orchestration engines are quite complex today, and there is a lot of expertise needed to manage them. I don't know that expertise is worth trying to find, train, and retain for most organizations. We should just outsource the container management to someone else. We might need to know how we change the configuration of some resources, but that's minor knowledge, and really, I suspect that outsourced K8S (shorthand for Kubernetes) will have GUI tools that let you easily pick and choose the CPUs, memory, etc. and then an export of the JSON or YAML or whatever is needed for the config. Most of us likely need the skills to export, save (in a VCS) the files, and then submit them to the cluster. A few years ago I went through a bunch of courses and reading material on Kubernetes. I set up some small clusters, I experimented with pods, I even was excited to think about managing containers for various services. What I discovered is that Kubernetes is complex, hard, and something I want someone else to run. Once I set up a cluster in Azure I thought I'd never want to do this on-premises again. Much like email. I have run email servers, but ... I'd like to never run one again, which is how I feel about Kubernetes. I think containers have proven more complex and harder to work with than many people thought. I know there are plenty of people using them, but it's a minority. I see many more organizations still building monoliths, or microservices that run as processes, or client-server apps. Not that many people are excited and using containers. That may change, and if you go to the cloud, containers give you portability that many other solutions don't, so I'd recommend them there. However, they are still a bit immature, and hard to manage. I think it will be a while before we see lots of databases on containers. Even if we do, I'm not sure we need to learn Kubernetes as database people. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How Big Data Supports Gen AI]( Prasenjit from SQLServerCentral This article includes an overview of how big data and Artificial Intelligence (AI) models work together. [External Article]( [Recursive CTEs: Transforming and Analyzing Data in PostgreSQL, Part 3]( Additional Articles from SimpleTalk The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL. [Blog Post]( From the SQL Server Central Blogs - [3rd Party Applications Have Issues]( Kevin3NF from Dallas DBAs As a SQL DBA, what do you do when a vendor application has performance problems that are code related? Server settings don’t generally seem to be an issue. Queries... [Blog Post]( From the SQL Server Central Blogs - [Get row counts of all tables in a Microsoft Fabric warehouse]( Koen Verbeeck from Koen Verbeeck I loaded some built-in sample data from Wide World Importers into a Fabric warehouse. You get an option to load sample data when you create a new pipeline in... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Multiple Query Trace Flags I want to enable two trace flags, 4199 and 4137, for a single query. How should I do this: -- A SELECT a.AddressID , a.AddressLine1 , a.AddressLine2 , a.City , a.StateProvinceID , a.PostalCode , p.FirstName , p.LastName FROM Person.Address a INNER JOIN person.Person AS p ON p.rowguid = a.rowguid WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (QUERYTRACEON 4199, 4137); -- B SELECT a.AddressID , a.AddressLine1 , a.AddressLine2 , a.City , a.StateProvinceID , a.PostalCode , p.FirstName , p.LastName FROM Person.Address a INNER JOIN person.Person AS p ON p.rowguid = a.rowguid WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137); -- C SELECT a.AddressID , a.AddressLine1 , a.AddressLine2 , a.City , a.StateProvinceID , a.PostalCode , p.FirstName , p.LastName FROM Person.Address a INNER JOIN person.Person AS p ON p.rowguid = a.rowguid WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (QUERYTRACEON 4199), (QUERYTRACEON 4137); 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) Creating Statistics on XML I have a table in SQL Server 2022 that is an XML data type. I have an index on this column and want to create statistics using CREATE STATISTICS for some of the data in the column. What should I do? Answer: Nothing, you cannot create statistics on an XML column manually. Explanation: An XML column is not valid for CREATE STATISTICS and there is no CREATE XML STATISTICS command. Ref: CREATE STATISTICS - [ [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 2019 - Administration [Extend Event with sqlserver.login]( - My knowledge of Extended Events is limited, but I am trying to capture some login details with extended events and can see actions for: sqlserver.client_app_name sqlserver.client_hostname sqlserver.database_name sqlserver.username etc I would also like to find out the client interface (.Net SqlClient Data Provider, ODBC, OLEDB etc), which is client_interface_name in sys.dm_exec_sessions. Does anyone know if […] [ghost backups processed 0 pages]( - I've got something I'm not aware of performing backups of my dbs at random times in the day. I think it is performing backups however it says 0 pages processed. Regardless, anyone have any idea oh how to trouble shoot this? [The OS returned the error '(null)' while attempting 'DeleteFile' filestream.hdr]( - I have a SQL Server 2019 Enterprise Edition on CU 25. It has in-memory oltp enabled. I have a full and a diff backup I'm trying to restore under a new database name, followed by some log files. The full restores fine but then the diff throws this error and I can't find anything about […] SQL Server 2019 - Development [plugging a repo version number into the tabular model]( - hi we run 2019 std. we saw this week that someone hid 3 important columns (2 are calc'd measures) from our sales cube. i'm in the process of setting up a repo for our bim going forward. i'm not the only one that can deploy and process. its totally possible that these were hidden before […] [Stored Procedures - passed string than variable length]( - Dear all, I was just wondering: Can you change the default behaviour as illustrated below? The user passes a longer string than it was defined in the proc parameter and may not get a feedback, that the string was not processed as he/she wanted. (e.g. when inserting the value into a table) create procedure prp […] [Multi row tally query with max value]( - Hello folks, I would be very grateful if someone could assist me with this problem. I have been having an issue getting this query to display correctly. This is basically some geological drillhole data being captured by depth intervals. A user request came in for having this displayed in single 1 meter intervals. A tally […] [timeout in vs for ssas tabular deploy is 0 but i still timeout deploying]( - hi we run 2019 std but i'm testing this on 2016.  following some old notes and starting from the debug tab in vs, i picked options, went to bi developer and set the properties you see below. but i still get a timeout on each line of the process when deploying as shown in the […] [Table variable declared within cursor persists across loop iterations]( - If you run this code it creates a 2 row cursor loop. Within the cursor the temp table @spec is declared and inserted into (as the output of a select). What was unexpected for me is in the second iteration of the loop the temp table contains 2 rows. The "solution" to this issue I'm […] SQL Azure - Administration [Looking for a Recommended Azure SQL Managed Instance Book]( - Can anyone recommend an Azure SQL Managed Instance book? I'm looking for information about "care and feeding," best practices, etc. SQL Server 2005 Integration Services [Remove comma inside Comma Delimited File csv in SSIS Using Script task]( - Hi, I have two tables: one for headers with 9 fields and another for lines with 6 fields. Both header and lines are highlighted in yellow. I need to combine both the headers and lines using a UNION operation to display the result below. From the line table, I need to add 3 empty fields […] SQL Server 2022 - Administration [AG listener cant be removed]( - Testing with AG on Linux with Cluster=NONE. it was all going ok and as expected but now I have a very strange situation after a failover - AG listener is "null" - so I can not drop it and in the same time I cant add a new one cause one already exist. Anyone experienced […] [Page could not be moved]( - Hello Everybody, I have a database on which I try to empty a file and get rid of it. But I get this error: Cannot move all contents of file "databasename" to other places to complete the emptyfile operation. DBCC SHRINKFILE: Page 9:7748583 could not be moved because the partition to which it belonged was […] SQL Server 2022 - Development [Inserting 100K rows Performance - Baseline Performance]( - We're trying to understand how quick new versions of SQL server can be. Obviously server specs come into play. Can anyone run this simple test and share the output (ms). Create simple Table CREATE TABLE [dbo].[Data]( [Id] [int] IDENTITY(1,1) NOT NULL, [Comment] [varchar](50) NOT NULL, [CreateDate] [datetime] NOT NULL,  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED ( […] [SQL-CTE reqursive query]( - I have table TicketNumbers i    TicketNumber UID 2   10                       09901a22c7c3acc6786847c775f1d113 6   5                         00dad28bef21f916240d6e8c1c1bd67d 12 20             00dad28bef21f916240d6e8c1c1bd67d I need to produced 35 rows (UID also must be present in result) for the same rules: 10 sequence number started from 1 (row i=2) than next 5 […] [Multiple processes accessing the same table. some to write others to read / Lock]( - Dears, Hope this message finds you well We have a log table which is used at same time by some processed to read and others to write. This is causing deadlock What can we do to secure that we end up with deadlocks? Shall we shift the isolation level on the read queries or something […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -