How can you import a bacpac to an Azure Managed Instance? Simply connect to the Managed Instance using the newest build of SSMS (SQL Server Management Studio). Next, right-click Databases and choose Import Data-tier Application..

Next follow the import wizard. Oh dear! Looks like we can’t because the wizard fails to load.

A quick googling on Bing pointed to some forum posts that suggest adding registry keys to fix this issue. Not having time for troubleshooting I decided to go with ‘ole reliable… the command line.

SQLPACKAGE.EXE is a command line utility that can be used to export and import Data-tier Applications. To learn more about SQLPACKAGE check out this reference:

https://docs.microsoft.com/en-us/sql/tools/sqlpackage

I’ve found it quite useful to create a batch file (.bat) when importing many bacpac files because you can run it and then check the results later.

When using SQLPACKAGE to import your command should include /a:import /tcs: /sf. The reference above gives a lot of examples for importing and exporting.

Take special note that when importing to Azure SQL Database you’ll want to validate the database configuration to be sure it’s sized appropriately. For example, using /p:DatabaseMaximumSize=150 will result in a premium SKU with a database of 150 GB. Please don’t forget this because Azure SQL DB can be costly.

It’s worth noting that you can import bacpac files using the Azure portal but only for Azure SQL Database and in my experience the Azure portal import often fails with larger database bacpac files and can take hours before the import starts. With that said I generally use SQLPACKAGE instead.

I would think that the majority of SQL Server professionals primarily use SSMS (SQL Server Management Studio) to do everything they need in their day-to-day work and I’ll wager that many have never tried using Azure Data Studio; but, they should.

This post isn’t about why you should give Azure Data Studio a try. Instead, I’d like to focus on a new feature called Notebooks. If you’re unfamiliar with the notebook concept don’t worry they are easy to work with and actually quite cool.

A few things to know

1. To start using Notebooks in Azure Data Studio, simply press CTRL-SHIFT-N or choose New Notebook from the File menu.
2. You can add code or text blocks and they can be added before or after existing blocks.
3. Each code block is similar to a stored proc in that declared variables and table variables are only available in the block itself and are not accessible to any other block of code.
4. Temp tables are at the connection level and they can be used at anytime by another code block as long as they have been created before use. Nothing new here.
5. You don’t have to use T-SQL. In fact you can change the kernel to several different types such as python.

As it turns out I was on a six hour flight from NYC to Seattle on an old Alaska plane that didn’t have any screens in the seats. Having nothing better to do I thought I’d gamble with Notebooks or at least see if I could make a gambling game to use for demo purposes.   First thing you’ll need is Azure Data Studio installed. Once that’s done you can check out my Black Jack notebook.

I believe learning should be fun and often times unconventional ways of learning are the best. While the code for the game is not “production ready”, it’s not meant to be either. My hopes are that the game gives you a few scenarios that help you quickly acclimate to this new cool feature. Enjoy!

What is “How’s My Database?” I’d say it’s something that should have been written a long time ago. Query tuning is a difficult task and can take a lot of practice to become good at. My hopes are that this tool will help the community tune their queries easier, raise awareness to certain issues, and train up those that are just starting out.

You can find the tool in the Windows 10 app store. Just search for “How’s my database”.

How’s My Database is a simple Universal Windows App (UWP). Microsoft didn’t include SYSTEM.DATA.SQLCLIENT in UWP until recently with the release of .NET STANDARD 2.0. Since then they’ve added enough features to allow me to create this tool. I’ve also included the Syncfusion treegrid control to aid in displaying a query plan. I’d like to also thank ComponentOne for lending me some tools for this project; however, I didn’t end up using any.

I’d really love if Microsoft could add SYSTEM.DIAGNOSTICS.PERFORMANCECOUNTER to UWP apps so I could truly write the health check or migration app I’ve always wanted to. Sadly I don’t think it’s on any road map.

UWP is limited from accessing the network locally and thank you to Lance McCarthy of Telerik for helping me with a workaround to access local SQL instances from the tool. You simply need to grab the Windows 8 AppContainer Loopback Utility and use it to unblock any UWP app.

Without this tool you won’t be able to connect to a SQL instance on the local machine.

So what can this tool help with? Lot’s of things.

For example, the “Problems with IN” post I wrote sometime back (thanks Joe Sack for teaching me this).

It can tell the difference between a scan forced by a UDF or scalar function as well.

There are actually about 40 things it checks for.

Current limitations are that queries with a cursor or temp table are not analyzed. There’s also a bug where the missing indexes and warnings appear on the wrong node/operator. Since the tool is using estimated plans at the moment, it may not be as accurate.

I’m planning on a few new features in the next month to add feeding the utility a query plan and displaying the original query. I’m also planning on adding history and the ability to execute a query from the tool. Before we get to those we need to fix some known bugs though. I’m hoping that you. Yes! you can help me identify other bugs to make this a great tool for the SQL community.

Please keep in mind that this is a BETA tool and it’s also free. Please use kind words.

The missing index DMVs are great but they’ve always been missing something.

What are they missing you ask? They currently tell you what table they are for but not what query. How do I know if the queries that sponsored this missing index are business critical or not? Wouldn’t it be nice to know what statements caused this “missing index” to appear?

You may or may not know that you can use xquery to query your xml query plans from cache. If you’re already familiar with this concept then this should be really quick to understand. If you’re not, don’t worry. Xquery is really simple.

We can query our cached plans by using the sys.dm_exec_cached_plans DMV. Joining sys.dm_exec_query_plan gives us the XML query plan and we can then query within that XML with the following statement. This example looks for plans that have a missing index for the address table.

Want the full query? Grab it from my TechNet Gallery here:

https://gallery.technet.microsoft.com/Find-statements-for-13e8c2f4

It’s worth a mention that I rewrote a query from a blog post Jonathan Kehayias did on implicit conversions to get the missing indexes. I wonder what fun you’ll come up with by searching the XML plans?

Hopefully, this new feature will help those that are interested and those that are not so interested in security.

To run the report simply right click a database; choose Tasks; choose Vulnerability Assessment; and finally click Scan for Vulnerabilities…

Once clicked the scan dialog will open and there’s not really much to do except click “OK”.

From here the Assessment Results will be shown. There’s not hundreds of data points but it’s a great start with 52 checks.

The really amazing thing about this is that it even provides a script that you can run to “fix” your issues. Though it should be noted that you may actually “need” the code it’s fixing. Before running these scripts you’d want to discuss with your team and remediate any potential concerns before you break something.

Watch towers go on the corners, barracks go somewhere over there, make doors at each end of the barracks, mess hall goes there, put the drapes up on that window, place the latrine over there, ect, ect…

We do the same with our data. We create tables to store the data, views to mask some data, stored procedures to retrieve the data, ect, ect…

Data is defined by the business. How your store it is planned by you; or, probably someone before you that you really, REALLY dislike because of the choices they made with the DDL/schema.

When you buy a house, you place your furniture in rooms visually. Sometimes it’s really hard to look around the things that the owner has in the room. You think, WHY?!?! This is too big for this room. Why would they do this.

If you’ve worked with data for some time you know where this is going. If you haven’t let me move on. Let’s say you have a table with customers and for whatever reason you place ProductID in the customers table. Why would you do this? It’s like putting a sink in the living room. You’re going to question everything after seeing that.

It’s not limited to things in wrong places though. I’ve seen in many cases where the data type is just entirely wrong.

DATA TYPE & SIZE

In this post I’m going to focus on numeric data; though, it’s worth noting that unicode (nvarchar) is twice the size of non-unicode (varchar) data.

In SQL Server date and numeric types have different sizes depending on what you choose. With that said you’d be wise to choose a type that matches whatever you’re storing. Don’t buy size 14 shoes if you have a size 10 foot, right?

INT types

Bigint: 8 bytes

(-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808)

Int: 4 bytes

(-2,147,483,648 to 2,147,483,647)

smallint: 2 bytes

(-32,768 to 32,767)

tinyint: 1 byte

(0 to 255)

Numeric types

Decimal & Numeric:

Precision:

1-9       |   5 bytes

10-19   |   9 bytes

20-28   |  13 bytes

29-38   |  17 bytes

Float (4 or 8 bytes), real (4 bytes), money (8 bytes), smallmoney (4 bytes)

SCENARIO

Let’s say that you have a student loan database. You want to track students, their loan amount, how much they’ve paid, and how much they still owe.

Money may be a logical data type choice; though, I’ve seen many developers complain about rounding issues and they chose to use numeric or decimal instead.

Smallmoney is only 4 bytes and can support payments of up to $214,748.36. To store this in decimal you’re looking at 5 bytes and storing up to $999,999.99. That’s a really large payment!

What if I want to SUM small money values that would equal more than $214,748.36?  It won’t fail. Don’t believe me? Give it a try:

DECLARE @test TABLE (a int, b smallmoney)
INSERT INTO @test (a, b) VALUES (1, 214748), (2, 214748)
SELECT SUM(b) FROM @test

It worked!

Now let’s say that the original developer chose DECIMAL(38,2). What would happen? First, instead of 4 or 5 bytes the data would be 17 bytes! We’d also be storing up to $99.9 Decillion. That’s a real word, I didn’t make it up!! What’s that number look like? It’s nearly too wide for this blog page. HAHA!

$99,999,999,999,999,999,999,999,999,999,999,999.99

So here is the question. Why would you make a data type that is so large? It’s going to store payments and that is more money than exists in the world. I’ve seen this on a few occasions in production databases.

Let’s examine how this affects the table.

I created a simple schema and added PKs after.

CREATE TABLE loans.Student
(    StudentID bigint identity(1,1),
FirstName nvarchar(50),
LastName nvarchar(50)
)

CREATE TABLE loans.Loan
(    LoanID bigint identity(1,1),
StudentID bigint,
LoanDate datetime,
LoanAmount decimal(38, 2),
LoanBalance decimal(38, 2)
)

CREATE TABLE loans.Payments
(    PaymentID bigint identity(1,1),
StudentID bigint,
LoanID bigint,
PaymentAmount decimal(38, 2)
)

Next, I used dbForge Data Generator to populate my tables with enough data to express my point. 200,000 loans, 148,000 students, and 5,000,00 payments. I use this tool because it’s easy and it has a lot of realistic data that can be populated to make my demos feel like production data.

And now the testing:

I have had a script for quite some time that I use to look at tables and their size. Please feel free to use it.

DECLARE @DBID int = DB_ID(N’EpicFail’),
@TableSchema nvarchar(50) = ‘Loans’,
@TableName nvarchar(50) = ‘Loan’,
@TableTwoPartName nvarchar(100)

SET @TableTwoPartName = CONCAT(@TableSchema, ‘.’, @TableName)

–View the indexes on a table
SELECT isc.COLUMN_NAME, isc.DATA_TYPE, ic.*
FROM sys.index_columns ic
INNER JOIN INFORMATION_SCHEMA.COLUMNS isc on (ic.column_id = isc.ORDINAL_POSITION)
and (TABLE_SCHEMA = @TableSchema)
and (TABLE_NAME = @TableName)
WHERE object_id = object_id(@TableTwoPartName)
ORDER BY INDEX_ID

–Display the index levels and their size
SELECT index_depth AS D
, index_level AS L
, record_count AS ‘Count’
, page_count AS PgCnt
, avg_page_space_used_in_percent AS ‘PgPercentFull’
, min_record_size_in_bytes AS ‘MinLen’
, max_record_size_in_bytes AS ‘MaxLen’
, avg_record_size_in_bytes AS ‘AvgLen’
FROM sys.dm_db_index_physical_stats(@DBID, object_id(@TableTwoPartName), 1, 1, ‘DETAILED’)

–Display data about the pages used in an index
SELECT p.index_id, au.*, p.rows
FROM sys.allocation_units au
INNER JOIN sys.partitions p on (p.hobt_id = au.container_id)
WHERE p.object_id = object_id(@TableTwoPartName)

Looking at the table, we can see that the data is stored on 1667 pages. That’s 8k per page so 1667*8k would be 13,336 bytes for the 200,000 rows.

What if we ALTER TABLE and change the datatype to decimal(9,2)?

Now we only have 1064 data pages! We just cut our overall size of the table by a third and didn’t affect the functionality at all!!

How would this affect query performance? Well thinking about it you could say that the less pages there are the better performance you’ll have not only for some things like scans but also for maintenance like statistics or index rebuilds and possibly even backups (depending on how much wasted space is removed).

Here’s a really simple example of a scan:

Note that with the larger data we have more pages. Logical reads are from the buffer (RAM); but, these pages may not always be in RAM and would have to come from storage.

The cost is higher due to the extra pages as well.

Here we have the smaller datatype results:

SUMMARY

Whenever possible, be sure to size your data types appropriately. This practice will ensure the best performance for your production loads.

Need help with a query plan? Check out my website: www.howsmyplan.com

Let’s say you want to view carriage returns in your query results. Grid results may display them as a space, which isn’t all that helpful. In this case, changing to text results will display the text as text with carriage returns and line feeds just like you’d see in a document. Only there’s one problem.

To keep the results readable the maximum number of characters to display for each column has been reduced from 65536 for grid results all the way down to 256 for text results!

Changing this default is very simple and is done via the options dialog. Simply navigate to the Query Results > SQL Server > Results to Text section and you’ll see the “Maximum number of characters displayed in each column:” setting. Change it to what you desire and click “OK”.           *A restart of SSMS may be needed to complete the change.

Now more or less text should be displayed depending on what you changed the value to.

Enjoy!