Hi Team,

I am not able to understand the root of a performance problem before I can fully address it.

I am working on SQL SERVER 2008 R2 DB. We have transactional DB, wherein multiple user can login and perform operations concurrently.

We have used 'N' number of stored procedures and functions. As DEVELOPER, I want to identify SQL objects which are taking time on production.

I am facing problems like,

1. I don't have access to production DB.

2. On lower environment (ALPHA, BTAT, CERT), we have less data and poor performance is not observed. So I can not use my lower environment.

3. I can not use SQL SERVER trace on production, as it will put load on production DB. (lower region trace is not giving me enough information.)

Can I ask production team to install some free DB performance monitoring tools to install?

What type of information, I should ask from production DB people (as after putting request, they can provide me that details after few days, and asking same type of information again may not be feasible)?

Performance counter use?

• Edited by BhavSAn 1 hour 29 minutes ago

Being a developer where you don't have access to production its difficult for you to analyze problem as troubleshooting requires absolute access. You would be in position to troubleshoot and tune queries which DBA would give to you saying that this is costly query and taking long time on prod and using excessive resources as well. This is how IMHO team works

I would also ask how deft are you with SQL Server performance troubleshooting remember it does not only involves query. A single parameter (like max degree of parallelism) if set incorrectly can force system to perform poorly.

I would say ask experienced DBA to have a look at the system take his feedback and if it is related to query tuning you would be able to contribute better.

Yes, perfmon counters can and should be used in production.

But so should profiler, that is trace scripts logging to local disk files, with good filters to keep the volume down, can be run on any modern production system with very little impact.

Any third-party monitor is going to do the same thing and have the same impact.

But if you have even performance-view permission you can use the DMVs to look at the procedure cache and get a pretty good read on what is using the most resources.  Even that is not completely impact-free, but it's pretty minor.

I've been there with crazy places that want you to diagnose something you can't touch, all you can do is to keep coming back to them until they see reason.

Josh