A Fine Slice Of SQL Server
Recently I’ve seen recommendations about putting PowerShell modules on every SQL Server. I must admit it has got me thinking if this is indeed worthwhile.
In addition, it makes me wonder if it’s actually better to put the Powershell modules on a select number of management servers instead?
If you are wondering which modules I could be talking about, I mention some in a previous post which you can read in detail here.
Read on for Kevin’s thoughts on the matter.
Comments closedHolger von Jouanne-Diedrich takes us through concepts in reinforcement learning:
At the core this can be stated as the problem a gambler has who wants to play a one-armed bandit: if there are several machines with different winning probabilities (a so-called multi-armed bandit problem) the question the gambler faces is: which machine to play? He could “exploit” one machine or “explore” different machines. So what is the best strategy given a limited amount of time… and money?
There are two extreme cases: no exploration, i.e. playing only one randomly chosen bandit, or no exploitation, i.e. playing all bandits randomly – so obviously we need some middle ground between those two extremes. We have to start with one randomly chosen bandit, try different ones after that and compare the results. So in the simplest case the first variable
e=0.1is the probability rate with which to switch to a random bandit – or to stick with the best bandit found so far.
Click through for various cases and a pathfinding example in R. H/T R-Bloggers
Comments closedErik Darling shows off a neat trick for inserting with parallelism into a table variable:
One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have.
While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert to put some data in there.
No matter how big, bad, ugly, or costly your insert statement is, SQL Server can’t parallelize it.
That’s just what he wants you to think and then the trap goes off.
Comments closedArun Sirpal wants to change the report interval for a Query Store report:
While not specific to SQL Server 2019 (I was using this version to do some testing) I was struggling to find how to change the time period of analysis for the Query Store reports within SSMS.
This is not a ground breaking post but hopefully a helpful one! So, I load up the “Top 25 resource consumers” report and by default it will show data for the past hour. So what do you do, or should I say what do you click to change the time interval for the report?
Read on for the two screenshots which answer this question for you.
Comments closedTracy Boggiano hosted this month’s T-SQL Tuesday, on the topic of SQL Server on Linux:
I noticed a common theme in how easy it is to install SQL on Linux that tells me if you didn’t think you had time to install SQL on Linux then you probably do and should give a try in the near future.
Thanks for all that participated!
Let me put it this way: one of my employees, who had never really worked with SQL Server before, got SQL Server on Linux installed through Docker in about 15 minutes. It’s really easy to do.
Comments closedRob Collie takes us through a good use of FILTER() in DAX:
The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function. In both examples here, I’ve highlighted the offending measure in yellow.
CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6)
CALCULATE([Sightings per Year],
Observations[TTL Min]>[Avg Sighting Length in Mins])In the first formula, I was trying to use a measure on the left side of the comparison, and in the second, I was trying to use a measure on the right side of the comparison. Both are illegal.
Read on to see why and how you can use FILTER() to solve these problems.
Mike Cisneros shows us an example where unexpected label values can throw off your readers:
The internet immediately latched onto the seemingly absurd collection of months portrayed in this chart. The bill, dating from June of 2019, included 13 prior months of usage from as early as August of 2016, as recently as March of 2019, and in a random order.
Soon, our non-U.S.-based friends pointed out that the dates made even less sense to them, as (of course) their convention is not to show dates in MM/YY format, but in YY/MM format.
And with this, the truth of the matter became obvious: the dates were in neither MM/YY format nor YY/MM format; they were in MM/DD format, and excluded labeling the year entirely.
Even small things can make a difference in your ability to get the message across to users.
Comments closedAchilleus gives us an overview of SnowflakeDB:
There is no dark magic involved in improving the efficiency of your queries. Based on whom you ask this can be considered as a standout feature or a major hindrance but I am not a fan of tuning queries according to my workload as I feel the way data evolves so quickly in organizations. It becomes more tricky to play catch up to turn the all necessary knobs to make the query faster.
Snowflake claims they tune all the queries “automagically” via a dynamic query optimization engine. No need for any indexes, updating statistics, partition keys or pre-shard data for even distribution when you scale up. All of this will be done by their patent-pending dynamic optimization.
But I still feel snowflake can work on providing the necessary knobs for people who would like to tune their queries.
Read the whole thing. In short, it’s a technology worth looking at, but it’s not going to work perfectly in all cases.
Comments closedRecently, I discovered three PowerShell Core variables that could be very helpful when it comes to determining the operating system in PowerShell. PowerShell has become a cross-platfrom tool and can be installed on Linux or MacOs, too. So it could happen that you have to determine the operating system first before starting any other actions.
Click through to see what they are and how to use them.
Comments closedNiels Berglund has an interesting scenario for us:
We see how the procedure in Code Snippet 2 takes relevant gameplay details and inserts them into the
dbo.tb_GamePlaytable.In our scenario, we want to stream the individual gameplay events, but we cannot alter the services which generate the gameplay. We instead decide to generate the event from the database using, as we mentioned above, the SQL Server Extensibility Framework.
Click through for the scenario in depth and how to use Java to tie together SQL Server and Kafka.
Comments closed