A Fine Slice Of SQL Server
SQL Server stored procedures can return data in three different ways: Via result sets, OUTPUT parameters and RETURN values – see the docs here.
I have previously blogged about getting result sets with FromSqlRaw here and here.
I have blogged about using OUTPUT parameters with FromSqlRaw here.
In this post, let’s have a look at using RETURN values.
Click through for the process.
Comments closedEd Elliott uncovers a mystery:
To understand this we need to take a look at how we can create a UDF in .NET that is called by the Java VM Apache Spark code because, that is logically, what happens. In our application we call into Apache Spark and ask it to do things like read from a file, run some transformation and write files back out again. With UDF’s, we ask Spark to run a UDF and Spark comes back to our UDF, passing it some data and asks the UDF to execute but the Java VM does not understand how to execute .NET code.
Read the whole thing.
Comments closedAndrew Pruski has moved some images around:
A couple of months ago Docker announced that they would be implementing a 6 month retention policy for unused images in the Docker Hub.
This was due to kick in on the 1st of November but has now been pushed back until mid 2021.
I’ve had multiple Windows SQL Server container images up on the Docker Hub for years now. It’s been a great platform and I’m very thankful to them for hosting my images.
That being said, I want to make sure that the images that I’ve built are always going to be available for the community so I have pushed my SQL Server images to the Github Container Registry.
I guess I should do the same.
Comments closedErik Darling talks about the intersection of two performance tuning topics:
Yesterday we saw a case where the Gather Streams operator was costed quite highly, and it prevented a parallel plan from being chosen, despite the parallel plan in this case being much faster.
It’s important to note that costing for plans is not a direct reflection of actual time or effort, nor is it accurate to your local configuration.
They’re estimates used to come up with a plan. When you get an actual plan, there are no added-in “Actual Cost” metrics.
Read on to see how you can monkey’s paw your way through this problem by introducing exciting, new problems.
Comments closedAndy Leonard recommends not using backup-and-restore as an approach of moving SSIS packages around:
First, please do not misunderstand. You should back up SSISDB just like you back up all other databases – especially in Production. You should also conduct Disaster Recovery exercises in which you restore SSISDB from the latest backup, or avail yourself of Always On availability groups and / or Windows Server Failover Clustering.
With that caveat in mind, read on to see why.
Comments closedAnthony Nocentino asks what’s in a name:
In this post, we will explore how a Pod name is generated, Pod Name lifecycle, how it’s used inside a Pod to set the system hostname, and how the system hostname is used by SQL Server to set its server name metadata.
Read the whole thing.
Comments closedAndy Galbraith troubleshoots a problem:
Unfortunately a common error in many of our client environments is this:
—
Error: 8623, Severity: 16, State: 1.
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.–There are many potential causes for this, and the text of this particular error is very well-written because the primary cause is exactly what is listed – a complex query.
Read on to see how to find this complex query, as well as a few examples of complex queries.
Comments closedOne way you can work around the above scenario is to install multiple virtual machines. Now the first thing you might realize is that this will also take up a lot of compute and storage.
In reality, I use to use this method myself in the past using Hyper-V. To reduce the amount of storage the virtual machines used in Hyper-V I use to used parenting disks.
Since the introduction of containers and Docker this has become a less popular option. However, you can still read an old post of mine with tips in here.
Click through for additional options.
Comments closedTomaz Kastrun has a not-so-useless function:
The full moon function, or should we call it fool moon – due to it’s simplistic and approximate nature, calculates the the difference between the date (only date, no time, no long/lat coordinates) and Julian constant. Should you be using a different calendar, don’t run the function, just look out the window.
The function is written based on generalized equation for julian day numbers and months. Another one could be to calculate RMSE of the predicted values and realization of lunar behavior (lunatic start time). In this case – reversed engineering – you would use the the approximate date/time for the first new moon after that date if the synod period was constant. This number than obtained is only empirically proven by recursively solving for the new “possible date/time” of lunar behavior and calculate the prediction error. In order to minimize the RMSE value of the difference between the full moon dates/times predicted formula and the dates/times for the full moon over the next 10 years you get something like this.
Click through for the function as well as sound advice if it’s not a full moon.
Comments closedAs described in the blog post Apache Kafka® Needs No Keeper: Removing the Apache ZooKeeper Dependency, when KIP-500 lands next year, Apache Kafka will replace its usage of Apache ZooKeeper with its own built-in consensus layer. This means that you’ll be able to remove ZooKeeper from your Apache Kafka deployments so that the only thing you need to run Kafka is…Kafka itself. Kafka’s new architecture provides three distinct benefits. First, it simplifies the architecture by consolidating metadata in Kafka itself, rather than splitting it between Kafka and ZooKeeper. This improves stability, simplifies the software, and makes it easier to monitor, administer, and support Kafka. Second, it improves control plane performance, enabling clusters to scale to millions of partitions. Finally, it allows Kafka to have a single security model for the whole system, rather than having one for Kafka and one for Zookeeper. Together, these three benefits greatly simplify overall infrastructure design and operational workflows.
Read on to see where this story is at and what kinds of changes you’ll have to make to code.
Comments closed