Running reports alongside short transactions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Running reports alongside short transactions

Hi all our system is mostly short transactions but customers also wish to run reports on the live database. These can take up to several minutes and affect the performance of the rest of the system. Other than not doing it, does anyone know the best way to deal with this problem? thanks Iain Clark
Do they really need the most current state or could they live with a time lag of n minutes? If a small lag is acceptable you would replicate to a reporting server and do your reporting from there. If no time lag is acceptable, you could try using the NOLOCK hint while being aware that such results might be inaccurate due to the nature of NOLOCK. However, NOLOCK can take off some load from the server. I would go down the reporting server route though. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Hi thanks for the reply. Many of them already have a separate reporting instance which is up to a day old but some reports really are better with up-to-the-minute data. I’ve thought of using READ UNCOMMITTED for reports but I’m a little concerned about data being included which is subsequently rolled back. This is not likely to happen often but could occasionally. Iain
True, if the data needs to be accurate, NOLOCK (or READ UNCOMMITTED) is no option at all. Well, it seems as if you already have evaluated the commonly used approaches. A 5 or 15 minutes lag isn’t acceptable? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
It might be, but how would we implement such a short lag? Log-shipping do you mean? At the moment there is a nightly copy of the whole database.
Either log-shipping or replication. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
is transactional replication ? or Snapshot repliication for every 15 mints or say 30 mints colud solve issue?
If it is work then is thier any load on Production server because of replication?
]]>