Press "Enter" to skip to content

Author: Kevin Feasel

Powershell Speed Testing

Published 2017-11-30 by Kevin Feasel

Shane O’Neill shows off a Powershell script which allows you to simplify performance testing:

Apart from catching up on news during my commute I only really use notifications for a certain number of hashtags i.e. #SqlServer, #tsql2sday, #sqlhelp, and #PowerShell.

So during work, every so often a little notification will pop up on the bottom right of my window and I can quickly glance down and decide whether to ignore it or check it out.

That’s what happened with the following tweet:

Click through for Shane’s demo.

Comments closed

Vertical Selection In SSMS

Published 2017-11-30 by Kevin Feasel

Bert Wagner shows off vertical selection in SSMS (using the Alt key):

Sometimes when writing an ad hoc query you might want to take the results of one query and put them into an IN() statement of another query.

Sure, you can write a subquery to put into your IN() statement…but that’s too much work for a one-time use disposable query.

What you can do instead is:

  1. Copy your values of interest

  2. Paste them into your IN() statement

  3. Hold down the ALT key while dragging the mouse down in front of all of your pasted values

  4. Type a comma (see video above for an easier demonstration).

For SSMS speedrunning strats, you can also hold down ALT + SHIFT and use your keyboard arrow keys instead of using the mouse.

Comments closed

How Non-Clustered Index Key Columns Are Stored

Published 2017-11-30 by Kevin Feasel

Kendra Little walks through page-level details on a non-clustered index:

Just like in the root page and the intermediate pages, the FirstName and RowID columns are present.

Also in the leaf: CharCol, our included column appears! It was not in any of the other levels we inspected, because included columns only exist in the leaf of a nonclustered index.

Kendra does a great job of explaining the topic.

Comments closed

Handling Session State With Memory-Optimized Tables

Published 2017-11-30 by Kevin Feasel

Perry Skountrianos shows how to configure ASP.Net to use memory-optimized tables for session state:

ASP.NET session state enables you to store and retrieve values for a user as the user navigates the different ASP.NET pages that make up a Web application. Currently, ASP.NET ships with three session state providers that provide the interface between Microsoft ASP.NET’s session state module and session state data sources:

  • InProcSessionStateStore, which stores session state in memory in the ASP.NET worker process
  • OutOfProcSessionStateStore, which stores session state in memory in an external state server process
  • SqlSessionStateStore, which stores session state in Microsoft SQL Server database

This blog post focuses on the SqlSessionStateStore provider and describes how you can configure it to use SQL Server In-Memory OLTP as the storage option for session data. You can either use the latest ASP.NET async version of the SQL Session State provider (which is the recommended approach), or configure an earlier version of the provider to work with In-Memory OLTP by downloading and running the In-Memory OLTP SQL scripts from our sql server samples github repo.

The me of seven years ago really needed this.  But with the strong shift against session-based data collection and back to stateless or client-held state paradigms, I’m not sure how many people this helps.

Comments closed

Power BI Helper: Expression Dependency Trees

Published 2017-11-30 by Kevin Feasel

Reza Rad announces a new feature of Power BI Helper:

I’m excited to share the news with you that we have added a new feature in Power BI Helper; Expression Tree. Expression Tree will expand the tree of expression for a Measure or calculated column, you can see what other measures are used to create this expression, and where other measures, calculated columns, or even normal columns are located (in which table). This feature is in addition to previous two features of this tool which were; Showing tables and fields used in visualization pages of a Power BI Report, and ability to search for a column or table that used in visualization pages of a report. In this post, I’ll explain how this new feature works.

Read on for the explanation.  I can see this being quite useful.

Comments closed

Additional Restore-DbaDatabase Functionality

Published 2017-11-30 by Kevin Feasel

Stuart Moore shows off a few examples of advanced Restore-DbaDatabase usage:

No matter how hard the dbatools; team tries, there’s always someone who wants to do things we’d never thought. This is one of the great things with getting feedback direct from a great community. Unfortunately a lot of these ideas are either too niche to implement, or would be a lot of complex code for a single use case.

As part of the Restore-DbaDatabase stack rewrite, I wanted to do make things easier for users to be able to get their hands dirty within the Restore stack. Not necessarily needing to dive into the core code and the world of GitHub Pull Requests, but by manipulating the data flowing through the pipeline using standard PowerShell techniques, all the while being able to do the heavy lifting without code.

Click through for several examples.

Comments closed

Tidy Word Vectors Revisited

Published 2017-11-29 by Kevin Feasel

Julia Silge revisits her Hacker News word vectorization problem:

So hooray! We have found word vectors again, a bit faster, with clearer and easier-to-understand code. I do argue that this is a real benefit of this approach; it’s based on counting, dividing, and matrix decomposition and is thus much easier to understand and implement than anything with a neural network. And the results?

Click through to see the new method, as well as some basic analogy testing.

Comments closed

Machine Learning Data Preparation Tips

Published 2017-11-29 by Kevin Feasel

Jen Underwood has some good tips when preparing data for a machine learning operation:

Data preparation for machine learning requires business domain expertise, bias awareness and an experimental thought process. Before preparing your data, you’ll first define a business problem solve. During that exercise, you’ll select an outcome metric and brainstorm potential input variables that influence it from many varied perspectives. From there you will begin identifying, collecting, cleaning, shaping and sampling data to run through automated machine learning model processes.

Note that it is also not unusual for relevant machine learning input data to occur outside of existing transactional processes. If that is the case, you can still start creating a first-generation machine learning model with existing data and continue to build new model versions over time as supplementary data is acquired.

Click through for the ten tips.

Comments closed

Configuring And Monitoring Distributed AGs

Published 2017-11-29 by Kevin Feasel

Tracy Boggiano has some advice on configuring and monitoring distributed Availability Groups:

Monitoring can be tricky with distributed AGs because of the way it shows up in SSMS.  The distributed AG created on the primary AG does not have the ability to show you a dashboard to monitor traffic like the typical AG and the secondary replicas do not even show the distributed AG.  So, to monitor the distributed AG you are best to head to the DMVs.  DMV sys.availability_groups has the is_distributed column that will allow you to only see the distributed AGs.  Below is a query adapted from the Microsoft Docs query to add additional information to check on the status of the distributed AGs which you must run on the primary AG.

Click through for more details.

Comments closed

Legacy Cardinality Estimation In SQL Server

Published 2017-11-29 by Kevin Feasel

Kellyn Pot’vin-Gorman explains what the Legacy Cardinality Estimation setting does in SQL Server:

Oracle DBAs have used the CARDINALITY hint for some time and it should be understood that this may appear to be similar, but is actually quite different.  As hinting in TSQL is a bit different than PL/SQL, we can compare similar queries to assist:

TSQL
SELECT CustomerId, OrderAddedDate 
FROM OrderTable 
WHERE OrderAddedDate >= '2016-05-01';
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
go
PL/SQL

Where you might first mistake the CE hint for the following CARDINALITY hint in Oracle:

SELECT /*+ CARDINALITY(ORD,15000) */ ORD.CUSTOMER_ID, ORD.ORDER_DATE 
FROM ORDERS ORD WHERE ORD.ORDER_DATE >= '2016-05-01';

This would be incorrect and the closest hint in Oracle to SQL Server’s legacy CE hint would be the optimizer feature hint:

SELECT /*+ optimizer_features_enable('9.2.0.7') */ ORD.CUSTOMER_ID, ORD.ORDER_DATE FROM ORDERS ORD 
WHERE ORD.ORDER_DATE >= '2016-05-01';

If you’re wondering why I chose a 9i version to force the optimizer to, keep reading and you’ll come to understand.

Read on for the comparative explanation as well as more details on SQL Server’s legacy cardinality estimator hint and database-scoped configuration setting.

Comments closed