question

Mohamed4053 avatar image
Mohamed4053 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,
performance-tuninginterview-questionstuning
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bernard avatar image
bernard answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.