A Fine Slice Of SQL Server
Erik Darling embraces dynamic SQL:
You’ve got tables. So many tables And columns. So many columns.
Users — demanding as they are — might wanna see, search, and order by all sorts of things in those many columns in those many tables. Writing a query to do that is hard at first, especially if you’re afraid of dynamic SQL.
Read on for reasons why dynamic SQL is usually the right answer here and check out a video as well.
Comments closedKenneth Fisher pulls out the company directory:
This month the topic we are blogging about is Upgrade Strategies. Or, how do we look at SQL Server upgrades. In my case I want to talk about the absolute hardest part of any upgrade at my company.
I should point out that I work for a large company with a lot of moving parts. Over the course of my tenure here I’ve helped to support hundreds to thousands of SQL Server instances. And at least for us, the technical part of an upgrade isn’t too bad. Where we almost always run into problems is Who do we contact?
Read on for Kenneth’s thoughts on the topic.
Comments closedDavid Fowler is looking for lead blockers:
By far the most common cause for poor performance that I see is blocking. Unless you’ve got some monitoring in place it’s near impossible to identify the cause of any historical blocking incidents. Luckily, if you’re doing as I do and running the First Responder Kit regularly into tables, you will have a history of running statements thanks to sp_BlitzWho. That data will contain the SPIDs of any blocking processes.
Now, if you’ve ever had to look at that data and tried to figure out what the blocking chain is then you’ll know that it can be a total headache.
But David has a solution to find those fullbacks and pulling guards.
Comments closedGrant Fritchey is learning about PostgreSQL:
One of the first things I worry about when I’m looking at a new system is the status of the backups. I don’t see anything in PostgreSQL that will lead me to a different conclusion. However, I didn’t realize just how big a can of worms I would open by pursuing backups and restores.
Instead of talking immediately about backups, let’s talk about restores.
This is a bit different from what we’re used to in SQL Server, so definitely check it out if you are looking at Postgres development or administration.
Comments closedPaul Randal explains why there is yet another tier of Azure SQL Database:
Over the past several years, I’ve helped numerous customers migrate SQL Server workloads to Azure SQL, including Azure SQL Database, Azure SQL Managed Instance, and Azure SQL Virtual Machines.
In this article, I’ll explain some of the challenges of optimizing the compute cost for an Azure SQL Database deployment and review how the serverless compute tier can greatly simplify it.
Click through to see where the serverless tier fits and how you can make it work best in your environment.
Comments closedWhen you sign-up for the Power BI service at PowerBI.com (this address redirects to App.PowerBI.com), use establish a tenant for your organization, hosted in the Azure cloud. Even if you setup a 90-day trial account, you have a tenant that you can upgrade later on. By default, all of the datasets, reports and other assets you publish to the workspaces in your tenant, are in a shared capacity. This means that Microsoft will manage the storage and resource allocation among shared servers in their data centers. Larger organizations will benefit from a dedicated capacity “Premium” tenant. This means that Microsoft dedicates at least one virtual machine in the Azure data center, with guaranteed capacity, for your tenant. With Premium-per-User (PPU) licensing, you pay per user.
Click through for a list of available options and some of the implications of each.
Comments closedJared Poche looks for differences:
In my most recent blog post, I looked at the Query Variation report, which compares the recent performance of queries versus their historical performance to either highlight improvements or regressions in performance. The Waits Variation component does the same, but comparing the recent waits for a query to its historical waits.
One thing to keep in mind, is that if a given query is changed in any way (to change the filter, return additional columns, or include a hint), the changed query will have a different query_id in Query Store. In which case, both the Waits Variation and Query Variation procedures will not compare the historical performance of the old query to the recent performance of the new one.
Read on to see how.
Comments closedT-SQL Tuesday this month is all about upgrades, so here are a few more thoughts on the topic. Let’s start with Reitse Eskens:
Things change when you’re working with a lot of data or when you’re trying to use SQL Server to the max. At some point you’ll see new features that will support your workload or add security for your data. Here are the steps I’d take to evaluate new versions.
Todd Kleinhans tells a horror story with a happy ending:
I had just been laid off for the first time in my life from a dot com. I was a classic ASP web developer, a junior development DBA, and I knew Access and FileMaker Pro. Interviewed and got hired on as a contractor to help with Access and ASP.
Before me, a local consulting company was retained to help them with the migration from Access to SQL Server. It was a disaster.
Rob Farley thinks about ways to make the upgrade process smoother:
Comments closedI don’t envy application vendors who don’t have a strong DevOps story. They need to get it in place so that they can scale, but it doesn’t happen overnight. The road to good deployment practice is long and is threatened by all kinds of things. Code coverage is rarely complete, and problems seem to find those places that don’t have good testing in place yet (typically because problems are avoided in the areas that do have good testing). All this is so much easier when a project is starting from scratch, and not the culmination of a decade or more of development needing to be compatible with the last four versions of SQL Server and Windows.
Martin Schoombee keeps customer software separated:
As a consultant I work on at least a few projects at a time, and prefer to isolate my development environments by creating an Azure VM for each customer. Isolating the environments are great because I can focus on the software and setup I need for that customer, and will never be in a situation where VPN clients or different software versions clash with each other.
With my development environments in Azure I am truly mobile, can work from anywhere and can lose my working machine at any point without much impact beyond getting another one.
Click through to see how Martin can do this with hardly a problem.
Comments closed(NOTE: I have returned to Microsoft and am working as a Solution Architect in Microsoft Industry Solutions, formally known as Microsoft Consulting Services (MCS), where I help customers build solutions on Azure. Contact your Microsoft account executive for more info. That being said: the views and opinions in this blog are mine and not that of Microsoft).
There certainly has been a lot of discussion lately on the topic of Data Lakehouse, Data Mesh, and Data Fabric, and how they compare to the Modern Data Warehouse. There is no clear definition of all these data architectures, and I have created a presentation using my own take that I have been presenting frequently internally at Microsoft and externally to customers and at conferences. Hopefully these presentations, blog posts, and videos can help clarify all these data architectures for you:
Click through for several useful resources to help differentiate these topics.
Comments closed