A Fine Slice Of SQL Server
Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use January first to December 31. So we get the entire year, is that what we want? Or do we want to see the sales for the previous year until the day we have data for this year so we can compare? Both need different DAX so let’s take a look.
Read on for a detailed analysis, including where you might go wrong.
Comments closedJeff Mlakar talks about a topic I like—dropping lots and lots of stuff:
Let’s assume that you have lots of tables that need to be dropped according to some criteria. Trying to do them all at once isn’t a good idea. Even with a powerful server it will either take forever or simply never finish.
For example – you may have millions of tables in sys.tables or millions of indexes you need to drop. SQL Server won’t process them well if you try to run it as one big statement.
I’ve never had millions of tables or millions of indexes to drop and now I am jealous. Regardless, Jeff has two techniques for us when you have a lot of work to do. And if you do need to figure out key dependencies, I have a script for that.
Comments closedMichael Bourgon explains what the WAIT_AT_LOW_PRIORITY option does with table partitioning and that it is available in Standard Edition:
But how about WAIT_AT_LOW_PRIORITY? That was introduced in 2014 to make table partitioning deal better with That-Dude-From-Accounting-Who-Kicks-Off-A-Massive-Query-On-Friday-at-5pm, which causes partitioning to hang on Saturday when you’re trying to add and remove partitions.
Read on for a demo.
Comments closedBruno Stecanella shows us how to use MonkeyLearn to perform sentiment analysis in Python:
Sentiment analysis is a set of Natural Language Processing (NLP) techniques that takes a text (in more academic circles, a document) written in natural language and extracts the opinions present in the text.
In a more practical sense, our objective here is to take a text and produce a label (or labels) that summarizes the sentiment of this text, e.g. positive, neutral, and negative.
For example, if we were dealing with hotel reviews, we would want the sentence ‘The staff were lovely‘ to be labeled as Positive, and the sentence ‘The shared bathroom was absolutely disgusting‘ labeled as Negative.
Click through for a demo.
Comments closedAfter Defne’s awesome monster Weekly Charts in the last 14 days (here’s number 1, here’s number 2), this weeks’ Weekly Chart keeps it small. Today we launched callout lines for our locator maps (they are lines connecting marker and label), and I wrote a few words about how to create them and when – so I will simply refer you to this announcement blog post and explain the importance of our new feature with this map:
In moderation, they can be quite useful for indicating and explaining important but subtle points you’d like to make.
Comments closedGrant Fritchey takes us through an interesting scenario around Query Store:
This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them.
Don’t panic. Of course you can force a plan with a table variable, most of the time. Steinar had a fairly focused problem. Before I go on to explain the issue, let me be really clear, Steinar figured out the issue all on his own. When he outlined the problem, I saw immediately what his conclusion was going to be. What’s spurring this blog post is that Steinar said he’d searched on the internet and no one had talked about the issue yet. So, let’s talk about it.
Read on for the problem as well as solution.
Comments closedKevin Chant shares resources for getting started with SQL Server Big Data Clusters:
In a previous post I shared current SQL Server 2019 learning resources, which you can view in detail here.
However, SQL Server 2019 Big Data Clusters are very involved. So, I thought I better dedicate a whole post to further learning resources for it.
Because some people have different learning methods I have included references to both documents and videos in this post. In addition, I have created the below links in case somebody wants to go directly to a specific section.
Kevin’s put together quite a few useful links here.
Comments closedItzik Ben-Gan provides quality information on working with subqueries in SQL Server:
In this plan you see a Nested Loops (Left Semi Join) operator, with a scan of the clustered index on Customers as the outer input and a seek in the index on the customerid column in the Orders as the inner input. You also see an outer reference (correlated parameter) based on the custid column in Customers, and the seek predicate Orders.customerid = Customers.custid.
So why are you getting the plan in Figure 1 and not the one in Figure 2? If you haven’t figured it out yet, look closely at the definitions of both tables—specifically the column names—and at the column names used in the query. You will notice that the Customers table holds customer IDs in a column called custid, and that the Orders table holds customer IDs in a column called customerid. However, the code uses custid in both the outer and inner queries.
Itzik covers three specific scenarios, all of which can cause trouble to database developers who haven’t been burned yet. And sometimes even those who have.
Comments closedKenneth Fisher takes us through a security issue:
If you have to deal with linked servers then you probably have or will run into the following error:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure.
Kenneth explains why you might get this even when you’re just editing a procedure and not directly hitting a linked server, as well as a few tips for fixing the issue.
Comments closedAnthony Nocentino walks us through persistent volumes and Kubernetes:
One of the key principals of Kubernetes is the ephemerality of Pods. No Pod is every redeployed, a completely new Pod is created. If a Pod dies, for whatever reason, a new Pod is created in its place there is no continuity in the state of that Pod. The newly created Pod will go back to the initial state of the container image defined in the Pod’s spec. This is very valuable for stateless workloads, not so much for stateful workloads like SQL Server.
This means that for a stateful workload like SQL Server we need to store both configuration and data externally from the Pod to maintain state through the recreation of a Pod. Kubernetes give us constructs two constructs to do that, environment variables and Persistent Volumes.
Read on for a good bit of background and a few scripts to help you get started.
Comments closed