Press "Enter" to skip to content

Curated SQL Posts

Publishing a dacpac with .NET Core

Published 2020-05-13 by Kevin Feasel

Erik Ejlskov Jensen shows how to deploy a Visual Studio database project from .NET Core:

In this post, I will describe how you can build a SQL Server Database project in order to create a .dacpac file, using .NET Core only – dotnet build.

For a while now, it has been possible to publish a .dacpac (meaning apply it to an new or existing database) using the cross-platform version of sqlpackage.

But building a database project (.sqlproj) was only possible on Windows, as the .sqlproj project type is based on the classic .NET Framework .csproj project type.

However, thanks to a smart open source effort, you can now also build a .dacpac file, even on a Mac or Linux build agent.

Read on to learn more.

Comments closed

Tips for a Smooth Power BI Report Server Experience

Published 2020-05-13 by Kevin Feasel

Josh Crittenden has a few tips for people installing Power BI Report Server:

As is the case with any enterprise BI product, careful planning and consideration of how the technology will be leveraged should be made up-front. This will help avoid speed bumps during implementation and ensure a smooth ride as you roll out your BI and analytics solutions. Whether you’ve been working with PBIRS from day one, or are starting to use the platform for the first time, the goal of this post is to showcase tips and techniques that you can turn around and apply in your own environment.

Additionally, for those of you considering a hybrid Power BI implementation (Power BI service and Power BI Report Server), we will compare specific features of each platform below.  

Read on for the tips.

Comments closed

Capturing CHECKDB Output

Published 2020-05-13 by Kevin Feasel

Erin Stellato shows how you can track the results of those automated CHECKDB runs you’re doing:

First, you need to be running CHECKDB on a regular basis.  If you’re going to ask what you mean by regular basis, I’ll direct you to a post of Paul’s that is still relevant: Importance of running regular consistency checks.  There are a few factors to consider, but in general I recommend at least once a week.  If you’re a 9 to 5 shop and have the maintenance window to run CHECKDB daily, feel free to do that.

Erin walks us through it and also recommends checking out Ola’s scripts for integrity checks. I’d add to that Minion CheckDB.

Comments closed

Finding Index Usage Stats in Query Store

Published 2020-05-13 by Kevin Feasel

Grant Fritchey gives us another option for determining whether an index is in use:

One of the most frequent questions you’ll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead.

I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one.

What if we queried the information in Query Store?

Be sure to read Grant’s warning before jumping into this, but at least it gives us another option, as well as a better understanding of which queries are using particular indexes.

Comments closed

Using the SSIS Hadoop Components

Published 2020-05-12 by Kevin Feasel

Hadi Fadlallah walks us through the HDFS file source and destination components:

To test these components, we will create an SSIS package and add three connection managers:

1. Hadoop Connection Manager: to connect with the Hadoop cluster (check the previous article)
2. OLE DB Connection Manager: to connect to SQL Server instance where AdventureWorks2017 database is stored
3. Flat File Connection Manager: We will use it to export data from HDFS Source:

I wonder if they ever fixed the 4K screen resolution problem (kind of tells you how often I use SSIS anymore…). That was one of the things which made these components unusable for me on any modern screen.

Comments closed

Certificate Management with SQL Server 2019

Published 2020-05-12 by Kevin Feasel

Niko Neugebauer walks us through improvements in certificate management with SQL Server 2019:

If you have ever used them for connection encryption (TLS 1.2), you might have had some battles with the certificates, having to go into the registry to edit the thumbprint and if you doing a Failover Cluster or Availability Group installation – you would have to enjoy this operation on the multiple nodes. Not-so-very-user-friendly to say at least!
So many times, it would scare-off a non Server/Database Administrator or a junior Server/Database Administrator from trying those features.

Niko also mentions something very interesting about SQL Server Configuration Manager compatibility at the end of the post.

Comments closed

Understanding Memory Grants

Published 2020-05-12 by Kevin Feasel

Taiob Ali walks us through the concept of memory grants:

DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory.

(Amount needed to store all temporary rows in memory. This depends on the cardinality estimate, expected number rows and average size of row). This is called additional because a query can survive lack of such memory by storing part of temporary rows on hard disk. A query is not guaranteed to have the full amount if the total exceeds the preset limit.)

Read on for explanations of each of the elements in MemoryGrantInfo.

Comments closed

Row Counts and Arrow Widths in SSMS

Published 2020-05-12 by Kevin Feasel

Hugo Kornelis takes us through one of the niceties SQL Server Management Studio (and subsequent tools like SentryOne Plan Explorer and Azure Data Studio) has for reading execution plans:

Because both the Actual Number of Rows and the Estimated Number of Rows are very valuable for many use cases, Microsoft has built their primary tool for viewing execution plans (SQL Server Management Studio) to highlight the value of at least one of these two properties, in a visual way, when you look at the graphical representation of an execution plan.

Look at the arrows that show which operator (is called by and) returns rows to which operator, and that therefore can be thought of as representing the data stream. In most execution plans, they are not all the same width. Management Studio uses the width of the arrow as a quick, simple visual representation of the value of Actual Number of Rows (in an execution plan plus run-time statistics, aka “execution plan plus” or “actual execution plan”), or of the value of the Estimated Number of Rows in an execution plan only (aka “estimated execution plan”).

This first post is the happy path post, where the information is correct and useful. Later posts promise to cover cases in which this does not hold.

Comments closed