SQL Server: Script to Start and Stop a New Trace without Using Profiler GUI

SQL Server Profiler is an useful tool, but basically, important part of profiler is trace which this graphical interface shows. Trace data can be collected and saved with in a file, even without using SQL Server Profiler graphical interface.

To do so you need a script that will perform this task. Good thing is that, SQL Server Profiler helps us to create this script, through following path.

Now, only you need to change it according to your requirements. Here is a complete script for auto trace which we use on production servers, to capture quires taking time more than 5 seconds to execute. Script can be used in a job to execute on daily bases. 

 

To stop this auto trace, you just need to change its status to STOP and then CLOSE. Keep in mind to close a trace, you must stop it first.

SQL Server: Using SQL Profiler to Capture Stored Procedure Call from Application

SQL Profiler is a high-quality tool to figure out different database issues, like “Which are costliest queries running”, “Which quires acquired exclusive locks”, “Which indexes are missing” and so on. But in development environment and on production when solving a problem, developers like to use SQL Profiler to get exact procedure call which is being generated by front end application.

Worst practice is, that developers like to use existing built in templates for this purpose and normally use default one i.e. STANDARD.  If you are also using SQL Profiler for this procedure call purpose, then selecting STANDARD trace template is not a good choice, as on production server it affects its performance and even on development server it returns much more extra information.

Good practice is, if you have not created your own template then always select TUNING.

 It also has some extra information so when you only need to catch procedure calls generated from your application then click on “Event Selection” tab and keep only “RPC:Completed” event. You need not to select “Sp: stmt Completed” as you just need to capture “execute procedure calls” and not all the statement inside this procedure. You can also omit “SP: Batch Completed” as we need calls that are only generated from application. If you also need to capture calls from SSMS then you can keep it.

To avoid extra work pressure on server and to get your required results only, you must also apply filters on “Database Name” and “Text”

 Use % sign, just like you use in LIKE operator.

Now run your trace and you will find your required results quickly and clearly, even without putting extra work load on database server.

SQL Server Performance: Not All Fast Queries Are Innocent

To, find out costly quires, a majority of DBAs like to visit SQL Profiler. Queries with higher CPU and READ/WRITE are marked as costly quires. I have observed that most of the time quires with less CPU and READ are ACTUAL BIG PROBLEM. Cumulative effect of multiple executions of these commonly considered well performing quires normally put more pressure on system as compare to occasionally executing costly quires. Simple if a query giving result in 10 milliseconds but being executed 10000 time, with in a short time, then definitely it’s a REAL costly query.

To get optimum performance, identification of such COSTLY quires is necessary. This can be achieved by creating history of trace data for peak and off peak hours of your database. Follow given steps for this task.

• Open profiler, and select following columns
• EventClass, TextData, Duration, CPU, Reads, Writes
• Create a filter on your required database
• Start your trace, and later save this trace data to some trace output file.
• Load trace data from trace file to trace table by using following query

SELECT * INTO Trace_Table

FROM ::fn_trace_gettable('C:\YourTraceFile.trc',default)

• Once the trace data is imported, use following query to find quires with high CPU and READ/WRITE values

SELECT COUNT(*) AS TotalExecutions,

      EventClass,

      TextData,

      SUM(Duration) AS TotalDuration,

      SUM(CPU) AS TotalCPU,

      SUM(Reads) AS TotalReads,

      SUM(Writes) AS TotalWrites

FROM Trace_Table

GROUP BY EventClass,TextData

ORDER BY TotalReads DESC

Why order by TotalReads and not CPU, read this

Sql Server Performance: Which Statement Triggered the Recompile Event

In last post “Performance Counter to Count Stored Procedure Re-compilations” we have discussed about a useful counter of system performance counters i.e. SQL Re-Compilations/sec. In this post, we have discussed that if nonzero values are consistently occurring for this counter, we should seriously search for the culprit stored procedures and then statement with in that stored procedure.

Let me explain, how Sql Profiler can help us to find statement in a stored procedure which actually triggered the recompile event.

Open Sql Server Profiler and start a new trace but with following events

·         SP:Starting

·         SP:StmtStarting

·         SP:Recompile

·         SP:Completed

You can select these events by selecting check box “Show all events” (as mentioned in screen shot. Click “Run” button to start trace.

Meanwhile execute following query as an example

CREATE PROCEDURE proc_testRecompilation

AS

    CREATE TABLE #t ( a INT )

    SELECT  *

    FROM    #t

GO

EXEC proc_testRecompilation

Now shift your focus to Sql Profiler to examine trace, which must resembling following screen shot.

Watch closely the statement which appeared before and after the SP:Recompile. This is the statement in stored procedure which actually triggered recompilation. Once we have detected the culprit statement, we must find out the reason behind this happening. In our example statement triggered recompilation because of "Recompilations Due to Interleaving Data Definition Language (DDL) and Data Manipulation Language (DML) Operations". What are other reasons behind recompilation occurring read previous post.

Note: In our example you will only see the recompile event on the first execution of the procedure, or if you drop and re-create the procedure each time you execute the script.