April 11, 2011 at 2:57 am
Hi All,
What are ther steps need to take when we need to tune the stored procedure.
Ex : need to check the table design , index design , execution plan to find out the index scans or table scans, sql trace to check hight time consuming statements
Thanks & regards
Deepak.A
April 11, 2011 at 3:24 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 10:33 pm
Thanks for your reply Gail Show.
I want some guide lines reagarding what are the best practices we need follow. When we need to write the Remote query ( in the federated Database Desigin)
April 11, 2011 at 10:43 pm
deepak.a (4/11/2011)
I want some guide lines reagarding what are the best practices we need follow. When we need to write the Remote query ( in the federated Database Desigin)
We're gonna need a bit more from you then that. What do you consider a remote query (linked server, called from the .NET app, etc?) and what is a federated database design? I'm afraid that's a completely new term to me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 11, 2011 at 10:59 pm
Hi Craig Farrell,
Actually the Data are distributed to multiple Database. now i got the requirement to copy the Data from one Database to other Database in the stored procedure
Example : i have a tables like User and userdeatils and userprofile
if they pass userid i have to copy the entire user related data.
input will be the UserID,source server and target server and DB name.
i have written the sql code like below
Select col1,col2 .. coln from [Serevrname].[DBName].[schemaname].table1 x
join [Serevrname].[DBName].[schemaname].table2 y on <condition>
Where <condtition>
Thanks & regards
Deepak.A
April 12, 2011 at 1:25 am
Deepak, I think we're having a language barrier issue here, but I'll try to respond to each of these pieces as I *think* you intended them. Please re-clarify anything I've gotten inaccurate. More information is always better here.
deepak.a (4/11/2011)
Hi Craig Farrell,Actually the Data are distributed to multiple Database. now i got the requirement to copy the Data from one Database to other Database in the stored procedure
Most data is usually distributed, but you have one SOR, or System of Record, that the other get specific data from. Pushing data to other databases/servers is usually done via triggers and linked servers (worst choice), service broker and endpoints (personal favorite), or a form of replication (most common).
Doing it by proc or by trigger means that if one server goes down, so does the current one effectively. The transaction won't complete or the procs will error if the other server(s) are unavailable. This is not best practice, and usually only done under extreme circumstances.
Example : i have a tables like User and userdeatils and userprofile
if they pass userid i have to copy the entire user related data.
input will be the UserID,source server and target server and DB name.
Wow. :ermm: By preference I would never do this. Ever. There are a number of ways to do this better, especially because of the dynamic source/target server. If my guess as to what you're trying to do are correct, you're going to iterate this process. You'd be better off simply transactionally replicating the couple of tables involved here to ALL the possible servers that would desire the data.
However, because you're going to ask anyway, you would have to build a dynamic SQL statement to do this, possibly using EXECUTE AS sa to make sure you can generate new linked servers in case you don't have one setup. Also, you won't go by servername here, you'd go by linked servername.
i have written the sql code like below
Select col1,col2 .. coln from [Serevrname].[DBName].[schemaname].table1 x
join [Serevrname].[DBName].[schemaname].table2 y on <condition>
Where <condtition>
This makes more sense to me, but this isn't copying the data. You're merely using a linked server to get data from a foreign server. A common technique but can be a bit tough to optimize. However, you wouldn't want to make this dynamic and have the servers as parameters. I'm not really sure why you would want dynamic servers in this case, you'd always want to go to the primary database to get the most recent data.
Unless, by federated database, you mean a distributed transaction database, or NoSQL. Something like MongoDB. That's a whole different problem, and MS SQL Server was not meant to be distributed that way, at least in any current incarnation. It's relational and holds very tightly to ACID principals.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 12, 2011 at 6:35 am
It really sounds like you've got a non-standard design, so I doubt any standard set of checklists on performance tuning is going to help. All the stuff Wayne supplied is great. The only thing I didn't see him mention was the use of OPENQUERY as a mechanism to offload as much of the processing as possible on to remote servers rather than pull large data sets across the network. Here's the entry in Books Online on OPENQUERY.
"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
April 12, 2011 at 11:05 am
Grant Fritchey (4/12/2011)
It really sounds like you've got a non-standard design, so I doubt any standard set of checklists on performance tuning is going to help. All the stuff Wayne supplied is great. The only thing I didn't see him mention was the use of OPENQUERY as a mechanism to offload as much of the processing as possible on to remote servers rather than pull large data sets across the network. Here's the entry in Books Online on OPENQUERY.
:hehe: Wayne? 🙂
I did briefly mention OpenQuery, but I didn't get deep into it nor link. Guess I buried it in there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 12, 2011 at 11:12 am
Craig Farrell (4/12/2011)
Grant Fritchey (4/12/2011)
It really sounds like you've got a non-standard design, so I doubt any standard set of checklists on performance tuning is going to help. All the stuff Wayne supplied is great. The only thing I didn't see him mention was the use of OPENQUERY as a mechanism to offload as much of the processing as possible on to remote servers rather than pull large data sets across the network. Here's the entry in Books Online on OPENQUERY.:hehe: Wayne? 🙂
I did briefly mention OpenQuery, but I didn't get deep into it nor link. Guess I buried it in there.
Craig, Wayne, Somebody had some good information...
Sorry.
"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
April 12, 2011 at 6:00 pm
Grant Fritchey (4/12/2011)
Craig Farrell (4/12/2011)
Grant Fritchey (4/12/2011)
It really sounds like you've got a non-standard design, so I doubt any standard set of checklists on performance tuning is going to help. All the stuff Wayne supplied is great. The only thing I didn't see him mention was the use of OPENQUERY as a mechanism to offload as much of the processing as possible on to remote servers rather than pull large data sets across the network. Here's the entry in Books Online on OPENQUERY.:hehe: Wayne? 🙂
I did briefly mention OpenQuery, but I didn't get deep into it nor link. Guess I buried it in there.
Craig, Wayne, Somebody had some good information...
Sorry.
LOL, no worries. Someone's going to actually "quote" or commend Gail at the rate we're heading... 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 13, 2011 at 11:07 pm
Most data is usually distributed, but you have one SOR, or System of Record, that the other get specific data from. Pushing data to other databases/servers is usually done via triggers and linked servers (worst choice), service broker and endpoints (personal favorite), or a form of replication (most common).
yes we have implemented transactional replication.
Wow. :ermm: By preference I would never do this. Ever. There are a number of ways to do this better, especially because of the dynamic source/target server. If my guess as to what you're trying to do are correct, you're going to iterate this process. You'd be better off simply transactionally replicating the couple of tables involved here to ALL the possible servers that would desire the data.
yes transactional replication implemented to do this , but the sceneria is from the application they will perform import user along with user details same data needs to copy one more time for another profile. If it is available in the same server then it will be local data insertion /updation ,if source and target are different then it will remote call this is the exact use case why i'm doing this in stored procedure.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply