SQLServerCentral.com Community Update for January 24, 2022

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

A Key-value Cloud Database

The Voice of the DBA

Daily Coping Tip

Be gentle with yourself when you make mistakes

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Knowing When to Respond

I ran into this quote on the Microsoft Learn site, which I thought was a great way to think about how to administer a system: "Without a baseline, every issue encountered could be considered normal and therefore not require any additional intervention."

When I've had users file tickets or complain about things not working well, I've found more often than not their perception has changed more than the actual performance. I've been called for "slow applications" only to find out that "slow" was 30 seconds and the complainer wasn't sure how long it used to take, but today being end of the quarter, it is slow. Digging into monitoring history has shown that the query always took at least 20s and could take over 30s. My main takeaway was a little stress for users sometimes culminates in unnecessary work for operations staff.

There certainly are times when a database query takes longer than expected but is it because the system is overloaded or there's a lot more data? When was the last time this ran and what changed? Are there more queries against the same objects than in the past? Even when there are real problems, without knowing how a system typically looks at this time, we may struggle to quickly determine where the problem lies. We may not even know how to craft a good solution without some baseline.

Maybe the best reason for me to know a baseline is for triaging and prioritizing issues. Seeing a server at 100% CPU is one thing, but if this is a daily occurrence, I might decide another issue is more important. Especially at 2 am.

Having a baseline for your systems is important. Build a system if you must, buy one if you can, but get monitoring set up for your systems. It will help you focus development efforts when changed code doesn't work as expected. It also helps your operations staff to help them respond more efficiently to future issues.

Steve Jones - SSC Editor

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

Featured Contents

Working with Fail Activity in Azure Data Factory

arindamxs from SQLServerCentral

Introduction In this article, we will talk about a newly introduced activity in Azure Data Factory, which is the fail activity. The fail activity is very useful to stop or fail the pipeline at the specified point. For example, a lookup activity might return no data or a switch activity executes the default block. We […]

Exploring the Flyway Schema History Table

Additional Articles from Redgate

Flyway uses a schema history table to track the version of each database, recording in it every versioned migration file applied to build that version. It's worth understanding exactly how Flyway uses this table, the possible dangers of moving it to a non-default location and how to do it safely, if required.

How to replace an identity column with a sequence number

Additional Articles from SimpleTalk

SQL Server provides two ways to include an incrementing number in a table. Greg Larsen explains how to replace an identity column with a sequence number.

From the SQL Server Central Blogs - Why Is “WHERE 1=0” Slow?

Grant Fritchey from The Scary DBA

I saw a question the other day, questioning why they’re creation of temporary tables was so slow. What they were doing was (a much more complicated version of) this:...

From the SQL Server Central Blogs - Bulk Insert – Insights That Will Make You Drool

SQLRNNR from SQL RNNR

Bulk insert can be an extremely helpful tool to help ingest data into SQL Server efficiently. Sometimes, it is necessary to capture metrics in regards to the bulk insert...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	A Key-value Cloud Database
Which Amazon product provides a key-value NoSQL database?
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)

Cosmos DB Request Units

How is a Cosmos DB Request Unit (RU) defined?

Answer: The cost to read a 1KB item when the ID and partition key are known

Explanation: An RU is the resource required to read a 1kb document when the partition key and ID are known. Ref: Request Units in Azure Cosmos DB - https://docs.microsoft.com/en-us/azure/cosmos-db/request-units

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

How to reclaim space from a table - Hi All I have 1 TB database and i cant shrink it as it shows zero % free space .. when I checked into the report of disk usage by table .. there is 1 table which has unused space 700 GB . This table has no Clustered index and only has non clustered index […]

dirty select and snapshot isolation - Hello everyone Can anyone explain the difference to me please btewwen ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON and ALTER DATABASE [test] SET [READ_UNCOMMITTED] ON GO because in both cases my select works well even if my update is still running thanks

SQL Server 2017 - Development

Need help to calculate Month in integer into Year and Month - Hi, I've this, declare @t1 table ( Id int primary key, HowManyMonth int); insert into @t1(Id, HowManyMonth) values(1, 17); insert into @t1(Id, HowManyMonth) values(2, 37); How to make it output like this? Id | PeriodDisplay ------------------------ 1 | 1 Year(s), 5 Month(s) 2 | 3 Year(s), 1 Month(s) Please help

SQL Server 2016 - Administration

Extended Events - how can I query the location of File Target of an XE Session? - If I know the XE Session name, is there a query to find out what file path is defined for File Target of the session?

SQL Server 2016 - Development and T-SQL

Linked Server UPDATE & DELETE fails . But INSERT & SELECTs work fine - Hello, I have a Windows 2019 machine. I have SQL Server 2016 and the v18.1 version of SQL Server Management Studio. I have created a linked server & I am trying to execute some queries on a remote Db2 server. I am using MSDASQL provider, i.e. 'Microsoft OLE DB Provider for ODBC Drivers'. The INSERT […]

Knowing ERD for Joins and Subquerries - Hi Experts, I am learning and when I want to practice Subqueries and Joins from the database say Adventureworks or T-SQL Information Schema, I am not able to know how to join and how to create an ERD. My challenge in any system is that I need to know the data model before I practice […]

SQL Server 2019 - Administration

Good t-sql script possibly to list partitions and used/free space on Linux - I have quite a few scripts to monitor space on Windows server, but I couldn't find a good t-sql script that can actually list out partition names and space details. Does anyone have one?

SSIS Error when trying to run a SQL Server Agent Job - SQL Server Integration Services was already installed on the target server when I backed up the SSISDB from the source server(ss2016) and restored it to the Target Server(ss2019). I then ran the related sql server agent job that executes one of these packages and I got the following error below. The only recommendation that I […]

SQL Server 2019 - Development

SSIS SFTP - Hi everyone I am trying to create an SSIS package that will access an SFTP site. SSIS has an FTP task but no SFTP. I read online that SSIS does not support SFTP directly. Has anyone ever set up SFTP in SSIS? If yes how did you do it? I am new to SSIS so […]

tricky query with patterns of consecutive rows - Hi! I'm having struggle with selecting rows, that match "dynamic" rules as described: if there are 10 or more rows containing a value greater than 1000 this is my START signal if there are 10 or more rows containing a value smaller than 1000 this is my STOP signal i want everything between a START […]

Power Query SSIS Error - Hi I am using SQL Server 2019 and Visual Studio 2019. There are two components - Power Query Source and OLE DB Database. I went to the connection manager and tested each connection. The system was able to connect successfully for both components. These "look" ok. I am getting below error when I try to […]

General Cloud Computing Questions

Are all learning resources just rote memorization? - Title says it all, recently got frustrated prepping for GCP's ACE with Qwiklabs because that's all it felt I was doing, just following steps, and for example altho in their Coursera courses they do explain some of the background as to why some of the components exist, I find its still missing why they need […]

General

How to practice OOP in C++ effectively? - I am currently writing a desktop application with Qt and C++. While I have learned some things about OOP from working on this project, I do not feel I am really using the full potential of OOP. I am not creating a lot of my own classes. I generally don't have an idea of what […]

ASP.net C# - Dropdown List - I am working to get a drop down to work in a DetailsView with the intent to show the current value from a dataset, along with other additional values so that users can edit the data. In this example, this is a for State drop down. If my current state is DC, than I want […]

This email has been sent to newsletter@newslettercollector.com. 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.