A Fine Slice Of SQL Server
Aaron Bertrand continues looking at columnstore indexing:
The idea is I would have eight instances of an application (SQLQueryStress) that would each run on a dedicated scheduler, handling only the data destined for a specific partition / filegroup / data file, ~100 million rows at a time
It was interesting seeing how to pull that off. Aaron also has some extended findings, so check it out.
Comments closedTomaz Kastrun has a bit of fun with the date February 2, 2020:
As of writing this blog-post, today is February 2nd, 2020. Or as I would say it, 2nd of February, 2020. There is nothing magical about it, it is just a sequence of numbers. On a boring Sunday evening, what could be more thrilling to look into this little bit further 🙂
Let’s kick R Studio and start writing a lot of useless stuff.
Tomaz also compares US versus EU palindromic dates and visualizes the different distributions.
Comments closedDave Mason takes a look at generating fake PII in R:
I’ve been thinking about R and how it can be used by developers, DBAs, and other SQL Server professionals that aren’t data scientists per se. A recent article about generating a data set of fake transactional data got me thinking about this again and I wondered, can R be used to obfuscate PII data?
In a word, yes. Well, mostly. (More on this in a bit.) As with anything R-related, there are probably multiple packages that are useful for any given task. For this one, I’ll focus on the “generator” package.
Click through to see what it does and Dave’s thoughts on the topic. It would also be possible to generate fake data in R by hitting a web API like Daniel Hutmacher’s service.
Comments closedEmanuele Meazzo has an update to the SQL Diagnostic Book:
Just a quick note, the SQL Diagnostic Book has been updated, here is the changelog:
– Updated FirstResponder’s Kit and Glen Berry’s Scripts
– Added SQL Assensment API Notebooks
– Added sp_whoisactive installation and basic documentation notebooks
Click through to see where you can get the book.
Comments closedBob Pusateri has a two-parter on Powershell notebooks. First up is the problem:
PowerShell Notebooks are a great new feature in Azure Data Studio, first becoming available in the November 2019 release. Like SQL notebooks, PowerShell notebooks are based on Jupyter Notebooks format, which are interactive documents containing text and executable code blocks.
Having some working PowerShell code that I wanted to share along with explanations and examples, I created a PowerShell Notebook. The only problem was my functions would never initialize. Actually they would never stop initializing – I would run the cell they were defined in, and it would just keep running forever.
And then Bob has the solution:
It turns out I did not have the latest version of the PowerShell Kernel running on my machine. The latest version is currently 0.1.3, and I had 0.1.2. Upgrading appears to have solved this issue for me – yay!
This solution also raises the issue that there is no notification from Azure Data Studio that a PowerShell Kernel exists or is in need of updating. I (and probably others) will just believe that as long as Azure Data Studio is up to date, we’re good to go. So how does one update their PowerShell kernel? Well, it’s simple, but not intuitive.
Read on to see how.
Comments closedDave Bland shows how you can create a new database using Azure Data Studio:
Regardless of what tool we are using, SQL Server Management Studio or Azure Data Studio, the need to create new databases is always present. Using Transact SQL is an option in both tools. What is not an option in both tools is to right click and to go “New Database”. This has been in SSMS for many years, however it is NOT present in Azure Data Studio.
If you really do want to create a new database using a GUI in Azure Data Studio, Dave shows you the extension you need. It’s not as fully-featured as the wizard in Management Studio, so it would make sense to understand what that wizard is doing and learn the T-SQL yourself.
Comments closedErik Svensen shows us what happens in DAX functions when February 29th rolls around:
Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.
Well – in fact there was a few surprises depending on what you select from you date table.
Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.
Click through for those surprises. They aren’t bugs, as the functions have to do something and the possible set of valid somethings is greater than one.
Comments closedPaul Andrew has a public service announcement for us:
As far as I can tell Microsoft do an excellent job at managing data centre capacity so I completely understand the reason for having limitations on resources in place. There is no such thing as a limitless cloud platform.
Note; in a lot of cases (as you’ll see in the below table for Data Factory) the MAX limitations are only soft restrictions that can easily be lifted via a support ticket. Please check before raising alerts and project risks.
Click through for the limits, and “contact support” definitely is good advice if you’re expecting to push past those limits.
Comments closedMax Vernon ties event notifications to Service Broker:
My previous post shows how to configure an Event Notification to fire whenever a login event occurs. The post uses Service Broker to receive those Event Notifications into a queue, which is then processed by a stored procedure and saved into a standard SQL Server database. This post provides a quick+dirty VB.Net command line monitor that shows how full a Service Broker queue is.
The following code should be pasted into a blank Visual Studio VB.Net console project. It is trivially easy to translate this into C#, but I like VB – what can I say.
Click through for the code. No F# translation from me, however, as I am lazy.
Comments closedAshish Kumar and Jorge Villamariona continue a series on data lakes:
People from a traditional RDBMS background are often surprised at the extraordinary amount of control that data lake architects have over how datasets can be stored. Data Lake Architects, as opposed to the Relational Database Administrators, get to determine an array of elements such as file sizes, type of storage (row vs. columnar), degrees of compression, indexing, schemas, and block sizes. These are related to the big data oriented ecosystem of formats commonly used for storing and accessing information in a data lake.
It is a bit of a different world and it comes with trade-offs. The whole thing is worth reading.
Comments closed