Press "Enter" to skip to content

Curated SQL Posts

Schema Modification Locks With CC Compliance

Published 2017-05-04 by Kevin Feasel

Lori Brown explains why you might see schema modification locks after enabling Common Criteria compliance:

We have a client who has no idea how or when Common Criteria was enabled on their production system. All they know is that performance has been slowly degrading. After collecting performance data, we found that there were high LCK_M_SCH_M waits which is a schema modification lock that prevents access to a table while a DDL operation occurs. We also found blocked process records where a LOGIN_STATS table in the master database was waiting a lot. This table is used to hold login statistics. When there are a lot of logins and outs there can be contention in this table.

When you enable Common Criteria compliance, something called Residual Information Protection (RIP) is enabled. RIP is an additional security measure for memory and it makes it so that in memory a specific bit pattern must be present before memory can be reallocated(overwritten) to a new resource or login. So with lots of logins and outs, there is a performance hit in memory because overwriting the memory allocation has to be done.

It’s worth reading the whole thing.

Comments closed

Stop Using Domain Admin!

Published 2017-05-04 by Kevin Feasel

Sean McCown has had it with people using Domain Admin accounts as service accounts:

If you paid close attention, you’ll notice the ‘DomainAdmin’ portion of that name.  Yep, you got it right… they were running SSRS under the domain admin account.  The Windows guy thought that it would be too much trouble to manage the permissions and get everything right on all the shares and DBs that it needed to access.

So this is when I pretty much lost it.  These guys were running  SSRS under a domain admin account because they were too lazy to do the right thing.  It’s unthinkable.  There may be some reasonable excuses why you’re not able to change your current security model to something better.  You may even be able to convince me that you’re not just being lazy.  But to actively be lazy about your security isn’t something I’m going to take lying down.  Hey, I know it’s your shop, and I know you can ultimately do whatever you like, but I’m going to make sure you know what you’re doing.

Your SQL Server (and related) service accounts should not be Domain Admin.  Period.  This isn’t one of those “Well, it depends…” types of scenarios; there is no reason ever to use an account with Domain Admin rights as a SQL Server service account, and it is security malpractice to do so.

Comments closed

Interleaved Execution

Published 2017-05-04 by Kevin Feasel

Arun Sirpal looks at how Interleaved Execution affects table cardinality estimates with multi-statement table-valued functions in SQL Server 2017:

Joe states in the article “MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions. Interleaved execution will help workload performance issues that are due to these fixed cardinality estimates associated with multi-statement table valued functions.”

This is exactly what I saw where the below is just a basic screen shot of 1 of many tests that I carried out.

Read the whole thing for more details.

Comments closed

Deeper Into Adaptive Join Optimization

Published 2017-05-04 by Kevin Feasel

Erik Darling has a couple blog posts getting deeper into Adaptive Join Optimizations in SQL Server 2017.  First, Erik discusses the basics:

You see, in every plan, you see both possible paths the optimizer could have chosen. Right now it’s only limited to two choices, Nested Loops and Hash Joins.

Just guessing that Merge Joins weren’t added because there would have been additional considerations around the potential cost of a Sort operation to get the data in order.

Be sure to read Brent’s comment that in the initial release, it will just support columnstore indexes.  Then, Erik talks about execution plan details:

Some points of interest:

  • Actual Join Type: doesn’t tell you whether it chose Hash or Nested Loops
  • Estimated Join Type: Probably does
  • Adaptive Threshold Rows: If the number of rows crosses this boundary, Join choice will change. Over will be Hash, under will be Nested Loops.

The rest is fairly self-explanatory and is the usual stuff in query plans.

Good stuff here.

Comments closed

Power BI Report Server

Published 2017-05-04 by Kevin Feasel

The Power BI team knows how to make me happy:

Today Microsoft announced Power BI Premium — a capacity-based licensing model that increases flexibility for how users access, share and distribute content in Power BI. The new offering also introduces the ability to manage Power BI Reports on-premises with the included Power BI Report Server.

