A Fine Slice Of SQL Server
Max Trinidad has a post showing how to fix a busted master database in SQL Server on Linux:
For some reason, I finished doing an “sudo apt update” follow by the “sudo apt upgrade“, and after doing some OS updates “my SQL Server stopped working”. The following message was logged in the log event: (see Image)
Click through to see how to force a rebuild of master.
Comments closedHere we see a Seek Predicate that looks for OrderDate values between two values that have been worked out elsewhere in the plan, but creating a range in which the right values must exist. This isn’t >= 20110805 00:00 and < 20110806 00:00 (which is what I would’ve made it), it’s something else. The value for start of this range must be smaller than 20110805 00:00, because it’s >, not >=. All we can really say is that when someone within Microsoft implemented how the QO should respond to this kind of predicate, they gave it enough information to come up with what I call a “helper predicate.”
Now, I would love Microsoft to make more functions sargable, but that particular request was Closed long before they retired Connect.
But maybe what I mean is for them to make more helper predicates.
The problem with helper predicates is that they almost certainly read more rows than you want. But it’s still way better than looking through the whole index.
Read the whole thing.
Comments closedEugene Meidinger explains the difference between M and DAX as languages using a cooking metaphor:
I like to think of M as this sous chef. It does all the grunt work that we’l like to automate. Let’s say that my boss asks for a utilization report for all of the technicians. What steps am I doing to do in M?
- Extract the data from the line of business system
- Remove extraneous
- Rename columns
- Enrich the services table with a Billable / NonBillable column
- Generate a date table
This is all important work, but I would have to do the same work for a variety of reports. Many of the steps tell me nothing about the final product. I would generate a date table for most of my reports, for example.
I think the metaphor holds.
Comments closedStacia Varga has a post covering some of the yeoman’s work of data cleansing:
For now, Power BI continues to my tool of choice for my project. My goals for today’s post are two-fold: 1) finish my work to address missing venues in the games table and 2) to investigate the remaining anomalies in the games and scores tables as I noted in my last post.
To recap, I noted the following data values that warranted further investigation :
Total Goals minimum of 0 seems odd – because hockey games do not end in ties. I would expect a minimum of 0 so I need to determine why this number is appearing.
Total Goals maximum of 29 seems high – it implies that either one team really smoked the opposing team or that both teams scored highly. I’d like to see what those games look like and validate the accuracy.
Record Losses minimum of 0 seems odd also – that means at least one team has never had a losing season?
Similarly, Record Wins minimum of 0 means one team has never won?
Record OT minimum of 0 – I’m not sure how to interpret. I need to look.
Score minimum of 0 seems to imply the same thing as Total Goals minimum of 0, which I have already noted seems odd.
This is the kind of stuff that we talk about as taking 80-95% of a data science team’s time. It’s all about finding “weird” looking values, investigating those values, and determining whether the input data really was correct or if there was an issue.
Comments closedEven though automatic tuning wasn’t enabled, SQL Server picked up on the performance changes. I got a recommendation in sys.dm_db_tuning_recommendations.
- reason: Average query CPU time changed from 2127.84ms to 66291.9ms
- state: {“currentValue”:”Active”,”reason”:”AutomaticTuningOptionNotEnabled”}
The details also include the query id in question, and the plan_id of the “fast plan”.
It’s nice to check those out for a couple of weeks before turning automatic tuning on; that way, you can get more comfortable with the types of changes the tuning engine recommends, and if you happen to have a system which is terrible for automatic tuning, you can know that before turning the feature on.
Comments closedDavid Smith points out new reticulate package:
With reticulate, you can:
Import objects from Python, automatically converted into their equivalent R types. (For example, Pandas data frames become R data.frame objects, and NumPy arrays become R matrix objects.)
Import Python modules, and call their functions from R
Source Python scripts from R
Interactively run Python commands from the R command line
Combine R code and Python code (and output) in R Markdown documents, as shown in the snippet below
The first thing that came to mind when reading this was the implementation of the keras package in R and how it calls out to TensorFlow (written in Python). The ability to make R vs Python an “and” instead of an “or” proposition is quite powerful.
In this particular example, when you look at the codeword, actual data cells are 6(blue cells) & 3(red cells) are the parity cells which are simply obtained by multiplied our data cells to generation matrix.Storage failure can be recovered by the multiplying inverse of generator matrix with the extended codewords as long as ‘k’ out of ‘k+m’ cells are available.Therefore, here Data Durability is 3 as it can handle 2 simultaneous failure, Storage Efficiency is 67% (as we are using only 1 extra block i.e. 6/9) & only we need to store half number of cells as compared to original number of cells, we can conclude that we also have only 50% overhead in it.
It’s a good explanation of one of the biggest improvements to HDFS over the past several years.
Comments closedCedric Charlier shows how to ignore case when running Table.Distinct in Power Query:
Note that at the top of the table, I’ve twice the value ADO.NET, once with uppercase and once lowercase for the “Net” part. If I try to apply a
Table.Distinct, the two values will be considered as distinct and one of them won’t be removed.Will it be an issue? If this your key and it’s part of a model where this key is part of one side of a one-to-many then it will be an issue.
Read on for the solution.
Comments closedJose Mendes has an introduction to the Cosmos DB graph engine:
Gremlin is the graph traversal language of Apache TinkerPop, an open source Graph Computing Framework. Gremlin allows the users to write complex queries to traverse their graphs by using a composed sequence of steps, with each step performing an operation on the data stream (further details here). There are 4 fundamental steps:
· transform: transform the objects in the stream
· filter: remove objects from the stream
· sideEffect: pass the object, but yield some side effect
· branch: decide which step to take
Click through for a quick example showing how to create and populate a graph.
Comments closedLouis Davidson wants to see what he can cast to a bit type:
There are no other textual/alpha string values that will cast to a bit value, but the numeric values that will cast to a bit are voluminous (even some that are in string format). Consider the following eight statements:
SELECT CAST(100 AS bit);
SELECT CAST(-100 AS bit);
SELECT CAST(99999999999999999999999999999999999999 AS bit);
SELECT CAST(-99999999999999999999999999999999999999 AS bit);
SELECT CAST(88.999999 AS bit);
SELECT CAST('1' AS bit);
SELECT CAST('2' AS bit);
SELECT CAST('999999' AS bit);Danged if they didn’t all work, and all return 1.
Check out what else Louis tries to cast to a bit type.
Comments closed