Notes: MS SQL

I was patching up our 1cFresh setup again (more trouble with data history — I'll tell that story sometime) when I ran into the platform's little UUID storage trick once more. Figured I'd write about it.

Long story short, UUIDs for metadata objects, just like those for regular object references, are stored in a 1C database as sixteen bytes. Except when you look at those bytes, they don't look anything like the UUIDs you're used to: for example, 8e1f9db8-4be2-11ed-a101-2c4d5450919d shows up in the database as 0xA1012C4D5450919D11ED4BE28E1F9DB8. The first time I saw this, I thought: some kind of hash, or a bit of little-endian wizardry.

What the platform actually does is rearrange the UUID groups while keeping the byte order within each group intact:

UUID:   aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
SQL:    dddd eeeeeeeeeeee cccc bbbb aaaaaaaa

Why? My guess is that it's an attempt to make database indexes work more efficiently. The thing is, the platform uses UUID v1 when automatically generating references for documents, catalog items, and similar objects. These UUIDs are time-based, with the timestamp split into three parts. The low part comes first, followed by the middle and high parts:

8e1f9db8-4be2-11ed-a101-2c4d5450919d
│        │    │    │    │
│        │    │    │    └─ Node ID (48 bits)
│        │    │    └────── Clock sequence + variant (16 bits)
│        │    └─────────── Time high + version (16 bits)
│        └──────────────── Time mid (16 bits)
└───────────────────────── Time low (32 bits)

In a 1C database, the same UUID looks like this:

0xA1012C4D5450919D11ED4BE28E1F9DB8
  │   │           │   │   │
  │   │           │   │   └─ Time low (32 bits)
  │   │           │   └───── Time mid (16 bits)
  │   │           └───────── Time high + version (16 bits)
  │   └───────────────────── Node ID (48 bits)
  └───────────────────────── Clock sequence + variant (16 bits)

So the rearrangement puts the timestamp parts in order from most to least significant. With the same clock sequence and Node ID, a later timestamp gives you a larger binary UUID value.

References form part of index keys. When keys arrive in a random order, new entries land all over the B-tree. If the target index page is already full, it has to be split: allocate a new page, move some entries over, and update the links in the tree. That's called a page split, and it can increase index fragmentation and hurt performance — both immediately and down the road.

With mostly increasing keys, though, new entries usually go at the end of the index. Pages fill up more sequentially, and inserts are often cheaper.

(of course, lots of concurrent inserts bring another problem: everyone wants to write to that same last page and starts elbowing each other out of the way, but that's a story for another day)

(love stuff like this: you're just staring at some weird little bunch of bytes in a database, and then you start to see what looks like a perfectly reasonable architectural decision made by some unknown developer years ago)

I already wrote about the error mentioned in this post’s title earlier, but here's the refresher: the platform chokes on dates later than the year 3999 (things start blowing up at 3999-12-01, I guess). In theory, dates like that should never make it into the database in the first place. In practice... Well, thanks to bugs in application code — and in the platform itself — they sometimes do.

The usual symptoms are pretty random and annoying: some reports stop working, some documents refuse to post, totals recalculation crashes, and so on. Basically, any code that touches records with those broken dates is now having a bad day.

The fix I outlined in the post linked above does work, but it's relatively slow: you need to configure the tech log, collect the output, and then parse it. The problem is that the platform crashes the moment it touches the first bad date, and there may be lots of them scattered across different tables. So you often end up doing this in multiple passes: run a check, hit an error, fix it, run again, hit the next one, repeat.

A few years ago, because I wanted a faster way to deal with this, I wrote a PostgreSQL query for it. The idea is simple:

1. Find all date fields in the database.
2. Build one giant query against those fields to look for dates beyond 1C's limit.

So yes, this query generates another query. Very normal behavior. Run the generated query, and you get the full picture: a list of tables containing invalid dates. After that, it's just engineering work — inspect the tables and decide what to do. In our case, bad dates sometimes show up in totals and turnovers, so we can just delete those rows and recalculate totals using the standard tools.

Why do this at the DBMS level? Because 1C itself can't really help here — as a reminder, the platform crashes as soon as it touches the bad records. That includes reads, not just writes. Also, in this case, going directly against the database is simply faster and more convenient.

The other day I rewrote the same query for MS SQL. It turned out longer — because, well, MS SQL likes to make you earn it — but the idea is exactly the same.

If you want to use it, keep this in mind:

• The _Fld626 field in the query text is the Fresh separator. In your database it may have a different name, or it may not exist at all.
• The query is written for a database that uses a 2000-year offset. If your database does not use that offset, you'll need to adjust the condition accordingly — see DATEADD().
• I added the XML output trick (FOR XML) to stop SSMS from truncating the generated mega-query. That seemed faster than messing around with type casts. The side effect is that before running the generated query, you need to replace > with >.

The other day my colleague and I were debugging an issue, nothing fancy, just another "why is this query behaving weird?" moment.

Simplified, the idea was: we read a table from the database and dump the result into a temp table. If a certain condition is met, we still want the temp table to be created, but it must be empty (regardless of whether the source table has rows or not).

The query looked roughly like this:

SELECT
    Table.Field1 AS Field1
FROM
    Table AS Table
WHERE 
    &Parameter

If we needed to copy rows from the source table into the temp table, we passed TRUE into the parameter; if we wanted the temp table to be empty, we passed FALSE.

Looks simple at first glance, but this trick is a silent performance foot-gun if the table being read is large.

The reason is how DB engines work with parameterized queries. Both MS SQL and PostgreSQL build the execution plan based on the query text, and in this example the parameter value does not affect the decision of whether the table should be read or not.

As a result, when this query runs, both engines will pedantically scan the whole table (or its index), even if the parameter is FALSE. In that case each row is just discarded, so the logic is technically correct, but we're wasting resources on a pointless scan and polluting the buffer cache, slowing down the system overall and diligently contributing to global warming :)

The fix is simple: inline TRUE/FALSE in the query body as a constant instead of using a parameter. Or use TOP so the query text is even simpler:

SELECT TOP 0
    Table.Field1 AS Field1
FROM
    Table AS Table

At the SQL level this turns into something like "SELECT TOP 0 ... FROM Table" for MS SQL and "SELECT ... FROM Table LIMIT 0" for PostgreSQL. The final plan will still contain a read operator, but the executor won't actually request any rows, so there's no real data scan (yay).

P.S. If you don't care about having proper column types in the temp table, you can even do this:

SELECT TOP 0
    UNDEFINED AS Field1

The performance gain, however, is so tiny that it's probably not worth over-optimizing for this. There are better hills to die on.

Once again, I came across a nasty bug when the platform broke the data history metadata table.

Outwardly, it looks like this: you update the database configuration, and when you try to restructure, the error “The data history metadata table contains duplicate records. Delete the duplicate records” pops up. The platform does not offer any clear way to find such records.

The problem can be solved at the database level. The table referenced by the error is _DataHistoryMetadata. It contains metadata versions of each object for which data history is being maintained. This allows the platform to understand what attributes the object had at any point in time if the data history is maintained for the object.

How does it work? Well, when the list of an object’s attributes changes (for example, an attribute was added to the catalog), the platform writes its metadata: specifically, it adds a new entry to _DataHistoryMetadata and stores in it the current list of object attributes, as well as the version number of this list (for example, when history is enabled for an object, the first version of metadata is saved, when adding some attribute, the second version is saved, and so on).

The platform also puts a mark in the created record that this particular version of the object is the most actual, and then removes this mark from the version that was marked as actual before.

So, the problem is that the platform sometimes forgets to take the last step, and two versions appear in the table at once, marked as current. The Designer understands this but cannot do anything.

The solution follows from the algorithm above: you need to find conflicting versions and revoke the mark of actuality from the one that is older. It is better to use queries: data history is often enabled for a bunch of objects, and the list of their attributes is constantly changing — in general, there will be so many versions in the table that the devil will break his leg.

If you also encountered this problem and are therefore reading this text, you can use the queries that I wrote:

1. get-issues.sql checks that there is an issue: it looks for metadata versions that are also marked as actual.
2. fix-issues.sql removes the actuality mark from those versions that are actually outdated.

Both queries are written for Microsoft SQL Server. If you use PostgreSQL, then here they are for this DBMS.

The queries will require a slight adaptation to a specific database: they use the _fld626 field, which is the data separator. In your _DataHistoryMetadata table, this field may be called differently, so you need to update its name to the current one. It will be difficult to make a mistake — the table has only one field with the _fld prefix.

P.S. Please bear in mind that the license agreement prohibits access to the database, bypassing the platform’s tools, so you can only go for such experiments if there are no other options left.

I did some reading online about date types in MS SQL Server, and I no longer wonder why 1C still clings to its date offset. People describe a whole bundle of DATETIME2 problems:

1. You can’t do basic date arithmetic. Want to subtract two dates or add a day? You’ll need a workaround.
2. Standard functions still return good old DATETIME, DATEADD for example. If you store your data as DATETIME2, you have to convert it.
3. Fields of this type don’t index well, since each DATETIME2 value is stored backwards, time first, date second. So the DBMS gets its row count estimate wrong and comes up with a bad execution plan.

You can read more on Towards Data Science or, for example, SQL Server Central.

MS SQL Server DATETIME fields cannot store dates before 1753. Try writing January 1, 0001 to the database, for example, and you’ll get an out-of-range value error. I thought this was a funny old quirk for such a well-established DBMS until I stumbled across the reason.

Briefly, Britain adopted the Gregorian calendar in 1752, losing eleven days in the process. That created a problem: suppose a user wants the difference in days between 1653 and 1753. What should happen? Account for the missing days? Ignore them? Add hints or settings?

Apparently, the developers decided not to get into all that and just cut off the date range. If you need earlier dates, DATETIME2 doesn’t have that restriction.

As for 1C, the platform originally used DATETIME and devised a special workaround to avoid trouble storing dates before 1753. In short, when writing dates to the database, it quietly adds two thousand years to each one, then subtracts them when reading. A user sees January 1, 2000 in 1C, while the database actually stores January 1, 4000.

I wonder why 1C hasn’t thrown this out yet. The platform uses DATETIME2 now, so it shouldn’t need the offset trick anymore. Maybe there’s a catch, or maybe they’re just being careful, but the DBMS developers themselves seem quite confident:

Your great great great great great great great grandfather should upgrade to SQL Server 2008 and use the DateTime2 data type, which supports dates in the range: 0001-01-01 through 9999-12-31.

Joe Stefanelli (SQL Server developer)

Perhaps it’s simply at the bottom of the priority list. Adding and subtracting two thousand years for every date increases hardware load, of course, but it gets lost among everything else.

PageIOLatch waits, which I wrote about today, are easy to encounter in any system, even small ones. PageLatch waits are the opposite: they’re hard to notice until you have several thousand users.

They occur when a page is already in RAM and several users try to modify it. Until one user finishes, the others must wait. The wait is usually tiny, but with many users competing for the page, it becomes noticeable.

Two things can cause this.

An Index Hotspot

This happens when we try to write heavily to the last page of an index, primarily one with a monotonically increasing key. Examples include indexes on reference-type fields: since late 8.2 versions, the platform has generated sequential GUIDs, reducing disk fragmentation and making the index key monotonically increasing.

What is an index with a monotonically increasing key? In 1C, register indexes on the period are one example. They don’t grow perfectly monotonically, but the important point is that we generally write data at the end. Document number and catalog code indexes also belong here: a new number is assigned and written to the end of the index.

Fixing a hotspot means changing the design: you need writes to land in different parts of the index, not all at the end.

tempdb System Pages

Creating or dropping a table updates a number of internal pages: IAM (Index Allocation Map), PFS (Page Free Space), GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), and others.

Why does this matter for tempdb? The 1C platform regularly creates an enormous number of tables there. Those internal pages get updated so often that you start seeing PageLatch waits.

MS SQL Server can optimize system page updates for tempdb so they happen less often, but sometimes even that isn’t enough, especially when tables are created with indexes, a very common case in 1C applications.

There are a few ways to tackle it. First, for DBMS versions before 2016, disable mixed extents using trace flag 1118. That removes the need for two internal page types, GAM and SGAM, so you no longer have to wait for their updates. An extent is eight data pages, or 64 kilobytes. If it contains pages from one table, it is uniform; if from several, mixed.

Second, split tempdb into multiple files. Since 2016, the DBMS splits it into eight files by default. Each file has its own internal pages, so spreading the work across files cuts the waits.

Third, reduce the number of temporary tables to lower the load on tempdb. Rewrite the most frequent queries so they run acceptably fast without temporary tables.

When a data page is read from disk into the buffer cache, it is latched for a very short time: it cannot be written, and in some cases cannot be read either. Waiting for that action to finish is a PageIOLatch wait.

Maybe you don’t have enough RAM. Or maybe some lousy queries are churning through the buffer cache, reading millions of rows to return three. The DBMS repeatedly fails to find the required pages in cache and has to read them from disk again and again.

So if you’re seeing a lot of PageIOLatch waits, faster disks, more RAM, and better queries will probably help.

Why would you need to know when MS SQL Server started? Say we’re looking into DBMS waits and want to know how long the sys.dm_os_wait_stats DMV has been collecting data. It keeps its data in RAM from the moment the DBMS starts, just like the other DMVs.

You can get the startup time from sys.dm_os_sys_info:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

There’s another way, using tempdb. It’s created when the server starts, so its creation time gives us the same answer. You can get it from sys.databases:

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

One catch: the server’s startup time doesn’t necessarily tell you when DMV data collection began. Someone might have cleared the stats by hand, for example like this:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

Whenever a SQL query could run but is waiting for another resource, information about the reason for the wait is recorded. You can access it through sys.dm_os_wait_stats.

Here are a couple of ready-made scripts to make sense of it:

• A script from Ian Stirk’s article “Uncover Hidden Data to Optimize Application Performance,” published in MSDN Magazine back in 2008. It lists wait types ordered by time, from the most prominent to the least.
• A script from Paul Randal’s article about analyzing MS SQL Server waits; there’s also a Russian translation on Habr. It filters out waits that always occur and can usually be ignored. It also adds a link to a detailed explanation of each wait, such as this description of CXPACKET.