Using fn_dblog

I like the fn_dblog function even if it is undocumented. It enables you to read from you transaction log which contains very valuable information about stuff that is happening in your database. It is for example the only way I now at this time to determin which object experienced a page split if you search for the LOP_DELETE_SPLIT operation.

SELECT *
FROM ::fn_dblog(NULL, NULL)
WHERE operation = 'LOP_DELETE_SPLIT'

This is the usage of that system function you see everywhere, just passing NULL's for the @start and @end parameters, which could limit the LSN's (Log Sequence Number) to be searched. It took me some time to figure it out because just passing the output of the function as a parameter simply doesn't work. You can't use a LSN like for example 00000073:000020fa:0001 as a value for the parameters, you have to translate the hexadecimal values into its numeric equivalent (115:8442:1).

Let's show this with some code and because there's no easy way to make this conversion, I came up with something quick & dirty. At last an example of the actual usage of the parameters of fn_dblog on the web ;-)

SET NOCOUNT ON
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)

SET @LSN = (SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))
PRINT @LSN

SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT @tbl EXEC(@stmt)

SET @LSN_HEX =
(SELECT i FROM @tbl WHERE id = 1) + ':' + (SELECT i FROM @tbl WHERE id = 2) + ':' + (SELECT i FROM @tbl WHERE id = 3)
PRINT @LSN_HEX

SELECT *
FROM ::fn_dblog(@LSN_HEX, NULL)

Missing checkpoints in Profiler

Earlier on, I reported the new possibility to trace the start of a checkpoint from within profiler. I tested that new event with manual checkpoint-statements, because that's exactly what happens in the production environment of our application where we force them.

Anyway, we just discovered that with a 'normal' checkpoint, triggered by the database engine itself, the event isn't raised. You can easily simulate this behavior by comparing a profiler trace with the perform counter MSSQL:Buffer Manager-Checkpoint pages/sec on a database with some load. Compare the periodic checkpoint with one you forced yourself. Weird and disappointing.

Deployed package version

You develop SSIS-packages from within the Business Intelligence Development Studio (or better, just call it Visual Studio) and you use Management Studio for deploying and managing packages on a certain environment. Simple question I got today from a colleague: how do you determine the version that is deployed on a server?

In the old days with DTS-packages that was straightforward, but now with SSIS, I couldn't find the elegant answer straightaway. Yes, you can export the package to the file system and open it in VS, but that's no decent solution. Querying msdb like the solution below - it's an option:

