SQL Server and Python Tutorial (2023-11-06)

SQLServerCentral Newsletter for November 6, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server and Python Tutorial]( - [Don’t use DISTINCT as a “join-fixer”]( - [Five database DevOps practices for boosting team productivity]( - [From the SQL Server Central Blogs - Common Mistakes in SQL Server – Part 6]( - [From the SQL Server Central Blogs - Setup Ola Index Maintenance with Azure Runbooks and Terraform]( - [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Question of the Day - [The Temporary Synonym]( The Voice of the DBA  Don't (Always) Be a Hero I saw [a comic from Kendra Little]( recently, which reminded me of my first SQL Server crisis. I got hired as a contractor to support a large Novell network. In my first chance to work with SQL Server, a new database server running SQL Server 4.2 on OS/2 1.2 was installed on our network to support a new data entry application. This was mandated by a government legal change on Jan 1. As the junior person, I was supporting the installation by corporate developers on Dec 31 at 5 p.m. We finished, ate dinner, and then I watched them do some smoke testing and prepping the application and database servers for go-live at midnight. It was exciting to me as a young professional. At around 12:30 am, as I was getting ready to leave, the system locked up. We found the database server unresponsive, so we rebooted it. This continued to happen, with my eventually paging my boss and having him come in the early morning. We worked through the night and into the next day. I didn't leave until early morning on Jan 2nd, with the need to return that night. We had an overloaded, unstable system that required us to work long hours babysitting the server. It was worse for our clients, who had to manually record data and then try to perform data entry during slow times to catch up the system. I was a hero that January, as were my co-workers, with all of us logging around 100 hours each week that month. It was a rough time, and I learned a lot about SQL Server, OS/2, and eventually, Windows NT. I also learned about poor software testing, especially load evaluation. I learned how easy it was to overwork yourself, and how heroics are needed but can't be business as usual. Being a regular hero isn't something that is conducive to being effective or efficient with your staff or your business. I saw this later when I came to Denver and a poorly designed system that needed patches or reboots every week overloaded me and my staff. Much of what we do as developers or operational staff is to support others. We provide them with systems to get their work done, whatever that work might be. We work hard, and we find solutions to challenges, but we ought to not only provide stability to customers, we need to do that over time. That means that we need to have a staff that we can count on over time. If we overload them or struggle to retain them because of burnout, we can't easily do that. We also can't get new, perhaps more important work done if we are constantly dealing with the same issues. I've seen management do this, and they end up with staff that isn't efficient, can't tackle new work, or turn over constantly. Some of you might know of a company like this in your area where they are always hiring because they aren't a place anyone wants to work. Someone will work there because they need a job, but that company is never very efficient. Eventually, they'll struggle with their competition and lose business. Or maybe not, maybe they'll linger on with lots of missed opportunities. I've also seen IT professionals live like this and thrive on emergency situations. If you've read [The Phoenix Project]( you're familiar with Brent, who is the go-to person for anything. I've been that person, and I've seen that person in different organizations. While I always appreciate them, I'm often frustrated because they become too busy to handle my requests and there is a constant stream of requests because things aren't being fixed or solved. It's never a good situation. We need to be heroes at times, but those ought to be emergencies, and they ought to be rare. Most IT professionals (and others) I know will work longer or harder when needed, but the need can't be constant or even regular. It should be an unusual situation. If it's not, then something has to change, at least for me. Either the technology or the staff. As I gained experience and savvy, this has usually meant I go find another job because the situation has become "emergencies are status quo." Life's too short to live like that. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Server and Python Tutorial]( Daniel Calbimonte from SQLServerCentral This article will show the basic outline for how Python scripts can access and work with data in SQL Server. [External Article]( [Don’t use DISTINCT as a “join-fixer”]( Additional Articles from SimpleTalk I’ve quietly resolved performance issues by re-writing slow queries to avoid DISTINCT. Often, the DISTINCT is there only to serve as a “join-fixer,” and I can explain what that means using an example. [External Article]( [Five database DevOps practices for boosting team productivity]( Additional Articles from Redgate Speed of delivery and protecting data can often feel incompatible, but there are industry-proven database DevOps practices that bring them together in harmony. Across each of these five key practices, there’s a theme of removing barriers and cognitive load for teams; but crucially, they are also putting safeguards in place to reduce the risks to production environments. [Blog Post]( From the SQL Server Central Blogs - [Common Mistakes in SQL Server – Part 6]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP In SQL Server, there are various scenarios in which you may want to consider recompiling a query or a stored procedure to ensure optimal query performance. Recompilation generates a... [Blog Post]( From the SQL Server Central Blogs - [Setup Ola Index Maintenance with Azure Runbooks and Terraform]( hellosqlkitty from SQLKitty Yes, you still need to do some work to maintain indexes in Azure SQL Database. This post will walk you through setting up statistic updates and index maintenance using... [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Site Owners from SQLServerCentral Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You'll learn to identify precisely why your queries are running slowly. And you'll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Temporary Synonym What happens when I run this code? CREATE SYNONYM TempSyn FOR tempdb.#mytable 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) The EXCEPT Query I have a couple tables that are similar, but the data is different. CREATE TABLE Original ( productID INT , Productname VARCHAR(20)); CREATE TABLE NewVersion ( productID INT , Productname VARCHAR(20)); GO INSERT dbo.Original (productID, Productname) VALUES (1, 'PC Case') , (2, 'Motherboard') , (3, 'CPU') , (4, 'RAM') , (5, 'SSD'); INSERT dbo.NewVersion (productID, Productname) VALUES (1, 'PC Case') , (2, 'Motherboard') , (3, 'CPU') , (4, 'DRAM') , (5, 'SD'); How many rows are returned from this query? SELECT * FROM dbo.Original AS o EXCEPT SELECT * FROM dbo.NewVersion AS nv;  Answer: 2 Explanation: This query returns 2 rows. There are two items that are different (ram v dram and ssd v sds). These are returned as separate rows. Ref: EXCEPT - [ [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 2017 - Administration [Execute AS and Impersonate]( - I am trying to write up a stored procedure to allow users to execute jobs in MSDB that are owned by another user. The job-owning user is a service account (set up as non-interactive) has elevated permissions which I do not want the business users to have themselves. The problem is that I can't seem […] [Troubleshooting a SQL Server Wait Resource Issue"]( - I'm encountering some wait resource entries in sysprocesses without specific details. Has anyone faced a similar situation? Any insights on how to decode or address this issue would be greatly appreciated. [help decoding a wait resource from sysprocesses]( - Hi After some help . I've got a bunch of wait resources from sysprocesses that don't include specific resource type i.e its not prefixed with KEY, or TAB etc This type of format 66:1:6666666 So I presume the first numbers are the databaseid, maybe the second number is file id , but I cant decode […] Administration - SQL Server 2014 [How to include newly system generated tables to SQL replication?]( - Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) […] SQL 2012 - General [Can I still get a SQL 2012 eval for my home lab?]( - Just for testing upgrades.  All the links are dead.  I understand it's not in support but like to test the full upgrade properly. SQL Server 2019 - Administration [SSIS - "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.".]( - Hi All, SSIS error: Recently the following error "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring. The SSIS package is executed through SQL Agent using a Domain service account and running as a job for years No changes to SQL Server no changes to the fileserver permissions etc. 1st thing checked is […] [Replica stuck in resolving state during upgrade]( - Hi, Last week we tried to perform an upgrade on a 2 node sqlserver 2012 availability group. OS is windows server 2019. Cluster functional level is set to 9. We set secondary node to manual failover, assync commit, etc.. the secondary node was updated ok. On the primary, still sqlserver 2012, all was also good […] [SQL Service is not starting after upgrade...]( - Seems to be Windows is somehow decided to upgrade something in SQL Server and after that it can't start. SQL Service is starting correctly only with trace flag 902. How to fix the issue? Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151, state 1, severity 16. This is a […] [Can trigger execution be excluded only for a particular login?]( - Hi, I have an ERP application which connects to SQL 2019 database by an user - "vwuser". Whenever the users does transactions via ERP , data population happens. Very few people have backend access, but auditors are asking for tracking the data modifications that happen via Backend (say data corrections via Update / Delete). The […] Reporting Services [Dynamic Subscription query with parameters????]( - Hi,  I'm trying to create a query within a dynamic subscription with a parameter for a Start and End Date. The query will not validate when I try to use @StartDate and @EndDate Something like: WILL VALIDATE --- Select ID from mytable where date between '10/1/23' and '10/31/23' Once I change the date to […] Third Party Products [Is SQL Search 3 still supported?]( - I'm looking through my machine to see what I have installed and what I can remove. I found a product called SQL Search 3, which is by Red-Gate. Is it still supported? Article Discussions by Author [Handling a Cancelled Online Index Rebuild in SQL Server]( - I was wondering what the expected behavior is when an online index rebuild (non-resumable) is cancelled in SQL Server? Are there any potential risks or considerations to keep in mind? Through Mcdvoice homepage, customer or participant can give his honest opinion (positive or negative feedback) to improve product quality, service, store quality & norms. […] SQL Server 2022 - Administration [time for stats to get updated when auto update statistics async = true]( - hi, I'm using the following database version of MS SQL SERVER Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) I was experimenting with the Auto Update Statistics Async = TRUE in my test database and noticed that the time it took for my table column stats to get updated took anywhere from immediate to […] [stats get updated even when query is trivial]( - hi, I'm using the following version of SQL Server Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) In my database, I've got the following options set to TRUE Auto Update Statistics Auto Update Statistics Asynchronously I've got the following script create table testtab (col1 int) DECLARE @count INT; SET @count = 1; WHILE @count<= […] [SQL Server - Kerberos Configuration Manager - Unable to connect]( - am currently working through replacing a development and production SQL server, moving from server 2012 to server 2022. These two servers have the SQL Service running as an AD account. Thus, I have gone through and configured the proper SPNs in Active Directory and configured Kerberos Constrained Delegation. Adding the SPN for the FQDN and […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -