I have about eight transactions servers accessing one MS SQL database. Every second, all transactions server will make a query to SQL Server. 90% of the time, the MS SQL server is distributing same result sets to all transactions servers. However, when number of transactions increase, SQL Server unable to cope with the request and dead lock begins to appear. I discovered that the query link to quite number of tables and retriving about 40+ fields. So, the query become slower and slower when number of transactions increase. I would like to find out anyone has any idea to set the SQL server to execute the query every second and push the result set to all the transaction servers. Is there any component to perform the job?
Hi If your transaction servers are DB servers then you can replicate your transaction s on that DB server ie the process( or code ) which retireves 40 + fields can be dumped in a table and that table can be replicated using SQL server replication to all your Transaction servers OR You can populate the table and refer that table to all your transaction servers.
First, welcome to Forums … Running the query every second and sending results to servers is inapplicable since minimum time interval of any schedule is 10 sec. · So I prefer to enhance this query and focus on the 3 outlines below: · Reduce no of joins between tables · Implement covering compound indexes there , you could have a look on my articled for this regard http://www.sqlserverpath.org/blog/2012/01/09/towards-t-sql-queries-of-0-sec-sixth-part/ Moreover, if you share you query with us , it would be better
