asked
Performace tuning
Hello All, Good Day. Interviewer asked me a question, if a user is telling to you as he experiencing slowness in database. What to you do to resolved that issues. I informed him i will check the blocking and i will run the profiler to find the which query is causing the issues(He replied me, if we run the profiler in production machine it will degrade the performace more, Then i struck up ) Please guide me know what are basic things we will check and then go furture. Database level and code level. Thanks,
In order for this site to work, you need to help us out. Please indicate all helpful answers by clicking on the thumbs up next to them. If any one answer lead to a solution, indicate that by clicking on the check mark next to that answer.
I would take a look at another [thread][1] on the same lines. @Grant mentions a few good tips and books to increase your knowledge. In addition a reasonable starting point would be to check the basics: - CPU utilization - Memory consumption - Disk stats e.g. Disk queue length - Wait [stats][2] - Utilize XEvents rather than a SQL Trace [1]:
http://ask.sqlservercentral.com/questions/91637/recommendations-for-performance-tuning.html [2]:
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
the easiest build in tool without profile would be activity monitor in sql server where you can find all basic stats regarding cpu, memory. Further more you can see which queries(stp) take very long and see if you can improve something. - check the machines that run the application maybe the bottle neck is over there - check the connections (internet, intranet)
The Profiler interface is for consuming the output of trace events, not for connecting right to the production server. Also, you could capture query metrics using extended events which are even more lightweight than trace events. You could also use dynamic management views to see some behaviors of queries in the system. You can look at general performance metrics as was suggested, but it's frequently difficult to correlate them to a specific performance problem.
17 People are following this question.
