The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries in SQL Server Box.
SQL SERVER – Get Latest SQL Query for Sessions – DMV
In recent SQL Training I was asked, how can one figure out what was the last SQL Statement executed in sessions. The query for this is very simple. It uses two DMVs and created following quick script for the same. SELECT session_id, TEXT FROM sys.dm_exec_connections CROSS APPLYÂ sys.dm_exec_sql_text(most_recent_sql_handle) AS ST While…
Read MoreSQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit
During several recent training courses, I found it very interesting that Worker Thread is not quite known to everyone despite the fact that it is a very important feature. At some point in the discussion, one of the attendees mentioned that we can double the Worker Thread if we double…
Read MoreSQL SERVER – DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action
I just received an email from one of the readers asking for help with error he encountered while attempting to run DMV. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action. Fix/Solution/Workaround: The above error is usually generated when the user…
Read MoreSQL SERVER – Recently Executed T-SQL Query
About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC INPUTBUFFER. The Short Answer in is both of them will be deprecated. Please…
Read More
SQL SERVER – Measure CPU Pressure – Detect CPU Pressure
The CPU is responsible for not only SQL Server operations but also all the OS tasks related to the CPU. Let us learn about measuring CPU Pressure.Â
SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions
This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs that are associated with Transactions. 1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server. 2) sys.dm_tran_current_transaction – Returns a single row that displays the…
Read More