Press "Enter" to skip to content

Author: Kevin Feasel

Interpretability Issues in Monitoring Tools

Published 2020-01-29 by Kevin Feasel

Brent Ozar explains how it’s hard to explain things without context:

However, outside of that window, you may not have any wait time on CPU at all. If queries are simple enough, and there isn’t enough concurrency, then as we discuss in Mastering Server Tuning, you can end up with SOS_SCHEDULER_YIELD waits with no time attached to them. You wouldn’t notice a CPU problem at all outside of that 8AM window because you don’t have a CPU bottleneck – yet. And to be fair, you probably wouldn’t tune a server in this kind of shape, either.

But when you DO need to tune a server that isn’t running at 100% capacity, picking the right query to tune isn’t just about wait stats: it’s also about which queries you need to be more responsive, and how you need to plan for future load. If your company tries to run a flash sale, and a lot of folks try to check out at the same time, Query T is going to knock your server over. But wait-stats based tools won’t see that coming: they’ll still be focused on Query R, the only one that spends a lot of time waiting on CPU.

Good food for thought.

Comments closed

Features for Securing Data in SQL Server

Published 2020-01-29 by Kevin Feasel

Jonathan Kehayias gives us a set of features we can use to secure data in SQL Server:

Recently I have been having quite a few discussions around security of data inside of SQL Server and how to prevent the massive data breaches that we have been hearing about on the news.  Like most things some people want “THE ANSWER” or THE SOLUTION to securing the data inside of SQL Server. Unfortunately there isn’t a single solution that solves all of the problems that are potentially out there.  Security of data requires defense in depth, starting with a secure configuration and installation of SQL Server.  Often times, defense in depth also means changes to the database, the application, and how you do business.

Also read the comments to understand why Dynamic Data Masking and Row-Level Security aren’t really security features. I totally agree that Dynamic Data Masking is not a security feature; Row-Level Security I’d argue is, but is quite limited in its scope, much more so than any of the features Jonathan lists.

Comments closed

Transitivity and Query Tuning

Published 2020-01-29 by Kevin Feasel

Jared Poche talks about the ability to use either predicate on a join for filtering:

You may recall the transitive property from elementary school math class. It states:

If A = B, and B = C, then A = C

The SQL Server optimizer can and will use this property, and it can lead to issues in your queries. When I’m writing a query, I have a clear idea of how I want it to operate. But using the transitive property, SQL Server has additional options one might not expect, and this may occasionally cause things to go awry

Click through for an example. Most of the time, the optimizer is smart about using transitivity, but sometimes it can go wrong.

Comments closed

Diagnosing and Resolving CPU Issues

Published 2020-01-29 by Kevin Feasel

Jeff Iannucci has a story to tell:

Anyhow, [Erik] Darling’s tool is designed to provide five data sets showing a quick view of memory and processor usage based on several dynamic management views (DMVs). It’s gives you a quick look into what’s going on when the CPU and/or memory of your SQL Server instance are feeling discomfort.

Why do I mention this? Because little did I know the very next day I would be presented with a fine opportunity to use this tool. And by opportunity I mean a server on fire. (Well, not literally, but still…)

Click through for a description of sp_pressure_detector and a testimonial of its utility.

Comments closed

Finding Tables with High Write Frequency

Published 2020-01-29 by Kevin Feasel

Michael J Swart has an interesting query for us:

You have excessive WRITELOG waits (or HADR_SYNC_COMMIT waits) and among other things, you want to understand where.

Microsoft’s advice Diagnosing Transaction Log Performance Issues and Limits of the Log Manager remains a great resource. They tell you to use perfmon to look at the log bytes flushed/sec counter (in the SQL Server:Databases object) to see which database is being written to so much.

After identifying a database you’re curious about, you may want to drill down further. I wrote about this problem earlier in Tackle WRITELOG Waits Using the Transaction Log and Extended Events. The query I wrote for that post combines results of an extended events session with the transaction log in order to identify which procedures are doing the most writing.

But there are times when you just want a quick and dirty script, and that’s what Michael has for us today.

Comments closed

Data Lake Storage and Data Processing

Published 2020-01-28 by Kevin Feasel

Ashish Kumar has started a series on data lake essentials:

Data Lake architecture is all about storing large amounts of data which can be structured, semi-structured or unstructured, e.g. web server logs, RDBMS data, NoSql data, social media, sensors, IoT data and third-party data. A data lake can store the data in the same format as its source systems or transform it before storing.

The main purpose of a data lake is to make organizational data from different sources, accessible to a variety of end users like business analysts, data engineers, data scientists, product managers, executives, etc, in order to enable these personas to leverage insights in a cost-effective manner, for improved business performance. Today, many forms of advanced analytics are only possible on data lakes.

Click through for more information on what a data lake should provide—whether that be in-house or a cloud provider.

Comments closed

ACID Transactions with Cosmos DB

Published 2020-01-28 by Kevin Feasel

Hasan Savran shows how you can use the Cosmos DB SDK to create ACID transactions:

What about Azure Cosmos DB? It’s a NoSQL database, probably you can’t do ACID Transactions right? WRONG! Azure Cosmos DB has been supporting ACID transaction for some time now. We were able to create ACID transactions by using stored procedures of Cosmos DB. Last year (2019) Cosmos DB team introduced ACID transactions to Cosmos DB SDK. Now, we can create transactions by using C# just like writing transactions by using SQLClient class for SQL Server!

      To create an ACID transaction in Cosmos DB SDK, we need to use TransactionalBatch object. You need add all operations in transaction to TransactionalBatch object. All the operations attached to the TransactionalBatch object must share the same partition key. In the following example, I created three objects and attach them to TransactionalBatch object. To start the transaction, I ran the ExecuteAsync() function.  This function runs the transaction and returns the responses for each operation.

I’d think you would need to set a strong consistency level as well.

Comments closed

Fixing Screen Repainting Issues in SSMS

Published 2020-01-28 by Kevin Feasel

Greg Low has a workaround for an annoying problem:

Once again, I’m seeing lots of customers reporting screen repainting issues in SQL Server Management Studio (SSMS). It mostly seems to affect version 18 but I’ve also seen it in version 17. And it’s most prevalent on Windows 10.

The typical issue is that you click on another open tab, and the contents of the tab doesn’t repaint. You are still seeing the previous tab. If you click into the tab, you start to see bits from both tabs.

Click through to see the fix. I’ve seen this issue pop up though I don’t remember seeing it on the latest version of SSMS 18…though now that I say that, I’m guaranteed to have the problem hit me today.

Comments closed

Financial Statements in Power BI

Published 2020-01-28 by Kevin Feasel

Joseph Yeates has started a series on creating financial statements in Power BI:

This post is part 1 in my series on creating financial statements in Power BI! I’m starting with creating an Income Statement. The source data and Power BI file used in the example below can be found here.

I loaded the source data into the Power BI report. It consisted of three tables:

– Fact table: contains dollar amount of transactions
– GL table: contains categorization of transactions
– Calendar table: contains date information for the data model

This will be interesting to watch, especially because this kind of task is generally handled in a tool like Reporting Services instead of Power BI.

Comments closed