Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server Katmai >> SQL Server Katmai Manageability & Tools

How enabling DMF affects speed of table creation

Perf hit is expected, but looks like currently it's quite a lot.

Say I can create ~500 tables and views (250+250) per second without DMF.

Once I enforce naming conventions (tbl% for tables and vw% for views), the rate of object creation falls to 3 objects per second.

Seems like a pretty high tax.

Also, database that does not subscribe to the policy is experiencing a tangible hit as well (500 -> 200).

Is this expected? Has this been noticed and any conclusions drawn yet? Will there be improvements?

Please let me know if I should file a connect bug.

Results (policy is enabled for Database1 and not enabled for Database2):

With policy enabled:

Test 1: Creating tables and views in database 1

28objects in 10000ms

Test 2: Creating tables and views in database 1 - all in one transaction

34objects in 10000ms; including commit time: 10350ms Test 3: Creating tables and views in database 2

2008objects in 10000ms

Test 4: Creating tables and views in database 4 - all in one transaction

2264objects in 10000ms; including commit time: 10033ms

Policy disabled completely:

Test 1: Creating tables and views in database 1
5592objects in 10000ms
Test 2: Creating tables and views in database 1 - all in one transaction
14086 objects in 10000ms; including commit time: 10220ms
Test 3: Creating tables and views in database 2
7588objects in 10000ms
Test 4: Creating tables and views in database 4 - all in one transaction
14456 objects in 10000ms; including commit time: 10233ms

For reference, here's the test script I used:

/*

USE master

DROP DATABASE DMFTest1

DROP DATABASE DMFTest2

GO

CREATE DATABASE DMFTest1 ON

( NAME = N'DMFTest1_1', FILENAME = N'H:\DMFTest1_1.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

,( NAME = N'DMFTest1_2', FILENAME = N'I:\DMFTest1_2.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

,( NAME = N'DMFTest1_3', FILENAME = N'J:\DMFTest1_3.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

,( NAME = N'DMFTest1_4', FILENAME = N'K:\DMFTest1_4.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

LOG ON

( NAME = N'DMFTest1_log', FILENAME = N'O:\DMFTest1_log.LDF' , SIZE = 100000KB , MAXSIZE = 500000KB , FILEGROWTH = 10%)

GO

CREATE DATABASE DMFTest2 ON

( NAME = N'DMFTest2_1', FILENAME = N'H:\DMFTest2_1.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

,( NAME = N'DMFTest2_2', FILENAME = N'I:\DMFTest2_2.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

,( NAME = N'DMFTest2_3', FILENAME = N'J:\DMFTest2_3.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

,( NAME = N'DMFTest2_4', FILENAME = N'K:\DMFTest2_4.MDF' , SIZE = 10000KB , MAXSIZE = 20000KB, FILEGROWTH = 10%)

LOG ON

( NAME = N'DMFTest2_log', FILENAME = N'O:\DMFTest2_log.LDF' , SIZE = 100000KB , MAXSIZE = 500000KB , FILEGROWTH = 10%)

GO

ALTER DATABASE DMFTest1 SET RECOVERY SIMPLE

ALTER DATABASE DMFTest2 SET RECOVERY SIMPLE

GO

USE DMFTest1

GO

CREATE PROCEDURE DropTablesAndViews

@TablePrefix varchar(20)

,@ViewPrefix varchar(20)

AS

DECLARE @s varchar(max)

BEGIN TRAN

WHILE EXISTS (SELECT TOP 1 Name FROM sys.views where name like @ViewPrefix+'%')

BEGIN

SELECT TOP 1 @s = 'DROP VIEW '+ Name FROM sys.views where name like @ViewPrefix+'%'

EXEC(@s)

END

WHILE EXISTS (SELECT TOP 1 Name FROM sys.tables where name like @TablePrefix+'%')

BEGIN

SELECT TOP 1 @s = 'DROP TABLE '+ Name FROM sys.tables where name like @TablePrefix+'%'

EXEC(@s)

END

COMMIT

GO

USE DMFTest2

GO

CREATE PROCEDURE DropTablesAndViews

@TablePrefix varchar(20)

,@ViewPrefix varchar(20)

AS

DECLARE @s varchar(max)

BEGIN TRAN

WHILE EXISTS (SELECT TOP 1 Name FROM sys.views where name like @ViewPrefix+'%')

BEGIN

SELECT TOP 1 @s = 'DROP VIEW '+ Name FROM sys.views where name like @ViewPrefix+'%'

EXEC(@s)

END

WHILE EXISTS (SELECT TOP 1 Name FROM sys.tables where name like @TablePrefix+'%')

BEGIN

SELECT TOP 1 @s = 'DROP TABLE '+ Name FROM sys.tables where name like @TablePrefix+'%'

EXEC(@s)

END

COMMIT

*/

GO

USE DMFTest1

DECLARE @TestDurationMSint= 10000

DECLARE @TablePrefixvarchar(20)='tblGood'

DECLARE @ViewPrefixvarchar(20)='vwGood'

DECLARE @iint= 0, @svarchar(max)

--cleanup

EXEC DropTablesAndViews @TablePrefix,@ViewPrefix

DECLARE @StartTimedatetime

DECLARE @EndTimedatetime

SET @StartTime=GetDate()

SET @EndTime=DATEADD(ms,@TestDurationMS,@StartTime)

SET @i=0

WHILEGetDate()< @EndTime

BEGIN

SET @s='CREATE TABLE '+@TablePrefix+CONVERT(VARCHAR(20),@i)+' (i INT NOT NULL)'

EXECUTE(@s)

SET @s='CREATE VIEW '+@ViewPrefix+CONVERT(VARCHAR(20),@i)+' AS SELECT * FROM tblGood'+CONVERT(VARCHAR(20),@i)

EXECUTE(@s)

SET @i+=1

END

PRINT'Test 1: Creating tables and views in database 1'

PRINTCONVERT(VARCHAR(20),@i*2)+' objects in '+CONVERT(VARCHAR(20),@TestDurationMS)+'ms'

EXEC DropTablesAndViews @TablePrefix,@ViewPrefix

SET @StartTime=GetDate()

SET @EndTime=DATEADD(ms,@TestDurationMS,@StartTime)

SET @i=0

BEGINTRAN

WHILEGetDate()< @EndTime

BEGIN

SET @s='CREATE TABLE '+@TablePrefix+CONVERT(VARCHAR(20),@i)+' (i INT NOT NULL)'

EXECUTE(@s)

SET @s='CREATE VIEW '+@ViewPrefix+CONVERT(VARCHAR(20),@i)+' AS SELECT * FROM tblGood'+CONVERT(VARCHAR(20),@i)

EXECUTE(@s)

SET @i+=1

END

COMMIT

PRINT'Test 2: Creating tables and views in database 1 - all in one transaction'

PRINTCONVERT(VARCHAR(20),@i*2)+' objects in '+CONVERT(VARCHAR(20),@TestDurationMS)+'ms; including commit time: '+CONVERT(VARCHAR(20),DATEDIFF(ms,@StartTime,GetDate()))+'ms'

USE DMFTest2

EXEC DropTablesAndViews @TablePrefix,@ViewPrefix

SET @StartTime=GetDate()

SET @EndTime=DATEADD(ms,@TestDurationMS,@StartTime)

SET @i=0

WHILEGetDate()< @EndTime

BEGIN

SET @s='CREATE TABLE '+@TablePrefix+CONVERT(VARCHAR(20),@i)+' (i INT NOT NULL)'

EXECUTE(@s)

SET @s='CREATE VIEW '+@ViewPrefix+CONVERT(VARCHAR(20),@i)+' AS SELECT * FROM tblGood'+CONVERT(VARCHAR(20),@i)

EXECUTE(@s)

SET @i+=1

END

PRINT'Test 3: Creating tables and views in database 2'

PRINTCONVERT(VARCHAR(20),@i*2)+' objects in '+CONVERT(VARCHAR(20),@TestDurationMS)+'ms'

EXEC DropTablesAndViews @TablePrefix,@ViewPrefix

SET @StartTime=GetDate()

SET @EndTime=DATEADD(ms,@TestDurationMS,@StartTime)

SET @i=0

BEGINTRAN

WHILEGetDate()< @EndTime

BEGIN

SET @s='CREATE TABLE '+@TablePrefix+CONVERT(VARCHAR(20),@i)+' (i INT NOT NULL)'

EXECUTE(@s)

SET @s='CREATE VIEW '+@ViewPrefix+CONVERT(VARCHAR(20),@i)+' AS SELECT * FROM tblGood'+CONVERT(VARCHAR(20),@i)

EXECUTE(@s)

SET @i+=1

END

COMMIT

PRINT'Test 4: Creating tables and views in database 4 - all in one transaction'

PRINTCONVERT(VARCHAR(20),@i*2)+' objects in '+CONVERT(VARCHAR(20),@TestDurationMS)+'ms; including commit time: '+CONVERT(VARCHAR(20),DATEDIFF(ms,@StartTime,GetDate()))+'ms'

SELECT'Should be 0'=@@trancount

[26666 byte] By [AlexeyYeltsov-MSFTTAPcustmr] at [2008-2-7]
«« Application.Restart() not working
»» Help me join data from 2 common tables into 1 row. I'm outta ideas!
# 1

