A Fine Slice Of SQL Server
Robert Cain continues a series on learning the Kusto Query Language:
The
agofunction is very similar to thenowfunction, which was discussed in my previous post Fun With KQL – Now. In this article we’ll take a look atago, see how it works and how it differs fromnow.We’ll be using both the
nowfunction in this post, so if you aren’t familiar with them please go read my articles Fun With KQL – Print and Fun With KQL – Now.
Click through for proper use of ago().
Tracy Boggiano opens a can of worms:
So, what to rant about? Should your managers have access to your SQL Server instances? It depends. You say on what. Well, just one thing. What type of manager do you have?
Read on for Tracy’s thoughts on the matter. Speaking as a manager, I tend to agree. If you don’t know what you’re doing, better not to have the ability to mess things up. I can think of oddball scenarios where you might still want the manager to have (at least theoretical) access to a system, primarily as a backstop in case the line staff get locked out or someone gets hit by the lottery bus and you suddenly need to bring in a new employee. That’s more of “break glass in case of emergency” access, though.
Comments closedGlenn Berry gives us two rants for the price of one:
This is rant #1. Even though I have an unusual fascination with computer hardware, I am still somewhat taken aback when I encounter DBAs who have absolutely no idea what type of hardware they are using. I’ll sometimes ask a DBA “What processor does your most important database server have?”, and I often get a “deer in headlights” look in response. Then a mumbled response, “I’m not sure, maybe a Xeon?”.
Read on for this rant, as well as the origin story of Glenn’s outstanding SQL Server Diagnostic Information Queries.
Comments closedGarry Bargsley troubleshoots a transactional replication issue:
Use transactional replication to replicate similar data from four databases to one Azure DB. This sounds pretty straightforward for anyone who has done any replication work.
However, once I had everything setup and working, things stopped working, and it was a head-scratcher as to why. I made the proper settings configurations on each article, or so I thought. Let me show you the scenario in more detail.
Read on for the answer.
Comments closedGilbert Quevauvilliers is making a list:
In the example I did not want to specify all the country names one row at a time. This not only takes a long time, but if I had to then make updates it could be painful too.
The requirement was for certain countries to have their names and the rest be grouped into “Other Countries”
Read on to see how Gilbert was able to combine the set of “Other Countries” together.
Comments closedI once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).
The manager of infrastructure had heard about SQL Server’s missing index recommendations:
Yep, it ends about as poorly as you’d expect.
Comments closedJoe Obbish and Erik Darling tag team on this one. First, Joe looks at the details of what the CCI ordering process does:
The sort for inserting into an ordered columnstore is a DML request sort. It appears to use the same internal mechanism as the sort that’s added for inserting into partitioned columnstore tables. The difference is that the data is sorted by the specified columns instead of a calculated partition id. In my testing, the sort appears to be a best effort sort that does not spill to tempdb. This means that if SQL Server thinks there won’t be enough memory then the data will not be fully sorted. Parallel inserts have an additional complication.
And Erik has a messy work-around:
Anyway, I decided to dig in and see what was going on behind the scenes. Which of course, means query plans, and bothering people who are really good at debuggers.
Most of the problems that you’ll run into in SQL Server will come from sorting data.
Whenever I have to think about Sorts, I head to this post about all the different Sorts you might see in a query plan.
Definitely read both posts.
Comments closedRonan Harrington executes a function for each row in a dataframe:
In this section, we want to tidy the different types of flight in the data set by increasing the number of rows and decreasing the number of columns. For a given airport on a given day, instead of having multiple columns/variables for arrivals, departures and total number of flights, we want to have one column describing the flight type (e.g. arrival or departure) and one column with the value of that flight type/number of flights. This will give the data set a tidy structure.
Click through for the process and the script. H/T R-Bloggers.
Comments closedMichael Mayer announces updates to shapviz:
In a recent post, I introduced the initial version of the “shapviz” package. Its motto: do one thing, but do it well: visualize SHAP values.
The initial community feedback was very positive, and a couple of things have been improved in version 0.2.0. Here the main changes:
Read on for those changes.
Comments closedDeepthi Goguri wants a CNAME record:
If you are using the Servername and database name in the connection strings in all the applications connecting to your database being migrated, this process gets tough and tedious to update once the database is migrated to a different server. It is always advised to use database DNS in the connection strings instead of the IP address or the Servername.
This also applies to the report server datasources as well.
I know it is hard to make the change but if the change is good, it is worth the effort.
I think this starts to get tricky as the number of servers and instances increases, especially if your DNS records differ considerably from your server names. Still, I completely agree: using CNAMES makes life a lot easier.
Comments closed