Keep it Simple and Smart
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
» Read moreThis 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
» Read moreSQL 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
» Read moreWith 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
» Read moreWith 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
» Read moreProfiler 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
» Read moreFn_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 } )
» Read moreWe can find the wait stats as posted here We can Reset or clear the wait stats using — Reset or clear the wait stats counter DBCC SQLPERF (waitstats, clear) Hope this will help you.
» Read moreWe 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
» Read moreVarinder Sandhu is using WP-Gravatar