SQL Server 2005 System Views Map

I always seem to forget the link for the great SQL Server 2005 System Views Map so I might as well dedicate a little post about it. The map gives an overview that seems quite chaotic at first, but when you zoom in you can find valuable information to be able to combine several related dmv's.

Be creative with BCP

My favorite colleague - you know the one :) - left me with a simple task before he left on a holiday. I had to finish this export for him that we decided to rewrite. He had to export several quite large recordsets to multiple XML-files. We agreed to use FOR XML EXPLICIT instead of a custom made SSIS component to be able to deploy it on our current production environment, still being SQL Server 2000.

When using FOR XML EXPLICIT, you don't get that many options to export the result to a file because of the special nature of that result. But with BCP, everything is possible - at least I thought so. Because of the complexity of the export, some data had to be staged temporarily in the stored procedure that retrieved the data and formatted the XML. And that's where the trouble started.
With the code below you simulate the core of the problem - which isn't FOR XML by the way.

USE TempDB
GO

CREATE PROC usp_bcp_test
AS
SET NOCOUNT ON
IF 1=0
BEGIN
DECLARE @Tbl TABLE (Id INT)
INSERT @Tbl VALUES (1)
END
SELECT 1 as ColA, 'test' as ColB
GO

DECLARE @Stmt VARCHAR(1024)
SET @Stmt = 'BCP "EXEC TempDB.dbo.usp_bcp_test" QUERYOUT C:\TEMP\tst.txt -c -S ' + @@SERVERNAME + ' -T'
EXEC master.dbo.xp_cmdshell @Stmt
GO

DROP PROC usp_bcp_test
GO

I try to use the output of the stored procedure as the 'query' for BCP, but this fails because of the INSERT-statement in the stored procedure, even though it's unreachable code. BCP gives the following feedback:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
NULL

Using Profiler, I discovered that BCP issued the following statement to determine the resultset it can expect.

SET FMTONLY ON EXEC TempDB.dbo.usp_bcp_test SET FMTONLY OFF

In the messages pane of SSMS you can see the following:

(0 row(s) affected)

(0 row(s) affected)

Which explains the fact that BCP complains about the stored proc: it expects the INSERT statement to give a columnlist for its resultset, which it doesn't have of course. Moving the SELECT clause to the begin of the stored proc makes this construct work because BCP only looks at the first resultset.

The solution to this problem is straightforward: split the retrieval and the formatting over multiple stored procs so that the stored proc used for the BCP only contains a simple SELECT-statement.

Nice little detail: I was only able to simulate this behavior in SQL Server 2005, not with SQL Server 2000.

Plan cache gets cleared

"Blog this!", my colleague said to me, so I am. Just a simple note this time to mention that when you change the max degree of parallelism on an instance, the plan cache gets cleared. Kind of logic when you think about it because you are changing the rules for the stored plans, but nevertheless he thought it was useful info.

EXEC sp_configure 'max degree of parallelism', 0
RECONFIGURE

Behavior of Logins/sec

We are in the middle of migrating our production environment from SQL Server 2000 to SQL Server 2005, but during the stress tests this week I noticed something scary. The performance counter "SQL Server:General Statistics - Logins/sec" indicated a factor 1000 more for SQL 2005. I suspected a severe problem with our connection pooling but in spite of that very high number of logins, there were very little logouts/sec and the number of user connections stayed stable. Also Profiler didn't capture those logins (actual and failed ones) and the familiar sp_reset_connection showed up whenever a connection was reused.

Nothing wrong with the connection pooling luckily, but it seems that the behavior of the performance counter has changed between versions. It seems to indicate not only the actual logins, but also the reuse of connections because of connection pooling. A colleague put the matter on a forum for confirmation. Tbc?

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!