The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

One thing I learned while working as a database administrator over 17 years is the importance of teamwork across departments. Many times, we have to rely on network and SAN administrator to make changes to their environments in order to make SQL Server run more efficiently. There are times where the storage and network create bottlenecks for SQL server and after doing all the possible tuning you can do with your code and configuration you must turn to them for modifications to their hardware. Knowing how to effectively work with other departments without placing blame is crucial.

As most do, we take our work personally. After all it’s your hard work, decisions and efforts that make the environment run as it does. Telling network admins and SAN admins their environment is running poorly or misconfigured can cause a very adverse reaction. At times it can even cause them to refuse to work with you or make an effort to help fix the issue. I often refer to this as, telling them their babies are ugly. You need to learn how to nicely do this without pointing out the flaws in their genetics.

How do we do this?

The most important thing you can do is make friends with the other departments approaching them in a respective and friendly professional manner. Developing a cohesive relationship right off the bat is key, especially in a new job. It’s important to establish that you know their work is important and that you also appreciate their work. I tend to do this with food as a sign of appreciation. I bring in donuts or pizzas to other departments to show my appreciation. It helps to get me on their good side if I am ever in a position where I need to call their babies ugly. Having a friendly relationship with them helps soften the blow. Food always seems to do the trick when making friends.

Next you need access to see their babies, which are their environments, so you can get concrete numbers to show that the network or storage is causing a bottleneck for SQL Server. If your environment is using VMware, ask for read-only access to vSphere so that you can see how it’s configured and monitor your virtual machines. Get to know the environment and do your research so when you have to ask questions you are knowledgeable in exactly what you are questioning. I find being familiar their environments and knowing the proper vocabulary before going into discussions helps aid in conversations.

In addition to that, start gathering solid performance metrics with trends so that you can present proof that those systems are causing SQL Server issues. Numbers don’t lie. By doing this you are presenting a fact base issue and not an opinion. This is very important, as it is not us against them. Many times, charging at someone with allegations, especially without concrete numbers, that their systems or hardware is problematic can be like personal attack.

Take a look at the below performance monitor counters. These are just a few counters will help support your allegations, there are many more out that should also be utilized but I start with these.

Latency\Queue

Memory

CPU

Next you need to show them that you have made efforts on the SQL Server side to tune your environment as much as you can. Keep a running list this of the improvements you’ve made. Have you done anything to reduce the amount of IO SQL is creating, i.e., tuned queries, added indexes, re architected data loads, optimized configurations to fit best practices? By doing this it shows you have made an attempt to do everything possible to rectify the issue on your side.

Lastly, always remember they too may be struggling with a non-optimal environment. They may have their hands tied with old equipment, such as an old spinning disk SAN and no budget for improvements. They may be battling too many VM’s on their hosts because they just can’t buy any more hosts to balance their demand and that’s what could be causing your SQL Server bottlenecks. Sometimes other administrators have no control over how ugly their babies are and have little options for making them prettier. It’s important to keep this in mind.

Getting your SQL Server environment as optimized and running at peak performance is always the goal of the database administrator. To do this often requires teamwork with other departments. Keeping the things in mind that I have mentioned can help make that easier. Remembering to not call their babies ugly or blaming SQL Server problems on them personally is essential to working cohesively to rectify the bottlenecks.

Monica Rathbun from SQLServerCentral.com

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

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. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Webinars

SQL in the City Streamed: Standardizing Database Change Management across your Organization - Date and time: Wednesday April 3, 09:00-14:00 Eastern / 14:00 - 19:00 BST Microsoft MVPs Kendra Little, Grant Fritchey, Steve Jones, and Kathi Kellenberger lead the speaker line-up for this free virtual event. Join online for sessions designed to broaden your skillset, support your ongoing learning, and keep you up-to-date with the industry....(more)

Webinar: Don’t just think DevOps. Think Compliant Database DevOps! - Wednesday March 20, 15.00-16.00 GMT / 10.00-11.00 CDT How can the promise of releasing changes to the database faster and easier be balanced with the need to keep data safe and remain compliant with legislation? Join this webinar to discover how the answer lies in in going one step further than database DevOps and thinking about Compliant Database DevOps. ...(more)

Webinar: The Importance of Monitoring your Azure SQL Database - Wednesday March 6, 15.00-16.00 GMT / 9.00-10.00 CST Monitoring Azure SQL Database is still a necessary part of understanding how your system is behaving and ensures that you have the information needed to make necessary decisions about your databases in a timely and accurate manner....(more)

Vendors/3rd Party Products

Avoid running out of Disk Space ever again using SQL Monitor - It is rare for a database to suddenly run out of space. It is usually a predictable event, if you are aware of the rate of growth of the database, and of the changes in the free space on the storage. Therefore, it’s a good idea to monitor these metrics, and to set up alerts. ...(more)

Monitoring Azure SQL Database with SQL Monitor - Monitoring application activity, alongside the database, is a good example of how development and operations people can share their skills for mutual benefit, to get a better understanding of what is happening with an application....(more)

T-SQL

Does sp_executesql WITH RECOMPILE Actually Recompile Anything? - No, No It Doesn’t. But it’s fun to prove this stuff out. Let’s take this index, and these queries....(more)

Error converting data type varchar to numeric where ISNUMERIC finds only numbers - I am writing some SQL to form the basis of views to pull data from a Microsoft SQL Server source into Azure SQL Database, and then the data would go to Power BI. The data was all presented in string format initially (not my data, not my monkeys, not my circus), and I wanted to correct the data types before the data got into Power BI....(more)

How To, and Not To, Escape a String In TSQL - You are never too old to learn an important lesson. Like most programmers, I read programming language documentation for one of two reasons. 1. You have just found out about something new 2. You find out that you were wrong about something you thought ......(more)

Tools

Using the Wait Statistics Report in Azure Data Studio - Azure Data Studio, which used to be called SQL Operations Studio, has a bunch of extensions available, including one called Server Reports from Microsoft. Last year they took my wait stats query (from here) and made it into a report as part of Server Reports extension. In this quick post I’ll show you how to install that extension and look at the report....(more)

Installing Extensions to Azure Data Studio - If you’re even thinking about experimenting with, let alone actively using, Azure Data Studio, you need to plan on installing a few extensions. Buck Woody has a great list that you should look through in this blog post. If you’re just getting started ......(more)

Released: Public Preview for SQL Server Management Packs 2008-2019 (7.0.13.0) - We are getting ready to update the SQL Server Management Packs. This includes the existing versioned MPs for 2008-2016, as well as the new version agnostic SQL Server MP for 2012 and up (for more info on the version agnostic MP, see here). Please install ......(more)

Tech News

Gartner Identifies Top 10 Data and Analytics Technology Trends for 2019 - According to a recent press release, “Augmented analytics, continuous intelligence and explainable artificial intelligence (AI) are among the top trends in data and analytics technology that have significant disruptive potential over the next three to ......(more)

Google May Release a Video Game Subscription Service, Console in March - The Information reported that Google's "Project Yeti," a game streaming service, will launch at GDC on March 19....(more)

PowerPivot/PowerQuery/PowerBI

Thoughts On The New Power Query Source In SSIS - Yet more evidence that Power Query is taking over the world: there’s a new Power Query data source for SQL Server Integration Services in the latest version of SSDT. Here’s the blog post announcing it (the actual announcement is halfway through): https://blogs.msdn.microsoft.com/ssis/2019/02/15/deeper-integration-and-new-connector-for-ssis-in-adf/ And ......(more)

DAX Studio Parameters Dialog - I recently updated the documentation on daxstudio.org to include a page on the Parameter Support, but I thought this might also benefit ......(more)

Using Power BI To Track My Activities - As a MS MVP one of the things you have to do is keep track of all the “things” you do for community, whether it be volunteering, organizing, speaking, etc. It can be a bit daunting trying to keep track of all of it. But hey, I’m a Data Platform MVP, ......(more)

Power BI Slicers, Filtered export, Magic Quadrant and more… (February 18, 2019) - Thanks for watching this week's Power BI news roundup! Last weeks roundup 2 Minute Tuesday Patrick's tech video Adam's tech video Sources SYNC SLICERS & SYNC SLICERS ADVANCED OPTIONS (@pkamasani) Definitive Guide to Implement On-premises Data Gateway ......(more)

Performance Tuning SQL Server

How to get Live Execution Plan using Extended Events - Have you ever found yourself in a situation where you were supposed to troubleshoot a long running expensive query that was hammering your SQL Server? I bet you wanted to get and analyse the actual runtime execution plan of this query. In ......(more)

Microsoft News

Microsoft’s Developer Blogs are Getting an Update - In the coming days, we’ll be moving our developer blogs to a new platform with a modern, clean design and powerful features that will make it easy for you to discover and share great content. This week, you’ll see the Visual Studio, IoTDev, and Premier ......(more)

HA/DR/Always On/Clustering

“Basic” Availability Groups – Some Rumors Persist - I was having a twitter interaction the other day. Since High Availability and Disaster Recovery topics are on my mind as I just wrapped up delivering an online class on SQL Server HA/DR last week and am preparing for my next free webinar series talking ......(more)

DevOps and Continuous Delivery (CI/CD)

Regression Testing Overview - In a previous post, I touched on the point of testing and briefly touched on regression testing. In this post, I will be going into more detail about what regression testing is and why it’s important to do it....(more)

Three Key Requirements for Continuous Integration in Cloud Environments - In my previous blog post I discussed hybrid cloud computing. The phrase Hybrid Cloud Computing is used to describe the coexistence of multiple environments, at least one of which is a cloud environment. Hybrid ......(more)

You’re ready for DevOps, but is your workplace? - DevOps is gathering pace. More and more organizations are recognizing that by encouraging collaboration, cooperation, and communication, they can release features faster and offer advantages to customers sooner. That’s the promise, but in order to adopt ......(more)

The Conflict Between Data Protection and DevOps - There are demands on your business. Demands to keep ahead of your competition. Demands to be compliant with one or many data protection regulations. And demands to minimise stress on resources like disc space and time. These demands are conflicting and can lead to the database becoming a bottleneck. Steve Jones shows us how to resolve the conflict in this article....(more)

SQL Provision Makes Light Work of Compliance - With SQL Provision it takes seconds to spin up database copies for dev and test, with whatever level of protection you need – masked data, unmasked data, or synthetic data sets. This means you can meet the needs of DevOps while ensuring your data’s safe and your business stays compliant....(more)

Data Privacy, Complianace, and GDPR

Protecting Data During Development - In the years before joining Redgate, I was a SQL Server database consultant. I spent a lot of time doing SQL Server health checks, query tuning, and ETL (extract, transform, and load) development. Every customer was different and getting to the resources I needed to do the job at each shop varied as well......(more)

Career Growth

What is the Best Training for Business Analysts? - The rate of change in the tools available to a Business Analyst has never been faster than it is today. The market is changing so quickly ......(more)

Why and how you should speak at a conference - You may think that speaking at usergroups and conferences is reserved for a select group of elite professionals. Let me tell you right away that this is not the case, and that you should seriously consider a rewarding side career as a speaker at usergroups ......(more)

T-SQL Tuesday #111 Roundup - Thank You! First, we owe a debt of gratitude to my friend Adam Machanic (blog | @adammachanic | Data Education) for starting T-SQL Tuesday in the first place. Thank you, Adam! Next, we owe a huge shout-out to Steve Jones (blog | @way0utwest | SQL Server ......(more)

Big Data

PolyBase Revealed: CDH Quickstart And PolyBase - All of my experience with Hadoop has been with the Hortonworks Data Platform. But to make sure that the stuff I’m advising in PolyBase Revealed works for Cloudera too, I grabbed the Cloudera QuickStart VM and took it for a spin. The two products are ......(more)

Backup and Recovery

DBA Myths: Can a differential backup get bigger than the full backup? - The thing about DBA Myths is that they are generally widespread and widely believed. At least I believed this one until I posted What’s a differential Backup? and Randolph West (b/t) pointed out that my belief that differential backups can’t get larger than full backups was incorrect....(more)

Implementing Automated Database Backup and Restore with Default Means - Introduction You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means. This example will cover a number of approaches – from checking the database’s integrity ......(more)

Azure SQL Database

SQL Login Failures and Azure SQL - We ran into an issue recently with a large number of SQL Login failures triggering an alert for our sysadmins. This was in one of our Azure SQL databases so the usual tools to troubleshoot alerts don’t always apply. We did have auditing turned on at ......(more)

Migrating your existing on-prem SQL Server database to Azure SQL DB - If you are in the process of moving an existing .NET application to Azure, it’s likely you’ll have to migrate an existing, on-prem SQL database as well. There are a few different ways you can go about this, so let’s go through them. Data Migration Assistant ......(more)

What does Azure SQL DB Automatic Index Tuning actually do, and when? - Azure SQL DB’s Automatic Tuning will create and drop indexes based on your workloads. It’s easy to enable – just go into your database in the Azure portal, Automatic Tuning, and then turn “on” for create and drop index: ENGAGE Let’s track what it does, ......(more)

Azure SQL Data Warehouse and Data Lake

10 Things to Know about Azure Data Lake Storage Gen2 - Azure Data Lake Storage (ADLS) Gen2 reached general availability on February 7, 2019. This post will help you understand its advantages and what you need to know to get started. If you would like to become more familiar with the concepts of a data lake, ......(more)

Analysis Services / BI on the MS Stack

The BI Journey: The Expert’s Advice – Part 1 - Creating a quick dashboard with a few reports may be the beginning of a BI solution, but unless time is spent on the underlying model, it won't be adaptable enough to grow. In this article by Gogula Aryalingam, you'll see how the advice of a mentor helped a newbie understand how to create a flexible model that will lead to a valuable BI solution....(more)

AI/Machine Learning/Cognitive Services

Re-imagining the Business with Artificial Intelligence - Artificial Intelligence (AI) will be a strategic capability and competitive advantage, no doubt. Statistics speak to that point. According to AI startup advisor Steve Ardire during his presentation (co-presented with Mico Yuk) at the DATAVERSITY® Enterprise ......(more)

New studies highlight how AI is transforming employee productivity and accelerating business results - Explore the benefits of artificial intelligence (AI) for knowledge workers and harness the full potential of Microsoft 365 with new research by Forbes Insights and Forrester Consulting. The post New studies highlight how AI is transforming employee productivity ......(more)

This is why AI has yet to reshape most businesses - For many companies, deploying AI is slower and more expensive than it might seem. ...(more)

Administration of SQL Server

Proposed SQL Server defaults: disable lightweight pooling - A few months ago I suggested that the following settings should be the default for most SQL Server instances. Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ......(more)

Release: SQL Server Migration Assistant (SSMA) v8.0 - Overview SQL Server Migration Assistant (SSMA) for Oracle, MySQL, SAP ASE (formerly SAP Sybase ASE), DB2, and Access allows users to convert a database schema to a Microsoft SQL Server schema, deploy the schema, and then migrate data to the target SQL ......(more)

Creating SQL Server Agent Job Schedules for Ola Hallengren’s Maintenance Solution - Data Platform MVP Ola Hallengren has created and maintained his free SQL Server Maintenance Solution script for over eleven years now. This script creates some objects in your master system database (by default), and it also creates and enables twelve ......(more)

Why is SQL Server Consolidation Better Than Having a Scattered Environment? - Having a scattered Microsoft SQL Server database platform is cumbersome to maintain, as you continue to accumulate waste capacity to your database servers. Therefore, you should consider consolidating your platform to make it more manageable. Let us ......(more)