Reorganize Columnstore Indexes

I have created a script to help figure out when to reorganize columnstore indexes in SQL Server 2016.

Note that this script requires SQL Server 2016 (or later) because the database engine team made some great changes to columnstore indexes, allowing us to use REORGANIZE to clear out deleted rows and compact row groups together, as well as its previous job of marking open delta stores as available for compression.

The code is available as a Gist for now, at least until I decide what to do with it.  Comments are welcome, especially if I’m missing a major reorganize condition.

Incidentally, with CTP 3.3, I’ve noticed that there’s no reason to run index rebuilds over index reorganizations, at least in my environment.  It’s possible that there are plenty of environments in which it makes sense to do regular rebuilds, but I’ve noticed reorganization to be more efficient in terms of compacting row groups together, and it’s an online operation to boot.

Cross-Database Memory Optimized Queries Still Not Available

In SQL Server 2014, we learned that memory-optimized tables cannot join to tables outside the current database.  I wanted to see if this had changed in SQL Server 2016 CTP 3.2.

CREATE TABLE [dbo].[MOTest]
(
	MOTestID BIGINT IDENTITY(1,1) NOT NULL,
	ProductSubcategoryName VARCHAR(50) NOT NULL,
	CONSTRAINT [PK_MOTest] PRIMARY KEY NONCLUSTERED
	(
		MOTestID ASC
	)
) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

INSERT into dbo.MOTest
(
	ProductSubcategoryName
)
VALUES
('Mountain Bikes'),
('Road Bikes'),
('Touring Bikes'),
('Cranksets'),
('Chains'),
('Something Else');

SELECT TOP (100)
	*
FROM AdventureWorks2014.Production.ProductSubcategory ps
	INNER JOIN dbo.MOTest m
		ON m.ProductSubcategoryName = ps.Name;

My result:

(6 row(s) affected)
Msg 41317, Level 16, State 5, Line 24
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
In other words, we still cannot cross databases with memory-optimized tables.

New Talk Planned

I’m introducing a new talk for 2016:  Securing SQL Server.  Here’s my abstract:

A default SQL Server installation is reasonably secure, but “reasonably secure” doesn’t cut it anymore in an era in which one bad line of code, one week password, or one open port can result in your customer database ending up on Pastebin.  In this talk, we will look at different methods of securing a SQL Server instance, from venerable (principle of least privilege, Transparent Data Encryption) to novel (Always Encrypted, row-level security).  These tools and techniques will show us ways for developers, database administrators, and network specialists to work together to secure corporate assets and prevent CISOs from ending up on the nightly news.

My plan is to hit on a few topics, introduce some resources to learn more about the topic, and move on.  The goal is to provide an overview of some of the tools and technologies available, including:

  • Applying the principle of least privilege to SQL Server logins, users, and roles.
  • Implementing Transparent Data Encryption.
  • Implementing Always Encrypted in SQL Server 2016.
  • Implementing row-based security, both in SQL Server 2016 and prior to 2016.
  • Using network segmentation to limit access to SQL Server instances.
  • Writing good, parameterized SQL to prevent against SQL injection.
  • Reducing the SQL Server surface area.

There are entire books on SQL Server security, so a one-hour talk can’t cover everything.  Nonetheless, my intent with this talk is to give everybody who attends at least one new resource.

Checking SSRS Report Usage

At work, I recently was asked whether some reports were still in use. These are SQL Server Reporting Services reports, so fortunately there’s an easy way to find that out. Go to the SQL Server instance hosting Reporting Services and go to the ReportingServices database. Then, the following query will get reports run over the past week:

SELECT
	el.ReportPath,
	SUM(CASE WHEN el.RequestType = 'Interactive' THEN 1 ELSE 0 END) AS Interactive,
	SUM(CASE WHEN el.RequestType = 'Subscription' THEN 1 ELSE 0 END) AS Subscription
FROM dbo.ExecutionLog2 el
WHERE
	el.TimeStart > DATEADD(WEEK, -1, GETUTCDATE())
GROUP BY
	el.ReportPath
ORDER BY
	el.ReportPath;

This also breaks out interactive report requests versus subscriptions to see if people are seeking out these reports or if you’re pushing them.

Presentation Goals For Next Year

This was a big year for me as a presenter, having the opportunity to present at a dozen SQL Saturdays, not to mention SQL Server and .NET user group meetings in the Raleigh area as well as Lynchburg, Virginia.  Over the course of the next year, I want to expand upon my hard-earned experience and take the next step as a speaker.  Here are my goals:

  1. Present four new talks.
  2. Present to two brand new audiences.
  3. Speak at 12 SQL Saturdays and eight user groups.
  4. Get paid to speak at a conference.

Present Four New Talks

Over the past year and a half, I’ve honed two talks to the point that I’m happy to give them anywhere, any time.  I also have a couple of talks that I’ve given in the past and would like to get tuned to the point that I’m totally comfortable with them.  That range is okay, but I want to expand and give some brand new talks.  Some of it is me embracing the new, shiny things—R and Hadoop—as well as some of the “weird” side of the SQL world, like BIML.

Why four talks?  Because that sounds like an achievable goal.  It’s one talk per quarter, with the expectation that I can give each talk two or three times, sharpening it as I go along.  I’ll have plenty of material for user groups, and this will make me a more enticing option to bring to a user group or conference, because I’ll have a broader range of topics.  This will also help me grow as a speaker.

In order for me to grow as a speaker, I need to get out of my comfort zone present to some new audiences, and the easiest way to do that is to build up talks geared more for Python developers, data analysts, Hadoop developers, and people who know the technologies better than I do, but may not know how to tie them to SQL Server.  This leads right into my next topic.

Present To Two New Audiences

The RTP area has an abundance of user groups covering all kinds of technical interests.  Historically, I’ve presented at the .NET User Group’s data SIG as well as the local PASS Chapter.  I really enjoy both of those groups, but I’m also a member of several other user groups, including Powershell, Hadoop, and research & analytics groups.  I’ve never presented before these audiences, but as I get to know people in that field a little better and begin to understand the world according to a Hadoop developer or administrator, or what makes a data analyst tick, it will help me grow in my career and open up new interests.  This is an exciting prospect.

To these audiences, I think the biggest thing I have to bring to the table is my knowledge of the SQL Server side of things.  This will let me focus on topics like how to integrate SQL Server + Hadoop (using PolyBase, SSIS, Sqoop, etc.) or SQL Server + R (using RODBC or SQL Server 2016).  A fair percentage of the audience may not use SQL Server, but I want to entertain and inform, even if it’s not quite in their wheelhouse—that’s what I do with Hadoop, after all, when I bring it to audiences of .NET and SQL developers who haven’t yet seen the elephant in the room.

I’d love to be able to expand from there and become more than “the SQL Server guy who knows a little X” but the only way to do that, short of a career change, is to start at the beginning, make these connections, and learn from others in these communities.  And like the saying goes, there’s no better way to learn than to teach.

Speak At 12 SQL Saturdays And 8 User Groups

Holy moley, that’s a lot of talks.  I had the great fortune to speak at SQL Saturdays across the eastern US and midwest, and it was hard at times.  2016 is going to be the year in which I take those hard-earned lessons from the past year and push a little further.  I love helping people learn, really enjoy traveling to speak, and have grown to appreciate the value of making these connections with other speakers, community leaders, and conference attendees.

So where do I plan to speak?  Obviously, the answer is dependent upon who selects me, but I’d love to repeat at the SQL Saturdays I hit last year, as well as adding some places like Kansas City (for the barbecue crawl and to see some friendly faces in the area) and China.  The wife and I are thinking of a trip to China next year and the opportunity to present at a conference in Peking, Shanghai, Tianjin, or someplace else would fit perfectly with our plans.

A little closer to home, I know there are several user groups in the North Carolina and Virginia area, and introducing myself to those SQL Server User Groups and .NET User Groups would be a great way to give back to some of those places.

Get Paid To Speak At A Conference

I don’t want to turn speaking into a full-time job, but I think the next big step for me is to get accepted to speak at a conference in which I get paid to speak.  I love SQL Saturdays and what they bring to the SQL Server community.  They’re a fantastic way of seeing the world and getting good, free training.  But for me as a speaker, the next step is to get accepted to a conference which will pay me to come out.  It’s not so much about the money (although hey, I like money), but rather the vindication in knowing that I have the skills to teach and train people willing to fork out hard-earned cash.  I want to show that I can earn that respect.

Note that “paid conference” doesn’t have to be a huge one like PASS Summit or DevConnections.  I may submit for some of those, but I expect that I’m at least a year or two away from serious consideration.  I need to hone my skill and improve my performances before I think I’m ready to speak at the big show.

Presentation Year In Review

This year was my breakout year for presenting.  Here’s a quick look by the numbers.

User Group Meetings

I presented at the following user group meetings this year:

  1. TriNUG Data SIG, January 21st.
  2. TriPASS, March 17th.
  3. Lynchburg SQL Server User Group, March 26th.
  4. TriNUG Data SIG, April 22nd.
  5. F#/Analytics SIG, July 28th.
  6. TriNUG lightning talk, December 9th.
  7. TriPASS, December 15th.

SQL Saturdays

I presented at the following SQL Saturdays this year:

  1. Cleveland, February 7th.
  2. Tampa, February 28th.
  3. Richmond, March 21st.
  4. Jacksonville, May 9th.
  5. Rochester, May 16th.
  6. Nova Scotia, June 8th.
  7. Chattanooga, June 27th.
  8. Columbus, July 11th.
  9. Indianapolis, August 8th.
  10. Omaha, August 15th.
  11. Pittsburgh, October 3rd.
  12. Raleigh, October 10th.  — Yeah, I didn’t technically present here, but I ran it, so I’m counting it!
  13. Charlotte, October 17th.

Podcasts

I participated in my first podcast this year, when Carlos Chacon interviewed me for the SQL Data Partners podcast.