Hi
http://www.sql-server-performance.com/

Properly designed database and application code should not be affected much
by size increase. Of course, depends on how much is the increase, if
increased with TBs then it may differ.

Here are a few articles and resources on performance tuning and
troubleshooting:
http://www.sql-server-performance.com/articles/per/SQL_Server_Performance_Coding
_Standards_p1.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning
_waits_queues.mspx

Also, the Best Practices site has lots of useful scripts and articles:
http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Most effective thing you can do both for yourself AND your application
performance, is to hire a professional to give you a performance review and
mentor you in how to do the investigation and mitigation.

Self study using one of several sql server performance tuning books will
obviously help as well.