April 23, 2015 at 10:47 am
Just giving ALTER TRACE allows you to run Profiler there are problems in how it runs. It doesn't pull any trace templates and it locks up (and I have to kill it with task manager) if I stop a trace and click to modify the trace properties. Are there other permissions that I need to fix this behavior?
April 24, 2015 at 7:46 am
are you the DBA? if not get someone who is sysadmin to run the trace and if they experience the same issues you can rule permissions out.
April 24, 2015 at 8:02 am
Don't use the Profiler GUI against a production system. It's got horrible overhead and can hang itself as a result. Use a server-side trace instead. ALTER TRACE is all you need, there's no other permissions necessary.
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
April 24, 2015 at 8:08 am
it all depends on what resources the server has got, and what the workload is like. I have one server that profiler tracing is no problem for, and another where it has a big impact.
April 24, 2015 at 8:16 am
cunningham (4/24/2015)
it all depends on what resources the server has got, and what the workload is like. I have one server that profiler tracing is no problem for, and another where it has a big impact.
I had a server where using profiler had no effect at all. Until the day that I started Profiler and crashed the server, requiring two cluster failovers (the secondary node had config problems) and 10 minutes of downtime at the busiest time of the day
The GUI takes nasty latches, can and will degrade performance and may even crash the server. Rather use a server-side script which has much less impact or, on SQL 2012 and above, just use Extended Events which are even lighter weight and lower impact.
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
April 24, 2015 at 8:27 am
That sounds like a bad day at the office!
April 24, 2015 at 9:33 pm
First, I am not the DBA (unfortunately). Second this is not a production system, it's actually multiple servers in our test environments. I was granted alter trace but when I run Profiler it doesn't even list the default set of templates and I can't save a template and as I stated in the original message if I stop the trace and click the trace properties button Profiler locks up tighter than a drum. Finally, the servers are 2012 servers and the Profiler is from the 2012 client tools.
I've not seen this behavior before and was curious if anyone else had seen it and figured out what it was.
April 25, 2015 at 12:07 pm
Have you got a dba to run it to see if they get the same issues? Just to rule out permissions?
April 26, 2015 at 1:21 pm
Of course when the DBA runs it they have higher permission so they don't have the problem.
April 27, 2015 at 7:30 am
Are you running Profiler from your desktop? If so, it sounds like the desktop tools didn't install properly.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
April 28, 2015 at 7:03 am
Yes, I'm running Profiler from my desktop but it works fine against servers, i.e. my local installation, to which I have full access. It's only those servers that I have limited access to that I have this problem with.
April 29, 2015 at 7:30 am
buddy__a (4/28/2015)
Yes, I'm running Profiler from my desktop but it works fine against servers, i.e. my local installation, to which I have full access. It's only those servers that I have limited access to that I have this problem with.
As someone suggested earlier, it sounds like it might be an issue with how the client tools are installed on your PC, especially the part about not seeing templates. Confirm if it works with a local instance.
The following "power user" permissions work for myself in the role of troubleshooter in high volume production systems. I've found that running a trace by itself isn't helpful unless you can also followup and do a few other things like querying data management views or object schemas.
However, if all you need are minimal permissions to run Profiler traces, and "alter trace" by itself isn't working for some reason, then perhaps next request "view server state".
use SpecificDatabase;
-- grant select on any table or view:
exec sp_addrolemember db_datareader, 'DBPowerUsers';
-- grant user permission to view execution plans:
grant showplan to 'DBPowerUsers';
-- server level permissions require that context be changed to [master] database.
use master
-- grant user permission to view object schemas:
grant view any definition to 'DBPowerUsers';
-- grant user permission to view system tables and views:
grant view server state to 'DBPowerUsers';
-- grant user permission to start sql profiler traces:
grant alter trace to 'DBPowerUsers';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 11, 2015 at 6:46 am
I just wanted to follow up on this just in case anyone was as dumb as me and was using the 2012 version of Profiler to profile a 2014 SQL Server. Once I upgraded to the 2014 client tools it all worked fine. Note to self (and everyone else), you can profile down versions but not as well on up versions.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply