No SQL Saturdays, but #dataweekender is online

The forced upon social distancing is new and – to say the least – different! For me, this means I’m exploring my local surroundings (we have really beautiful walking and running paths close to my house) a LOT more than usual. I’m spending my lunches running instead of looking for the best indian restaurants around the office.

Data Community Weekender Europe conference May 2nd, Call for speakers end on April 17th

A big part of my – and thousands of other SQL Server professionals – life is the Microsoft Data Platform Community – the SQL Family. That has obviously also seen some recent changes. I was gonna spek on SQL Saturday in Zagreb and Stockholm, I planned to go to London for SQL Bits and I had ideas for the SQL Server Usergroup which I’m co-leading. None of that happened. Speakers around the world tell the same story.

But instead of letting Nothing replace all of that, new things are happening. I’m speaking in Ohio next week. Or at least I’m speaking to user groups whose members live in Ohio. Online obviously, I’m not travelling to Ohio for a tuesday and a wednesday session. For the swedish SQL Server Usergroup (SQLUG Sweden), we were delighted to have Mark Hayes (T) speak about Power Platform the other day (Thanks Mark!).

Another thing which is happening is that online conferences are popping up. Me and a group of SQL Community organisers decided it’s too booring to just wait for social distancing rules to pass. That’s how Data Community Weekender Europe started. It’s really a crazy idea. From when Call For Speakers page came up, to when the conference happens (May 2nd), it’s roughly three weeks. In that time, we in the organising committee need to market the event to potential speakers, get session submissions in in a timeframe which is one tenth of a normal conference, do session selection, market the event to attendees and get the technical platform in place. Plus potentially recruiting volounteers for event day. But guess what? We WILL do it. We already have some great session submissions in and we’re working out the technical platform. We in the organising committee are all seasoned SQL Community organisers. We have pulled off crazy schedules before, though perhaps not at this scale. But we WILL pull it off. Stay tuned for the attendee registration. It will be announced here and on social platforms.

If you’re a Microsoft Data Platform speaker, send in your submission! It won’t be like attending a physical conference. The speakers’ dinner will be much less crowded, you’ll have to have your post session beer on your own. But you’ll be able to provide your amazing content. So thank you in advance, for submitting to this crazy idea of a conference!!

I spoke on SQL Server User Group Sweden

SQL Server User Group (SQLUG) Sweden had a meeting on October 24th. This one felt a bit special to me, as it was kind of a replay of my first time public speaking, in two ways. My first time speaking in public about SQL Server was on SQLUG Sweden, a few years ago. SQLUG Sweden arranged a Local Community Edition, with local speakers. That first time, it was me and Daniel Hutmacher (Twitter , Blog) speaking. And on October 24th, it was, again, me and Daniel Hutmacher speaking.

Continue reading “I spoke on SQL Server User Group Sweden”

Duplicate key in sysclsobjs using DBCC CLONEDATABASE

This blog post is about an error message I got the other day when using DBCC CLONEDATABASE in a T-sql-script. But first some background to DBCC CLONEDATABASE.

I was pretty excited about the DBCC CLONEDATABASE command, which was introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1. It creates a schema-only (that means all the database objects, but no data) copy of a database, keeping all statistics data, so that you can troubleshoot Query plans for certain queries without having to copy all the data. Before DBCC CLONEDATABASE (and to be honest probably also afterwords, DBCC CLONEDATABASE doesn’t replace all the needs) one had to make a full copy of a database to get the statistics data along. That’s usually copied to a test box. If the test box is identical to your production box, you’re almost fine. But on your test box, you don’t have the cached execution plans from the production box. Therefore, you might end up with very different Query plans in your test box. With DBCC CLONEDATABASE, you get a readonly copy of a database, on your production box and you can use that to tweak your queries and see what new estimated execution plans they get.

Continue reading “Duplicate key in sysclsobjs using DBCC CLONEDATABASE”

OPTION(USE HINT) – New SQL Server 2016 SP1 feature

Many SQL Server developers and admins found, after upgrading to SQL Server 2014, that some queries started taking much longer time than before. The reason is the new cardinality estimation formula which was introduced in SQL Server 2014. Cardinality Estimation is done all the time by the SQL Server optimizer. To produce a Query plan, the optimizer makes some assumptions about how many rows exist for each condition in the table. In most cases, the new cardinality estimation formula in SQL Server 2014 and onwards gives slightly better estimates and the optimizer therefore produces slightly better plans. In some cases however, mostly when there are predicates on more than one column in a WHERE clause or JOIN clause, the 2014 cardinality estimation is a lot worse than in previous versions of SQL Server.

Continue reading “OPTION(USE HINT) – New SQL Server 2016 SP1 feature”

Archiving with the OUTPUT clause

Most database developers have been faced with the task to archive old data. It could look something like this:

CREATE TABLE dbo.Cars(
  CarID int identity(1,1) PRIMARY KEY,
  BrandName varchar(100),
  ModelName varchar(100),
  releaseYear smallint
);

CREATE TABLE dbo.Cars_Archive(
  CarID int,
  BrandName varchar(100),
  ModelName varchar(100),
  releaseYear smallint,
  ArchivedDateTime datetime DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_Cars_Archive PRIMARY KEY(CarID, ArchivedDateTime)
)

And updating a row would often require a stored procedure and some explicit transactions

Continue reading “Archiving with the OUTPUT clause”

Statistics on ascending columns

I have had an annoying problem for a while. In a database used for a statistical survey system reporting is painfully slow in the beginning of each reporting period.

The system
The tables contain a few million rows. Ola Hallengren’s index maintenance (which includes UPDATE STATISTICS) is running weekly. Each month is a new reporting period. When a new reporting period opens, there are no rows for the current period. From the first day of the month, we receive input, each input being less than 2000 new rows in the table.

The problem
Reporting of any previous period is always consistent in execution time – around 3 seconds to produce a full report. That’s an OK performance. But when reporting is done for current period early in a reporting period, execution takes up to 10 minutes.

Continue reading “Statistics on ascending columns”

Generate time slots

Here’s an Inline Table Valued Function (TVF) for generating time-slots from a start-date to an end-date, given a certain time for each slot, given in minutes.

This would be useful for many applications, like scheduling systems, sales statistics broken down into certain slices of time etc. The function does have some limitations, eg there can’t be more than 100.000 minutes between start and endtime. This is easily fixed by just adding Another CROSS JOIN to CTE2, or by changing the DATEADD-functions to use hour instead of minute if that fits your purpose.

Continue reading “Generate time slots”