Tag Info

Hot answers tagged

21

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


17

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

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


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


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


10

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


10

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


9

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

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


8

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


8

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


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

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


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

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

In Profiler, click "show all events" and go to the Errors and Warnings listing. You should be able to check Exception and User Error Message. The Exception class will show you the actual error and User Error Message will show you the message displayed (e.g., "Incorrect syntax near ..." or whatnot). You mention a message going into the error log; there's ...


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


6

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where ...


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

Yes, the act of monitoring will require some resources. Running it on an overloaded server could kill it. You'll actually monitor real life load: your actions could get lost in the noise of this load. We run it on production sometimes. Mainly with a text filter for specific code, or with CPU/duration filters to trap longer running queries. And we don't ...


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

Even though SQL Profiler does not come with SQL Server Express, you can still create server-side traces using T-SQL. Check out sp_trace_create and sp_trace_setevent. The page for sp_trace_setevent also has a list of traceable event numbers. Finally, if you need a place to get started, this link may serve as a good jumping-off point.


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

If you tell Profiler not to filter out its own queries, you'd probably see it calling these stored procedures: sp_trace_create to create a trace sp_trace_setevent to add or remove events and columns (can only be called on a stopped trace) sp_trace_setfilter to apply a filter to a trace (can only be called on a stopped trace) sp_trace_setstatus to start and ...



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