Perfomance Tuning

Question

Post reply

Perfomance Tuning

Joy Smith San

SSC-Insane

Points: 24877
More actions
April 22, 2011 at 1:58 am

#143804

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

magasvs SSCertifiable Points: 7660 More actions · Answer 3

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.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

I too doubt it... Checking all those as well. Thank you somuch.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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

magasvs SSCertifiable Points: 7660 More actions · Answer 6

Yes. This could be external programs effect. Check if there is antivirus running at night and if database files excluded from scanning.

sachnam SSCrazy Points: 2317 More actions · Answer 7

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.