Press "Enter" to skip to content

Author: Kevin Feasel

Query Optimizer Hotfixes

SQL Scotsman covers the query optimizer hotfixes which you can turn on with trace flag 4199:

The query optimiser hotfixes contained under Trace Flag 4199 are intentionally not enabled by default.  This means when upgrading from SQL Server 2008 R2 to SQL Server 2012 for example, new query optimiser logic is not enabled.   The reason behind this according to the article linked above is to prevent plan changes that could cause query performance regressions.  This makes sense for highly optimised environments where application critical queries are tuned and rely on specific execution plans and any change in query optimiser logic could potentially cause unexpected / unwanted query regressions.

Read the whole thing.

Comments closed

Switching In Powershell

Chrissy LeMaire explain the switch command in Powershell:

Even less code and makes total sense. Awesome. There’s even more to switch — the evaluations can get full on complex, so long as the evaluation ultimately equals $true. Take this example from sevecek. Well, his example with Klaas’ enhancement.

The refrain with switch is, always make sure you cover every case and don’t let cases fall through when you don’t intend them to.  Fortunately, Powershell doesn’t allow fallthrough, so that makes it easier.

Comments closed

Microsoft R Server 9.0

David Smith reports that Microsoft R Server 9.0 is now available:

Microsoft R Server 9.0, Microsoft’s R distribution with added big-data, in-database, and integration capabilities, was released today and is now available for download to MSDN subscribers. This latest release is built on Microsoft R Open 3.3.2, and adds new machine-learning capabilities, new ways to integrate R into applications, and additional big-data support for Spark 2.0.

There’s also a new version of Microsoft R Client and Microsoft R Open.

Comments closed

Range-Based Dimensions

Jana Sattainathan has a couple blog posts on range dimensions.  First is durations:

The data is in increments of 300 seconds going from 0 to 31536000 seconds (1 year). So, this table can be used to analyze activities that take less than 1 year. The last row’s Dimension value should be used for everything that takes over one year (or you can generate more rows based on your need).

The second is size ranges:

In the middle there, one of the bar charts is “Backup Count & Duration by Size”. As the title says, this chart helps me determine which backups are small/large and determine how many backups are in each of those “Duration” buckets. The duration bucket that I used in this case could have been easily changed from GB ranges to TB ranges. For example, I filtered the chart to check counts of backups that are over 1 TB.  As one can see, I have a couple of databases that are in the 2.5 to 3 TB backup size range.

Often times, ranges are enough for analysis and that greater detail of a backup being 12.8 GB versus 12.81 GB obscures more useful information.

Comments closed

Using WinDocks

Andrew Pruski demonstrates WinDocks, for people without Windows Server 2016 available:

So the first thing to do is get a new server with Windows Server 2012 R2 installed. Then once that’s up and running, you need to install SQL Server…

…wait, what??

The WinDocks software is different from the previous docker software that we’ve worked with in that it needs an instance of SQL installed on the host in order to use it’s binaries to create SQL within the containers. The instance won’t need to be running, it just needs to be installed.

Check out WinDocks; it’s focused around Dockerizing older versions of SQL Server.

Comments closed

Backup Encryption

Daniel Jones shows how to use backup encryption in SQL Server:

The backup encryption in SQL server is needed due to following reasons:

  • Way to Keep Database File Secure: Users need to encrypt SQL server database backup files because this procedure provides complete security to copy of SQL server data. This security measure will keep transaction logs, tables, and other server data safe from any person, who wants to make use of these data in wrong manner.

  • Accessed Only By Authorized Person: It is impossible to restore an encrypted backup file, if a person is not having certificate or asymmetric key for decryption. Therefore, it means that only authorized persons who are knowing credentials of encrypted backup file can restore data with its full access.

Encrypting backups (and storing the encryption key somewhere independent of the backups themselves) can help prevent a very bad day.

Comments closed

Debugging LINQ

Michael Sorens has an article on using OzCode to debug LINQ statements:

OzCode’s new LINQ debugging capability is tremendous, no doubt about it. But it is not a panacea; it is still constrained by Visual Studio’s own modeling capability. As a case in point, Figure 17 shows another example from my earlier article. This code comes from an open-source application I wrote called HostSwitcher. In a nutshell, HostSwitcher lets you re-route entries in your hosts file with a single click on the context menu attached to the icon in the system tray. I discussed the LINQ debugging aspects of this code in the same article I mentioned previously, LINQ Secrets Revealed: Chaining and Debugging, but if you want a full understanding of the entire HostSwitcher application, see my other article that discusses it at length, Creating Tray Applications in .NET: A Practical Guide.

This is quite interesting.  My big problem with LINQ in the past was that Visual Studio’s debugger treated a LINQ statement as a black box, so if you got anything wrong inside a long chain of commands, good luck figuring it out.  This lowers that barrier a bit, and once you get really comfortable with LINQ, it’s time to give F# a try.

Comments closed

The Central Limit Theorem

Vincent Granville explains the central limit theorem:

The theorem discussed here is the central limit theorem. It states that if you average a large number of well behaved observations or errors, eventually, once normalized appropriately, it has a standard normal distribution. Despite the fact that we are dealing here with a more advanced and exciting version of this theorem (discussing the Liapounov condition), this article is very applied, and can be understood by high school students.

In short, we are dealing here with a not-so-well-behaved framework, and we show that even in that case, the limiting distribution of the “average” can be normal (Gaussian.). More precisely, we show when it is and when it is not normal, based on simulations and non-standard (but easy to understand) statistical tests.

Read on for more details.

Comments closed

Checking On Stats

Kendra Little has a script to get vital information regarding a table’s statistics:

For most modern versions of SQL Server, I like to join to sys.dm_db_stats_properties() — you can get a LOT of detail in a single query! (This works with SQL Server 2008 R2 SP2+ / SQL Server 2012 SP1+ / All higher versions)

Here’s the query, looking at a sample table in the WideWorldImporters database:

Click through for the script, as well as a version which works on 2005 and 2008.

Comments closed