SQLUGBE Session – Resources

Last week I presented a session to the SQL Server User Group Belgium. The session about CDC was my first ever UG presentation, and it was exciting and scary at the same time. But I was glad there were some familiar faces in the audience. Not only Pieter Vanhove (Blog | @Pieter_Vanhove) was attending, but also Koen Verbeeck (Blog | @ko_ver) and Johan Bijnens (@alzdba). This made it a bit easier to talk to the group of attendees (about 25), because I knew I could use their help if things didn’t go as planned. But the session went okay. I felt a bit too nervous, talked too fast, and didn’t really watch the time. All rookie mistakes of course. There was a 2,5 hour timeslot reserved, and in the end I talked about CDC for 2 hours and 15 minutes! Surprisingly, it didn’t feel like that at all! Sorry to all the attendees for that!

A few attendees came by after the session, and told me their opinion on the session. That first feedback was better than I could imagine. The rest of the feedback will be send to me later, so hopefully that feedback will be as good as the first feedback I got. This experience convinced me I want to look for the next challenge. A new event, a different audience, and new topic(s).

For all the attendees (and the ones who just want to check out the slides and demos), I shared the resources:

 
Slide deck (pdf)
Slide deck + demos (zip)

 
Thanks again to all attendees, thank you SQL Server User Group Belgium for organizing the session, and thank you Kohera for sponsoring the session!

 
 
UPDATE:
The evaluation just came in, and it’s better than I could ever expect!

Question: Fulfilled this evening your technical expectations?
Score: 7.8 / 9

Question: How do you evaluate presentation skills?
Score: 7.56 / 9

Question: How do you evaluate presenters knowledge?
Score: 8.2 / 9

Looking back at 2014, and forward to 2015

The last year was a little bumpy, and had a lot of ups and downs. Looking back at 2014, I accomplished a lot of cool things, and set some things in motion for 2015 already. So what happened for me in 2014?

 
FORG
2014 was the first year I joined the Friends of Red Gate (FORG) program. I was surprised and felt honored, that I got the confirmation in February that I was a part of FORG. Red Gate has always been a company I respected a lot (so much, that some people asked if I had Red Gate stocks, when I mentioned them again in a conversation). The fact that they let me in their program was a huge honor for me. They enabled me to contact the product teams directly, which (hopefully) ended up in us (FORG members and Red Gate developers) improving the tooling this year.

For next year, I’m hoping I can continue working with Red Gate. There are a lot of things I would like to do in cooperation with Red Gate, that I didn’t or couldn’t do this year. Hopefully 2015 will be the year that I can do some of the things I have on my to-do list!

 
SQLCoop
In February of 2014, I started SQLCoop with 3 other people:

Julie Koesmarno (Blog | @MsSQLGirl)
Mickey Stuewe (Blog | @SQLMickey)
Chris Yates (Blog | @YatesSQL)

 
We wrote a total of 8 posts this year, in which we talked about a number of subjects. Hopefully for next year, there will be more posts about various subjects.

 
Blogging
My blogging in 2014 didn’t go exactly as planned. At the end of last year, I set myself a goal: try to blog more often, and try to write a blog post once a week. I really tried to keep up with that pace, but unfortunately I lost that pace early in the year. But I did manage to publish 30 posts this year (including this one). This is one more than last year. So I did reached my goal (blog more than last year), but not as much as I would’ve liked.

 
Blog traffic
The blog traffic increased again, in a way I didn’t expect. Between 2012 and 2013, the traffic to this blog was quadrupled. This was a stunning success, and I didn’t expect any growth for this year. But looking at the statistics right now, I see that the visitors to my blog are more than doubled comparing to last year! I was hoping for a small increase, but not this much! Looking at that, I can only hope I helped a lot of people save time, with the ramblings I call blog posts!

 
Events
This was the year of the great events. In July, I attended SQLBits in Telford, UK. This was a great event, wonderfully organized, and certainly an event everyone needs to visit at least once! People told me this was the biggest and nicest SQL event in Europe, and I can tell you: they weren’t lying! What an event, what an attendees, and what a great job from the organizers!

