0

I have researched this on here (and other sites) quite a bit. I feel like I am missing something simple. So please direct me to another posting or help answer my question.

Basically, we have out grown our current database server. The main issue is high CPU usage because of poorly written reports on top of a poorly designed relational database. The other problem is due to the IO usage for both workloads running. Users experience locks and issues trying to save into the system during high usage times and when the reports are pointing to the same server. Indexing and maintenance is done frequently. There is little fragmentation in the indexes that would lead to increased IO or CPU. Locking (by the reports) has been minimized, however other queries still need to acquire locks to maintain compliance.

I am forced to split the two main workloads from it... Which by the way, should happen anyway, since one is an OLTP workload and the other is a reporting workload using SSRS.

Here are the three main options I have tried to create a separate reporting database also with the reasons why each one will not work.

Things tried already:

  1. Full database copy – This is what we have been doing but it is slow and our reports will have 12 hour old data. This does not meet our business requirements or end user expectation.

  2. Log Shipping – I tried to implement this but we lose the 15 minute restore option for our backups (Data Protection Manager). Being able to restore the database in 15 minute increments seemed more important than having a separate reporting database. This is a valid point made by my supervisor, though it is becoming an increasing problem due to the issues the users are experiencing with the OLTP side of the system. The other problem with log shipping is that the report server would be down while the restore happens on the reporting database.

3.Replication – Transactional Replication is Microsofts recommended solution. It would keep our 15 minute backups in place as well as give us almost live reporting data. Due to some of the code they use in our medical records system, it is not compatible with replication. The heavy use of truncate (not supported by replication) as well as the (questionable) ability to enable end users to create new database columns (changes to the schema) via the web interface.

Any help out there? Thank you.

M1ke901
  • 1
  • 1
  • Have you tried mirroring? – Chris McKeown Feb 06 '13 at 22:41
  • Whilst not strictly an answer to your question, there is a 4th option - as the DBA, you have access to the SQL Profiler, and the SQL Query Tuner. Run the profiler for 20 minutes during some of these badly written reports, load that trace into the Query Analyzer, give it a long work time and let it do its work. It might come up with some surprising indexes or statistics you can add to the database that will help, and adding indexes shouldn't impact the code running on the DB (unless they're using HINTs, which they shouldn't). – Mark Henderson Feb 06 '13 at 23:17
  • You say that indexing and maintenance are done frequently, then go on to say that there is little fragmentation in the indexes. Have you thought about using Hallengren's index maintenance scripts so that indexes with little fragmentation and not unnecessarily reindexed? What kind of lag could be tolerated on the reporting side if you separate it from the OLTP side? Why can't you use the tlog backups generated by "Data Protection Manager" to restore to another database on a different server? – Darin Strait Feb 07 '13 at 00:20

1 Answers1

0

Since I seem to not have the ability to reply to answers posted, I am going to reply this way. Sorry for the confusion.

Mark Henderson: Yes, I have done quite a bit of optimizing these queries. Unfortunately, there is only so much I am able to do to get them performing better. Some of it has to do with the code of the 3rd party medical records system... Some of it has to do with the lack of resources I am given for the server to handle both workloads adequately. The indexing is worth checking back on, though I have done that quite a bit, I will continue to investigate.

CMcKeown: I was reading more about mirroring recently. The caveat with that seems to be similar to log shipping. While a snapshot happens, the reporting database will be offline and the users will be disconnected. Mirroring, as I understand it, will also require an availability group, which could require changes to the medical records application that I may not be given the ability to implement.

darin strait: Yes, I have scripts in place that only defragment or rebuild indexes that are only a certain percent fragmented for each. This was necessary due to the amount of growth in the database. The indexes on tables that are updated frequently fragment quicker than the other indexes... So, it was necessary to handle those separately. We have had a few discussions over the lag time on the reporting data. At first up to 24 hours was fine, then we got some push back from the users, now many of the reports apparently need to have almost live data. I am trying to push for hourly or half hour windows at this point. Part of the problem is satisfying everyone, and if I'm not able to, then making the right people as happy as possible. I don't know why I have not considered using the log backups sent to DPM to populate the reporting database. Without really looking into it, the problems would seem to be the same with log shipping, in that the database would have to be offline while it is being restored. I also do not know if there is a good automated way for DPM to handle a task like that. It is worth investigating so I thank you for that idea.

darin strait: Thank you for the correction on the availability group and the Asynchronous Database Mirroring. However, from what I see the same issue applies with the snapshots as it does with the log shipping restores. The users will be disconnected while the snapshot is created. We are using enterprise edition, so this is an option. It may be a better option in the end, due to it not interfering with our current backup system. Thank you for the input, I will take it under serious consideration.

user1441145
  • 1
  • 1
  • "mirroring" != "availability group". Availability groups are a SQL 2012 thing. You would be looking at something more like this-> http://msdn.microsoft.com/en-us/library/ms187110(v=sql.105).aspx – Darin Strait Feb 07 '13 at 00:24
  • Also relevant: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/read-from-your-mirror-in-sql-server – Darin Strait Feb 07 '13 at 00:31
  • Regarding mirroring - the mirrored database is in a perpetual state of 'Restoring', and therefore isn't usable (similar to Log Shipping), but what you can do is create a read-only snapshot of that database for reporting purposes. – Chris McKeown Feb 07 '13 at 00:34