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 ...
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 ...
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:
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 ...
Generally, you can expect that the row count reported will correspond to the number of rows returned to the caller (and to the (x row(s) affected) message in SQL Server Management Studio in the case of a top-level query).
However, where the statement contains nested calls, such as to a scalar user-defined function, row counts generated in the (repeated) ...
Network I/O is included in the rpc_completed duration*, so I would expect you'll see improvement in the workload you've described.
I enabled TCP/IP on my local SQL Server 2016 instance, and then ran a series of queries through a .NET application that uses an ORM. Here's a comparison of sp_statement_completed and rpc_completed Extended Events targets for ...
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'
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 ...
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 ...
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 ...
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....
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:...
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 ...