A Fine Slice Of SQL Server
Jen McCown explains the different between backup and back up:
Let it be known that the word “backup” is a noun (it refers to a thing), and “back up” is a verb (it refers to an action.
I’m going to back up the database.
It will produce a backup.
I’ll save that backup until we back up the database three more times.
Now back up a moment here; I need to come up with a backup plan…
Comments closedKendra Little shows Management Studio execution plan improvements in 2016:
The best features are the ones that you use all the time. SQL Server 2016 Management Studio’s bringing improvements in navigating around execution plans.
Click + Mouse Scroll: Zooming!
You can now make your plans bigger and smaller with this combo. It will zoom into the region where you have the mouse.
Click + Drag lets you move the plan
This is really handy for moving right to left.
Good for those times when SQL Sentry Plan Explorer isn’t available.
Comments closedGinger Grant gives a brief discussion of linear regression:
There are two types of indicators for linear correlation, positive and negative as shown on the following charts. The Y axis represents Grades, and the x axis is changed to show positive and negative correlation of the amount of X on grades. When X is the amount of study hours, there is a positive correlation and the line goes up. When X is changed to watching cat videos, there is a negative correlation. If you can’t draw a line around the points there is no correlation. If I were to create a graph where X indicated the quantity of the bags of Cheese Doodles consumed on grades, it would not be possible to draw a straight line, where the data points cluster around it. Since this is Line-ar regression, if that line doesn’t exist there is no correlation. Knowing there is no correlation is also useful.
Simple linear regression is a powerful tool and gets you to “good enough” more frequently than you’d think.
Comments closedRichie Lee points out an article on the SQL Server team’s source control strategy:
I’ve always advocated a dev/main/release process, but I’ll admit this has weaknesses, not least that testing will usually only take place properly in one branch, and that bugs found in one branch may not find there way “back” or “forward”, but to go with one branch means that you are forced to keep the quality at production-code quality and make use of feature switches. Certainly it’s an ambitious way of working, and Microsoft’s ALM documentation suggests that no branches is reserved for smaller teams, but surely if the SQL team at Microsoft are able to do it then certainly it’s a branching strategy worth considering?
Read the linked article as well. This is an interesting look from the inside of how SQL Server gets developed.
Comments closedPaul Randal discusses common temp table anti-patterns:
It’s quite common for there to be a latching bottleneck in tempdb that can be traced back to temporary table usage. If there are lots of concurrent connections running code that creates and drops temporary tables, access to the database’s allocation bitmaps in memory can become a significant bottleneck.
This is because only one thread at a time can be changing an allocation bitmap to mark pages (from the temp table) as allocated or deallocated, and so all the other threads have to wait, decreasing the workload throughput. Even though there has been a temporary table cache since SQL Server 2005, it’s not very large, and there are restrictions on when the temporary table can be cached (e.g. only when it’s less than 8MB in size).
This is great advice; read the whole post.
Comments closedAaron Bertrand has an intro-level post on using dates and times in SQL Server:
I urge you to always use
yyyymmdd(without the dashes) for a date without time – it will never fail, regardless of regional, language, or dateformat settings, and across any of the date/time data types. (And absolutely do not store it as a string data type in SQL Server – always store it as a proper date or time data type.)
This was a big one for me because I tend to use yyyy-mm-dd.
Comments closedSimon Sabin has thoughts on Reporting Services 2016:
I recently installed SQL Server 2016 on my surface to get all our SQLBits reports sorted. What I couldn’t figure out was why it was so quick. I thought it might be because it was a local install and running on an SSD based surface but that couldn’t account for the blazing difference with previous versions.
Well the answer is much better.
I am looking forward to Reporting Services 2016, even though I rarely use SSRS anymore.
Comments closedKenneth Fisher warns that you should specify schemas in scripts:
But why? I mean the table gets created either way and since the default is dbothere is no real reason to name it.
Actually no. The default is not in fact dbo. It frequently is dbo but by no means always. The default schema is part of your USER information. Specifically theDEFAULT_SCHEMA option. Well, unless you are a sysadmin. Then it actually does always default to dbo.
Schemas are a very powerful grouping mechanism, and they’ve been around long enough that if you aren’t taking full advantage of them, you really should.
Comments closedKoen Verbeeck shows a quick way to add multiple existing packages to a project:
You select the package you want to import and you’re done. But the problem is, you can select only one single object? What if you want to import 20 packages to your project? Kind of annoying to repeat the same process 20 times, isn’t it?
Luckily there’s an easier way. Instead of going for the obvious Add Existing Package, right-click on the project itself. In the context-menu, choose Add, ignore Existing Package and click on Existing Item.
If you have a large number of packages to import, this will save you a few minutes of tedium (or hand-editing a project file).
Comments closedGrant Fritchey looks at how the query optimizer treats views:
The important point to note is that the optimizer is absolutely not treating the view like a table. The optimizer is treating the view like a query, which is all it is. This has both positive and negative impacts when it comes to query performance tuning and this view. You could spend all sorts of time “tuning” the view, only to find all that tuning you’ve done tossed out the window when the query doesn’t reference a column in the view and that causes the optimizer to rearrange the plan. I don’t want to convey that this is an issue. It’s not. I’m just trying to emphasize the point that a view is just a query.
In a subsequent post, Grant promises to talk about the potential perils of nested views. That’s where people start running into trouble, when a nested view gets to be so complex that the query optimizer gives up and takes it literally.
Comments closed