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,
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.