A Fine Slice Of SQL Server
Grant Fritchey answers an interesting question:
Strictly speaking, a recompile isn’t really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here’s one way to get it done.
Click through for an extended event which does the job.
Comments closedMatthew Roche gives us a date dimension:
A bunch of folks from the Power BI community shared their favorite Power Query date dimension queries – you can look at the Twitter thread for links if you’re interested. With their inspiration (and code!) I picked what I liked, discarded what I didn’t, and came up with this:
Click through for that code.
Comments closedAndy Leonard has a four-parter four us on database solutions in SQL Server Management Studio. Part one provides an introduction:
I like Microsoft Visual Studio a lot. I know some members of the team that developed Visual Studio, and they are scary-smart individuals who have forgotten more about developing software than I will ever know.
For some reason, I am not fond of SQL Server projects in Visual Studio. I believe the reason is that I am not familiar with the template. Please note I used the word fond intentionally. It’s an emotion. In this case, it’s all about me. I believe my emotion would change if I took the time to learn more about the Visual Studio SQL Server project template.
I continue to attempt to learn VS database projects. In the meantime, I prefer SQL Server Management Studio solutions.
Part two shows how to add a new query:
One solution is to add instrumentation to T-SQL scripts. I personally like to write T-SQL scripts that idempotent (a fancy way to describe “re-executable with the same results”). One way to write idempotent T-SQL is:
1. First check for the current state
2. Provide feedback (instrumentation) on the status
3. Provide more feedback on actions driven by the status (yep, more instrumentation)
Part three includes tables and views in the mix:
Click the “New Query” button in SSMS and add the following T-SQL:
Part four includes stored procedures:
Note the DDL to manage stored procedures is very similar to the DDL for managing views.
If all goes according to plan, the first execution of the s.i DDL T-SQL statement should generate the following messages:
Andy also shows how to use SQLCMD to create a proper deployment script.
Comments closedThe Hadoop in Real World team has a one-liner for us:
A quick answer that might come to your mind is to call the count() function on the dataframe and check if the count is greater than 0. count() on a dataframe with a lot of records is super inefficient.
count() will do a global count of records in the dataframe from all partitions and then add all the intermediate counts together to get the final count. You will find this approach very slow for big dataframes.
Click through for a much faster one-liner.
Comments closedTomaz Kastrun is ready for Silvester:
New Year’s eve is almost here and what best way to celebrate with fireworks. Snap, pop, crack, boom. This is the most peaceful, animal friendly, harmless, eco, children friendly, no-fire-needed, educative and nifty fireworks.
To get the fireworks, fire up the following R function.
I mean, but I enjoy fire… Though you could launch these in R and save the good stuff for the 4th of July.
Comments closedI troubleshoot an unfortunate error message:
Based on this error message, the command is timing out after one minute. My initial thought is that there must be a network restriction preventing me from communicating with Key Vault, and I know what my first answer is.
This story even has a red herring, which means it’s a set of junior sleuths and a talking dog away from being a ’90s cartoon.
Comments closedWolfgang Strasser sees a new role:
I’ve spotted a new Azure Purview permission level – Policy Authors. This new permission level is connected to the new Data Access Policy Management in Purview (as of today 2021-12-30 in preview; https://docs.microsoft.com/en-us/azure/purview/how-to-access-policies-storage)
Click through for a little bit more detail.
Comments closedI was contacted by a lawyer that was using a 3rd party application to store emails, keep track of time, etc.
The backend of the application is SQL Server Express edition, which has a hard limit of 10GB for the data file.
One quick note for people with lots of LOB data, remember to reorganize with LOB_COMPACTION = ON as that’s the only way to be sure. Also, depending on how old the version of SQL Server is, there was a bug with LOB compaction which affected SQL Server 2014 and earlier. But, uh, hopefully you’re patched past that point…
Also, getting up to 2016 SP1 means that Express Edition gets data compression. It wouldn’t directly help in this case, but if you have a lot of non-LOB data on Express Edition, it can work wonders, for some definition of “wonders.” After all, if you’re using Express Edition, wonders are by definition pretty small.
Comments closedImke Feldmann has another function to implement:
The Excel NORM.INV function returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. So unlike the NORM.DIST function, that returns the probability of a threshold value to occur under the normal distribution (in CDF mode), this function returns the threshold value that matches a given probability.
Click through for the function definition.
Comments closedI have a post borne from frustration:
I have an Azure Synapse Analytics workspace which uses a managed virtual network and includes data exfiltration protection. I also have a Spark pool. My goal is to import a few packages and use them in a Spark notebook.
Doing so is pretty easy from the Synapse workspace. I navigate to the Manage hub and then choose Apache Spark pools from the Analytics pools menu. Select the ellipsis for my Spark pool and then choose Packages.
From there, because I plan to update Python packages, I can upload a requirements.txt file and have Pip do its job.
But then it doesn’t… Click through to learn why, as well as the workaround for this. It’s stuff like this which makes me say data exfiltration protection is a feature administrators will (mostly) like and developers will hate. Especially because there’s no obvious indicator why this was happening in the error message itself.
Comments closed