CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
gbro3n
gbro3n

Reputation: 6967

SQL Server - Avoiding write timeouts on logging table due to reporting queries

I have two very busy tables in an email dispatch system. One is for batching mail for dispatch, the other is used for logging. Expensive queries are ran that use both of these tables to produce stats for a UI. I would like to remove the reporting overhead on these tables as I am seeing timeouts during report generation.

My question is - what are my options for reducing the query overhead on these two tables while generating the report data.

I've considered using triggers to create exact copies of the tables. Is there any built in functionality in SQL server for mirroring data within a database? If I can avoid growing the database unnecessarily though that would be an advantage. It doesn't matter if the stats are not real time.

Upvotes: 0

Views: 93

Answers (2)

Sumit Chaudhary
Sumit Chaudhary

Reputation: 118

You can use Resource Governor for SQL Server. Unfortunately, I have only read about it and haven't used it yet. It is used to isolate workloads on SQL Server. Please try and let us know if it helps. Some helpful links: MSDN SQLBlog technet

Kind Regards, Sumit

Upvotes: 0

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

There is a built in functionality for this scenario and it's known as Database Snapshot. If you run a query against a DB snapshot table, no shared locks should be created on original database.

Upvotes: 1

Related Questions