patternsqlMinor
How does using separate schemas affect SQL Server 2008 performance?
Viewed 0 times
2008sqlseparateschemasaffectusingdoeshowserverperformance
Problem
I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or stored procedure, and the prefixes mean we have to scan five or six xharacters in before we even see the start of the unique name. I would like to use separate schemas for tables that are just used to drive the UI (menus, roles by person, etc.) and for those that are dimension tables vs. fact tables, etc.
My question is, will there be performance impacts from using multiple schemas (schemae?) was opposed to the good old dbo for everything?
My question is, will there be performance impacts from using multiple schemas (schemae?) was opposed to the good old dbo for everything?
Solution
There may be an impact on the performance of the query optimizer, albeit a small one, depending on your coding style. If you reference a table without a schema, the optimiser has to try to identify the table first by checking tables within the users default schema (if there is one), then using dbo., and then everything else. If you explicitly reference the tables as schema.table, which is probably good practice anyway, then even this minor overhead will be avoided.
Context
StackExchange Database Administrators Q#14478, answer score: 3
Revisions (0)
No revisions yet.