A Fine Slice Of SQL Server
This post is inspired by an odd situation I ran into in a project I’m working on. I have the need to pull specific revisions of files out of a git repository, save those files, and then execute the contents. This all worked fine until it didn’t. I received some complaints that unicode characters in the files we getting mangled, and sure enough they were. But why? In this post I’ll explain what happened to me, and ways you can avoid it yourself.
Read on to learn how.
Comments closedJeff Iannucci has a script which removes ad hoc plans from the Query Store:
Now, rather than being my usual rambling self I want to be very direct here: this solution will NOT give you the same behavior as “optimize for ad hoc workloads.” That setting keeps query info without the plan during the first execution, but then keeps the plan after the second execution.
That’s kinda like a surgeon with a scalpel. What is below is much more drastic. We’re going to break out a chainsaw for Query Store.
Chainsaw solutions to scalpel problems? Now you have my interest.
Comments closedMax Vernon breaks out the internals toolbag:
Occasionally I’ve needed to determine the physical location of a row stored in SQL Server. The code in this post uses the undocumented feature,
%%PHYSLOC%%, which returns a binary representation in hexadecimal of the location of each row returned in aSELECTstatement. The system table valued function,fn_PhysLocCracker, is used to decode the binary value returned by%%PHYSLOC%%to provide thefile_id,page_id, andslot_idfor each row.
Read on for a demo. Unlike most demos of this sort, Max is using a partitioned table, so that’s something new.
Comments closedIn most of my presentations all around the world, I still get this question often: “Is there a Read-Only license for Power BI?”, and often starts with “I have some end-users, who are not building any reports, I don’t want to pay for Developer License for them”. I have written about Licensing in Power BI previously, however, I believe that the article is not explaining it clearly enough and there are still some questions around it. So here I am going to talk about this only: The Read-Only license for Power BI.
Read on for the answers. It’s not all terrible news, but at the very low end, the answer isn’t great.
Comments closedAdam Conway, et al, review Gartner’s 2020 Data Science and Machine Learning Platforms Magic Quadrant:
Gartner has released its 2020 Data Science and Machine Learning Platforms
Magic Quadrant, and we are excited to announce that Databricks has been recognized as a Leader.
Click through for the quadrant and explanation. It’s an interesting set of results, that’s for sure.
Comments closedJon Morisi has a primer on Sqoop:
In this article, I’ll walk through using Sqoop to import data to Hadoop (HDFS).
“Apache Sqoop(TM) is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.”
With respect to SQL Server, Sqoop has two good use cases: pulling data from SQL Server into HDFS, and pulling data from HDFS into a staging table in SQL Server.
Comments closedI have a post covering some important things to keep in mind when reviewing a regression:
The Line is NOT the Data
One of the worst things we can do as data analysts is to interpret a regression line as the most important thing on a visual. The important thing here is the per-state set of data points, but our eyes are drawn to the line. The line mentally replaces the data, but in doing so, we lose the noise. And boy, is there a lot of noise.
This was my first point, but I think it’s the most important one to keep in mind: just because we draw a line and there’s a best fit doesn’t mean that fit is actually any good. And if the fit isn’t any good, the line is…optimistic with regard to how informative it is.
Comments closedDavid Eldersveld diagnoses a weird issue:
Recently when working with a table in Azure SQL Database, I came across an issue that manifested itself in Power Apps in a few ways.
The message stated that the data source is read-only, when I knew that my SQL credentials had the correct permissions to insert and update table records. I was successfully doing this with other tables, so SQL was not the issue.
Attempting to use functions like Remove and Patch provided the message, as did Form.Edit.
The solution kind of makes sense after the fact but there’s no way I’d have guessed it.
Comments closedBrent Ozar shows why the StartDate + EndDate pattern is not great for filtering:
If all you need to do is look up the memberships for a specific UserId, and you know the UserId, then it’s a piece of cake. You put a nonclustered index on UserId, and call it a day.
But what if you frequently need to pull all of the memberships that were active on a specific date? That’s where performance tuning gets hard: when you don’t know the UserId, and even worse, you can’t predict the date/time you’re looking up, or if it’s always Right Now.
This is where I advocate pivoting to a series of event records, so instead of a start date and end date, you have an event type (started, expired, cancelled, etc.) and a date. There are other alternatives as well, but it’s a good thought exercise.
Comments closedPhil Factor dives into sys.dm_exec_describe_first_result_set_for_object():
I’ve been working on a project unkindly nicknamed ‘The Gloop’ because the code is a bit amorphous. Basically, it is an approach to documenting SQL Server databases, using the facilities provided such as the metadata views and DMFs. Although it is relatively simple to record the result returned by a table-valued function I’d rather neglected the stored procedures because there was no metadata that could produce the first result set produced by a procedure or trigger.
I’d been silly because there is, of course, an Execution system DMF that does it: sys.dm_exec_describe_first_result_set_for_object(). it takes as its parameter the object_id of a procedure or trigger and describes the first result metadata for the module with that ID. It has the same result set definition as sys.dm_exec_describe_first_result_set.
I’m going to have to try this out, but I have problems with sp_describe_first_result_set and how it blows up if you use a temp table in the stored procedure whose result set you’re grabbing.