Power BI Report Server will be generally available late in the second quarter of 2017.

I like this a lot for internal company dashboards.

Comments closed

Database Role Members

Published 2017-05-03 by Kevin Feasel

Nate Johnson has a quick demo showing how to add users to database roles:

The “old” method involves calling system stored-procedures, sp_addrolemember and sp_droprolemember, in which you pass the role-name and username.  The “new” method, supported starting with SQL 2012, is to use the command-phrases ALTER ROLE [role] ADD MEMBER [user], and ALTER ROLE [role] DROP MEMBER [user].

Nate shows both methods, so check it out.

Comments closed

SQL As A Limiting Agent

Published 2017-05-03 by Kevin Feasel

Bert Wagner has advice for application developers:

Basically, if you are running code similar to above, the reason your job is slow is because you are not optimizing where your work is being performed:

  • Every time you write SELECT * you probably are bringing back more data than you actually need — you are hurting your performance.

  • Every time you don’t have a WHERE clause, you are hurting your performance.

  • Every time your process queries the database multiple times (ie. multiple SELECT statements in your job to bring back data), you are hurting your performance.

It’s nothing new for data professionals, but for application developers who avoid the database as much as possible due to a lack of knowledge, this might be a good wake-up call.

Comments closed

Temp Table Caching

Published 2017-05-03 by Kevin Feasel

Paul White explains how to cache temporary objects:

Table variables and local temporary tables are both capable of being cached. To qualify for caching, a local temporary table or table variable must be created in a module:

  • Stored procedure (including a temporary stored procedure)
  • Trigger
  • Multi-statement table-valued function
  • Scalar user-defined function

The return value of a multi-statement table-valued function is a table variable, which may itself be cached. Table-valued parameters (which are also table variables) can be cached when the parameter is sent from a client application, for example in .NET code using SqlDbType.Structured. When the statement is parameterized, table-valued parameter structures can only be cached on SQL Server 2012 or later.

The first time I heard about this was a SQL Saturday presentation that Eddie Wuerch did.  Paul does a great job talking about the requirements (and noting that table variables are eligible as well), making this well worth the time to read.

Comments closed

Advice For A Budding Data Scientist

Published 2017-05-03 by Kevin Feasel

Charles Parker riffs off of an Edsger Dijkstra note:

It’s still early days for machine learning. The bounds and guidelines about what is possible or likely are still unknown in a lot of places, and bigger projects that test more of those limitations are more likely to fail. As a fledgling data engineer, especially in the industry, it’s almost certainly the more prudent course to go for the “low-hanging fruit” — easy-to-find optimizations that have real world impact for your organization. This is the way to build trust among skeptical colleagues and also the way to figure out where those boundaries are, both for the field and for yourself.

As a personal example, I was once on a project where we worked with failure data from large machines with many components. The obvious and difficult problem was to use regression analysis to predict the time to failure for a given part. I had some success with this, but nothing that ever made it to production. However, a simple clustering analysis that grouped machines by the frequency of replacement for all parts had some lasting impact; this enabled the organization to “red flag” machines that fell into “high replacement” group where the users may have been misusing the machines and bring these users in for training.

There’s some good advice.  Also read the linked Dijkstra note; even in bullet point form, he was a brilliant guy.

Comments closed

Using Talend To Build Shape Files

Published 2017-05-03 by Kevin Feasel

Paul Hernandez has a demo where he uses Talend’s product to convert latitude and longitude pairs to a shape file:

Input data

Customers coordinates: a flat file containing x,y coordinates for every customer.

Municipalities in Austria: a shape file with multi-polygons defining the municipalities areas in Austria: source

Goal

The goal was to “look-up” the coordinates in the shape file in order to get the municipality code GKZ which in german stand for “Gemeindekennzahl”.

Check out the demo.

Comments closed