It is advisable not to have transactions that run for a long time. If you need to access large data in a transaction that needs to be sent to the client, then you can have that operation at the end of the transaction. Transactions that require user input to commit are also a degrade factor, ensure that explicit transactions are wither committed or rollback at some point of time. Also, you could find a boost in the performance if the resources are accessed in the same order. Proper utilization of isolation levels helps minimize locking.

Efficient Design Considerations

The way you design your database impacts greatly on the performance of SQL Server. When you are working with tables, always use proper data types for the columns. If your data has very large chunks of characters then you can go with text data type. Check if proper primary and foreign key relationships are defined across various tables. Make a practice of normalizing your database first and then work around de-normalizing for improving any performance. You may use indexed views for de-normalization purpose. Analysis job usually takes more of the system resources, so it is recommended to use separate servers for Analysis and Transaction processing jobs.