Here is another thing i found and modified a little.

Takes under a second for 1 millon rows and returns gaps size sorted 😉

Change tables and fields surrounded by []

----------------------------------------

SELECT

LastSysId + 1 AS GapFrom,

NextSysId - 1 AS GapTo,

NextSysId - (LastSysId + 1) AS GapSize

FROM

(

SELECT

(

SELECT TOP 1

[SysId]

FROM

[yourtable]

WHERE

[SysId] < a.[SysId]

ORDER BY

[SysId] DESC

) AS LastSysId,

a.[SysId] AS NextSysId

FROM

[yourtable] AS a

LEFT JOIN

[yourtable] AS b ON a.[SysId] = b.[SysId] + 1

WHERE

b.[SysId] IS NULL

) AS a

WHERE

LastSysId IS NOT NULL

ORDER BY

3 DESC