Tag Info

Hot answers tagged

16

You can use server side trace (different from using Profiler GUI that incurs more resources) during your testing or your business cycle and capture only stuff related to SP's. Then you can load that in a table or excel for further analysis. Second approach, is to use DMV sys.dm_exec_procedure_stats (with limitation that if sql server is restarted, then the ...


15

I use SQL Profiler against production all the time. When done correctly (filtering so that you get back a very small amount of data) against a server the risk is minimal. Tracing everything down would be useless.


14

Try this free book from redgate: http://www.sqlservercentral.com/blogs/aloha_dba/archive/2009/01/21/mastering-sql-server-profiler-e-book-available-for-free.aspx Brad McGee also has some videos on his website: http://www.bradmcgehee.com/videos/ This video on technet: http://technet.microsoft.com/en-us/sqlserver/ee861095 This site also has more general SQL ...


13

Using Sql Server Profiler (GUI tool) to trace a production server is not a good idea. But it depends on load. Use server-side sql tracing (see sp_trace_XXX procedures) instead of it. Also I have found articles: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing, Automating Server Side Tracing in SQL Server Avoid Causing Problems with ...


11

Is it worth getting past the steep learning curve to take advantage of the extended events? Definitely yes. Extended Events is a new platform with better performance, scalability than SQL Trace and the functionality that is in SQL Server 2008 is kind of limited when compared to SQL Trace and also there is some learning curve. Rest assured, ...


11

It just means that the text of the statement contained the string "password" and SQL Server "helpfully" has masked it as a security feature to prevent you seeing some one else's password. I was able to reproduce this as follows CREATE TABLE T(X varchar(1000)) Connection 1 BEGIN TRAN INSERT INTO T VALUES('password1') WAITFOR DELAY '00:01:00' SELECT * ...


11

EventClass 15 is "Audit Logout" there's no TextData to associate with that. It's simply the connection closing. TextData would only be associated with events like "SQL:StmtStarting", etc... See here for more info: http://weblogs.sqlteam.com/mladenp/archive/2007/11/09/Map-SQL-Server-Profiler-EventClass-ID-to-its-name-in.aspx


9

Several options: You could use SQL Server Profiler against the Workgroup Edition server from a different client tools installation of SQL Server Developer, Standard, Enterprise or Evaluation Edition. Use the semi-documented trace flag 4032, as explained by Tom LaRock. Evaluate third party tools like the free DataWizard SQL Performance Profiler or xSQL ...


8

I wouldn't worry too much. The idea is to parameterise statements for re-use. That is, avoid compiling Consider you have 3 different clients run 3 separate statements: SELECT col1, col2 FROM SomeTable WHERE col3 = 1 SELECT col1, col2 FROM SomeTable WHERE col3 = 15 SELECT col1, col2 FROM SomeTable WHERE col3 = 42 Each of these has different text ...


8

Yes, it is available in the installer for development edition. Without firing it up, I'm not sure if the installer has changed between 2008 and 2008R2 but in 2008 there are two options for installing client tools. Management Tools (Basic) which installs SSMS, SQLCMD and Powershell support Management Tools (Complete) adds Profiler, Database Tuning Advisor, ...


8

For debugging purposes you can trace them explicitly into the Profiler using a custom event, via sp_trace_generateevent: declare @tracedata varbinary(8000); set @tracedata = cast(@transferdate as varbinary(8000)); exec sp_trace_generateevent 82, N'@transferdate', @tracedata ); set @tracedata = cast(@oldcust_id as varbinary(8000)); exec ...


7

To view the trace file contents in SMSS, I am using the following function: FN_TRACE_GETTABLE. To run FN_TRACE_GETTABLE, the users don't need any permissions on the server's file system. Only the account that is running the service must have the permissions to access the trace file, but it already has them, because it has created the trace file in the first ...


7

The quickest (and cheapest) way to find out what is hitting your databases is to use sp_whoIsActive (http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx)


7

