SQL Server Performance

SQL Performance

Discussion in 'ALL SQL SERVER QUESTIONS' started by nicholas, Mar 5, 2012.

  1. nicholas New Member

    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?
  2. Hrishi_nk New Member

    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.
  3. Shehap MVP, MCTS, MCITP SQL Server

    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

Share This Page