Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the rights to use this tool. Apparently you can kill other people queries and this is too dangerous.

Is there a way to check the status of the server with code? (Or view the activity without needing those rights so I can manually run them?)

share|improve this question

migrated from stackoverflow.com May 28 '13 at 12:21

This question came from our site for professional and enthusiast programmers.

3  
To use activity monitor you just need VIEW SERVER STATE permissions. To kill another user's query you would need the ALTER ANY CONNECTION permission which you probably don't have. –  Martin Smith May 28 '13 at 11:30
    
Thanks Martin, thanks to you I just found out my admin was lying to me! –  Freddy May 28 '13 at 12:47
    
@Freddy Can you tell us what rights you have on the server which you want to monitor ? That way we can give you better suggestions that will work for you. –  Kin May 28 '13 at 13:30
    
I can't provide you a list, but I know I recently got the db_owner rights, hope this helps... –  Freddy May 28 '13 at 13:49

3 Answers 3

up vote 1 down vote accepted

IT depends on what you want to actually monitor. Also do you want to log the data you have collected for baselining or performance trend reporting as well ?

There are many more areas to concentrate like Index tuning, query plan analysis, etc. This is just a tip of ice-berg but will serve a good starting point.

share|improve this answer
    
They told me I can't get rights for any sys.dm_os related type. I will definitely try the long running queries with dm_exec. Thanks for the answer! –  Freddy May 29 '13 at 7:23

The below query uses the sys.dm_os_performance_counters DMV to capture the performance counters of SQL Server. You can change the WAITFOR DELAY options and the Numeric suffix after the second GO command to modify the time interval and the number of times you want the code to be executed.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('Tempdb..#PerformanceMonitor') IS NOT NULL
DROP TABLE #PerformanceMonitor;

CREATE TABLE #PerformanceMonitor
( 
[object_name]   NVARCHAR(255) NOT NULL,
[counter_name]  NVARCHAR(255) NOT NULL,
[instance_name] NVARCHAR(255) NULL,
[cntr_value]    BIGINT NOT NULL,
[cntr_type]     INT NOT NULL,
[LoadedOn]      DATETIME NOT NULL DEFAULT GETDATE(),
)
GO
INSERT INTO #PerformanceMonitor
([object_name],[counter_name],[instance_name],[cntr_value],[cntr_type]) 
(SELECT object_name,counter_name,instance_name,cntr_value,cntr_type
 FROM sys.dm_os_performance_counters)

WAITFOR DELAY '00:00:10' --Change the interval here
GO 10 --Change the number of times the data will be captured here
SELECT * FROM #PerformanceMonitor

Once you have enough data and you have narrowed down the counters which are relevant, you can decide upon the best time to run your queries. And also as mentioned in the answer by @Yaroslav ,it is very important to keep in mind what type of query you want to run and decide accordingly. And the most important thing, be careful with production data!!!

share|improve this answer
    
+1 for reminding about the importance of production data –  Yaroslav May 28 '13 at 12:32
    
I don't have and can't get the rights to any os type. So sys.dm_os_performance_counters is out of the question. :( –  Freddy May 28 '13 at 13:14
    
It requires VIEW SERVER STATE permission too. –  Martin Smith May 28 '13 at 13:42
    
well , it becomes difficult if you do not have VIEW SERVER STATE Permission. You can also use SP_WHO2 to get an idea of what processes are running at a given point of time, but even for that you will need sysadmin permission or VIEW SERVER STATE permission. –  S.A May 28 '13 at 13:47
    
I need to involve too much ppl to get the view server state rights, so I wont be able to do this –  Freddy May 28 '13 at 13:50

There is too much to track, logins, heavy queries execution, reads, writes, waits and more. The query you will run is just a report for example (heavy reads) or a big bulk insert? Or just maybe a performance tunning query or backup?

Check these links, maybe one can fit your needs.

And there is much more. Search for sql server monitoring scripts, or performance monitoring scripts. Based on the data you collect you can choose when to run your query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.