September 3, 2014 at 10:33 pm
Looking for material.
--Quote me
September 4, 2014 at 7:03 am
Can you further define what you mean by "extensibility of environments"? Are you talking about a scale out scenario? Scale out means that in order to support an increasing load on the system, you'll add more and more servers to manage the load. Or, are you talking about something else entirely?
"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
September 4, 2014 at 9:41 am
Thanks Grant.
By extensible I mean, current practices that will allow maximum future flexibility to rearranging server and network infrastructure. For example, in the near future we may acquire new servers in the cloud (Amazon) and offload some databases to 'up there'. That means separating databases that were on the same server and going back into SSRS reports and adding linked server aliases to the table join. <---is there a better way than using linked server aliasing (in anticipation of the move) for joining across databases and staying extensible at the same time?
Also, since you are Grant Fritchey, I would love to ask you a question about query optimization. If you run the same query twice in a row, will it automatically run faster the second time, simply because it's using the optimization plan from the first time you ran it? (I know it's an off shoot question/not meaning to annoy.)
--Quote me
September 4, 2014 at 12:17 pm
polkadot (9/4/2014)
Thanks Grant.By extensible I mean, current practices that will allow maximum future flexibility to rearranging server and network infrastructure. For example, in the near future we may acquire new servers in the cloud (Amazon) and offload some databases to 'up there'. That means separating databases that were on the same server and going back into SSRS reports and adding linked server aliases to the table join. <---is there a better way than using linked server aliasing (in anticipation of the move) for joining across databases and staying extensible at the same time?
Also, since you are Grant Fritchey, I would love to ask you a question about query optimization. If you run the same query twice in a row, will it automatically run faster the second time, simply because it's using the optimization plan from the first time you ran it? (I know it's an off shoot question/not meaning to annoy.)
Thanks for clarifying what you were after... And I'm not sure I have a good answer. The very first thing that comes to mind is to suggest that you don't, ever, have cross-database dependencies like that. Same thing goes for cross-database queries. But I'm honestly unsure of a good book that lays out that kind of decision process. I'll ping some friends to get them over here.
In answer to your second question... It depends. Let's say we're talking about a marginally complex query with no underlying issues. Yes. But, let's say we're talking about a very simple query, again, without issues, then the answer is yes, but you probably won't notice. Let's say we're talking about a query that could suffer from bad parameter sniffing, or has code that could, or will, lead to recompiles, then the answer is possibly yes, possibly no, it depends. But in general, yes, when a query gets run for the second time, you've eliminated one part of processing for that query, creating the execution plan.
"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
September 4, 2014 at 1:31 pm
Grant Fritchey (9/4/2014)
In answer to your second question... It depends. Let's say we're talking about a marginally complex query with no underlying issues. Yes. But, let's say we're talking about a very simple query, again, without issues, then the answer is yes, but you probably won't notice. Let's say we're talking about a query that could suffer from bad parameter sniffing, or has code that could, or will, lead to recompiles, then the answer is possibly yes, possibly no, it depends. But in general, yes, when a query gets run for the second time, you've eliminated one part of processing for that query, creating the execution plan.
And maybe you read the data from disk and stored it in memory the first time, so the second time the query can benefit from the cache?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 4, 2014 at 1:49 pm
Grant Fritchey (9/4/2014)
polkadot (9/4/2014)
Thanks Grant.By extensible I mean, current practices that will allow maximum future flexibility to rearranging server and network infrastructure. For example, in the near future we may acquire new servers in the cloud (Amazon) and offload some databases to 'up there'. That means separating databases that were on the same server and going back into SSRS reports and adding linked server aliases to the table join. <---is there a better way than using linked server aliasing (in anticipation of the move) for joining across databases and staying extensible at the same time?
Thanks for clarifying what you were after... And I'm not sure I have a good answer. The very first thing that comes to mind is to suggest that you don't, ever, have cross-database dependencies like that. Same thing goes for cross-database queries. But I'm honestly unsure of a good book that lays out that kind of decision process. I'll ping some friends to get them over here.
Offloading databases to the cloud can be great. The only real best practice (albeit no whitepapers) in regards to the use of the linked servers is to not do that between Cloud and on-premise databases.
Sure you can do the linked servers between the two environments, but that could cause some pretty bad performance issues. I would recommend grouping the databases that must be "linked" for cross database joins into the same environment or use another process such as ETL or replication to get data into the same environments.
Running a linked server across the internet is ripe with potential problems.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 4, 2014 at 2:41 pm
polkadot (9/3/2014)
Looking for material.
My two cents in the form of some quick questions:
1) is the budget big enough or do you have to compromise?
2) do you have the resources for implementing a private cloud infrastructure?
3) is the scalable path worth the investment?
4) what would be the estimated growth rate, starting with minimal as 1. 1 x a month, a year?
😎
September 5, 2014 at 8:19 am
I'm not the one making the decisions regarding the infrastructure. My query writing for SSRS reports will spiral out of control the day I have to add linked server aliases for vital joins, or (to avoid using linked server aliases 'if you don't want to, sys admin says' <smoothly>) attempt, as was suggested, to pull the data into SSRS piece meal and do the joins there.
AS IF.
So, Eirikur, I can't answer the questions you posed. I'm only trying to defend the user experience and stop a train with logical arguments.
Grant, thanks for feedback on second part of question. I dug around for more info and found I can find a query's plan handle and remove it with EBCC PREEPROCCACHE so that I start afresh when looking to see if optimization efforts changed....or to demonstrate how linked server aliased queries take longer.
--Quote me
September 10, 2014 at 4:47 am
Is this reporting against live (oltp) systems or dedicated reporting (data warehousing) systems?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 10, 2014 at 5:26 am
polkadot (9/5/2014)
Grant, thanks for feedback on second part of question. I dug around for more info and found I can find a query's plan handle and remove it with EBCC PREEPROCCACHE so that I start afresh when looking to see if optimization efforts changed....or to demonstrate how linked server aliased queries take longer.
I'd advise against running DBCC FREEPROCCACHE (or even EBCC FREEPROCCACHE ;-)) against a Production database!
Regards
Lempster
September 11, 2014 at 2:06 pm
Lempster (9/10/2014)
polkadot (9/5/2014)
Grant, thanks for feedback on second part of question. I dug around for more info and found I can find a query's plan handle and remove it with EBCC PREEPROCCACHE so that I start afresh when looking to see if optimization efforts changed....or to demonstrate how linked server aliased queries take longer.I'd advise against running DBCC FREEPROCCACHE (or even EBCC FREEPROCCACHE ;-)) against a Production database!
Regards
Lempster
But you can pass the plan handle to FREEPROCCACHE to remove a single plan. That's a fine thing to do in production, especially when dealing with a bad parameter sniffing issue.
"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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply