Showing posts with label Executing queries. Show all posts
Showing posts with label Executing queries. Show all posts

August 1, 2010

SQL SERVER: Find executing queries

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.
 
SELECT OBJECT_NAME(ObjectID) as ObjectName,
  st.Text, 
  DB_NAME(database_ID) as dbname,
  Blocking_session_ID as BlockingSessionID,  
  *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
 
It will give us details like about running SQL Objects, with Database name:
 
 
Reference : Tejas Shah http://www.SQLYoga.com

March 22, 2009

SQL SERVER: Unable to Launch Activity Monitor. How to add VIEW SERVER STATE Permissions to launch Activity Monitor

Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

152

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

151


Let me know it it helps you.