I'm a newbie.How many number of tables are recommended in a SQL Server Express database? mainly attaining best performance speedwise as an objective. Is it generally recommend to use two databases as compared to one for a single application? Your quick response will be highly appreciated
A:
What you can do to make things speedier:
-break up your databases so that they use multiple files across multiple, fast drives
-federate (not really something you'll do if you're running Express)
-Install memory, memory, memory
-I can't remember Express's limitations and I don't care to look them up, but on the configuration screen where you can assign the number of CPUs to dedicate to SQL, give it as many as you can. You should also be able to set affinity there (if not, then in Task Manager)
- Don't run anything you don't need (scheduler, report engine, Server, DHCP Client) if you don't have to
inked
2009-10-31 00:40:29
So, basically keep adding memory, memory, memory ... until you reach 1GB and then stop right. :)
BobbyShaftoe
2009-10-31 02:12:26
1GB? Heck no! I wouldn't run Windows XP with just 1GB. Add as much as the motherboard will take. Sometimes that means scrapping what you have on there for bigger sticks. Now, your OS may not recognize it all, but by the time you feel the pain, you'll decide to switch to a Server OS and that WILL like the memory. MS is giving away Server 2008 (or is it 2010?) to anyone with a high school or college e-mail address, too. FWIW.
inked
2009-10-31 12:29:11
It doesn't matter how much memory is installed, Express edition will only use up to 1gb
Cory
2009-11-01 04:09:27
+1
A:
SQL Express databases have a limit of 4GB in size. Within that limit, any number of tables is fair game. The number of tables makes absolutely no impact on performance. The only thing that drives performance of the application vis-a-vis the database is the proper design of the tables, both as logical model and as physical database structure (ie. correct choices of clustered indexes, non-clustered indexes, constraints, defaults, data types etc), and the proper querying and updating of the database ie. queries that can be satisfied (covered, efficiently) by the existing indexes.
Splitting an application database into multiple distinct databases is a bad idea. You are loosing consistency of the recovery unit (you can't backup/restore the two databases in a consistent state) and you need to replicate all the infrastructure around the database twice (security roles and permissions, maintenance activities and procedures etc). Also spliting an application database into distinct databases gaves absolutely no performance advantage.
Remus Rusanu
2009-10-31 01:26:52