In October there was SQL Saturday Holland. This year was (again) better than last year, and it was a great event! Also, I got the chance to volunteer at the event. Definitely something I’m doing again next year. It was really great to help the organizers out. It was a wonderful day with great sessions, and it was the moment I got my first speaking opportunity. Nothing but good things happened that day.

 
New friends
This year I’ve also met a number of wonderful people from the community. A lot of them I met at SQLBits this year, but also at SQL Saturday. It was great to meet Matan, Tobiasz, Chris, Julie, Nicky, Pieter, Brent, Kevin, Grant, etc, or talk to them again. Meeting all those people really shows how big and diverse the community is, and it reminds me why I like this community so much. I’m confident I will meet a lot of cool people next year, and catch up with people again after not seeing them for too long.

 
Speaking
This year I started to seriously think about speaking at events and user groups. All the good stories I heard from friends in the community made me consider this. In July during SQLBits, I submitted 3 sessions for SQL Saturday Holland, and that was the first step. I wasn’t surprised that I didn’t get picked. There were a lot of good speakers, and I was a newcomer with no experience. But at SQL Saturday Holland, I met Pieter Vanhove (Blog | @Pieter_Vanhove). We talked about all different kinds of subjects (including speaking), and he invited me to do a session at the SQL Server User Group Belgium in January 2015. This means I’m speaking at my first ever User Group session. Hopefully this will be the start of something good, with which I can give back to the community in time.

 
I want to with you all a very happy, successful and interesting new year! Go and try to make 2015 your year, and take that leap of faith when you have the chance! I know I did! :)

Speaking at SQL Server User Group Belgium

I am extremely proud and thrilled to announce that I will be speaking at the SQL Server User Group Belgium on the 15th of January 2015. The session is called “Speeding up your ETL with CDC”. In this session you’ll get an introduction to CDC, a look at why CDC is your best option, we’re going to look at the pros and cons of CDC, and see how easy it might be to implement it in your company.

About 2 months ago, I met Pieter Vanhove (Blog | @Pieter_Vanhove) at SQL Saturday Holland. We started talking about speaking at events, how to get started, and how I would like to try it out someday. Then Pieter asked me if I would be interested in speaking at SQL Server User Group Belgium. I said yes instantly!

I want to thank Pieter and the SQL Server User Group Belgium for this opportunity!

This will be my first user group session, and I’m not sure what to expect. I’m so glad I got so many tips from great friends in the community (thank you all!!!), and these tips will help me a lot. Let’s hope all works out, and that I can make my first event a success!

The event details can be found on the SQLUG.be website, where my session is announced. You can register here.

And thank you Kohera for sponsoring the event!

SQL Sentry Plan Explorer: You can’t live without it

Every data professional out there will run into slow running queries, or performance issues you can’t explain at some point. At that moment, it’s difficult to explain the problem without looking at an execution plan. SQL Server Management Studio (SSMS) has build-in functionality to look at these execution plans. But this isn’t always as useful as we would like it to be. But there is a great free tool that’ll help you with query-tuning and pinpointing the issue in bad performing queries.

 
Download
SQL Sentry Plan Explorer is free, and available on the website of SQL Sentry. Even though it says it’s a trial version, it won’t expire after a certain period. The only thing that’s “trial” in this version, is that some functionality is blocked in the free version. But all the good stuff is available in the free version.

 
Integration in SSMS
When you start the install, the install doesn’t ask you to shut down SSMS. But I recommend you do. If you don’t close SSMS, you won’t see the SSMS add-in menu. It will show after the setup is finished, and you start a new instance of SSMS.

 
Creating a query, and opening it in Plan Explorer
As an example, I’ve created a really bad query on the Adventureworks2012 database:

USE AdventureWorks2012
GO


DECLARE @MinPrice INT = -1;


WITH Shipping AS
(
SELECT
  PV.ProductID AS ProductID,
  UM.Name AS ShippingPer,
  CASE
    WHEN UM.Name = 'Each' THEN PV.StandardPrice
    WHEN UM.Name = 'Dozen' THEN PV.StandardPrice / 12
    ELSE @MinPrice
  END AS ShippingCostPerUnit
FROM Purchasing.ProductVendor AS PV
INNER JOIN Production.UnitMeasure AS UM ON UM.UnitMeasureCode = PV.UnitMeasureCode
)


SELECT
  P.ProductID,
  P.ProductNumber,
  P.Name,
  S.ShippingCostPerUnit,
  Quantity.TotalQuantity,
  P.ListPrice,
  dbo.ufnGetProductListPrice(P.ProductID, GETDATE()) AS XYZ,
  Locations.TotalLocations,
  P.ListPrice + S.ShippingCostPerUnit AS TotalCostProduct,
  Quantity.TotalQuantity * P.ListPrice AS TotalValueStock,
  ((Quantity.TotalQuantity * P.ListPrice) / Locations.TotalLocations) AS AverageValuePerLocation
FROM Production.Product AS P
INNER JOIN Shipping AS S ON S.ProductID = P.ProductID
CROSS APPLY
(
  SELECT SUM(Quantity) AS TotalQuantity
  FROM Production.ProductInventory
  WHERE ProductID = P.ProductID
  GROUP BY ProductID
) AS Quantity
CROSS APPLY
(
  SELECT COUNT(LocationID) AS TotalLocations
  FROM Production.ProductInventory --WITH(INDEX(0))
  WHERE ProductID = P.ProductID
) AS Locations
WHERE P.ListPrice <> 0
ORDER BY P.ProductID, P.ProductNumber, P.Name, TotalLocations ASC

 
If you run this query in SSMS, and you include the actual execution plan (Ctrl + M), it will show you the execution plan in a separate result window. In this window, you’ll have the option to right-click, and choose “View with SQL Sentry Plan Explorer”:

 
If you click this, you’ll open Plan Explorer, and it will show you the execution plan:

 
So, is that all?
I can almost hear you think: So what’s the difference between Plan Explorer and the default SSMS windows, besides the fancy colors? Just take a look at all the extra opportunities you get with Plan Explorer. For example, how does your join diagram look? Can you pull that from SSMS? No? Well I can do that with Plan Explorer:

 
Your most expensive operation in the query? Yes, you could do that by looking at the percentages shown in your queryplan. But can you show me why they are that expensive? Again, I can do that with Plan Explorer:

 
Can you do you job without it?
If I ask myself this question, I think I can honestly answer this with: yes. Yes, I can do my job without it. But this makes it SO much easier to pinpoint the problem, and to get a quick overview of the query performance. Normally I look at the queryplan in SSMS first, and then immediately open up a Plan Explorer window, to take a closer look at the problems.

So if you write queries on a daily basis, and you’re responsible for, or interested in, qery performance: download it today, and try it out yourself. I’ll promise you, you won’t regret downloading it!
If you want to read more about SQL Sentry Plan Explorer, don’t forget to check out these blog posts:

Julie Koesmarno: Analysing Execution Plans With SQL Sentry Plan Explorer
Mickey Stuewe: On sabbatical
Chris Yates: SQL Sentry Plan Explorer – Don’t Leave Home Without It

Restoring or Moving a database with CDC enabled

When you have CDC enabled on your database, and you want to move it on another instance or restore it somewhere, you need to take some extra steps to make sure CDC is transferred correctly. When asking myself the question how this works, I decided to do some testing.

 
Create test resources
In order to test this, we need to create some test resources. I’ve created a new database, added a table and enabled CDC:

CREATE DATABASE [CDC_DB]
 ON PRIMARY
(NAME = N'CDC_DB', FILENAME = N'C:\Databases\#Backup\CDC_DB.mdf')
 LOG ON
(NAME = N'CDC_DB_log', FILENAME = N'C:\Databases\#Backup\CDC_DB_log.ldf')
GO

USE CDC_DB
GO

CREATE TABLE dbo.Customer
  (CustomerID INT CONSTRAINT PK_Customer PRIMARY KEY IDENTITY(1,1),
   FirstName VARCHAR(50),
   LastName VARCHAR(50))
GO

EXEC sys.sp_cdc_enable_db
GO

EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Customer',
  @supports_net_changes = 1,
  @role_name = NULL,
  @index_name = 'PK_Customer'
GO


INSERT INTO dbo.Customer
  (FirstName, LastName)
VALUES
  ('John', 'Doe')
GO

Now that we have the resources, we can take a backup of the database:

BACKUP DATABASE CDC_DB TO DISK = 'C:\Databases\#Backup\CDC_DB.bak'

 
Restoring the backup
To show you the default restore doesn’t work, let’s restore the backup next to the original database:

RESTORE DATABASE CDC_DB_RESTORE FROM DISK='C:\Databases\#Backup\CDC_DB.bak'
WITH
   MOVE 'CDC_DB' TO 'C:\Databases\#Backup\CDC_DB.mdf',
   MOVE 'CDC_DB_log' TO 'C:\Databases\#Backup\CDC_DB_log.ldf'

If we now try to select data from the change table of CDC, we get an error message:

 
But CDC was in the old database, so why wasn’t it restored? If you look for a solution, Books Online / MSDN doesn’t say anything about this. But luckily other community members blogged about this before (see this and this article). There is an option you can add to your restore:

USE master
GO

DROP DATABASE CDC_DB_RESTORE
GO

RESTORE DATABASE CDC_DB_RESTORE FROM DISK='C:\Temp\#BackupTest\CDC_DB.bak'
WITH
   MOVE 'CDC_DB' TO 'C:\Temp\#BackupTest\CDC_DB.mdf',
   MOVE 'CDC_DB_log' TO 'C:\Temp\#BackupTest\CDC_DB_log.ldf'
, KEEP_CDC
GO

 
If you add “KEEP_CDC” to your backup statement, it will also restore CDC. But that’s not your only option.

 
Detach and Attach

Another option you have is to detach your database, copy the files to another location, and attach the database again. Let’s detach the database first:

USE master
GO
ALTER DATABASE [CDC_DB_RESTORE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'CDC_DB_RESTORE'
GO

 
Now that’s done, we can copy the files to another directory if needed. You could also just attach it on the same instance:

USE master
GO
CREATE DATABASE [CDC_DB_RESTORE] ON 
( FILENAME = N'C:\Temp\#BackupTest\CDC_DB.mdf' ),
( FILENAME = N'C:\Temp\#BackupTest\CDC_DB_log.ldf' )
 FOR ATTACH
GO

 
This also allows you to use CDC like nothing happened. The database is still CDC enabled, the change tables are there, and the captured data is still in the change table. But there’s one thing missing when you move your CDC database to another instance: the capture and cleanup jobs.

 
CDC Jobs
So how do we recover the jobs? Script them from one instance, and create them on the other instance? There’s a better way to do that. You can just run the statement below to add the jobs to your instance:

USE CDC_DB_RESTORE
GO
EXEC sys.sp_cdc_add_job 'capture'
GO
EXEC sys.sp_cdc_add_job 'cleanup'
GO

 
And if you move your database, you can also clean your instance, and run the statement below to remove the leftover jobs:

USE CDC_DB_RESTORE
GO
EXEC sys.sp_cdc_drop_job @job_type = N'capture'
GO
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup'
GO

 
It is possible, but…
Moving or restoring a database with CDC gives you a few challenges, and even though there are some issues, it’s not impossible. But before you start, you need to think about the approach you need to take, in order to move/restore a database without losing CDC. Depending on your environment and possibilities, you might want to use a specific option, or perhaps can’t use the option you would like. There is a way around, but I would recommend you to test it first, before you run this on production databases!

T-SQL Tuesday #59 – My Hero

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “My Hero”. If you want to read the opening post, please click the image below to go to the party-starter: Tracy McKibben (Blog | @RealSQLGuy).



 
Everybody needs a hero. Ever kid needs a champion. Someone you look up to. It doesn’t matter if you’re young or old, everybody needs that. If you don’t have a hero to look up to, it’s hard to dream big.

 
The unsung heroes
Not all heroes are out there like we expect them to be. There are some heroes that like to stay below the radar. But does that make them any less of a hero than a hero that is really out there? I don’t think so. But defining a hero is much easier when it’s a person that is well known by the community.

If you take one of the most known people out there, for example Thomas LaRock (Blog | @SQLRockstar), Brent Ozar (Blog | @BrentO), or Grant Fritchey (Blog | @GFritchey). Do you define those people as heroes? I know I do. They make an effort for the community, help people wherever they can, and they are working to make us all better SQL Server Professionals. But they are out there, and are widely acknowledged as heroes by many people. So defining those people as heroes, even though they are, is easy. It’s praising the unsung heroes that is hard.

 

 
The fallen heroes
There’s a well known saying that says: “All that glitters is not gold”. And that also goes for the heroes in the community. In the end, they are just people like you and me. They have good and bad character traits like everyone else. But some heroes turn villain in the end. They use their fame to get what they want, or mistreat other people for not being the hero they are. Unfortunately I know a few of those “heroes” as well…

But does mistreating people make that hero a little less of a hero? I think it does. Even heroes can fall of their pedestal. And even though people find you a hero, you can’t mistreat other people, or tell them they’re less than you. If you’re the hero, it’s your mission to lead other people to victory. Not to put them down, and convince them they can’t do something.

 

 
Become a hero
A hero can come in many shapes and sizes. And you don’t become a hero overnight (even though that would make things easier sometimes). It takes a lot of effort to become a hero, and to be seen as the hero. And you know what? They make it look so easy, and that’s their strength and weakness at the same time.

So when are you becoming a hero? What are your plans to become a hero? What steps do you need to take now, in a week, in a month, or even in a year? You can’t become a hero you say? Yes you can! Everybody can become a hero! Just try it, by giving back to the community. Answer questions on forums, write blogs, start presenting, anything is possible!

Are you the next hero Gotham needs…?

Follow

Get every new post delivered to your Inbox.

Join 60 other followers