Press "Enter" to skip to content

Author: Kevin Feasel

Naming Graph Edges

Greg Low is trying to find a common nomenclature for edges in graphs:

Positive (Forward) Direction

I’d also like to see the tables use a forward direction naming rather than reverse (like “Written By”). So perhaps:

($from_id) the member Wrote the post ($to_id)

($from_id) who Likes who/what ($to_id)

($from_id) the reply to the main post RepliesTo the main post ($to_id)

Avoid passive voice.  That’s good advice in general.

Comments closed

Maintenance Plans In SQL Server 2016

Kendra Little describes some changes to maintenance plans in SQL Server 2016:

Working with maintenance plans is supposed to be easy, but I find it to be quite difficult.

In part, this is because they can often be buggy. To write this post today, I had to go back and install SSMS 16.5, because I wasn’t able to configure logging or change some aspects about my test maintenance plan in SSMS 17. (I use case sensitive instances, and this bug also impacts things like the maintenance plan log dialog box.)

And in part this is because the documentation for maintenance plans doesn’t tend to be as clear as the documentation for TSQL commands. So in the interest of saving other folks time, I wanted to share what I learned about the Rebuild Index Task, Reorganize Index Task, and Update Statistics Task in SQL Server 2016.

Though if you’re reading this, you probably already have a better solution than maintenance plans…

Comments closed

Database Connection Leaks

Michael J. Swart explains how to find database connection leaks:

So, if your application experiences connection timeouts because of a database connection leak, the stack traces may not help you. Just like an out-of-memory exception due to a memory leak the stack trace has information about the victim, but not the root cause. So where can you go to find the leak?

Even though database connection leaks are a client problem, you can find help from the database server. On the database server, look at connections per process per database to get a rough estimate of the size of each pool:

This is a good thing to remember, particularly if you have a busy system.

Comments closed

Interactive CallBimlScript

Ben Weissman introduces a feature coming to BimlExpress:

So far, there wasn’t much you could do but either replicate a lot of logic, parse the XML returned, use countless annotations or have many many different versions of your callee.

All of these options aren’t too appealing, right?

But: help is here! In addition to the known CallBimlScript, you can now use: CallBimlScriptWithOutput! It allows you to make use of a dynamic object to be returned by the CallBimlScript (in addition to the Biml code) which effectively means: you can return any kind of information back to the caller.

I can see this being useful for debugging and for centralizing flow logic a little bit better.  Click through for a sample script.

Comments closed

Regularization Prevents Overfitting

Hui Li has an explanation of what regularization is and how it works to reduce the likelihood of overfitting training data:

Assume that the red line is the regression model we learn from the training data set. It can be seen that the learned model fits the training data set perfectly, while it cannot generalize well to the data not included in the training set. There are several ways to avoid the problem of overfitting.

To remedy this problem, we could:

  • Get more training examples.
  • Use a simple predictor.
  • Select a subsample of features.

In this blog post, we focus on the second and third ways to avoid overfitting by introducing regularization on the parameters βi of the model.

Read the whole thing.

Comments closed

R’s iGraph + SQL Server Graphs

Dennes Torres has a post which shows how to use R’s iGraph library to visualize graphs created in SQL Server 2017:

The possibility to use both technologies together is very interesting. Using graph objects we can store relationships between elements, for example, relationships between forum members. Using R scripts we can build a cluster graph from the stored graph information, illustrating the relationships in the graph.

The script below creates a database for our example with a subset of the objects used in my article and a few more relationship records between the forum members.

Click through for the script.

Comments closed

Tidygraph

Thomas Lin Pedersen announces tidygraph, a tidyverse library for dealing with graphs and trees in R:

One of the simplest concepts when computing graph based values is that of
centrality, i.e. how central is a node or edge in the graph. As this
definition is inherently vague, a lot of different centrality scores exists that
all treat the concept of central a bit different. One of the famous ones is
the pagerank algorithm that was powering Google Search in the beginning.
tidygraph currently has 11 different centrality measures and all of these are
prefixed with centrality_* for easy discoverability. All of them returns a
numeric vector matching the nodes (or edges in the case of
centrality_edge_betweenness()).

This is a big project and is definitely interesting if you’re looking at analyzing graph data.

Comments closed

Dynamic Unpivoting For Change Detection

Shane O’Neill has a script that dynamically unpivots a pair of rows and compares values column by column, storing the changes in XML:

Overall, the script is longer at nearly double the lines but where it shines is when adding new columns.
To include new columns, just add them to the table; to exclude them, just add in a filter clause.

So, potentially, if every column in this table is to be tracked and we add columns all the way up to 1,024 columns, this code will not increase.
Old way: at least 6,144.
New way: at least 2,048.
Dynamic: no change

Read on for that script.  Even though his developer ended up not using his solution, Shane has made it available for the rest of the world so that some day, someone else can have the maintenance nightmare of trying to root out a bug in the process.

Comments closed

System Health XE

Kenneth Fisher describes what is in the system_health Extended Events session:

Per BOL you get the following information:

  • Errors with a severity of >= 20.

  • Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902).

  • Non-yielding scheduler problems (Error 17883).

  • Deadlocks.

  • Sessions that have waited on locks for > 30 seconds.

  • Sessions waiting for a long time on preemptive waits (waits on external API calls).

Read on to learn more of the things this session contains as well as a couple ways you can access the data.

Comments closed