If you are on SQL Server 2008+ you can also use extended events with a histogram target. Possibly this would be more light weight than a trace. AFAIK you would need to create a different session for each database of interest though as I couldn't see any indication that bucketizing on multiple columns was possible. The quick example below filters on ...


6

I have used the MVC Mini Profiler as part of an application to profile SQL Azure but depending on your application it may or may not work. What kind of tasks are you doing that need profiling?


6

SQL Profiler does not come with SQL Server Express. For 2008 R2, you can get it with the installation media for datacenter, Enterprise, Standard, Web, Developer, or Workgroup licenses. If you have the installation media, you can get Profiler that way. Otherwise, Developer Edition is available for $50. There are some other tools available, and you could ...


6

i doubt that the ALTER TRACE permission is a server level permission, and access is at the server level; if a user can start a trace, he or she can retrieve event data no matter what database the event was generated in. for more info http://technet.microsoft.com/en-us/library/cc293611.aspx http://msdn.microsoft.com/en-us/library/ms187611.aspx


6

The manual page for order by optimization might help you, specifically the suggestion summary at the end of the article. Be careful on increasing sort_buffer_size and read_rnd_buffer_size, as I believe they are 'per thread' values, which means each mysql connection thread will get allocated the memory associated with each value. If too large, you can find ...


6

You may need to bump up the following variables as well: tmp_table_size max_heap_table_size If these are too small, the tmptable goes to disk quickly. If these are too large, the tmp table goes to quickly when the limit is surpassed but creates intermittency due to moving the large in-memory tmptable to disk before completing the tmptable's usage. So, ...


6

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way: WHILE (@@FETCH_STATUS = 0) AND             ( @About80MoreBooleanExpressionsHere) BEGIN /* 1 */     -- about 700 lines of logic, math and ...


6

I wouldn't bother with using Profiler at this point. You're likely just exasperating the issue, since streaming that back through a UI is not exactly light on resources. If you want to trace, set up a server-side trace and inspect the results afterward. But first, let's start simpler. When the procedure is running in its "stuck" state, look at the DMV ...


5

If you are using most ORMs (Link2SQL, EF, etc.) they cause this lovely little method to be used. If you are writing a parametrized insert statement it'll come through in this method as well.


5

Try a trace on SP:Completed and RPC:Completed with column filters on TextData for: % insert % % delete % % update % Might get the odd spurious result but it'll narrow the field. Add SQL:BatchCompleted if you have a mix of procs and statements.


5

SQL Server Profiler is a GUI that utilizes SQL Server Trace through the client-side. Due to this nature, yes you can potentially see a mild to severe performance impact depending on the environment. SQL Server Profiler is suited mainly for a quick glimpse at what is happening on the server (provided the database server can handle the extra latency). It is ...


5

If you open Profiler, go to File > Templates > Import and choose your .tdf file. You will have a new user trace template with the name [filename] (without the .tdf). Go to File > Templates > Edit and pick the trace template you imported. You should then be able to edit the server type by pulling the dropdown: Then you can save the template and that ...


5

This is probably caused by a blocking process. 3-5 times a day this is blocked by some update or other write that takes ~40 seconds. Your SELECT blocks until it can continue. If this is indeed the case, the problem can be identified using the Blocked Process Report Event. You will have to enable this event and set the threshold to ~35 seconds. See blocked ...


4

If you use server side tracing, the impact on performance can be minimized.


4

If you aren't seeing the activity in your trace, it may not be getting that far. You'll want to turn up some logging for your SSIS package.


4

Just use a trace template. Click File, New Trace, and choose the Replay template. That'll get you everything that happens.


4

I guess I can't comment on your question since this is the first time I've used stack exchange but is this an AD group that the user is in? Sometimes people say group when they mean SQL role so I just wanted to get some clarification. The reason I ask is because if the user has a login on the server of domain\olduser when you change the name in AD to ...



Only top voted, non community-wiki answers of a minimum length are eligible