32

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

MySQL has never come up with Query Profiling. Now that MySQL is being grandfathered by Oracle, I know this will continue to be the case. Yet, all hope is not lost. Since 2007, Percona has come up with some absolutely marvelous tools for everything a Developer and DBA would want, including Query Profiling. Percona's first set of tools, known as MAATKIT, ...


13

SQL Profiler does not come with SQL Server Express 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 even ...


12

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


12

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 From comments: The duration it ...


12

SQL Server never randomly chooses to delete or not delete a row. When you send a valid DELETE statement to SQL Server, it executes it. Guaranteed. Period. If there is an error in the statement, SQL Server will return an error. Are you seeing errors? The far more likely problem is the query itself is not targeting the rows you think it is, or those rows ...


11

You can find this question useful, it applies to tables and columns but suggests using a third party tool ApexSQL Clean which can also find unused stored procedures as well as all objects that are not referenced by any other object in database, or in external databases Disclaimer: I work for ApexSQL as a Support Engineer


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

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


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


10

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


9

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


9

You can narrow down the list by specifying DatabaseName or DatabaseID in Column Filters: Note however that you need to check the box "show all columns" first. As for inserts, updates and deletes you can filter by text column at the same place:


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

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?


8

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.


8

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


8

There is a possibility the web service code is turning on implicit transactions. As an example of how this works, I've created a simple table in tempdb, inserted a row, turned on implicit transactions, and deleted the row: USE tempdb; CREATE TABLE dbo.t ( someval int ); INSERT INTO dbo.t (someval) VALUES (1); SET IMPLICIT_TRANSACTIONS ON; DELETE ...


7

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


7

There is a free, open source tool called ExpressProfiler - https://expressprofiler.codeplex.com/ Very convenient tool. In addition to SQL Express databases, it even supports a localDB.


7

No, SQL Server does not track individual occurrences of queries, for the same reason you don't want to run profiler: tracing constantly can have a substantial performance impact on the server, and the last thing Microsoft wants to do is to turn on high-cost diagnostics most people will rarely or never use. Profiler is the absolute last tool you want to use ...


7

SET PARSEONLY ON will do this. FMTONLY will generate an error. EG: USE [AdventureWorks] GO set parseonly on go exec [uspGetBillOfMaterials] go set parseonly off


6

When the script is generated in Profiler 2005, the default value saved for the logical operator is 1='OR', so the trace will capture more information than we initially intended. exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME1' -- OR exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2' exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3' ...


6

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


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

The mysql_load() function calls the open_and_lock_tables() function to lock the table mentioned in the LOAD DATA statement. MySQL obtains an exclusive lock on the table so that it can very quickly load data into the table. There is very little overhead in the LOAD DATA process, just the bare minimum parsing is done to make it work. The CONCURRENT option ...


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


6

waitresource="KEY: 10:72057594039238656 (594b17b7493c)" waittime="20631" ownerId="9710782702" transactionname="UPDATE" The blocked process is waiting on a KEY (therefore on a row), for 20631 ms (~20s). The blocking transaction is still executing a statement, likely the UPDATE that causes the blocking. It is not blocked, if it would be ...


6

Every time I had to do this, I used the RML Utilities (x86 and x64 package download links can be found on this page). Basically, you just have to set up a server-side trace using one of the templates included in the package. The captured trace can be analyzed using ReadTrace (included in RML Utilities) and it populates a database with query analysis results....


6

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file: They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, SP:...


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