Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DB server comes to a crawl every two weeks Expand / Collapse
Author
Message
Posted Friday, January 03, 2014 9:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:21 PM
Points: 51, Visits: 192
Chris,
The only way I know of discovering the query is to set a trace, execute the action, and stop the trace.
Then I could copy it from the trace file and run it to see the plan.

Do you have any other ways to do this?
We have no access to code.

-tia,
Todd

Post #1527619
Posted Friday, January 03, 2014 11:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:20 PM
Points: 237, Visits: 763
What type of storage do you have?
Thin provisioning?
Did you see disk queues go up during these events?
Does it happen on the same days of week/time of day?
Any maintenance going on on the storage side?
Are your log files growing at all? At the time of the events?

Best of luck..
Post #1527658
Posted Friday, January 03, 2014 2:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 1,164, Visits: 6,272
TC-416047 (1/3/2014)
Chris,
The only way I know of discovering the query is to set a trace, execute the action, and stop the trace.
Then I could copy it from the trace file and run it to see the plan.

Do you have any other ways to do this?
We have no access to code.

-tia,
Todd



Search for "dmv and long running query".
Might be a clue in there.

We used SCOM to monitor the servers, and could run some reports to look for some patterns.
Handy place to start if you have it. Even at 15 minute captures, some things show up.
And it did have an SQL mgmt. pack available.

Once had an issue where they were doing backups for the domain, and it was killing us at 5am.
Kind of a pain to track down, as VM's were involved. Had to get up to the activity of the host to seem the bottleneck.
No mention of VM's in your thread, but something to tuck in the back of your mind for the future.

Post #1527731
Posted Friday, January 03, 2014 2:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
You can export the execution plan from the buffer cache using this dmv. You can find many examples of the queries if you google this dmv.

http://technet.microsoft.com/en-us/library/ms189747.aspx

Export the execution plan of a proc now and compare with when performance is slow.


Assuming your problem is all procs and queries I don't think this is going to make much difference. However it's a good tool for you to have in your arsenal.
Post #1527733
Posted Friday, January 03, 2014 2:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:21 PM
Points: 51, Visits: 192
arnipetursson (1/3/2014)
What type of storage do you have?
Thin provisioning?
Did you see disk queues go up during these events?
Does it happen on the same days of week/time of day?
Any maintenance going on on the storage side?
Are your log files growing at all? At the time of the events?

Best of luck..


Thanks for the response.
We've been down all these roads several times the past 6 months.

-Todd
Post #1527734
Posted Friday, January 03, 2014 3:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:21 PM
Points: 51, Visits: 192
MysteryJimbo (1/3/2014)
You can export the execution plan from the buffer cache using this dmv. You can find many examples of the queries if you google this dmv.

http://technet.microsoft.com/en-us/library/ms189747.aspx

Export the execution plan of a proc now and compare with when performance is slow.


Assuming your problem is all procs and queries I don't think this is going to make much difference. However it's a good tool for you to have in your arsenal.


Thanks for the link. I'l give it a go.

-Todd
Post #1527737
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse