Press "Enter" to skip to content

Month: October 2021

Measuring the Performance Impact of TDE

Published 2021-10-15 by Kevin Feasel

Manvendra Singh does the math:

Transparent Data Encryption (TDE) encrypts database files to secure your data. It also encrypts the tempdb database to secure your data in a temporary space. The process of encryption and decryption adds additional overhead to the database system. Even non-encrypted databases hosted on the same SQL Server instance would have some performance degradation because of tempdb encryption. Today I will show you performance impact analysis using few simple T-SQL statements by comparing their stats gathered before and after enabling TDE.

I will execute T-SQL statements (INSERT, UPDATE, SELECT, BACKUP DATABASE) before and after enabling encryption (Transparent Data Encryption) and gather their performance statistics during each execution. Finally, once we will have performance stats taken before and after enabling encryption then we will compare them to understand the performance impact analysis. I have used DBCC DROPCLEANBUFFERS before executing each query to clean the buffer cache.

Click through for the results. My rule of thumb is about a 5% performance overhead, but Manvendra shows us some of the particulars of what that means.

Comments closed

Enumerating Local Admins on a Computer with Powershell

Published 2021-10-15 by Kevin Feasel

Jess Pomfret has a Powershell snippet for us:

This morning I was working on pulling together some information which included whether certain accounts were in the local administrator’s group on some remote servers. I had the perfect snippet saved in my code repo so I was quickly able to answer that question – and then I realised I should share that with you all.

Click through for the script.

Comments closed

Organizing a Power BI Workspace: The Checklist

Published 2021-10-15 by Kevin Feasel

Melissa Coates has a plan:

How to organize workspaces in Power BI is one of those topics that comes up a lot. On one hand, it’s really easy to quickly create a workspace and keep moving. At the same time, it’s also really useful to have a strategy for how you scope your workspaces so they don’t get out of hand over time.

In this post & video we’re going to cover 4 sets of criteria to consider when planning for workspaces in the Power BI Service.

Click through for the video, as well as a post with the details.

Comments closed

Visual Studio Code, Markdown, and Snippets

Published 2021-10-15 by Kevin Feasel

Robert Cain takes us through Markdown and snippets in Visual Studio Code:

Seriously though, I do find this documentation language very useful, and easy to use. With just a few commands I can produce a nicely formatted document that can be displayed in my code editor, as well as on platforms like GitHub. I’ve even begun authoring these blog posts in Markdown.

A big reason for me is the ability to integrate it into my projects. VSCode, as well as the full blow Visual Studio, support Markdown (with of course the proper extensions installed). When I create a new PowerShell project in VSCode, I can store the projects documentation in Markdown format right alongside the PowerShell code.

By the way, two VS Code extensions I can highly recommend for their Markdown support are Markdown All in One and markdownlint.

Comments closed

Generating Markdown from SQL Server Metadata

Published 2021-10-15 by Kevin Feasel

Thomas Williams has built a documentation engine:

Sometimes there’s a need to create database documentation – an old fashioned list of tables, columns, views etc. – as part of delivering a project, or for analysts, project managers and end users.

In these situations I tend to automate. SQL Server is very “meta” in that it contains tables and records that describe, well, other tables and records. Coupled with the MS_Description extended property, it’s a powerful way to keep up-to-date documentation in close proximity to the code itself.

The script below will generate Markdown for database tables, views, stored procedures and functions. Column names and data types are listed for tables. View definitions are output. For stored procedures and functions, only the MS_Description extended property will be output.

Click through for a big disclaimer and a bigger script.

Comments closed

Fundamentals of Inline TVFs

Published 2021-10-15 by Kevin Feasel

Itzik Ben-Gan explains Inline Table-Valued Functions:

Compared to the previously covered named table expressions, iTVFs resemble mostly views. Like views, iTVFs are created as a permanent object in the database, and therefore are reusable by users who have permissions to interact with them. The main advantage iTVFs have compared to views is the fact that they support input parameters. So, the easiest way to describe an iTVF is as a parameterized view, although technically you create it with a CREATE FUNCTION statement and not with a CREATE VIEW statement.

It’s important not to confuse iTVFs with multi-statement table-valued functions (MSTVFs). The former is an inlinable named table expression based on a single query similar to a view and is the focus of this article. The latter is a programmatic module that returns a table variable as its output, with multi-statement flow in its body whose purpose is to fill the returned table variable with data.

Now that we have that sorted, click through to see examples and dive into performance ramifications.

Comments closed

Understanding Support Vector Machines

Published 2021-10-14 by Kevin Feasel

Luis Valencia takes us through the algorithm for support vector machines:

A support vector machine (SVM) is a supervised machine learning model that uses classification algorithms for two-group classification problem. Compared to newer algorithms like neural networks, they have two main advantages: higher speed and better performance with a limited number of samples (in the thousands).

Pepperidge Farms remembers when we used genetic algorithms to solve problems because support vector machines were too slow.

Comments closed

Supporting 100 Languages with Microsoft Translator

Published 2021-10-14 by Kevin Feasel

Krishna Doss Mohan and Jann Skotdal take us through the evolution of Microsoft Translator:

Today, we’re excited to announce that Microsoft Translator has added 12 new languages and dialects to the growing repertoire of Microsoft Azure Cognitive Services Translator, bringing us to a total of 103 languages!

The new languages, which are natively spoken by 84.6 million people, are Bashkir, Dhivehi, Georgian, Kyrgyz, Macedonian, Mongolian (Cyrillic), Mongolian (Traditional), Tatar, Tibetan, Turkmen, Uyghur, and Uzbek (Latin). With this release, the Translator service can translate text and documents to and from languages natively spoken by 5.66 billion people worldwide.

I’ve used the live translation service a few times. It’s a little clunky but it does work pretty well.

Comments closed

Scheduling Tasks in SQL Server Express Edition

Published 2021-10-14 by Kevin Feasel

Steve Stedman & co don’t need your SQL Agents:

One of the feature differences between SQL Server Express Edition and the other paid versions is the ability to schedule jobs or tasks using the SQL Server Agent.

Even if you are running SQL Server Express Edition, there is a need to run scheduled jobs.

I’ve used this option and yes, it does work. But it’s definitely a few steps down from using SQL Agent jobs. Still, if that’s not available to you, this is a viable fallback option.

Comments closed