Press "Enter" to skip to content

Month: March 2016

DATETIME2 Partition Elimination

Published 2016-03-11 by Kevin Feasel

Kendra Little shows that DATETIME2 implicit conversion can prevent partition elimination:

SQL Server is implicitly converting my date value to DATETIME2(7). That is a larger, more precise value than the data type I have in the table–FakeBirthDateStamp is DATETIME2(0).

That data type mismatch is preventing partition elimination!

This is a nasty issue to catch in production, especially after you spend a bunch of time arguing with devs that DATETIME2 is the way of the future, that it’s better because of the variable precision, etc.

Comments closed

R Tools For Visual Studio Launched

Published 2016-03-10 by Kevin Feasel

R now integrates into Visual Studio:

RTVS is an IDE and as such you can use it with any recent version of R such as 3.2.x. If you install the free Microsoft R Open, you automatically get some turbo options such as threading support on multi-processor machines, providing significant speedup for a variety of analytical functions, as well as package collections check-pointed to a particular date/version. Microsoft R Server provides Big Data support and additional advanced features that can be used with SQL Server.

This is an early release, so expect a few bugs and some missing functionality.  It also isn’t RStudio—it’s RStudio several years ago.  But what it does nicely is integrate with the rest of your stack:  you can tie together the R code, the C#/F# code which helps clean data, the SQL Server project which holds your data, etc. etc.

Comments closed

SSMS February Preview

Published 2016-03-10 by Kevin Feasel

Aaron Bertrand notes that there’s a new SSMS preview available:

Gianluca Sartori (@spaghettidba) blogged about the issues you may have noticed with older versions of Management Studio on HiDPI, UQHD, or 4K/5K screens in his post, “SSMS in High-DPI Displays: How to Stop the Madness.” Essentially, Windows tries to scale things for your DPI settings and, depending on the technology used to render your fonts and dialogs, this can end up with ugly and sometimes unusable screens. Gianluca’s fix is to make a registry change and add a manifest file to the SSMS directory so that SSMS no longer tries to render Windows’ scaling changes. In the latest builds of SQL Server 2016, SSMS now acts as if the manifest file were in place. Take a look at the following image:

That’s music to my ears; I use a high-resolution laptop and before Gianluca’s solution, it was impossible to use SSMS.  I’m looking forward to SSMS 2016, but probably won’t move until the add-ons I use are supported; I’ve grown to like them too much to make the jump, even on a trial basis.

Comments closed

Keep Scripts Up To Date

Published 2016-03-10 by Kevin Feasel

Andy Galbraith points out that scripts need maintained just like everything else:

The broader lesson here is to make sure you update your script libraries regularly – even if a script still runs and provides output (that is, you think it “works”) it doesn’t mean you are receiving valid data.

Although this example is about wait stats and wait types, it is applicable to a wide array of configurations and settings.  Changes like this are often version-related, but even within a version it can be decided that a particular wait type/trace flag/sp_configure setting/etc. is no longer important and can be ignored – or even worse, that some item is now important but wasn’t included in your original scripts!

This is an important note.  Things change over time, so our administrative scripts need to change with them.

Comments closed

Custom Power BI Visuals

Published 2016-03-10 by Kevin Feasel

Reza Rad shows us how to create a custom visualization in Power BI:

This is one of the coolest custom visuals I’ve seen so far. The reason is that this custom visual has a customization in it! with this visualization you can define regions in any picture or images, and map data points to the image in your Power BI report. The image can be everything; human body, airplane seat layout, shop floor layout or football field. You choose the image you want, then you define regions. Let’s have a closer look at this visual.

It’s amazing how easy Power BI makes that.  Almost easy enough for me to do it…

Comments closed

Are Trace Flags Dead?

Published 2016-03-10 by Kevin Feasel

Brent Ozar notes that several common trace flags are getting turned into all-grown-up commands:

SQL Server 2016 does away with these unintuitive trace flags by adding new ALTER DATABASE commands:

ALTER DATABASE SET MIXED_PAGE_ALLOCATION ON (or OFF, which is the new default behavior)

ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES (or AUTOGROW_SINGLE_FILE, which is still the default)

I think trace flags will still be around for quite some time as a troubleshooting mechanism, but I certainly prefer clearer naming (was that trace flag 1117…or 1171…or maybe…).

Comments closed

Get SQL Server Configuration Aliases

Published 2016-03-10 by Kevin Feasel

Chris Bell shows us how to get the list of aliases set up on a server:

It is also a pain to sit and transcribe the various alias settings to be able to rebuild them all on the next machine.

There is an export list option for the aliases on your server, that’s nice and all, but there isn’t a corresponding import option.

Plus you have to deal with 32 and 64 bit lists.

The very simple script below helps since you can use to get the details of both the 32 and 64bit SQL Server aliases you have setup on your system.

Ready for it? It’s a long convoluted one:

If you use server aliases, you’ll want to check out this script.

Comments closed

SQL Server On Linux Preview

Published 2016-03-09 by Kevin Feasel

James Anderson shows us where to sign up for the SQL Server on Linux preview:

Couple of thoughts on the sign up form:

  1. Red Hat is mentioned in the announcment but this form tells us that only Ubuntu or Docker images are currently available. Will it be available to all Linux distributions? What about OS X?

  2. As for Docker, does it ever make sense to have RDBMSs in containers? Containers are generally immutable.

Done.  I’m also excited about the continued Microsoft-Red Hat collaboration, not just because I live in Red Hat territory.

In the meantime, Denny Cherry and Joey D’Antoni give their thoughts.  Here’s one of Joey’s:

My favorite part of SQL Server on Linux? My Bash scripts for automation work again.

 

Comments closed