SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Trigger Ordering I

The Voice of the DBA

A Lack of Architecture and Planning

A few weeks ago, I was sitting in the audience, waiting for my turn to speak at DevOps Days in Minneapolis. Just before me, Xe Iaso delivered a funny and thought-provoking talk on building a social network on a whiteboard. It was very well done and had me feeling nervous about following that session.

The talk is a bit of a satirical look at an interview Xe had for a company that tried to get them to derive an architecture for a large distributed system. It was interesting to hear Xe note that often we have architecture diagrams of what we'd like to have, but never an explanation of how we implement a large system, especially one that has to grow as our workload grows.

This talk was a nice analogy of how often we get into situations where many of us can't believe our system was structured this way. We often wish we could completely redesign things from scratch, and we'd do it better. Why didn't the previous engineers think things through?

Watch the talk. It shows how a lot of software is built. We build what we think we need, but when we get overwhelmed, or often when we get unexpected pressures from others, we make decisions that seem to be the best ones in the moment. However, a few years later, with a few of these decisions behind us, we realize that each of those choices was too short-sighted. We have a mess of software the seems cobbled together rather than well-engineered.

I don't believe we can engineer everything well from the beginning. I also don't believe in early optimization, mostly because I think we are asked to build a lot of things that are never (or lightly) used. Who uses the paintbrush in Word or the FACTDOUBLE() function in Excel? However, I do believe that we ought to write code that performs well the first time, following patterns we (should) know well. We certainly should assume that whatever code we write will see at least 10X more data in production than in dev/test, so prepare for processing more data.

I'm sure many of you know of a codebase and system that doesn't run well. I hope most of you have another application that does run well and is fun to work on. Hopefully, the goal of your organization is to turn out more software like the latter than the former.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Data Prediction with SQL Server Machine Learning Services, Python, and Linear Regression

Mamta Dalal from SQLServerCentral

Lean how to implement linear regression in SQL Server by running Python code on your data in SQL Server using Machine Learning Services.

Level up your learning in 2024 with PASS Data Community Summit

Additional Articles from PASS

Connect, share and learn with the global data community in Seattle this November. Whether you're a DBA, Developer, Architect, Engineer or you're just getting started in your data career, there's a huge range of content for you to choose from (over 150 sessions and counting across 5 different tracks). Speakers this year include Steve Jones, Grant Fritchey, Ryan Booz, Bob Ward, Deepthi Goguri and Jennifer Stirrup. Check out the full program and register before September 18 to secure your ticket at the standard price.

Reduce SQL Server Blocking with READ_COMMITTED_SNAPSHOT

Additional Articles from MSSQLTips.com

This article explores how enabling READ_COMMITTED_SNAPSHOT on your SQL Server database might ease excessive blocking.

From the SQL Server Central Blogs - Service Broker Blocking

Zikato from StraightforwardSQL

This post is dedicated to all 10 other DBAs that use Service Broker (you know who you are).
The main reason for this blog post is that I’ve got no...

From the SQL Server Central Blogs - Monday Monitor Tips- Enabling Index Tracking in Redgate Monitor

Steve Jones - SSC Editor from The Voice of the DBA

There is a new index feature in Redgate Monitor, but it’s disabled by default. This post shows how to enable things. This is part of a series of posts...

The Unicorn Project

Site Owners from SQLServerCentral

In The Unicorn Project, we follow Maxine, a senior lead developer and architect, as she is exiled to the Phoenix Project, to the horror of her friends and colleagues, as punishment for contributing to a payroll outage. She tries to survive in what feels like a heartless and uncaring bureaucracy and to work within a system where no one can get anything done without endless committees, paperwork, and approvals.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Trigger Ordering I
I have multiple triggers on a table in SQL Server 2022. If I want to use sp_settriggerorder, what are my options for the @order parameter?
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)

Adding and Updating

I run this code, but get an error:

USE AdventureWorks
GO
ALTER TABLE Sales.CreditCard ADD defaultcard BIT

UPDATE sales.CreditCard SET defaultcard = 0

What's is the error?

Answer: Invalid column defaultcard

Explanation: The error is that the new column is in the same batch as an update, and it hasn't committed, so the update cannot run. This code, will run:

USE AdventureWorks
GO
ALTER TABLE Sales.CreditCard ADD defaultcard BIT

UPDATE sales.CreditCard SET ExpYear = 2025

Ref: No real reference here.

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

Searching for the Assumed Full Backup - I have this maintenance plan with differential backup and maintenance clean-up task in it. The plan runs once daily, and the clean-up task is deleting backup files older than 1 week. There is no other full backup plan or subplan there in SSMS. I ran the following script day before yesterday, yesterday and today to […]

SQL Server 2016 - Development and T-SQL

Can you pass an undeclared variable to a Stored Procedure - If I have a SP that calls another/different SP can I pass to that second SP a variable that has not been declared within the 1st SP and which was not passed to the 1st SP like the below? I am walking through some code in a SP and found that it had a variable […]

SQL Server 2019 - Administration

Windows 11 & sudden SSMS sorting nuisance - When I was still in Windows 10, I'd open up SSMS (version 18) and go to Object Explorer Details, where I would see the folders System Databases and Database Snapshots up at top and all the individual database names listed in alphabetically order (unless I sorted otherwise). Now that I've upgraded to Windows 11, something […]

HADR SYNC COMMIT / deadlock rebuild index - Hi, I have some interesting issue. Server1 2024-08-09 02:59:25- start session 234 with UPDATE STATISTICS on Table1 WA_sys_xxxx stat 2024-08-09 03:00:08 - start session 800 - with ALTER INDEX on Table1 with Index1 2024-08-09 03:00:25 - deadlock session 800 like victim and session 527, which is application with some MERGE 2024-08-09 03:04:22 - first record […]

Ping a sqlinstance - How do I (ping/check if up and running) a sqlinstance from t-sql ?

SQL Server 2019 - Development

can my phone run ssms inside our firewall? - Hi my phone is now upgraded to allow teams , outlook etc using my work account. Its a portal more or less. Is there a way to leverage my new portal to run ssms from my phone so i can check on the status of a couple of jobs rather than carrying my pc around […]

what have people done for ssrs params needing very precise start and end times - Hi we run 2019 standard. one of our mfg locations requires somewhat precise start and end param times along with the start and end dates controlling how an ssrs report is filtered. our main user doesnt like the idea of them typing hh.mm.ss.nnn next to the date showing when they pick a date from the […]

Integration Services

Destination Table - Condition Amount 0 - I need your assistance with an ETL process that runs every six months. Currently, we are in Calendar Year/FY 2025, which started in July 2024. The issue is with the "Condition Amount" for FY 2025. Data comes through correctly until the "Insert Data Into Task" step (please see the attached screenshot). However, it appears that […]

Design Ideas and Questions

Why is it a good idea to not restore a production database to test? - (I looked over all the forums here on SSC and this one seemed the best for my question. If you think I'm wrong, I'm sorry I posted my question here.) For years I've read here and elsewhere that it is not a good idea to restore a production database to its equivalent test database. I've […]

SQL Server 2022 - Administration

Collation Change - Any recommendations on changing the collation if DB ? There is dependent objects so simple alter won't work. Also DB to big to generate script with schema and dats

In primary alwayson replica index maintenance plan need tobe prepare fordowntime - Dear all, I have a 3.5 tb primary always on database with 2 secondary replica one is sync and dr is async In primary always on replica index maintenance plan need to be prepare for downtime can any one mention the order for the plan and its steps to follow Do we need to suspend […]

OLA's Maintenance scripts - should I be reorganizing? - Hi These are my current params for nightly maintenance: USE MASTER EXECUTE dbo.IndexOptimize @Databases = 'MY USER DB', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE', @FragmentationLevel1 = 25, @FragmentationLevel2 = 50, @SortInTempdb = 'Y', @MaxDOP = 2 I have removed 'REORGANIZE' as per Jeff's thread here: https://www.sqlservercentral.com/forums/topic/review-ola-hallengren-indexoptimize-parameters Am I correct in removing the […]

SQL Server 2022 - Development

Query timeout question - I am debugging (sort of) a problematic query, that gives a timeout error (30 seconds) in our company application. I've captured the query + parameters using Extended Events and when I run the exact same query with the same parameters in SSMS, it takes about 1 second. Anyone got any ideas what's happening here?

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.