Press "Enter" to skip to content

Month: April 2016

Statistics FAQ

Published 2016-04-19 by Kevin Feasel

Kendra Little has a great FAQ on statistics, from the standpoint of developers as well as administrators:

I’ve been asked a lot of questions about updating statistics in SQL Server over the years. And I’ve asked a lot of questions myself! Here’s a rundown of all the practical questions that I tend to get about how to maintain these in SQL Server.

I don’t dig into the internals of statistics and optimization in this post. If you’re interested in that, head on over and read the fahhhbulous white paper, Statistics Used by the Query Optimizer in SQL Server 2008. Then wait a couple days and chase it with it’s charming cousin, Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

I’m also not talking about statistics for memory optimized tables in this article. If you’re interested in those, head over here.

This is a great distillation of important and oft-misunderstood content.

Comments closed

Configuring Polybase

Published 2016-04-19 by Kevin Feasel

David Benoit walks through how to configure a Polybase cluster:

When you get to the PolyBase configuration screen, you have the option to run this as a standalone instance, or to use the SQL Server as part of a scale-out group. Personally, I don’t think that anyone should ever choose the standalone instance option. You can always run a SQL Server configured for a scale-out group as a standalone instance, BUT you can’t change (at least not today) a SQL Server configured as a standalone PolyBase instance to run as part of a PolyBase cluster once you have completed the install.

This note sounds like the argument for clustering all SQL Server instances.

Comments closed

New Columnstore Extended Events

Published 2016-04-19 by Kevin Feasel

Niko Neugebauer talks about extended events relating to columnstore indexes in SQL Server 2016:

In SQL Server 2014 we have had 18 Extended Events and with Service Pack 1 we have received 1 more to be a total of 19 Extended Events for studying the Columnstore Indexes and the Batch Mode processing. In SQL Server 2016 that number has been greatly increased – there are whooping 61 Extended Events, that will give us an important insight into the Columnstore Indexes.

Even more important, Sunil & his team have given an own category inside the Extended Events – a category that is named Columnstore, which will ease the search for the basic columnstore events. Be aware though not all Extended Events related to Columnstore Indexes are included in that category – even including all channels will give you 41 Extended Events, while hiding the other 20 Extended Events, which are sometimes not categorised at all and at other times are stored under different categories, such as Execution or Error, for example. I believe the reason behind not changing the old Extended Events category is quite simple – Microsoft always looks for avoiding breaking existing applications.

There’s a lot here to digest, so read the whole thing.

Comments closed

SQL Agent Job Run Length

Published 2016-04-19 by Kevin Feasel

Andy Galbraith shows how long that SQL Agent job ran:

So….when did “DatabaseIntegrityCheck – SYSTEM_DATABASES” start? At 1500 – is that 3pm?  You may be able hash out that this translates to 12:15am local time…but what if you want to perform datetime-style math on the RunDate/RunTime?  Sure you can do multiple leaps to say (RunDate>X and RunDate<=Y) AND (RunTime>A and RunTime<=B), but you then need to explicitly format your X, Y, A, and B in the appropriate integer-style format.  Wouldn’t it be easier to just be able to datetime math?

The next part is even worse – quick – how long did the first instance of “ServerA_RESTORE_FROM_PROD_V2” run?

4,131 somethings (seconds, ms, etc), right?

Maybe (maybe!) there was a valid reason for the SQL Agent tables to have such screwy values for date, time, and duration; regardless, this is a sheer pain to deal with today.

Comments closed

SQL Server 2016 RC 3 Available

Published 2016-04-18 by Kevin Feasel

Microsoft has announced RC 3 of SQL Server 2016:

SQL Server 2016 RC 3 is the last of our publicly-available release candidates. You can try this in your development and test environments, and it is available for download today.

In SQL Server 2016 RC 3, enhancements consisted primarily of bug fixes. We continue to refine the product for general availability. For the current release notes, see SQL Server 2016 Release Notes.

That “last of our publicly-available release candidates” thing says to me that we’re going to see RTM soon.

Comments closed

Max Server Memory

Published 2016-04-18 by Kevin Feasel

SQL Sasquatch explains what “max server memory” does and how it relates to private bytes:

Well, there’s obviously a relationship between private bytes and “total server memory”.  The peaks and valleys occur at the same time.   But there’s a delta between them, and the delta isn’t constant.

The difference between private bytes and “total server memory” is pretty well confined, too – not varying more than 10 mb in this interval.

Read the whole thing.

Comments closed

Azure SQL Data Wareouse

Published 2016-04-18 by Kevin Feasel

Robert Sheldon has a getting started guide for Azure SQL Data Warehouse:

In my Simple-Talk article Azure SQL Data Warehouse, I introduced you to SQL Data Warehouse and gave you an overview of the architecture and technologies that drive the service and make it all work. In this article, I go a step further and provide details about getting started with SQL Data Warehouse, demonstrating how to add a sample database and then accessing the server and database settings.

If you want to follow along with my examples and try out SQL Data Warehouse for yourself, you must have an active Azure subscription, even if it’s just the free trial. For those who have already used up their free trial, be aware that SQL Data Warehouse is a pay-as-you-go service, even though it’s still in preview, so unless you’re on an unlimited company budget or happen to have accrued MSDN credits, you’ll want to be judicious in how you try out the service. Fortunately, as you’ll see in this article, you can pause the compute resources when not in use, helping to minimize the costs associated with learning about the service.

This article is all about initial installation and configuration.

Comments closed

Basic SQLCMD

Published 2016-04-18 by Kevin Feasel

Steve Jones shows  some basic SQLCMD operation:

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times.

SQLCMD and OSQL aren’t things you typically need to use a lot, but sometimes you won’t have the ability to run Management Studio and it’s good to know that there are built-in alternatives.

Comments closed