Creating A Spinlock Convoy

Lonny Niederstadt explains how two performance issues can intertwine in an interesting manner:

I’d seen systems that implemented both trace flags as startup parameters simultaneously.  I’d helped organizations implement first T8048, then T4199 (based on the timing of my research and testing of the trace flags).  This was the first time that there was a desire to implement the trace flags one-at-a-time and we had the choice of which to implement first.

I hadn’t chosen to put T8048 in first previously – that was just the way everything worked out.  If I chose to follow that order – I’d be doing what I’d seen and done before.  But… there was also a reason to choose the reverse order, with T4199 first.  Spinlock issues – especially at that time – were considered more exotic performance issues than many of the “plan-shaping” issues that trace flag 4199 addressed.  Many administrators were much more familiar with that type of performance issue – eliminating significant waits, altering plan shapes, making the logical work of queries more efficient – than with the busy wait/management overhead of spinlocks.  Sometimes demonstrating an improvement that someone is already familiar with evaluating is a plus, helping to gain trust.  I didn’t know of a specific reason NOT to put trace flag T4199 in place, followed by T8048 later.  And in this case it seemed like building up some interpersonal capital might be a good idea.

Thinking through the full ramifications of trace flag changes is hard, even for sharp people like Lonny.  Read on for the details of what happened next.

Always sa

James Anderson rails on inappropriate sa account usage:

I can already hear managers saying:

If you don’t trust your employees, why employ them in the first place?

Well there is the whole accidental damage thing. I guess you could cover that by having a good backup\restore process (if your RTO and RPO permitted the downtime) but don’t expect to pass any security audits coming your way. Hint: your clients wont like this.

Plus, supposing everybody knows the sa account, there’s no way to know who accidentally(?) dropped the customer database.

Understanding Neural Nets

David Smith links to a video which explains how neural networks do their thing:

In R, you can train a simple neural network with just a single hidden layer with the nnet package, which comes pre-installed with every R distribution. It’s a great place to start if you’re new to neural networks, but the deep learning applications call for more complex neural networks. R has several packages to check out here, including MXNetdarchdeepnet, and h2o: see this post for a comparison. The tensorflow package can also be used to implement various kinds of neural networks.

R makes it pretty easy to run one, though it then becomes important to understand regularization as a part of model tuning.

Tales Of Terror

Robert Davis shares some of the wackier moments of his career:

From the day we started the merge until the day I left, there was a WTF moment almost every day. One of the biggest issues I had with my new manager (the whole list is too long for a single blog post) was that he treated his team like unskilled laborers. He did not support them or back them up. They were expected to do anything the dev teams asked them to do any time of day or night. Holidays, weekends, whenever. If they asked to have some code deployed, the DBAs were expected to do it within half an hour during the day or within an hour any other time. They would often get 2 or 3 deployment requests overnight. 1 AM, 2 AM, 4 AM, whatever. And the person on-call not only did it within an hour every time, but they were also expected to be in the office for their normal 40 hours that week.

There’s a lot of crazy in these stories.

When To Store JSON In SQL Server

Bert Wagner thinks about which conditions should hold for it to make sense to store JSON in SQL Server:

Every once in a while I hear of some technologist say that relational databases are dead; instead, a non-table based NoSQL storage format is the way of the future. SQL Server 2016 introduced JSON functionality, making it possible for some “non-SQL” data storage to make its way into the traditionally tabled-based SQL Server.

Does this mean all data in SQL Server going forward should be stored in long JSON strings? No, that would be a terrible idea. There are instances when storing JSON in SQL Server is a good choice though. In this post I want to create recommendations for when data should be stored as JSON and when it shouldn’t.

Protip:  anyone who says relational databases are dead is already working with one strike against.  Bert has great use cases for JSON as well as a good understanding that there are plenty of anti-use cases, making his post well worth reading.

Recovering From Failure

Edwin Sarmiento shares three stories of failure:

The year was 2010. A consultant was working on designing and deploying System Center Operations Manager (OpsMgr) and System Center Configuration Manager (ConfigMgr) for a customer. The goal was to operationalize the enterprise software deployment process and systems monitoring. The servers were prepared, SQL Server was installed, OpsMgr and ConfigMgr configured. The project was in the stabilization phase waiting for the go-live schedule.

Except…

People fail; how much they learn from failures makes the difference.

Checking Server Specs

Koen Verbeeck had an issue with server power:

Let me tell you the story of a small BI project I did a couple of years back. The project building a small data mart for the local branch of a very large multinational in consumer goods. The data was gathered by the retailers in the country, who send the details back to the company for analyses of the sales performance. The data was to be hosted internally at the company, not at the client. However, the server was commissioned but not yet delivered. In the meantime, I would simply start developing on my own machine – which was quite a powerful laptop with 8 cores and 32GB of RAM – and we would migrate the solution once the server arrived.

Read on for what happened.  I was half-expecting “And that’s when my laptop became the new server” to be the punch line.

An Expensive String Length Calculator

Ewald Cress describes an Inner Platform Effect issue:

When you spend a reasonable amount of time churning out simple form-based UI workflows, you inevitably get bored. And you try and abstract away the drudgery of building forms into something the Common Folks can manage, by building a framework, because Lord knows you can’t buy this sort of thing off the shelf. Inevitably, the arcane knowledge of how to churn out expensive work without obviously doing expensive work can safely stay within an inner circle of senior developers who will be the only people ever allowed to try and configure a no-programming-needed workflow.

Never mind. Their hearts were in the right place.

Could be worse?  Maybe they could have run a WAITFOR for the number of seconds corresponding with the string’s length and then collected that information from a Windows event log entry.

Violating Swart’s Law

Kenneth Fisher notes that maxima are not aspirations:

It could be databases on an instance, indexes on a table, columns in a table, etc. etc. And in case you were wondering, you can get the answers here.

You see this come up every once in a while. A forum question, a question on #sqlhelp, even in *shudder* your own systems. And the answer always comes back the same: Limits are not goals! Usually accompanied by a few jokes.

For more on this, Michael Swart coined Swart’s Ten Percent Rule.

Everyone’s A Sysadmin

Raul Gonzalez has a script to see if you’re in a terrible security situation:

I’m not sure what it’d scare me more, if there are many [sysadmin] or just [sa] because the first one is scary, but the second involves to find out who knows the [sa] password and knowing who did what, can be a real pain in the neck.

One way or another, as I said, I want to know the different people and level of access to my server[s], so back in the day I created this stored procedure which now I want to share with you guys.

We can find all that info using DMV’s and in my case I use sys.server_principals, sys.server_role_members and sys.server_permissions and some stored procedure which I bet it’s not that well known, sys.xp_logininfo which help to get more granular picture from Windows AD Groups.

Click through for the script.

Categories

March 2017
MTWTFSS
« Feb  
 12345
6789101112
13141516171819
20212223242526
2728293031