Our application support people have stated that something is failing in one of the applications presumably due to some unusual activity/load in the database during specific hours of the day - specifically between 08:30 pm to 09:30 pm every night. No tangible evidence has been provided, but rather, they are interested to see the statistics/charts of whatever that is happening in the database during the problem hour, and see if they can get any clues from it. Or at least, to make sure that there are no issues on the side of the database.
Previously I had told them that tracing sessions can help us understand what happens in the database during problem hours. (Mybe I shouldn't have said that?) Now they are pestring me to provide them with trace data but they don't know what events should be traced. They have given me a vague request that "Can you please provide statistics of good and bad period so we can compare?"
The issue is that I'm note sure what events should be included, either.
To help them troubleshout this issue, I have also been thinking of using MDW (Management Datawarehouse). It's currently not enabled.
Here are m questions:
- If I eventually decide to enable a SQL Server trace (other than the default which is currently active), what events should I target for the session?
- Isn't MDW a better choice? What does trace provide (in terms of performance insight) that MDW doesn't?
- Is MDW a licensed feature? (personally I don' think so but need to be sure)
About the environment:
This is a SQL Server 2008 R2 database, awaiting for an upgrade in the first half of next year. This is a production database.
SolarWinds is used for monitoring of our databases. As per my understanding, it collects a great deal of data already (things like user connections, active sessions, active transactions, Lazy writes/sec, etc). These data are collected every 5 minutes.
We have Oracle DBAs in the team but no other SQL Server DBA. That means unfortunately no one else is around to be able to help me.
I am also new in this environment, not really proficient with SQL Server, at least with regards to performance tuning, as I was mostly doing Oracle in my life.