Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge
Editorial
 

Databases made for Ops, not 'Oops!'

Databases must be designed and developed to be easy to maintain. From the start, a database should be simple to check, monitor and study. Monitoring systems can only assist; it is up to the database developer to make things easy for those who are subsequently given the task of keeping it running.

I wish I could smile in some saintly way, and tell you that I've always written my databases, or other systems, in such a way that anyone could check it and understand what was going on. Twice, in my professional career, I've been caught out.

"Where do you work? Ah, The Imperial Bank! I once worked there. How did you like it?"

"Well, it is fine most of the time…but evidently we once had a wild man developer who wrote an essential database. It uses all sorts of unusual performance tricks and unconventional techniques. Nobody who worked on it subsequently ever really knew how it worked and so it was a nightmare to make changes in line with changes in the business. What is worse, if it ever hits a problem, which is mercifully rare, nobody can easily profile it or even detect what is slowing it down. Some people left the company rather than be assigned to work on it."

"How terrible. It must have been some time after I left because I don't remember it." (Gulp!)

I had a similar experience, mercifully not with a database, after I wrote a system that automatically updated thousands of PCs within a corporate network. It lasted at least two decades purely because everyone was too frightened to replace it. I've been a poor team-player at times.

I hesitate to try to list all the requirements for monitoring and maintaining databases, for such an august and learned readership as Database Weekly, but I'll try to explain what I mean.

In the same way that the unit test must precede the creation of a database 'programmable object' such as a stored procedure, so must devising the means to monitor that object, and ensure it provides the necessary evidence to reproduce and fix any problems. You must, in a sense, plan its visibility. The same goes for processes, and the entire database. The alerting system, extended events and scheduled tasks provide the means for an excellent start. They are there for a good reason. You can, of course, also use a third-party monitoring system, and these will help by providing supervision, and general metrics, create graphs and establish baselines so that you can see easily the symptoms of a problem. However, only the database designer and developer can provide the means to baseline, monitor and log the specific processes that have been created for that particular database system. The database itself, dedicated to storing data, provides a uniquely easy way of recording the monitoring and logging data for these processes.

The closer working relationship between Dev and Ops has made it much easier to understand what makes Ops people smile and hum while they maintain production database systems. It pays to discuss, and then create, a written and agreed 'monitoring standard' that its appropriate for your organisation. It is surprising how often this turns up ideas that help development as well and leave everyone wondering why the blazes they've never been done before.

Phil Factor

Join the debate, and respond to the editorial on the forums

 
Redgate SQL Prompt
The Weekly News
All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit.
Analysis Services (SSAS)

Defining Key Performance Indicators in a SQL Server Analysis Service Cube

KPIs are a handy option in the data warehouse because they can be used across various dimensions and hierarchies.

Monitoring and Auditing your Azure Analysis Services

Kasper de Jonge shows how you can see who connected to a AS Azure database and what queries were sent.

Availability Group (AG)

The Curious Case of… very long failover times with a large ad hoc workload

In this case, the cause of the long failover time was enabling Query Store after upgrading, for a database with a large workload of ad hoc queries, meaning there was 100GB of Query Store data to load.

Azure SQL Data Warehouse (ASDW)

What You Need to Know About Data Classifications in Azure SQL Data Warehouse

Data classifications in Azure SQL DW entered public preview in March 2019. They allow you to label columns in your data warehouse with their information type and sensitivity level.

Azure SQL Managed Instance

Take a COPY_ONLY backup of TDE protected database on Azure SQL Managed Instance

The recommended way to backup and restore databases in Managed Instance is using built-in automatic backups and cross-instance point-in-time restore. However, if you need to use the manual backups, Jovan Popvic explains the procedure that you would need to follow.

Data Science

Connect Python to SQL

How to connect Python to Microsoft T-SQL, Postgres, MySQL and SQLLite

Database Administration

Execute SQL Scripts via SQLCMD on Multiple SQL Servers and Save Output as CSV Files

As a part of SQL Server production support and day to day tasks we usually come across situations where we get requests to run a T-SQL script against multiple servers. Sometimes the request is to not only to run the script, but save the output too

5 Questions to Ask When You Upgrade SQL Server

Whether you’re looking to go cutting-edge and hop on SQL Server 2019 as soon as it’s available, or whether you’re just trying to evacuate from SQL Server 2008 to anything that’s going to be supported after this summer, you’re probably doing upgrade projects. This is a good time to ask yourself 5 questions...

Things SQL Server Setup Doesn’t Do – And Should

Brent Ozar on questions that SQL Server setup wizard should ask you, by default, to help you protect the data

Database Builds

Static Data and Database Builds

Often, we can't build a fully functional SQL Server database just from the DDL code. Most databases also require what is often referred to as 'static', or 'reference' data, which will include such things as error messages, names of geographical locations, currency names or tax information. What's the best way to handle such data?

Performance Monitoring

SQL Server memory node memory footprints

Querying perfmon data to track memory usage patterns on NUMA nodes

Extended Events Max Dispatch Latency

The max_dispatch_latency property is the maximum duration that an event, once captured, would reside in the buffer before written to the target. The default for this property is 30 seconds, which is fine in practice but for live demos you can consider reducing it (minimum is 1 second).

Power BI

Moving a Power BI Data Model to Tabular

Ginger Grant shares a few tricks to make it easier to upgrade from Power BI Data Model to Tabular.

PowerPivot/PowerView/PowerQuery/PowerBI

Power Query Performance And Expanding Columns

A critique of a first attempt to write an M query to convert a folder full of images to text that caused PowerQuery to perform multiple file reads

Introducing the Power BI Performance Analyzer

This article describes how to use and interpret the information provided by the Power BI Performance Analyzer, finding the bottlenecks in slow reports.

R Script Visual in Power BI

Dave Mason takes us through his journey in learning how to visualize data with R, in Power BI desktop.

PowerShell

PowerShell 7 Road Map

A preview of areas of development for the PowerShell 7 release.

Scripting out SQL Server Data as Insert statements via PowerShell

As a companion script with his recent article abou...

Query performance

Index Key Column Order And Locking

Sometimes, simply switching the order of columns in an index means SQL Server takes more fine-grained locks, for a shorter period of time, causing less blocking.

Deadlock Graph Frustrations

No matter what you use to collect or display them, deadlock graphs can return some confusing information.

User-defined scalar functions suck – even when they don’t access data.

Even if your scalar function doesn’t touch tables, it still cripples performance by forcing serial processing, blowing up your CPUs, and obfuscating your query plans. Scalar user-defined functions: not even once.

Are Self Joins Ever Better Than Key Lookups?

Erik Darling explains a trick that has a specific use case, but can be quite effective when you spot it.

Minimal Logging with INSERT…SELECT and Fast Load Context

This post provides new information about the preconditions for minimally logged bulk load when using INSERT...SELECT into indexed tables.

What Parameter Sniffing Looks Like In The Plan Cache

If someone sends you a cached plan that’s slow, how can you tell if it’s because of parameter sniffing?

SQL Server Plan Cache Limits

Erin Stellato explains the limits on both plan cache size, and number of entries. If you’re seeing plan cache bloat due to an ad hoc workload, take note of the space consumed by your plan cache and consider enabling the optimize for ad hoc setting.

12 Ways To Rewrite SQL Queries for Better Performance

Bert Wagner summarizes 12 techniques he's learned over time, from trial and error, for rewriting queries to improve performance.

Index fragmentation, part 2

Does index fragmentation hurt query performance? Possibly. It depends on your workload. Tibor Karaszi explains.

Reporting Services (SSRS)

Reporting Services Basics: Creating Your First Report

In this article, Kathi Kellenberger demonstrates how to create a project and report using the SSRS Report Wizard. The wizard is fine for getting started, but you’ll soon learn about its many limitations. She also explains how reports fit in projects and solutions.

SQL Server 2019

Java & SQL Server 2019 Extensibility Framework: The Sequel

The SQL Server 2019 CTP 2.5 release introduces a new Java implementation; your Java code now needs to inherit an abstract base class from the Java Language Extension SDK.

SQL Server on Linux

Linux Scripting, Part III

How to build out Azure resources during deployments, using a few handy Azure commands for BASH.

SQL Server security

Introduction to SQL Server Security — Part 6

SQL Server has many tools used to make it secure. In this article, Robert Sheldon demonstrates several of these features including SQL Server Configuration Manager and the Vulnerability Assessment tool.

DBAs: Stop Denying Sysadmin to Developers

Kendra Little explains why DBAs don't allow sysadmin permissions in development, and why, it at all possible, they should.

T-SQL

SQL Server Cursor Example

In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors?

DEADLOCK_PRIORITY

When it detects a deadlock, SQL Server will, by default, pick the process the has the lowest cost to rollback. This can present a potential issue if the process is a business critical process. The DEADLOCK_PRIORITY allows us to have a bit of control over whether or not a process is chosen as the victim.

Diagnosing Why SYSUTCDATETIME is Faster than SYSDATETIME

On my machine, code that calls SYSDATETIME() 10 million times in a loop takes about 11.6 seconds to execute, while with SYSUTCDATETIME() it takes 4.3 seconds. Why is SYSUTCDATETIME() so much faster than SYSDATETIME()?

Unit Testing

Unit Testing, Databases, and You

As database changes are added to the DevOps pipeline, unit testing of those changes becomes crucial. In this article, Adam Hafner describes unit testing and how it applies to databases.

Virtualization, containers, and k8s

Should You Use Docker Containers on Windows?

Some recommendations for determining when it makes sense to run Docker Containers on Windows--and when it doesn't.

Containers: More on Volumes

Grant Fritchey plays around with container volumes, showing how to move a backup file into a container by just copying it to the volume, and then running a restore from that backup.

Containers: Create a Custom Container

Grant Fritchey creates a container with a database & some data and a couple of general customization, then creates from it a new custom image.

Webinars

Data privacy next steps: Compliant Database DevOps

Wednesday June 12, 16.00-17.00 / 10.00-11.00 Central - Find out how Compliant Database DevOps enables agility while safeguarding your organisation in the event of a data breach or regulatory audit.

Standardize Database Development Across your Organization in 4 Key Steps

Monday June 17 16.00-17.00 / 10.00-11.00 Central - Discover how adopting 4 steps will lay your foundations for automation and Compliant Database DevOps.

Standardize Database Development Across your Organization in 4 Key Steps

Monday June 17 16.00-17.00 / 10.00-11.00 Central - Discover how adopting 4 steps will lay your foundations for automation and Compliant Database DevOps.

Data privacy next steps: Compliant Database DevOps

Wednesday June 12, 16.00-17.00 / 10.00-11.00 Central - Find out how Compliant Database DevOps enables agility while safeguarding your organisation in the event of a data breach or regulatory audit.

provisioning

Bloor scores SQL Provision 4.5 out of 5 for test data provisioning

In this independent review of SQL Provision, the Bloor analyst finds that SQL Provision’s combination of database cloning and data masking stands out with “clear advantages over competing approaches, such as data subsetting or synthetic data generation".

Bloor 2019 Market Update on Test Data Management

Discover the latest market trends and assess your approach to test data management. For example, the Bloor analyst recognizes “…an increased emphasis on test data provisioning, as opposed to merely test data management.” The report also covers key capabilities offered by vendors in the market, including Redgate.

sql server monitoring

Do You Know What Secrets Your SQL Servers Are Sharing?

Here's how to check the current state of connections to the production SQL Server, what to look for and why.

 
RSS FeedTwitter
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. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -