Press "Enter" to skip to content

Author: Kevin Feasel

Grant View Definition

Published 2016-05-16 by Kevin Feasel

Matt Smith ran into an issue when trying to compare two databases using SQL Server Data Tools:

This appeared to work but didn’t display any results. When you look at the bottom status bar it reads

Comparison complete. No differences detected. Restricted comparison. See Error List for details.

I knew there we at least some differences. I then click on the Error List table below it which revealed

The reverse engineering operation cannot continue because you do not have View Definition permission on the ‘Warehouse’

The answer is pretty simple, so read on.

Comments closed

Deploying To Azure SQL Database

Published 2016-05-16 by Kevin Feasel

Julie Smith shows how to deploy a database (AdventureWorksDW) out to Azure:

This is telling us that four of the tables in the sample do not have clustered indexes. Azure SQL Database insists on a clustered index for every table. So without warranty, here is a script that I used to refactor my on-prem AdventureWorksDW2014 database. After making these fixes, I was able to deploy to Azure SQL DB from SSMS with no errors.

Julie includes the script she used to fix up AdventureWorksDW.

Comments closed

Dashboard Design

Published 2016-05-16 by Kevin Feasel

Melissa Yu explains how people look at dashboards:

Dashboards can be used to communicate a dense collection of information efficiently on a single canvas. Your audience has a limited amount of time to monitor key metrics to get a quick status and identify anything that needs attention. The attention span of the average human has gone from about 12 seconds in 2000 (when mobile phones became mainstream) to about 8 seconds today – a second less than a goldfish – according to a 2015 study.

Following data visualization design principles is key to making your dashboard easily consumable. A poorly designed dashboard can make your eyes jump all over the screen. While it won’t give you much insight, it may cause a headache. In the Western world, we read from top left to right, then zig-zag down left and scroll right again (in a Z-pattern). Understanding where the audience’s eyes will start and travel next allows you to guide them through your dashboard.

Check the link for more details.

Comments closed

Building A Prediction Engine

Published 2016-05-16 by Kevin Feasel

Richard Williamson explains how to build a prediction engine using technologies such as Spark, Kudu, Impala, and Kafka:

We’ll aim to predict the volume of events for the next 10 minutes using a streaming regression model, and compare those results to a traditional batch prediction method. This prediction could then be used to dynamically scale compute resources, or for other business optimization. I will start out by describing how you would do the prediction through traditional batch processing methods using both Apache Impala (incubating) and Apache Spark, and then finish by showing how to more dynamically predict usage by using Spark Streaming.

Of course, the starting point for any prediction is a freshly updated data feed for the historic volume for which I want to forecast future volume. In this case, I discovered that Meetup.com has a very nice data feed that can be used for demonstration purposes. You can read more about the API here, but all you need to know at this point is that it provides a steady stream of RSVP volume that we can use to predict future RSVP volume.

This is pretty dense, but it is a great look at one potential architecture leveraging Spark and several tools in the Hadoop ecosystem.

Comments closed

Working With Windows Server Core

Published 2016-05-16 by Kevin Feasel

Sander Stad has a couple of blog posts on working with Windows Server Core edition.  First, what happens if you lose your command prompt?

In my enthusiasm I clicked the “X” on the top right corner. So this happened:

Mayhem! How do I get my command screen back? Reboot?! NO WAY!

After figuring that out, Sander also explains how to perform updates:

The Windows Server Core Edition is a really good option because there aren’t that many binaries as there would be in a full installation. Due to the smaller number of objects you more stability, simplified management, reduced maintenance and a reduced risk to get attacked.

So you have a Windows Server Core Edition installed but want to update the server manually. Maybe this is a virtual machine on your local PC that needs updating and you don’t have WSUS running.

About everything you previously managed using the GUI in Windows is now done with the sconfig. You can edit your server’s name, the domain, network settings, date/time, shutdown the server but also manage the updates.

Admittedly, most Core installations will probably be in environments with a lot of automation around them, but sometimes you’re just doing a one-off thing.

Comments closed

Calculating Weekdays Between Dates

Published 2016-05-16 by Kevin Feasel

Daniel Hutmacher shows how to calculate the number of weekdays between two dates:

It has to be said, if performance is important (and it should be), you’ll know that user-defined scalar T-SQL functions can kill your query. So you might want to implement the above in the form of a CROSS APPLY instead

Here’s where I advocate for two tables on every instance:  a tally table and a date table.  The date table should look like a date dimension, including every potentially-interesting piece of information about a date—including if it’s a weekday.  It might not perform quite as fast as Daniel’s solution (I’d have to test to know for sure), but it’s definitely easier.   If you can’t get a viable date table on your instances for whatever reason, Daniel’s solution does work and does not require any additional objects.

Comments closed

Removing Snippet Line Numbers

Published 2016-05-13 by Kevin Feasel

Jens Vestergaard grabs some code and is left with line numbers he wants to remove:

Only too often I find myself searching the web for pieces of code, that I need for some odd solution. And almost every time, I come across a web page, where the code sample is displayed with line numbers. It’s always annoying to have to sit and delete those manually. No longer I say! Yes, you heard me right. Actually Visual Studio has a neat trick to help you get this done in a jiff.

This is useful for a lot more than just removing line numbers.

3 Comments

Minimum Memory Grant Hint

Published 2016-05-13 by Kevin Feasel

James Anderson looks at memory grant query hints:

This is one of a pair of query hints that can manage the memory grants of individual queries. The maximum memory grant hint allows you to limit the amount of memory an individual query can use. If the hint specifies a value lower than the amount of memory required to execute the query, the query will request the required amount. This is like a granular resource governor. For the rest of this post I will concentrate on the minimum memory grant query hint.

When the query optimiser builds an execution plan for a query, it calculates how much memory is required for the query to execute. This calculation is based on the estimated number of rows (used in sort or hash joins) and the average size of these rows. The optimiser calculates the least amount of memory required to perform the operation (required memory) and the amount of memory required to perform the whole operation in memory (desired memory). Each time the query is executed it requests a memory grant from the server. The query will perform much better if it’s granted its desired memory or more. If at run time the query has to perform sorts or hash operations on more than the estimated number of rows, it will spill to tempdb. Spilling to tempdb can be orders of magnitude worse for performance as your data has to be written to disk causing more IO.

This feels like the type of thing you might want to do once for a particularly recalcitrant query, but not something you want to do regularly.

Comments closed

SQL Injection Is Still Around

Published 2016-05-13 by Kevin Feasel

K Brian Kelley notes that SQL injection is still a viable security risk:

Keep in mind that from an architecture perspective, the primary place to stop SQL injection attacks is by validating the input when it comes in. If the input doesn’t match appropriate patterns, especially in the case of a banking application where the likely patterns for each input should be easily defined, you reject it at that level. It then doesn’t get appended or inserted into a text string which becomes the SQL statement to be executed against a database server.

If you don’t get it at this level, the ability to prevent the SQL injection attack gets much harder. Perhaps IDS/IPS can detect based on some text matches. We might be able to do the same thing within the database, say by using DML triggers. However, if the appended text generates queries that are basically what normally gets sent back, none of the back-end solutions are going to be very effective.

Pick your language and parameterize your queries.

Comments closed

Dealing With DocumentDB

Published 2016-05-13 by Kevin Feasel

Rolf Tesmer has an example of using DocumentDB:

How do you work out what DocumentDB will cost?  

Well, 1 RU corresponds to a GET of a 1KB document.  You then need to know the specifics of your workload such as number of documents, avg document size, how the documents are accessed (query patterns), and how often documents are updated.

I suggest loading your data and running your standard queries in the Azure Portal (see below) to see what RU’s your workload generates, and use that in your calculations.

Rolf also has some comparisons to SQL Server queries and some hints with integrating SQL Server with DocumentDB.

Comments closed