A Fine Slice Of SQL Server
Manoj Pandey shows how you can retrieve counts of records in Cosmos DB using the .NET client:
Here in this post we will use C# .net code (for beginners like me) to see how to:
1. Connect to a Cosmos DB instance
2. Get list of all Databases in a Cosmos DB
3. Iterate through all the Databases and get the list of all Collections (or Tables)
4. Get COUNT of all documents/items (or records) in these Collections
Click through to see how.
Comments closedDavid Fowler wants to generate a random number:
You see the problem? Well in the post that I mentioned, I adressed this issue and offered a solution using CHECKSUM() and NEWID().
That’s ok but can be a bit cumbersome. I recently stumbled across a function that I hadn’t realised was there before (and of course this is where you all tell me, we’ve known about that all the time), CRYPT_GEN_RANDOM().
My expectation with this was that CRYPT_GEN_RANDOM() would generate according to a uniform distribution. Performing a simple experiment with about 4 million entries against random numbers 0-255 (1 byte), I can confirm that this does approximate a uniform distribution.
Garry Bargsley troubleshoots a strange issue:
What are your troubleshooting steps when a job failure is reported?
1. Open SQL Agent Job History for the failed job
2. Look at the SQL Server Log
3. This was an Ola job, so look at the CommandLog table
4. Look at the text log file stored on the file system
5. Open the job step and get the code being executed and run in a new query windowNow that you and I have performed the same steps and found no smoking gun, where do you go next?
But what happens when all of your indicators look fine, yet the job is still failing? Read on for one possible answer.
Comments closedPatrick LeBlanc has some bad news for us all:
We’ve seen people comment that Power BI is SLOW. But, what they really mean is your report is slow. Patrick breaks things down to get you pointed in the right direction.
Click through to see what might cause your Power BI report baby to be ugly.
Comments closedJess Pomfret shows off an interesting way of using Azure Functions to apply tags to resources:
In part one I discussed how useful Azure tags can be, and specifically about how adding a ‘dateCreated’ tag can help you keep track of your resources, and how to find resources with certain tags using PowerShell. Part 2 and 3 are based around the fact that adding the ‘dateCreated’ tag is a great idea, but relying on a human to remember to add it is less than ideal. In part 2 we looked at using Azure Policy to automatically add the tag. Today’s post will cover another option using Azure Functions.
Azure Functions gives us a way of running serverless code, written in a number of different languages, triggered by specific events or timings. Looking through the documentation there are many use cases from processing files to analysing IoT workstreams. Our use case is to run a PowerShell script that tags any resources that are missing the ‘dateCreated’.
Click through to see how.
Comments closedErik Darling digs into a new series:
To start things off, we’re going to talk about query plan patterns related to windowing functions.
There are several things to consider with windowing function query plans:
– Row vs Batch mode
– With and Without Partition By
– Index Support for Partition and Order By
– Column SELECTion
– Rows vs Range/Global aggregatesWe’ll get to them in separate posts, because there are particulars about them that would make covering them all in a single post unwieldy.
Anyway, the first one is pretty simple, and starting simple is about my speed.
Read on for this quick coverage of row mode versus batch mode processing with respect to window functions.
Comments closedRandolph West reports on a change in SQL Server 2014:
I’ve been doing SQLskills training recently, and Paul Randal (blog | Twitter) reminded our class that zeroing out a transaction log file does not use zeroes (
0x00). Well, not since SQL Server 2014 at any rate.
Click through to see what SQL Server uses instead when “zeroing” out a log file.
Comments closedThe Hadoop in Real World team explains a subtle difference:
cache() and persist() functions are used to cache intermediate results of a RDD or DataFrame or Dataset. You can mark an RDD, DataFrame or Dataset to be persisted using the persist() or cache() methods on it. The first time it is computed in an action, the objects behind the RDD, DataFrame or Dataset on which cache() or persist() is called will be kept in memory or on the configured storage level on the nodes.
That’s the similarity, but click through for the difference.
Comments closedBefore using the model for predictive inference, one should have reviewed overall performance on a holdout dataset to ensure the model is sufficiently accurate for the business context. For example, for our problem is an average error of ~12% and 90% prediction intervals of +/- ~25% of
Sale_Priceuseful? If the answer is “no,” that suggests the need for more effort in improving the accuracy of the model (e.g. trying other transformations, features, model types). For our examples we are assuming the answer is ‘yes,’ our model is accurate enough (so it is appropriate to move-on and focus on prediction intervals).
Click through for the article.
Comments closedEuan Garden shares some numbers around Apache Spark performance in Azure Synapse Analytics:
To compare the performance, we derived queries from TPC-DS with 1TB scale and ran them on 8 nodes Azure E8V3 cluster (15 executors – 28g memory, 4 cores). Even though our version running inside Azure Synapse today is a derivative of Apache Spark™ 2.4.4, we compared it with the latest open-source release of Apache Spark™ 3.0.1 and saw Azure Synapse was 2x faster in total runtime for the Test-DS comparison.
Click through for several techniques the Azure Synapse Analytics team has implemented to make some significant performance improvements. It’s still slower than Databricks, but considerably faster than the open-source Apache Spark baseline.
Comments closed