Tag Info

Hot answers tagged

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


14

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


12

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

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

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


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

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

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


7

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


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)


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

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

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

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

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


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


4

If you want to use profiler for this without being resource intensive on server, I would do the following: Start profiler, connect to the server and choose a blank trace Add event: security Audit -> Audit Schema Object Access Event and choose these columns : ApplicationName, ObjectName, LoginName, Permissions, DatabaseID, DatabaseName, SPID, ObjectType, ...


4

Can you add more details? I hope you are using SQL Server and which version + edition? Are your log files are filling up? Are you taking transaction log backups regularly? What are the result of the below? select name, log_reuse_wait_desc from sys.databases DBCC OPENTRAN() You can use profiler to trace all the current activity but on a busy system, it ...


4

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


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


4

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


4

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


4

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


4

yes, you can use profiling, for an example query SELECT * FROM Country LIMIT 5; you will need to do the follow: mysql> SET profiling = 1; mysql> SELECT * FROM Country LIMIT 5; . . . mysql> SHOW PROFILES; +----------+------------+-------------------------------+ | Query_ID | Duration | Query | ...


3

I assume the original trace contains the actions of multiple clients, with many queries executing in parallel? Your playback via SQLCMD is occuring serially, which is why it will take much longer to complete the same workload. SQLCMD is the wrong tool for this, you need the RML Utilities. A very brief outline of the tools (taken from a previous answer): ...


3

It won't help you trace existing calls from your website but you can still obtain interesting performance metrics by running SQL commands from this tool directly: Enzo SQL Baseline. It offers options to load commands in parallel to simulate load and helps you compare performance metrics over multiple runs.



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