Michael Zilberstein

NHibernate and triggers

2008-11-19T09:24:05Z

Recently had an interesting experience with NHibernate (I was against working with it from the beginning for many reasons, but unfortunately I wasn't the one to decide). I added trigger to a table that performs several update / insert operation to other tables. Suddenly updating table via NHibernate returned error: NHibernate.AdoNet.TooManyRowsAffectedException: Unexpected row count: 6; expected: 1 So NHibernate doesn't support triggers that perform DML operations? Not quite. The solution is: add...(read more)

MERGE statement syntax modified

2008-09-16T18:03:47Z

This is probably known to all but I've just found out - MERGE command syntax had been modified since CTP6. In the beginning of 2008 I had built a system on CTP6 and this week, while preparing for production deployment, found out that procedures are failing with syntax error. It appears that in CTP6 MERGE contained the following blocks: WHEN MATCHED WHEN [TARGET] NOT MATCHED WHEN SOURCE NOT MATCHED In RTM, if you write WHEN TARGET NOT MATCHED, it fails compilation. New syntax is: WHEN MATCHED WHEN...(read more)

Query performance troubleshooting in SQL Server 2008: query_hash and query_plan_hash

2008-09-09T14:52:00Z

Recently I have noticed 2 new columns added to sys . dm_exec_query_stats and sys . dm_exec_requests DMVs in SQL Server 2008: query_hash and query_plan_hash. Those columns can greatly enhance performance monitoring process. In SQL 2005 main query I'm using for query performance monitoring is: SELECT TOP 10 qs.execution_count , ( qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes ) AS [Total IO] , ( qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes )...(read more)

Name resolution in SQL Server

2008-08-28T10:57:00Z

This post is inspired by recent post by Mladen Prajdić. Mladen had found out (as many did before him - hanging around in blogs and communities, I see questions about this issue from time to time) that name resolution works for temporary tables differs from the one for permanent tables. While for permanent table more than one CREATE TABLE statement with the same table name can be issued inside the same batch or procedure, for temporary table it will fail compilation saying: "There is already an object...(read more)

Partitioning by GUID

2008-06-11T00:40:14Z

Recently I had to use GUID (Global Unique Identifier) as primary key in order to ensure cross-server uniqueness. In many cases surrogate key (some sort of concatenation between server id and intra-server identity) would give a better performance but in my case due to application requirements surrogate key wasn't an option. Next step is partitioning by GUID column. Say, you want 3 partitions. Ho do you build partition function? Sure, you can perform a calculation and find the exact 2 values that will...(read more)

Scalability features I would like to have in SQL Server

2008-04-23T16:03:26Z

1. Active-Active cluster. Like RAC in Oracle. Maximum that commodity server can provide by now is 4 quad-core CPU machine. If application requires more, we can split the database (which is not always possible; and even when it is - such a solution often looks very artificial) or buy high-end server with more CPUs and much higher price line. The only thing that keeps SQL Server alive in the market for applications with high scalability requirements is totally unreasonable (IMHO) price that Oracle...(read more)

How do we know that Microsoft conducts performance tests?

2008-03-24T07:31:00Z

Funny comment in undocumented system stored procedure - and undocumented syntax of DBCC SHOW_STATISTICS. ...(read more)

How to find real database creation date

2008-03-18T05:56:30Z

Restoring database from backup or moving it to another server resets all the creation_date fields (in sys.databases , msdb..backupset etc'). So is there any way to find out the original creation date? Looks like there is one. It is stored in the boot page of the database (page 9) and you'll have to use DBCC PAGE in order to find it. DBCC TRACEON(3604) GO DBCC PAGE (YourDBName, 1, 9, 3) GO In the bottom of the page you'll find dbi_crdate field - it is the original creation date. Boot page fields are...(read more)

Have your database ever been backed up?

2008-03-01T14:15:00Z

Recently I've been asked by one of my clients to write a simple script that would find out whether particular database had ever been backed up (full backup). The reason for a requirement was semi-automatic application which would enable end users to manage backup activity including manually backup transaction log - not via Management Studio but via much more friendly UI designed for non-DBAs. As you know, for database running in Full Recovery Mode, transaction log can't be backed up before full backup is done at least for the first time. OK, I thought. Let's just check whether full backup exists for the database:

IF EXISTS( SELECT 1 FROM msdb..backupset WHERE database_name = 'MyDB' AND [type] = 'D' ) ...

Here comes the first catch. Imagine following scenario:

1. Create database MyDB.

2. Create full backup of 'MyDB'.

3. Drop database 'MyDB' (without deleting backup history).

4. Create new database with the name 'MyDB' or restore from backup.

What do we have now? Query will return true while actually backup we have belongs to another non-existing database. Try to backup transaction log of the new DB - you'll receive error.

First try failed - let's give another one. Both sys.databases and msdb..backupset contain creation date of the database. In sys.databases column name is create_date, in msdb..backupset it is database_creation_date. So let's just compare database_creation_date from the latest full backup row with database's create_date. If they're equal - we have full backup. Otherwise backup belongs to the previous incarnation of our database while full backup of currently existing db had never been taken. Sure, it should also work for the normal scenario - without deleted and restored databases on the way. So first let's check whether our theory works after second step of the scenario described above. But what happened? How comes, our database creation times are different?

Here comes second catch. For some strange reason, database_creation_date column in msdb..backupset doesn't contain milliseconds! Minutes, seconds - everything is there but for milliseconds its always 000. While create_date in sys.databases contains full date including milliseconds. Don't know who and for what reason cut milliseconds from the backup history table, but that's the way it works now.

To make long story short - it was the last barrier. After removing milliseconds part from database creation date in sys.databases, everything began to work. Here is the full script that will tell you whether some particular database had ever been backed up (full backup).

DECLARE @DBName SYSNAME
SET @DBName = 'YourDatabase'

DECLARE @LatestBackupDate DATETIME, @BackedUpDBCreationDate DATETIME
DECLARE @DBCreationDateNoMilliseconds DATETIME

-- In msdb..backupset table, database_creation_date column is in datatime format
-- but without milliseconds, so we remove milliseconds from database creation date.
SELECT @DBCreationDateNoMilliseconds =
    DATEADD(millisecond, -DATEPART(millisecond, create_date), create_date)
FROM sys.databases
WHERE [name] = @DBName
IF @DBCreationDateNoMilliseconds IS NULL
   BEGIN
       PRINT 'Database ' + @DBName + ' doesn' + CHAR(39) + 't exist on the server'
       RETURN
   END

SELECT
   @LatestBackupDate = backup_finish_date,
   @BackedUpDBCreationDate = database_creation_date
FROM msdb..backupset
WHERE
   backup_set_id = (
       SELECT MAX(backup_set_id)
       FROM msdb..backupset
       WHERE database_name = @DBName AND
       [type] = 'D' )

IF @LatestBackupDate IS NULL
   BEGIN
       PRINT 'Full backup had never been taken for database ' + @DBName
       RETURN
   END

IF @BackedUpDBCreationDate = @DBCreationDateNoMilliseconds
   PRINT 'Full backup exists for database ' + @DBName + '; Transaction log backups can be taken'
ELSE
   PRINT 'Full backup exists for the current version of ' + @DBName + ' database had never been taken. Existing backup refers to non-existing database with the same name'

1001st way to find SQL Server service start time

2008-03-01T13:43:00Z

There're a lot of ways to discover when SQL Server service had been last restarted. That's not a pure curiosity issue since almost all the DMVs and performance counters reset at service restart. So in order to calculate averages (IOs per minute, Waits per minute) etc, one needs to know the starting point. There're a lot of ways to find it. One is to take a look in Error Log (if log hadn't been deleted by numerious DBCC ERRORLOG commands). Another way is to look at sys.dm_exec_requests for start_time or in master..sysprocesses for login_time of system processes like LAZY WRITER. Recently I've found one more way: look in sys.databases for create_date of tempdb database. Since tempdb is re-created from scratch every time SQL Server restarts, it works. BOL reference about create_date column also says:
-- Date the database was created or renamed. For tempdb, this value changes every time the server restarts.

P.S. Results you receive trying different ways can vary - half minute here or there - because service restart doesn't happen in a second. But when you need to measure time since startup, 30 seconds usually won't be serious factor.