GL Trial Balance Slow

Recently our GL Trial Balance report became slow.  We have a bit over 3.6 million rows in tglTransaction.  We found that SQL Server 2012 stopped using an index which killed performance.  We ended up adding a hint to use index(XIE1tglTransaction).  If your GL Trial Balance report becomes slow look into doing that. 

Parents
  • How do you add a hint to use the index (XIE1tglTransaction)? Can anyone help me with this. Suddenly my GL Transaction Report started running slowly for my users, but not if you're logged in to the server as an admin... only an issue for users... and it just strated... any ideas?

Reply
  • How do you add a hint to use the index (XIE1tglTransaction)? Can anyone help me with this. Suddenly my GL Transaction Report started running slowly for my users, but not if you're logged in to the server as an admin... only an issue for users... and it just strated... any ideas?

Children
  • in reply to DanBarryAFLCIO

    To use an Index hint you would modify the specific query (probably in a stored procedure) and add a with Statement to the From Clause.

    SELECT
        column1,
        column2,
        ...
    FROM
        dbo.Tablename WITH (INDEX(IXIndexName))
    WHERE
        columnx = value

    However from you statement that it performs fine on the server I don't expect it is an index issue. Unusual that it just started for remote users.

    Just want to make sure when you say only an issue for users you are not meaning if a non admin user logs onto the server it performs slow as well?  Only when they are remote is it slow?

    Is it possible that maybe a large process was running across the network that maybe was using a large portion of the bandwidth?

    Kevin