Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Lots of Tables and a Little DMO

By Andy Warren,

Recently we had a post by a reader stating that with 2000 tables in a database it was taking 45 seconds to login it. He also indicated that adding them to the db_reader role made the problem go away. To see the entire thread (which is so far unresolved), follow this link:

Poor login speed with 2000+ tables

This seems like a good thread to explore. At first glance I'd expect Enterprise Manager to be slow because it would need to load first all 2000 tables, then select the permissions on all 2000 objects when you go to view permissions on a user. It's not slow to select 2000 rows out of sysobjects, but DMO is object based, so you lose most of the advantages of doing set based work as it loads one object at a time.

But just to log in to the db? The reader indicates that a different owner owns the objects, so maybe some overhead from having to follow all the ownership chains?

From what I can see in Profiler, if I connect as a user to master and then change to a test database, the only procedure that affects security is sp_MShasdbaccess. Looking at that proc, we see it's not very complicated:

;select name as 'dbname',
owner = substring(suser_sname(sid), 1, 24),
DATABASEPROPERTY(name, N'IsDboOnly') as 'DboOnly',
DATABASEPROPERTY(name, N'IsReadOnly') as 'ReadOnly',
DATABASEPROPERTY(name, N'IsSingleUser') as 'SingleUser',
DATABASEPROPERTY(name, N'IsDetached') as 'Detached',
DATABASEPROPERTY(name, N'IsSuspect') as 'Suspect',
DATABASEPROPERTY(name, N'IsOffline') as 'Offline',
DATABASEPROPERTY(name, N'IsInLoad') as 'InLoad',
DATABASEPROPERTY(name, N'IsEmergencyMode') as 'EmergencyMode',
DATABASEPROPERTY(name, N'IsInStandBy') as 'StandBy',
DATABASEPROPERTY(name, N'IsShutdown') as 'ShutDown',
DATABASEPROPERTY(name, N'IsInRecovery') as 'InRecovery',
DATABASEPROPERTY(name, N'IsNotRecovered') as 'NotRecovered'
from master.dbo.sysdatabases
where has_dbaccess(name) = 1
order by name

Of course we can't see what the has_dbaccess function is doing (or at least I'm not profiling the correct events). would seem the thing to do is try to reproduce the problem. I created 500 tables using the following code. You can download the complete solution file here. This is VB.Net 2003 code, but overall it's very much like what you would do using VB6 other than the error handling. Even with 500 tables I saw no noticeable delay when switching to the database via Query Analyzer.

Sub CreateTables(ByVal HowMany As Integer, ByVal UseRoleForTesting As Boolean)

        Dim oserver As SQLDMO.SQLServer
        Dim odb As SQLDMO.Database
        Dim oTable As SQLDMO.Table
        Dim oCol As SQLDMO.Column
        Dim J As Integer
        Dim oLogin As SQLDMO.Login
        Dim bLoginFound As Boolean
        Dim oUser As SQLDMO.User
        Dim oRole As SQLDMO.DatabaseRole

        Const TBL_NAME As String = "TEST"


            'connect to local using a trusted connection
            oserver = New SQLDMO.SQLServer
            oserver.LoginSecure = True

            'drop previous test db if it exists
            For Each odb In oserver.Databases
                If odb.Name = DB_NAME Then
                End If

            'create a testing database
            odb = New SQLDMO.Database
            odb.Name = DB_NAME

            'add a test login if it doesnt exist
            For Each oLogin In oserver.Logins
                If oLogin.Name = DB_NAME & "USER" Then
                    bLoginFound = True
                    Exit For
                End If
            If bLoginFound = False Then
                oLogin = New SQLDMO.Login
                oLogin.Name = DB_NAME & "USER"
                oLogin.SetPassword("", PASSWORD)
            End If

            'clunky, but repeat for the object owner
            bLoginFound = False
            For Each oLogin In oserver.Logins
                If oLogin.Name = DB_NAME & "USEROBJ" Then
                    bLoginFound = True
                    Exit For
                End If
            If bLoginFound = False Then
                oLogin = New SQLDMO.Login
                oLogin.Name = DB_NAME & "USEROBJ"
            End If

            'add user to db
            oUser = New SQLDMO.User
            oUser.Name = DB_NAME & "USER"
            oUser.Login = DB_NAME & "USER"

            'add user to db
            oUser = New SQLDMO.User
            oUser.Name = DB_NAME & "USEROBJ"
            oUser.Login = DB_NAME & "USEROBJ"

            'add role, then add user to role
            oRole = New SQLDMO.DatabaseRole
            oRole.Name = "SSCTESTROLE"
            oRole.AddMember(DB_NAME & "USER")

            'create a lot of tables
            For J = 1 To HowMany

                'name the tables sequentially
                oTable = New SQLDMO.Table
                oTable.Name = TBL_NAME & J.ToString
                oTable.Owner = DB_NAME & "USEROBJ"

                'just do a simple one column table
                oCol = New SQLDMO.Column
                oCol.Datatype = "int"
                oCol.Identity = True
                oCol.Name = "TestColumn"

                If UseRoleForTesting = True Then
                    oTable.Grant(SQLDMO.SQLDMO_PRIVILEGE_TYPE.SQLDMOPriv_Select, "SSCTESTROLE")
                    'grant access to our test user
                    oTable.Grant(SQLDMO.SQLDMO_PRIVILEGE_TYPE.SQLDMOPriv_Select, DB_NAME & "USER")
                End If


        Catch ex As Exception



            oserver = Nothing

        End Try

It's been a while since we've posted any DMO code, this combines a lot of different objects to give you a basic framework for creating test environments. To use it, you just call it like this:

 CreateTables(500, True)

So, I'm stumped. Is hardware masking the problem? Maybe I haven't recreated the situation exactly enough to see the problem? What's the largest number of tables you've worked with and have you noticed similar issues? Where would you look next to solve the problem?

Total article views: 6996 | Views in the last 30 days: 2
Related Articles


Comments posted to this topic are about the item [B]hx_DatabaseProperties[/B]


Invalid object

Invalid object error


object name from objid

object name from objid


object privileges

sql server 2000 - object privileges


SSIS Object Model

SSIS 2008 Object Model


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones