Close Menu

SQL-SERVER

 

longest running query using T-SQL

There might be situations where u might be getting requests from users saying that the entire website is slow or the application is slow. Situations like these could be because of a slow running query where users might have an impression of the enire server being slow and u might be getting Support Tickets related to that. Well Situations like these may demand you to look into some loong running queries on the server. Sometimes these long running queries may even be the cause for a complete server slowness depending on your isolation levels.

The main culprit in scenarios like these could be a long running query, which might be causing slowness.The best way to troubleshoot is to start looking for these queries one by one and identifying those queries and then troubleshooting the perfomance issue for that query. The below script helps us identify that.

In the image below shown the query is run on my personal laptop where no long running queries are present. However when u run the same on a production machine you will get a lot of queries being displayed in the results.

Before You Proceed We recommend you to read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.



Find Backup History on a Sql-Server 

 SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
 FROM sys.dm_exec_query_stats AS deqs
 CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
 ORDER BY deqs.last_execution_time DESC

you can download the Script here here

SQL-Server Scripts

  1. Find Backup History.
  2. Find Restore History.
  3. Find CPU Usage in the last Hour
  4. Last ran query along with the time it was executed
  5. Currently running queries
  6. Top 50 High CPU Utilization Cached Plan
  7. longest running query using T-SQL
  8. Find database files location
  9. Find index fragmentation
  10. Find Memory Usage
  11. Finding who ran a DELETE STATEMENT.
  12. Finding the number of transactions per minute
  13. Object Level Permissions
    1. Script to find object level permissions on a database with Create definition.
    2. Script to find server level logins and roles assigned
    3. Script to find database users and roles assigned
    4. Script to find Object level permission for user databases
  14. Script to find Orphaned users in an entire Instance of SQL Server
  15. SQL INSTANCE AND VERSION
  16. FIND WAITS IN AN SQL INSTANCE

    17. BLOCKING
  17. View wait stats for all block processes on SQL Server

Copyright @raghuveera.com | Website Designed and Developed and Maintained by Raghu
Connect with me @