Category: SQL-Performance

SQL Server – Last Execution Date/Time of a Stored Procedure

SQL, SQL-Advanced, SQL-Performance, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

If you want to get the  Last Execution Date/Time of a Stored Procedure then here is solution. Sys.dm_exec_procedure_stats it’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. This view has been introduced from SQL Server 2008. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is

» Read more
,

SQL Server – Stop/Close a Server-Side Trace

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

This function “sp_trace_setstatus” modifies the current state of the specified trace. Syntax sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status Example –See the Ruuning trace SELECT * FROM ::fn_trace_getinfo(NULL) — Stop thr Running Trace EXEC sp_trace_setstatus @traceid = 1 , @status = 0 — Delete the Running trace EXEC sp_trace_setstatus @traceid = 1 , @status = 2 Note: 0 = Stops the specified trace. 1 = Starts the specified trace. 2 = Closes the specified trace

» Read more
, ,

SQL Server – System Trace Functions

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

SQL Server provides the following system trace functions that are useful if you are working with trace. fn_trace_getinfo fn_trace_gettable fn_trace_getfilterinfo fn_trace_geteventinfo fn_trace_getinfo This function returns the information of a specified trace or all running traces. Syntax fn_trace_getinfo ( { trace_id | NULL | 0 | DEFAULT } ) Example See all the Running Trace SELECT * FROM fn_trace_getinfo(NULL) fn_trace_gettable This function export the Trace file data into Table. Syntax fn_trace_gettable ( ‘filename’, number_files ) Example See the example here fn_trace_getfilterinfo

» Read more
, , , ,

SQL Server – Find the expensive queries with Server Side Trace

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

With the help of this post sharing with you the procedure to find the expensive queries with Server Side trace. First we see how to create a server side trace script. Run the Profiler Trace as mentioned here Go to the File >> Export >> Script Trace Definition >> For SQL Server 2000 or For SQL Server 2005- 2008R2   You will get a Script for Server Side Trace as /****************************************************/ /* Created by: SQL Profiler */ /* Date: 01/11/2011

» Read more
,

SQL Server – Steps to create the Profiler Trace

SQL, SQL-Advanced, SQL-Performance, Varinder Sandhu

With the help of this post sharing with you the steps to create the Profiler Trace. Open the SQL Server Management Studio >> Go to Tools >> SQL Server Profiler It is mandatory for the user to have system admin rights to start the profiler. Once the profiler is started click on Menu >> File >> New Trace Below screen display for Trace Properties Here we are using the trace with default standard template for demo. You can choose as

» Read more

SQL Server – Profiler Trace Vs Server Side Trace

SQL, SQL-Advanced, SQL-Performance, Varinder Sandhu

Profiler Trace Profiler Trace using the standard default template supplied by SQL Server with SQL Profiler. SQL Profiler running on a client machine and it is connected to the SQL Server instance over the network. Server Side Trace The Script is used to create Server Side Trace. You can generate for server site trace with the help of SQL Profiler. Difference between Profiler Trace and Server Side Trace (Profiler Trace Vs Server Side Trace) Client-side traces dragged transaction throughput down as

» Read more
,

SQL Server – File stats using Fn_virtualfilestats

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

Fn_virtualfilestats() is in-build function in the SQL Server and really useful for understanding the amount of I/O within individual files on your SQL Server system. It returns I/O statistics for database files, including log files. Syntax fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } ) Example 1 File statistical information for a database select * from :: fn_virtualfilestats (1,1)   Example 2 File statistical information for all databases and files select * from :: fn_virtualfilestats

» Read more
,

SQL Server – Capture the Wait Stats to table for analysis

SQL, SQL-Advanced, SQL-Performance, SQL-Tips and Tricks, Varinder Sandhu

We can find the SQL Server wait stats by using DBCC SQLPERF (waitstats)   Now we need to capture all these wait stats to table for analysis –Create a temp table for waitstats CREATE TABLE #waitstats ( wait_type VARCHAR(500), Request REAL, wait_time REAL, signal_wait_time REAL ) — Insert the waitstats to table INSERT INTO #waitstats EXEC('DBCC SQLPERF(waitstats)') –Now you can analysis waitstats SELECT * FROM #waitstats   Hope this will help you to analyze the wait stats.

» Read more
,