Optimizing performance

[flex13]

New to SQL Server and was wondering if there are any 'standard' settings that can be tweaked to optimize data access performance. Looking to minimize connection times as well as ensure that the data is returned as quick as possible. If you know of any additional 'tweaks' then please feel free to share them.

Any insight would be greatly appreciated. Thanks in advance.

 

[SQLSister]

There are whole books written on optimizing performance. Suggest you get a hold of one as this is an extremely complex subject.

Some things to consider, the single biggest problem with performance comes not from settings but from poor database and query design.

INdexes are a must if you want to optimize perfomance as are things like avoiding cursors (alwyas try to find a set-based solution first), only calling the bare minimum of data that you you need for a particular purpose, using stored procedures instead of ad-hoc queries, avoiding the use of some t-SQL code such as searching with wildcards in the first character (makes it impossible to use the index) or using the not in construction (a left join with a where cluase is more efficient.). You should also where possible try to use integer field for joins which are much more efficient. Having your user interface validate data before trying to send it to the database will reduce network traffic by cutting down on multiple sends to get a record inserted. Keeping statistics up-to-date helps. Using the correct data type for the data is critical. Storing more than one piece of information in a field and then having to parse it out when you run a query is also inefficient.

Don't allow duplicate entry of data, make sure to place unique constraints onf those items which make a record unique.

Make sure your hardware is up to the task and do not run other application software on your SQL Server (except a mail client if you are running SQL Mail) Processing is faster if you have indexes and transaction logs on separate drives with separate drive controllers than the database tables.

There's more but this should give you some things to think about.