Press "Enter" to skip to content

Author: Kevin Feasel

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

Fast Queries Can Hurt Performance

Published 2016-04-18 by Kevin Feasel

Andy Mallon diagnoses a fast query which was hurting performance:

This code is coming from an ORM, which is parameterizing the filters, but not the (unnecessary & arbitrary) TOP value. The DMVs all viewed these are separate queries, so it was not aggregating the stats. It also wasn’t reusing the plan (thus chewing up even more CPU from frequent compiles). If the TOP had not been there, or it had been passed as a parameter, my initial query of sys.dm_exec_query_stats should have found it.

There are a couple of issues Andy works through, and his advice is good:  just because something runs quickly doesn’t mean it can’t (in aggregate) have a negative effect on your server.

Comments closed

Heaps

Published 2016-04-15 by Kevin Feasel

Derik Hammer walks us through heap tables as part of his indexing series:

A heap is a grouping of unsorted pages which are not linked. Page anatomy is out of scope for this series since all types of indexed and non-indexed tables use the same page structure but I do encourage you to check out here and here to learn more.

A heap is comprised of one or more index allocation map (IAM) pages which point to the data pages which make up the heap. The only exception to this is when you have a row which has been updated and could not fit in its page anymore. In that case, you get a forwarding pointer to the row which has been moved to an existing page with space or a new page. It is possible for you to produce a chain of forwarding records if the row continues to need relocation by further operations.

This is a good introduction to heaps and will feed into later work on how different types of indexes work.

Comments closed

Against DBCC Commands

Published 2016-04-15 by Kevin Feasel

Erik Darling doesn’t like (most) DBCC commands:

Not what they do, just that the syntax isn’t consistent (do I need quotes around this string or not?), the results are a distraction to get into a usable table, and you need to write absurd loops to perform object-at-a-time data gathering. I’m not talking about running DBCC CHECKDB (necessarily), or turning on Trace Flags, or any cache-clearing commands — you know, things that perform actions — I mean things that spit tabular results at you.

I completely agree.  One of the nicest things about SQL is that I can use the same syntax to read metadata that I can data.  DBCC commands are a jarring difference.

Comments closed

Modulo

Published 2016-04-15 by Kevin Feasel

Kenneth Fisher walks us through modulus division in SQL Server:

Now aside from the odd occasion when you actually need it for it’s simple purpose it’s a rather interesting way to get a rolling count. Basically you can use it to get a list back of 1,2,3,…n-1,0 where n is your divisor.

There are a few great use cases for modulo within SQL Server.  One not mentioned is building test data.  You can easily build a uniformly distributed set of randomized numeric values within a particular range using modulo math.

Comments closed