We are using SQL Server 2012 EE for our .NET application. Our database is hosted on Rackspace and I believe we have enough space allotted by them because we have Rackspace premium membership. We have more than 500 active users working on single production database.

Some of our applications retrieve more than 100s records at a time and all those application are set to auto refresh.

My question and concern is, we know we are running heavy query and if we have more users working at same time then well have deadlock issue or unexpected error.

We have implemented solution for deadlock but still users are experiencing the problem. How can we optimize our database to increase performance? Which sql database technology we used to increase performance? We are thinking to off load the data or database mirroring. We are not sure and would like to get feedback from expert.

Thanks for your reply.

If you have huge transactions and high volume of users, then inorder to avoid deadlock you should have to take care of batches that is being executed. If each user run some large batches its gonna be a heavy deadlock. Please see these best practices.

http://blogs.msdn.com/b/bartd/archive/2006/09/09/747119.aspx

To increase your sql server performance you would need to do plan and design A to Z. Say for eg storage best practices like isolating data and log file and TempDB for best throughput etc.

https://technet.microsoft.com/en-us/library/hh393539%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

SQL Server Best Practices

Deadlocks can be a serious problem in heavily-loaded SQL Server systems.

There may be no easy solution.

You should of course make sure you are processing tables in the same order across different transactions and using minimal locking.  However there can still be problems.

You can try the snapshot isolation levels to reduce contention, locking, blocking, and deadlocks.

You can also try the new memory-resident tables in SQL 2014/2016.

Most of all you can try optimizing the data model with the best indexes, speeding things up is often the best way to reduce or eliminate deadlocks.

Diagnosing the problem is generally the first step, fixing it the next.  Both can be complex.

Josh

• Proposed as answer by Naomi N 2 hours 19 minutes ago

As noted above there is no magic parameter setting solution. 

The hope is that the system performance be increased without major reengineering. 

Optimization steps:

1. Disk:  How to configure hard disks for optimal performance?

2. Memory:  How to configure memory for optimal performance?

3. Index:  Hire an expert to optimize indexes

4. Statistics: UPDATE STATISTICS nightly

I would start looking at the deadlock graph. Before that are you sure your database design is correct. Frequent deadlocks in production is mainly because of bad design. Are queries supported by index , how is tempdb configured, how much RAM is allocated to SQL Server

If you have deadlock graph share with us