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 ...
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, ...
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 ...
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))
INSERT INTO T VALUES('password1')
WAITFOR DELAY '00:01:00'
SELECT * ...
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 ...
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 ...
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
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 ...
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, ...
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 ...
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 ...
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:
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,
set @tracedata = cast(@oldcust_id as varbinary(8000));
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 ...
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
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.
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
BEGIN /* 1 */
-- about 700 lines of logic, math and if-parameter-...
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:
CREATE TABLE dbo.t
INSERT INTO dbo.t (someval)
SET IMPLICIT_TRANSACTIONS ON;
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 ...
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'
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 ...
I'm using Profiler 2012, talking to 2008, 2008 R2, and 2012 databases. I had to use a slightly different workflow than in the accepted answer.
When I try to follow that workflow, to migrate a template from 2008 to 2012, the Select template name dropdown depopulates, and clicking Save or Save As prompts me to choose a template to save.
Instead, copy your ...
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 ...
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 ...
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....
waitresource="KEY: 10:72057594039238656 (594b17b7493c)" waittime="20631"
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 ...
We had similar problem for huge queries. Often queries ran for hours (upto 7-8) depending upon the load on DB for 400M rows. However, our goal was to achieve group results, such as select col1, col2, col3, count(1), count(distinct col4) from table group by 1,2,3.
Underlying problem is same as yours though, as in both case DB sorts(orders) the results ...