January 20, 2011 at 11:02 am
WHAT ARE THE WAYS TO AUDIT SERVER LEVEL AND DATABASE LEVEL TEMPDB AND MEMORY ISSUES
January 20, 2011 at 11:38 am
I guess it depends on what you want to monitor.
There are lots of products out there that will monitor server memory use for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2011 at 11:57 am
Out of the box, no additional installs or purchases, you can use Performance Monitor to gather metrics on stuff like Memory usage. It can also be used to monitor I/O, CPU & Network as well as other interesting values.
An inexpensive, yet pretty powerful, 3rd party monitoring tool is Red Gate SQL Monitor.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 20, 2011 at 12:41 pm
Run a perfmon counter log for 24 hours and collect the following the counters to identify the Memory Pressures
Memory: Available Mbytes
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Buffer Manager\Pages/sec
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
PagingFile: %Usage
PagingFile: %Usage Peak
Check the following link to moniotor and troubleshoot TempDB issues
http://technet.microsoft.com/en-us/library/cc966545.aspx
Thank You,
Best Regards,
SQLBuddy
January 21, 2011 at 3:09 pm
I was looking options in sql server 2008 such as sys.dm_os_memory_objects ( primarily used to analyze memory use and to identify possible memory leaks)
How do I do auditing with the dmv?
January 21, 2011 at 6:03 pm
The DMVs are usually more for tactical style monitoring, checking information at the moment, not long term. To use the DMV for long term monitoring, you just need to capture data from it, over & over, using automated calls, probably through SQL Agent. But, remember, those calls, are just going against the same Performance Counters that the Performance Monitor is using.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 21, 2011 at 9:24 pm
Grant is Right. Follow his recommendations.
Thank You,
Best Regards,
SQLBuddy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply