A Fine Slice Of SQL Server
Michael J. Swart wants to know if you’re using CLR in your environment:
CREATE ASSEMBLYsupports specifying a CLR assembly using bits, a bit stream that can be specified using regular T-SQL. The full method is described in Deploying CLR Database Objects. In practice, theCREATE ASSEMBLYstatement looks something like:
After learning about assembly deployment, check out Michael’s one-question survey.
Comments closedBabak Behzad explains potential Hadoop NodeGroup performance bottlenecks:
As can be seen in the logs, the localityWaitFactor value is 1, but the delay that this code causes grows linearly with the number of required containers. Since our DFSIO-large benchmark creates 1,024 files, each 1 GB in size, it requests 1,024 YARN containers. Therefore, the code has to miss at least 1,024 scheduling opportunities until it schedules containers on this (wrongly assumed) OFF_SWITCH node.
But why is this delay enforced? This idea falls into a big area of scheduling research. The Delay Scheduling algorithm was introduced by Matei Zaharia’s EuroSys ’10 paper titled “Delay Scheduling: A Simple Technique for Achieving Locality and Fairness in Cluster Scheduling”.
That post is a bit deeper than my Hadoop administration comfort level, but if you’re given the task of performance tuning a cluster, this might be one place to look.
Comments closedEn El Margen charts voting clusters in UN data:
After some more digging, and a suggestion by @theMexIndian I decided to see more in the depth the unvotes database that I wrote about some weeks ago.
This time, amit suggested I do some hierarchical clustering of the votes. So here goes a very dirty first attempt…
Check this out as a case study in data analysis.
Comments closedJohn Sterrett discusses ways to migrate an on-prem database to Azure SQL Database:
This is great, except for the case I want to talk about today. What if you need to do a live migration with as little downtime (business wants no downtime) as possible with bigger databases. For example, say and existing 50 GB to 500GB database? Your only, option today is a good old friend of mine called transactional replication. You see, you can configure transactional replication and have the snapshot occur and all data in your current production system can be syncing live with your Azure SQL Database until it’s time to cutover which will make your cutover downtime as short as possible.
Below I will give you step by step instructions on how you can configure your subscriber. This would be your Azure SQL Database. The publisher would be your existing production database which could either be on-premise or an Azure VM.
Ah, replication: the cause of, and solution to, all of life’s problems. Or something. Do read the whole thing.
Comments closedRolf Tesmer looks at whether to use SSDs or Premium Disk for SQL Server instances on Azure IaaS:
To test the throughput I will run a set test with the TempDB database on D:\ (local SSD) and then rerun the test again with the TempDB moved onto F:\ (P30 premium disk). Between the tests SQL Server is restarted so we’re starting with a clean cache and state.
The test SQL script will create a temporary table and then run a series of insert, update, select and delete queries against that table. We’ll then capture and record the statistics and time.
The results were interesting; read on to learn more.
Comments closedJohn Yagecic has a Shiny app which gives a Monte Carlo analysis of Manning’s Equation:
Monte Carlo analysis is a great way to explore the impact of input variable uncertainty on the results of engineering equations, and with vector variables and distribution and sampling functions at its core, R is a natural platform for this analysis.
Check out his app, which has a link to the code. Amazingly, this is only 107 lines of code.
Comments closedStacia Varga has a follow-up to her Gentle Introduction to Data Analytics:
Q: How is data analytics on SAS different from R support for SQL2016?
A: I’ve not used SAS (although I did go to their campus to teach MDX to their engineers once upon a time… beautiful campus!), so I can’t say with any specificity. SAS also has multiple products so it would be difficult to describe differences. In general – and please understand there may be something new in SAS that I don’t know about – I think the difference is that R in SQL 2016 allows you to run functions on data inside the database, thereby leveraging database resources and also server resources for parallelization. I’d love to get input from readers to expand on this topic.
I can confirm that SAS has a beautiful campus.
Comments closedTara Kizer has a primer on storing WhoIsActive outputs for subsequent analysis:
Create a new job and plop the below code into the job step, modifying the first 3 variables as needed. The code will create the logging table if it doesn’t exist, the clustered index if it doesn’t exist, log current activity and purge older data based on the @retention variable.
How often should you collect activity? I think collecting sp_WhoIsActive data every 30-60 seconds is a good balance between logging enough activity to troubleshoot production problems and the storage needed to keep the data in a very busy environment.
I like having something like this in place because often times, when you need these results, it’s already too late.
Comments closedRamkumar Chandrasekeran discusses DeployR, an enterprise security model for R:
DeployR Enterprise is designed to deliver analytics solutions at scale to whomever needs it: inside or outside the enterprise. It also guarantees secure delivery of your analytics via DeployR web services. These secure web services integrate seamlessly with existing enterprise security solutions: Single Sign-On, LDAP, Active Directory, PAM, and Basic Authentication, can enforce access privileges already defined by your IT department for existing enterprise users and also have the capability to safely support anonymous users when needed.
There’s nothing groundbreaking here: it’s TLS (to encrypt network transmissions) and LDAPS (to control authentication and authorization). That there’s nothing groundbreaking is a good thing—that means companies will have most of the infrastructure in place to support this.
Comments closedAndrew Pruski notes an important bugfix in SQL Server 2016 CU1:
SQL Server 2016 CU1 has been released and one thing I noticed was: –
FIX: Canceling a backup task crashes SQL Server 2014 or 2016
That’s pretty nasty, when I originally clicked on the link I was expecting to see detailed a pretty precise set of circumstances in which that bug can occur but no no, apparently not. Cancelling any backup task can lead to this happening.
Andrew then argues in favor of waiting for SPs before deploying new versions of software, having been burned on it in the past. I don’t agree with that philosophy; regardless, I recommend reading his post.
Comments closed