WITH Folder (FolderID, ParentFolderID, FolderName) AS
(
SELECT folderid, parentfolderid, cast(foldername as varchar(max))
FROM msdb.dbo.sysdtspackagefolders90
WHERE parentfolderid IS NULL

UNION ALL

SELECT f.folderid, f.parentfolderid, cast(pf.FolderName + '\' + f.foldername as varchar(max))
FROM msdb.dbo.sysdtspackagefolders90 f
INNER JOIN Folder pf on pf.FolderID = f.parentfolderid
)
SELECT f.FolderName, p.name as PackageName, p.vermajor, p.verminor, p.verbuild
FROM msdb.dbo.sysdtspackages90 p
INNER JOIN Folder f on f.FolderID = p.folderid

That was the best I could come up with at that time, but at home I have more time to look into it. And actually it's quite simple, because of the great Summary-window (F7) in SSMS. It contains one report (General) when you connect to SSIS, click on a package and there is all the info you can consult in the system tables. Powerful stuff!

Monitoring checkpoints in Profiler

Back in the days when I was still in the stresstest-team of our application, I tried to monitor as much as possible from within the SQL Server Profiler. Ok, you still need to use Perfmon of course to capture the most common Windows performance counters, but I find it very handy to have an indication of when exactly checkpoints occur along with the statements that are less performant because of it.

In SQL Server 2000, I was forced to write a little routine to trigger my own (user defined) events for the Profiler as there are no standard events that can be monitored. I used the sysperfinfo view to provide me of an indication of when a checkpoint was in progress. I guess that reading the transaction log of the monitored database is a valid alternative, but I haven't actually tried that one (ah... the greatly undocumented ::fn_dblog()).
Either way, both methods require custom scripting and are not accurate.

Now, in SQL Server 2005, you get of course the option to correlate performance counters with events logged in Profiler, which is a very neat feature, but I like to store all the info in one SQL Trace file.
Luckily, you also get the Security Audit Database Operation Event which is raised when a checkpoint begins. It's a pity that there's no counterpart for the end of a checkpoint :(.

Performance tuning guide for SQL Server

I'm no Google-guru, so I actually had a lot of trouble finding the following documents. But because this is my long term memory, I couldn't do without them.
So here it is, some indispensable old school performance tuning stuff: Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing. And the even more unfindable document version.

And it also has a nice equivalent for 2005: Troubleshooting Performance Problems in SQL Server 2005.

Return types of IDENTITY-functions

I ran into some strange inconsistency a couple of days ago when I was reading about identity-columns, -functions and -properties in the SQL Server Books OnLine (BOL).
I was actually only looking for the correct syntax of DBCC CHECKIDENT for reseeding an identity-column of a table, as I was wondering what the datatype of the new_reseed_value has to be.

DBCC CHECKIDENT ('table_name' , RESEED , new_reseed_value)

No big secret though, the datatype of an identity column can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0. That explains why the return type for the functions @@IDENTITY and SCOPE_IDENTITY() is numeric, actually numeric(38,0), which is the datatype with the biggest precision of the whole list.
So far so good, but why has the similar function IDENT_CURRENT('table_name') a return type of sql_variant according to the BOL? It just doesn't make sense, certainly because I have no indication that the function actually returns a sql_variant. The way I see it now, IDENT_CURRENT simply returns numeric-data like the other functions, not sql_variant.

USE TempDB
GO
CREATE TABLE tmpTable
(
i NUMERIC(12,0) IDENTITY
)
GO
INSERT tmpTable DEFAULT VALUES
GO
DECLARE @sql_variant SQL_VARIANT

--Determining the datatype, precision and scale
SELECT @sql_variant = SCOPE_IDENTITY()
SELECT
SQL_VARIANT_PROPERTY(@sql_variant, 'BaseType') AS [BaseType],
SQL_VARIANT_PROPERTY(@sql_variant, 'Precision') AS [Precision],
SQL_VARIANT_PROPERTY(@sql_variant, 'Scale') AS [Scale]

--Determining the return type for the identity-functions
SELECT
@@IDENTITY AS [DT_@@IDENTITY],
SCOPE_IDENTITY() AS [DT_SCOPE_IDENTITY],
IDENT_CURRENT('tmpTable') AS [DT_IDENT_CURRENT]
INTO tmpReturnType

EXEC sp_help 'tmpReturnType'
GO
DROP TABLE tmpReturnType
DROP TABLE tmpTable
GO

We went to Devconnections Europe

Just back from Nice where we attended Devconnections Europe! At first I was very enthusiastic about it and in fact the speakers and sessions didn't let me down, but the organizers of the event seemed much less pleased after noticing that the attendance was rather low. But the beautiful city of Nice easily made up for the mess-up with the sessions and the poor service during the breaks.

The session of Itzik Ben-Gan about row numbers (Discover the Power of Row Numbers) was very inspiring. It really made the attendees think about T-SQL and the reason why new additions in 2005 can help us to overcome everyday problems in a more efficient fashion.

Grant Dickinson had a talk about programming against SSIS (Integrating Integration Services into Your Custom Applications) which gave me some ideas. Very hands-on stuff that I tend to use as soon as I see the opportunity.

And then there was the SQL-guru Gert Drapers of whom we attended most sessions (SQL Server 2005 Memory Internals / SQL Server 2005 Bulk Loading / SQL-CLR: A Performance Evaluation / Database Mirroring Inside Out / Exploring and Optimizing SQL Server 2005 Using Dynamic Management Views). Really too much interesting stuff to sum it up, just follow the link http://www.sqldev.net/events.htm ;-)

Last but not least, Matt Nunn gave us some interesting news about the future development on SQL Server and Visual Studio. His session about offline (Occasionally Connected Systems) gave us some input for our future development.