Keep it Simple and Smart
Stored Procedure has ability to execute faster than the same T-SQL statements executed individually because SQL Server recorded the query execution plan for Stored Procedure. In case you need to execute the stored procedure with different execution plan then use WITH RECOMPILE options Different ways to use the WITH RECOMPILE
» 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 moreDisplay Row Numbers in Query Editor by default doesn’t display the Row numbers of T-SQL. To show Row numbers in Query Editor, Go to Tools Click on Option Go to Text Editor Then go to Transact-SQL Check the Line Number Hope this is useful for you.
» Read moreVarinder Sandhu is using WP-Gravatar