Wednesday, June 28, 2006
Missing checkpoints in Profiler
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.
Tuesday, June 20, 2006
Deployed package version
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!
Monday, May 29, 2006
Monitoring checkpoints in Profiler
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 :(.
Friday, May 12, 2006
Performance tuning guide for SQL Server
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.
Sunday, April 30, 2006
Return types of IDENTITY-functions
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
Friday, April 28, 2006
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.
Sunday, April 23, 2006
We are going to Devconnections Europe
The subjects for the sessions of the SQL Server Magazine Connections look very interesting to say the least. And the abstracts promise us to treat some in-depth information on all the cool stuff in SQL Server 2005. I’m really looking forward to learn more about database mirroring, performance tuning, service broker, dynamic management views, memory internals, etc.
Experienced speakers, great sessions and a nice setting: Nice, here we come ;-)
Read more about the event and the sessions at http://www.devconnectionseurope.com/