Press "Enter" to skip to content

Author: Kevin Feasel

Thoughts On Exclusive Locks

Published 2018-12-07 by Kevin Feasel

Louis Davidson shares some thoughts on exclusive locks in SQL Server:


You will find that the SELECT statement executes, ignoring the exclusive lock, because it is not a write lock, and the data on the page has not been changed.
The main reason people try to do this is to force access to a row in a single threaded manner. For example, building their own sequence number, either in a row they update, or by trying to do MAX() on all of the data in a table to make sure only one reader gets the same value.
This is generally a bad idea, since locking an entire table is a generally bad idea, but if you needed to block readers, you can couple the XLOCK with a PAGLOCK. So, change the first reader to:

BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test WITH (XLOCK,PAGLOCK);

As Louis points out in the summary, locking is complicated.  Having a good understanding of the locking model will serve you well, though.

Comments closed

NoSQL? No! MoSQL

Published 2018-12-07 by Kevin Feasel

Steve Jones points out a bit of a shift at Google:


Google is doing more SQL, or at least shifting towards relational SQL databases as a way of storing data. At least, some of their engineers see this as a better way to store data for many problems. Since I’m a relational database advocate, I found this to be interesting.
When Google first started to publish information on BigTable and other new ways of dealing with large amounts of data, I felt that these weren’t solutions I’d use or problems that many people had. The idea of Map Reduce is interesting and certainly applicable to the problem space Google had of a global database of sites, but that’s not a problem I’ve ever encountered. Instead, most of the struggles I’ve had with relational systems are still better addressed in a relational system.

Read the whole thing.  Note that this is slightly different than Feasel’s Law, as Steve is focusing more on the consistency side of things rather than the interface.

Also, just going to leave this here:

Comments closed

When Azure SQL DB Tells You You’re Hitting Your DTU Limits

Published 2018-12-07 by Kevin Feasel

Brent Ozar shows us how to use sp_BlitzFirst to determine if you’re hitting DTU limits:


You’ve got an Azure SQL DB, and your queries are going slow. You’re wondering, “Am I hitting the performance limits? Is Microsoft throttling my queries?”
There’s an easy way to check: run sp_BlitzFirst. sp_BlitzFirst is our free performance health check stored procedure that analyzes a lot of common performance issues and then gives you a prioritized list of reasons why your server might be slow right now.

Click through for an example.  The same signal can mean “it’s time to move to a higher level in your tier,” “it’s time to move up to the next tier,” or “it’s time to tune that cursor in a cursor in a cursor which performs several scans of a very large table for each operation.”

Comments closed

Finding The SQL Server Port With T-SQL

Published 2018-12-07 by Kevin Feasel

Jack Vamvas shows us how to find the port SQL Server is listening on using T-SQL:


Question: Without going into the SQL Server Configuration manager via the GUI is there a command oriented method to extract the port number SQL Server is listening on?
 
Answer: There are a few different methods to extract the port number without going into the Configuration interface.
Method 1 – use xp_readererrorlog

Read on for an explanation of this technique as well as links to a couple other methods.  I hadn’t thought about using the error log as a source, but it works.

Comments closed

Preventing Credential Compromise When Using AWS

Published 2018-12-06 by Kevin Feasel

Will Bengtston walks us through techniques Netflix uses to protect credentials in AWS:

Scope

In this post, we’ll discuss how to prevent or mitigate compromise of credentials due to certain classes of vulnerabilities such as Server Side Request Forgery (SSRF) and XML External Entity (XXE) injection. If an attacker has remote code execution (RCE) or local presence on the AWS server, these methods discussed will not prevent compromise. For more information on how the AWS services mentioned work, see the Background section at the end of this post.

Protecting Your Credentials

There are many ways that you can protect your AWS temporary credentials. The two methods covered here are:

  • Enforcing where API calls are allowed to originate from.

  • Protecting the EC2 Metadata service so that credentials cannot be retrieved via a vulnerability in an application such as Server Side Request Forgery (SSRF).

Read the whole thing if you’re an AWS user.

Comments closed

Indexed View Matching

Published 2018-12-06 by Kevin Feasel

Erik Darling has a series of posts on indexed views, with the latest covering query matching even when using a keyword in creation of the indexed view itself:

There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT.

Fair enough, but you can do GROUP BY.

And what’s pretty cool is that the optimizer can match a query written to find distinct values to an indexed view with a group by.

Click through for the best example ever.

Comments closed

Continuing The Advent Of Code In T-SQL

Published 2018-12-06 by Kevin Feasel

Wayne Sheffield has a few more posts in the Advent of Code series.  His latest edition:

In Day 5, we find ourselves working with the polymers of the new Santa suit. A polymer (the input file), consists of units, represented by upper and lower case letters. Adjacent units of the same letter, but of different polarity (case), cancel each other out. This may lead to other units that can then cancel each other out. The goal is to reduce the polymer to as small as possible, and report back the reduced size.

Tasks:

  1. Perform a case-sensitive search/replace for each letter of the alphabet. The search is for a pair of the same letter, where one is upper case, and the other is lower case.
  2. Recursively perform this operation until the string can no longer be reduced.

In my opinion, the key part to this is that the operation needs to be performed recursively. I can think of only two ways to recursively perform an operation in SQL Server:

  1. A recursive common table expression (cte).
  2. Using a WHILE loop.

I don’t like using either of these mechanisms in SQL Server – they both perform operations in a “Row-By-Agonizing-Row” method, instead of a more set-based approach. However, set-based recursion usually performs extremely poorly. So, I’m going to use a while loop.

The recursion requirement does limit things a bit; otherwise I could see putting something together with the LEAD() window function.

Comments closed

Cross-Availability Group Login Management

Published 2018-12-06 by Kevin Feasel

David Fowler walks us through a problem about orphaned users and Availability Groups:

Now, I’m pretty sure that most of us will have been in the position where, after a fail-over we get inundated with calls, emails, Skype messages and carrier pigeon drops letting us know that so and so can no longer access the database.

When you look into it, you either find that the login never existed in the first place, so you create it or that it was there but the database user has become orphaned from it (happens when the login SID doesn’t match the SID of the database user, Adrian wrote about orphaned users in Dude where’s my access?).

You remap the orphaned user and everything is good again…  that is until the next time you failover and once again you’ll be hit with the same orphaned user problem.

Click through for the explanation and a permanent fix for this issue.

Comments closed

Connecting Power BI To Dockerized SQL Server

Published 2018-12-06 by Kevin Feasel

Chris Taylor shows us how to build a SQL Server on Linux Docker container and use it to supply data to a Power BI dashboard:

I (and many others) have done a series of docker blog posts over the last couple of years but they’ve all tended to evolve around spinning up a SQL Server 2017+ container for testing or demo purposes. This is only really the start, think of the bigger picture here, once you have your database environment the world is your oyster.

This blog post will show how we can use SQL Server 2019 CTP2.1 running on Linux (Ubuntu) in a docker container as our data source for a Power BI environment in next to no time!

These steps show a very manual process for completing this setup, if it is something you are looking to do frequently then I suggest creating a Dockerfile and/or yml file and use docker-compose. This way you can have all your setup in one file and it will be a single statement to get your SQL Server 2019 environment up and running.

Read on for the demo.

Comments closed