Does DMF impact DDL performance? Yes. Remember the old saying "There's no such thing as a free lunch."? Well it applies here. I think the real question is "What is an acceptable overhead?" What are the situations were DDL is sensitive to performance - "I really need my tables created < .1ms or I'm screwed!" I know this is an exaggeration, so please don't flame me.

Performance tuning is very hard (as any DBA worth his salt knows). In The June CTP we did very little perf tuning. We have some internal analysis and recommendations for lessening the overhead, but it won't get to zero. There are three areas that impact DMF performance:

1. The underlying queries to access policy meta data - we have a lot of control over these, unfortunately it appears this is not the biggest offender.

2. The pre-processing we do on events - before we jump into managed code we attempt to determine if the object in the transaction is something we care about (are there any policies that apply to it). This is not a 100% fool proof and does result in false positives. The main cost here is parsing the XML data we get from the event. It's amazing how costly it is to work with XML. And the kicker is we only need a small portion of the event data.

3. The SQLCLR - there is overhead in going to the SQLCLR. There's cold start time and then there's pure execution time.

We are working with the various teams across SQL Server on these problems.

You mention you hit some overhead on a DB that doesn't subscribe to any policies. These transactions hit #2 above. Meaning, it goes into the preprocessing logic which has to crack open the XML to find out which DB and what the object is. It's the opening of the XML that's expensive. Since the DB doesn't subscribe to any policies we never enter the SQLCLR. But you pay the price for preprocessing.

I go back to my original quesiton; what is an acceptable overhead for DMF?

Cheers,

Dan

DanJonesMSFT at 2007-9-28 > top of Msdn Tech,SQL Server Katmai,SQL Server Katmai Manageability & Tools...
# 2
I think the overhead is acceptable. Realistically, how many tables are you creating in a single transaction? If you need to create more than 3 tables a second then there is likely something seriously wrong. And I highly doubt users will complain that their single CREATE TABLE took 0.33 seconds instead of 0.002 seconds. You need to demonstrate this problem using 10000 tables because at the individual table it is not humanly measurable. And 10000 is not real-world.
aaronbertrand at 2007-9-28 > top of Msdn Tech,SQL Server Katmai,SQL Server Katmai Manageability & Tools...
# 3

Dan Jones> I go back to my original quesiton; what is an acceptable overhead for DMF?

aaronbertrand>If you need to create more than 3 tables a second then there is likely something seriously wrong. And I highly doubt users will complain that their single CREATE TABLE took 0.33 seconds instead of 0.002 seconds. You need to demonstrate this problem using 10000 tables because at the individual table it is not humanly measurable.

I completely agree that just throwing perf numbers out there does not necessarily indicate a problem, unless there's a tangible effect in a practical scenario. I should have said that from the beginning.

I do have an application which is fairly pretty heavy on table creation. But I ran into a bit of a bummer - how do I calculate the rate of table creation? There's a "Temp Tables Creation Rate" counter, but I wanted to estimate the "real" tables. Looks like my only option is to run a trace, and that's what I did for a short period of time.

Over 50 minutes, app created 6100 temp tables and 70 permanent tables.

If one came up with a very strange idea of enforcing naming of temp tables, the perf of the feature would not allow that.

But let's assume realistic scenario - only permanent tables have rules enforced. The overhead would have been 28 seconds (2.3ms per temp table and 200 ms per permanent table) - of course there's a bunch of assumptions in these numbers (do the numbers really add up that way on multi-proc machines, what will happen when there are much more rules, etc).

28 seconds amounts to ~0.9 percent. I have not yet made up my mind if 1% is a good deal for the feature, besides this is just a peek over tiny period of time.

You will be correct to ask "did you prove anything with this test". I'm not sure either. I guess one thing that I proved for myself is that perf degradation is something to watch for, it can't be completely written off, and it's desirable to lessen the overhead. It is also conceivable that there are real-world apps that would have larger overhead.

Thanks,

Alexey

AlexeyYeltsov-MSFTTAPcustmr at 2007-9-28 > top of Msdn Tech,SQL Server Katmai,SQL Server Katmai Manageability & Tools...
# 4

This is a good discussion and I in no way want to stop it. But I'm going to mark one of these threads as an answer so I stop receiving the "nag e-mail" that the forum has an unanswered question.

Cheers,

Dan

DanJonesMSFT at 2007-9-28 > top of Msdn Tech,SQL Server Katmai,SQL Server Katmai Manageability & Tools...
SQL Server Katmai
SQL Server Katmai Manageability & Tools

SQL Server Katmai Hot Topic

SQL Server Katmai New Topic

SQL Server Katmai

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified