Press "Enter" to skip to content

Author: Kevin Feasel

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

T-SQL Tuesday Roundup

Published 2016-04-18 by Kevin Feasel

Jens Vestergaard has his round-up of favorite SQL Server features:

This week, I have been looking forward to the time where I got to read through all the contributions to my#TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).

So, in no particular order, I give you the round-up:

I like T-SQL Tuesday for several reasons; one of the more selfish reasons is that each month, I get to expand my blogroll a little bit further.  This was a particularly good one, so check out the entrants.

Comments closed

Add And Remove Identity Columns

Published 2016-04-18 by Kevin Feasel

Dan Guzman shows how to use partition switching to add or remove identity columns from existing tables:

All tables are partitioned from a database storage engine perspective since SQL Server 2005, although multiple partitions require Enterprise Edition. The implication of this storage architecture is that one can use ALTER TABLE…SWITCH regardless of SQL Server edition to move the entire contents of one table to another without physically moving rows as long as the source and target tables have an identical schema and are physically aligned (indexes and tables on same filegroup). SWITCH performs fast storage meta-data changes so the operation typically takes less than a second regardless of table size. The target table must be empty before SWITCH. After SWITCH, the target table will contain the data and the source table will be empty (similar to TRUNCATE).

Since the identical schema restriction does not include the IDENTITY column property, SWITCH is a handy technique to add or remove IDENTITY from an existing column of a non-empty table. For an empty table, a simple drop and create is easier and more efficient.

This is one of the few really good uses of the SWITCH operator in standard edition, but if ever you do need it, you’ll be glad it’s there.

Comments closed