September 2, 2016 at 6:45 am
Hi,
I have a situation where a reporting tool that I use needs to query our OLTP database for real-time reporting. While the tool allows for users to see each and every table and visually join them, retrieve the columns, etc. (think QBE in Access...), it's not ideal for them to do that. Therefore, we've created several views that contain the needed fields from many different tables. As you can imagine, these views can sometimes take a long time to run because of all of the joins, data, etc. In addition, if the user wants a field from ViewA, a field from ViewB, and a field from ViewC, then the query that the reporting tool generates ultimately joins each of these views together, which I know doesn't help the query optimizer choose the best plan.
I know that some of you have provided some great input like remove any nested views, tune the queries themselves, and possibly add indexes. What I'm intrigued about is adding indexes on the views since the reports are already written against many of them, and even though some return dozens of fields, they're all necessary to provide to the user. I've attempted to create an index on one of the more CPU-intensive views in our non-production environment and received the following error:
Cannot create index. Object '<a table name within the view>' was created with the following SET options off: 'ANSI_NULLS'
Since the database is from a vendor whose software we use, and the fact that I'm not sure of what the implications would be, I obviously don't want to start changing settings at the database level just to be able to add the index to the view (and who knows what other of the many restrictions on indexed views I'll run into after this one).
Given some of my limitations with the reporting tool and database. Does anyone have any thoughts on how I might be able to approach this? I do plan to re-examine the queries themselves and look for indexes I can create on the underlying tables that would help the queries.
What have others who have run into this problem done?
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 2, 2016 at 9:38 am
1) If you put indexed views on top of an OLTP database you can DESTROY that application's performance and concurrency!! And by destroy I mean make absolutely unusable.
2) There are limitations and requirements to indexed views. Those often mean you cannot drop them into existing constructs. It's that simple.
3) My recommendation is that if you can accept some form of delay in the data being exposed that you set up log shipping to a second database (and server if at all possible) and do your reporting off of that. I have built systems for this at many clients over the years that ranged up to 7400+ databases on the report server. Dirt-simple, works fine, lasts a long time. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 2, 2016 at 9:42 am
TheSQLGuru (9/2/2016)
1) If you put indexed views on top of an OLTP database you can DESTROY that application's performance and concurrency!! And by destroy I mean make absolutely unusable.2) There are limitations and requirements to indexed views. Those often mean you cannot drop them into existing constructs. It's that simple.
3) My recommendation is that if you can accept some form of delay in the data being exposed that you set up log shipping to a second database (and server if at all possible) and do your reporting off of that. I have built systems for this at many clients over the years that ranged up to 7400+ databases on the report server. Dirt-simple, works fine, lasts a long time. 🙂
+ 1 for item 3
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 2, 2016 at 9:48 am
Thanks, Kevin! I've seen your sessions at SQL Saturdays before -- always very informative.
And just to clarify, you would recommend Log Shipping over mirroring or replication? Also, I assume that this requires a second license for SQL Server (and can theoretically be a version different from the instance the OLTP db is on?)
I'm working with 2014 for the OLTP database and have 2008 R2 available too.
Thanks again,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 2, 2016 at 9:54 am
Aye, I would take that approach, especially in building a reporting data mart on a separate server or just using another database on the existing server. That would give me the ability to use the schema and data model I want with the index strategies and so forth that support the reporting application.
That said, do you not have any type of reporting caching options within the reporting tool you use?
September 2, 2016 at 10:21 am
mikes84 (9/2/2016)
Thanks, Kevin! I've seen your sessions at SQL Saturdays before -- always very informative.And just to clarify, you would recommend Log Shipping over mirroring or replication? Also, I assume that this requires a second license for SQL Server (and can theoretically be a version different from the instance the OLTP db is on?)
I'm working with 2014 for the OLTP database and have 2008 R2 available too.
Thanks again,
Mike
Thanks for the props!
1) Mirroring can have SIGNIFICANT impact on performance, especially if you are on standard edition and it then must be synchronous. Also, if you are on standard you can't even set up a snapshot so the mirror copy is non-accessable for anything.
2) Replication can also have a SIGNIFICANT effect on the box, and is persnicketty and fraught with gotchas, limitations, caveats, provisos, etc.
3) I recommended a second server specifically (and solely) to avoid affecting production OLTP with the reporting workload. You can certainly restore your log backups to a reporting database on the same server if you desire, although I am pretty sure you will need to customize the built-in log shipping stuff if you do (which is easy once you are familiar with the objects in play - I do it all the time because if nothing else copying the log backups to a different volume is silly in most environments).
4) You can restore up-version but not down-version.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 2, 2016 at 11:31 am
Ok great advice. Thank you!
One more question -- If I plan to use Ola Hallengren's maintenance scripts for full, differential, and transaction log backups (or even if I create a database maintenance plan through the wizard), won't log shipping impact that in some way?
In other words, can I do my normal backups so that I can restore if I ever need to and not conflict with anything I'm doing with log shipping? How do the two co-exist?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 2, 2016 at 1:10 pm
mikes84 (9/2/2016)
Ok great advice. Thank you!One more question -- If I plan to use Ola Hallengren's maintenance scripts for full, differential, and transaction log backups (or even if I create a database maintenance plan through the wizard), won't log shipping impact that in some way?
In other words, can I do my normal backups so that I can restore if I ever need to and not conflict with anything I'm doing with log shipping? How do the two co-exist?
Thanks,
Mike
Ola's awesome stuff will work fine with log shipping, and if you roll your own you can just use the tlog backups his suite does. If you use the built-in log shipping stuff you simply exclude the log-shipped database from Ola's tlog backups. Otherwise you hose the LSN and recoverability.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 3, 2016 at 7:30 am
Thank you, Kevin and others.
Mike Scalise, PMP
https://www.michaelscalise.com
September 14, 2016 at 5:56 am
Hi Kevin,
I've been looking into log-shipping since, honestly, you have me a little scared about the potential performance impacts of mirroring and transactional replication.
It seems to be a well-liked feature, but
1) Users can't query the database while restore is taking place (and I think it even kicks users out if they're in)
and
2) You'd put it in STANDBY mode to do reporting from it, but that means that I can't create indexes to optimize the reporting.
I'm sure I'm missing something here--would you mind shedding some light?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 14, 2016 at 6:45 am
Mike Scalise (9/14/2016)
Hi Kevin,I've been looking into log-shipping since, honestly, you have me a little scared about the potential performance impacts of mirroring and transactional replication.
It seems to be a well-liked feature, but
1) Users can't query the database while restore is taking place (and I think it even kicks users out if they're in)
and
2) You'd put it in STANDBY mode to do reporting from it, but that means that I can't create indexes to optimize the reporting.
I'm sure I'm missing something here--would you mind shedding some light?
Thanks,
Mike
Both of those points are valid. You have some options for mitigating the first.
For mirroring, are you on Enterprise Edition of SQL Server? If not you cannot touch the database at all - no snapshot in Standard Edition.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 14, 2016 at 8:50 am
I'd be interested to hear what some of those options for mitigating #1 are.
Currently, we're using Standard Edition........
Mike Scalise, PMP
https://www.michaelscalise.com
September 14, 2016 at 3:30 pm
Check to see if anything is running currently before you start the restore. Wait a bit if they are. Decide if you want to kill them and force the restore to happen.
Give user a window in which they have full access. Just store up your tlogs and restore during your window of opportunity(s).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 15, 2016 at 5:53 am
I like it. Thanks again, Kevin.
Mike Scalise, PMP
https://www.michaelscalise.com
September 15, 2016 at 7:39 am
No worries mate! Glad I could help.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply