February 1, 2012 at 12:33 pm
The update query is the main query. we are using in that query and joining those tables within that update statement. Again I do not have permission to SQL profiler. And I haven't check the wait_type.
My manager just took it out and told me to analyse it because it takes long time some times.
I am attaching one .PNG file with this. It shows time taken by this query on specific date.
February 1, 2012 at 12:42 pm
Star Trek (2/1/2012)
The update query is the main query. we are using in that query and joining those tables within that update statement. Again I do not have permission to SQL profiler. And I haven't check the wait_type.
Please ask someone who does have permission to post the execution plan of the update (run it in a transaction and roll it back). Also please ask someone who has permission to check what the wait_type (and, if applicable wait_resource) is when it runs a long time.
Without knowing what the query is waiting on when it runs slow, we are guessing.
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
February 1, 2012 at 1:17 pm
I did ask for wait_type and he sent me this file. But, I never had experienced this before.....How can I read this.
I mean, how can I say for sure that this wait time is for this query or so.
February 1, 2012 at 1:35 pm
Nope, that is not what I'm asking for.
When the query is running and when it is running slowly, get the query's wait_type and wait_resource. The aggregated server-wide wait stats are useless for debugging a single query's performance.
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
February 1, 2012 at 1:59 pm
how can I get it for a particular query?
Do I need to write a query for that?
February 1, 2012 at 2:27 pm
Here is the plan and stats for update query.
February 1, 2012 at 2:34 pm
Star Trek (2/1/2012)
how can I get it for a particular query?Do I need to write a query for that?
The SPID itself needs to be reviewed during operations. You need sa equivalent access to do that.
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
February 1, 2012 at 2:38 pm
Star Trek (2/1/2012)
Here is the plan and stats for update query.
Is the column SUBORDER_ID defined the same way in both tables?
February 1, 2012 at 4:02 pm
Nothing immediately jumps out looking at the exec plan, going to need those wait stats. Your DBA should know how to get a single query's wait_type and wait_resource
Also, did you look for hash spills (or ask your DBA to)?
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
February 1, 2012 at 4:21 pm
How can we say from our execution plan that the specific part is allocated with less space? As you told me that hash join is occupied with less memory.
this one is just for my knowledge.....
Thanks
February 1, 2012 at 4:40 pm
The memory grant property on the select or update operator.
I could only compare for the selects, as for the update you only posted one plan
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
February 1, 2012 at 5:24 pm
😉
Hello,Just i can suggest you some thing maybe useful to decrease execution time which :==>in table STAGE_RETURNS_CLS_TMP ,you can add new Boolean column to indicate if have relation to another table or mot ,And put this condition at the where statement to filter joining rows ,it does make different?
February 2, 2012 at 12:03 pm
Hey..Gail,
I just wanted to know something from you is that can we use database snapshot or replication as development server?
My manager told me that now onward they will provide me a snapshot instead of development server. So, I just want to make sure that does it serve the same purpose as development box?
I am really very sorry as I am bothering you a lot....but, i am sure that you are the right person to answer this question.
The funny thing is that we do not have any DBA and when I mention about wait_type they all asked me what is that...as I mentioned they pushed me to find it out and I was not aware of those too....I got that query which is using two sys views join and we will implement on wait_type today.
Thanks for all your reply and I appreciate for your time.
February 2, 2012 at 1:32 pm
Replication, maybe, but it can give problems if you're updating/deleting the data. Also a serious security/data privacy concern. If you're under any of the SOX, HIPAA or similar, that would be a major breach.
Snapshot, well depends what he means by that. There are about 5 things called snapshot in and around SQL.
As for wait type, open up the books online page for sys.dm_exec_requests.
If you don't have a DBA, then who's admining the servers and getting the exec plans and the like?
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
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply