April 22, 2011 at 1:58 am
Expertrs,
One of our server is bit slow and creating some problem during a perticular time, around 1 AM. Inorder to analuse the issue, I have scheduled to run trace at 1 AM and capture all the data.
As a next step, shall I try Database Tuning Advisor with this trace data.? Or is there anything else that I can do with this profiler data.? (.. doing all these activities after long time, bit confused.)
Am also planning to capture all the necesary perf counters as well.
Please guide in proper direction. Thanks in advance.
San.
April 22, 2011 at 4:00 am
If you run DTA, do it on another server and do not blindly accept recommendations, test each one and see.
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 22, 2011 at 4:59 am
The other thing you can do with the trace data is feed it into the RML utilities. That will give you good reports on the longest running query, etc. From there you can make your own decisions. As Gail says, the DTA is not a terribly good tool. I don't recommend using it.
"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 22, 2011 at 9:47 am
I think that the first step should be checking if there are any jobs scheduled at this time or close to this time. CheckDB and index rebuild tasks usually take a lot of resources.
April 25, 2011 at 4:47 am
I too doubt it... Checking all those as well. Thank you somuch.
April 26, 2011 at 7:56 am
get a copy of the great free tool sqljobvis. It shows you a gantt chart of agent job executions, making it VERY nice for checking for overlaps. Not that your problem could be from batch jobs fired outside of sql agent though.
Also, if you are on a SAN, it could be your files are on a shared volume that has other things killing the disks at 1am, or the SAN is doing it's own maintenance, snapshots, backups, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 26, 2011 at 7:17 pm
Yes. This could be external programs effect. Check if there is antivirus running at night and if database files excluded from scanning.
April 26, 2011 at 10:37 pm
Also, check if some full\differential backup job is running during that time. And also are you using some external tool for backups or just native backups because when you run an external tool it runs outside the scope of sql server memory(Buffer pool). Try adjusting your memory configuration accordingly. Also check if some heavy Asynchronous batches are running during that time window may be through some Application or Engine.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply