February 25, 2016 at 5:57 am
I'm having one of those mornings with a million things to do, which means I'm not thinking straight on at least half of them. Would appreciate some general input to a general question.
Our current 2k8 setup has our OLTP and Reporting databases on the same server. Our new 2k12 environment splits the databases between Server A (OLTP) and Server B (Reporting). This would be great except we just discovered last week that some OLTP databases reference tables in the Reporting databases.
My temporary work around is to create faux Reporting DBs with views that point to Server B. Workaround because we can't freeze development on procs while we're working on the server upgrade. Temporary because the developers will have to fix this stuff shortly after the upgrade. The issue is there are 3 stored procedures being called from an OLTP database to one Reporting database. Each of these procs has multiple tables listed in it.
I was good with doing views with an added linked server when there was just SELECTs being done. The procs have added additional complications. Should I rewrite them for the faux databases to add the Linked Server to all the table names, causing an additional load on the LAN? Or is there a another way around this issue without forcing the devs to rewrite their code right now?
To be clear, the devs are swamped with urgent projects (per management), which is why we're letting this slide until after the upgrade. So while all suggestions are appreciated, any "have the devs fix their stuff" comments won't assist me at this time.
February 25, 2016 at 6:10 am
Views is probably the best approach. You could also look at synonyms, but I'm not sure they'll work the same way in this situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2016 at 6:27 am
I forgot all about synonyms, but you're right. Not the best solution. For one, I can't create one that just points to the server.dbname. I just tried it, it doesn't work. I'd need to create a synonym for each individual table, which would increase my maintenance overhead.
Views are probably where I'm going, but won't they have the same or more overhead as just rewriting the stored procs to call the linked server tables directly?
February 25, 2016 at 7:17 am
You should be able to spoof the query so that the views behave as if they were just a cross-database query. It won't be. It'll be a cross-server query and performance is going to be a pig. Create a database with the reporting database name and put the views in there, named for the tables that people want. Then have that view be a query into the other server. It ain't pretty and it's certainly a short-term Band-Aid. However, if you can't rewrite the queries correctly, that's a cost everyone should be willing to pay. We're heavily into TANSTAAFL here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2016 at 8:16 am
1) Make sure you configure linked servers optimally for your server scenario
2) Review the object accesses. Some linked-server queries can be refactored for HUGE perf gains depending on if it is optimal to "do it over there" or "bring it back here".
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2016 at 8:17 am
TANSTAAFL is big here too. Fortunately, the solution is short term. We're putting a hard date on when things need to be fixed, so no one will have any excuses.
February 25, 2016 at 8:19 am
TheSQLGuru (2/25/2016)
1) Make sure you configure linked servers optimally for your server scenario2) Review the object accesses. Some linked-server queries can be refactored for HUGE perf gains depending on if it is optimal to "do it over there" or "bring it back here".
Okay, I'm one of those people who do basic linked server stuff and only basic linked server stuff.
What should I be looking for as far as "do it over there" vs "bring it back here"?
February 25, 2016 at 8:41 am
OPENQUERY and passing the parameters to the remote server versus just connecting the tables and letting all the filtering occur wherever (SQL Server can, sometimes, figure out how to filter remotely, but it's pretty rare).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2016 at 8:47 am
Thanks, Grant. I'll look into that.
February 25, 2016 at 8:55 am
I'll keep OPENQUERY in mind (and discuss it with the Devs) when we fix things, but it isn't going to help me now. My current sticking point is stored procedures and BOL specifically states OPENQUERY doesn't execute remote stored procedures.
Still, I do appreciate the pointer and will start dropping hints to all and sundry.
February 25, 2016 at 9:05 am
Brandie Tarvin (2/25/2016)
TheSQLGuru (2/25/2016)
1) Make sure you configure linked servers optimally for your server scenario2) Review the object accesses. Some linked-server queries can be refactored for HUGE perf gains depending on if it is optimal to "do it over there" or "bring it back here".
Okay, I'm one of those people who do basic linked server stuff and only basic linked server stuff.
What should I be looking for as far as "do it over there" vs "bring it back here"?
A) Review all the options for linked servers. I know you are smart enough Brandi to figure the optimal/appropriate combination out. 😉
B) Mostly it involves whether or not a massive amount of rows needs to be transferred in either direction. That should be avoided. Likewise avoid things that can cause bad estimates/query plans (as you always should) such as mismatched data types, functions around columns in WHERE, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2016 at 6:04 pm
Hello,
based on my experiences when I used linked server I found these notes..
- if I have few rows without join , I did directly select from linked table
- if I have more rows and then have to work with them I inserted into some temp table on current server
- if I have join I inserted also whole table and work with this temp table...
based on some counting I found that this solutions is best in my environment ...
but I had for example one table from linked server and another in current server.
Also I changed some design of query, but still was problem with response
...below are worst examples what i have written
Before
Table 'Table1'. Scan count 5, logical reads 3170,
Table 'Table2'. Scan count 0, logical reads 38,
Table 'Table3'. Scan count 4, logical reads 167941
SQL Server Execution Times:
CPU time = 4002 ms, elapsed time = 1299 ms.
After
Table 'Table2'. Scan count 0, logical reads 64,
Table 'Workfile'. Scan count 0, logical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0,
Table 'Table3'. Scan count 1, logical reads 17,
Table 'Table1'. Scan count 1, logical reads 8,
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
or
Before
Table 'Worktable'. Scan count 30, logical reads 512156
SQL Server Execution Times:
CPU time = 3338 ms, elapsed time = 19675 ms.
After
Table '#Table00000000035E'. Scan count 0, logical reads 42,
Table '#Table00000000035E'. Scan count 1, logical reads 1
Table '#Table00000000035E'. Scan count 1, logical reads 1,
SQL Server Execution Times:
CPU time = 1513 ms, elapsed time = 1968 ms.
But maybe if you will want to get only some data, this view will be good choise.. will try also here ..but lot of times I work with linked table join current table and it was very slow
February 25, 2016 at 7:31 pm
Brandie Tarvin (2/25/2016)
...some OLTP databases reference tables in the Reporting databases.
How many tables? I am thinking a simple Transactional Replication Publication on Server B subscribed to by Server A (same database and table names of course) could potentially suffice if your LAN and servers can keep latency down to a practical level. Once your devs refactor their code the Replication can simply be dropped, as can the temporary database on Server A.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 26, 2016 at 5:33 am
Orlando Colamatteo (2/25/2016)
Brandie Tarvin (2/25/2016)
...some OLTP databases reference tables in the Reporting databases.How many tables? I am thinking a simple Transactional Replication Publication on Server B subscribed to by Server A (same database and table names of course) could potentially suffice if your LAN and servers can keep latency down to a practical level. Once your devs refactor their code the Replication can simply be dropped, as can the temporary database on Server A.
One proc has 5 tables in it. Another proc has 4. The third proc only has one, so that's not a problem.
I'm going back to the drawing board on these procs. Trying to rewrite them using the linked server has resulted in some weird errors. I think I need to involve the original programmer in my attempts.
February 26, 2016 at 3:59 pm
Brandie Tarvin (2/26/2016)
Orlando Colamatteo (2/25/2016)
Brandie Tarvin (2/25/2016)
...some OLTP databases reference tables in the Reporting databases.How many tables? I am thinking a simple Transactional Replication Publication on Server B subscribed to by Server A (same database and table names of course) could potentially suffice if your LAN and servers can keep latency down to a practical level. Once your devs refactor their code the Replication can simply be dropped, as can the temporary database on Server A.
One proc has 5 tables in it. Another proc has 4. The third proc only has one, so that's not a problem.
I'm going back to the drawing board on these procs. Trying to rewrite them using the linked server has resulted in some weird errors. I think I need to involve the original programmer in my attempts.
which errors? Just curious.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply