Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SQL Server Memory Expand / Collapse
Author
Message
Posted Wednesday, June 4, 2014 8:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:40 AM
Points: 5,989, Visits: 12,925
Grant Fritchey (6/4/2014)
Since this is SQL Server 2014, you can query the system_health extended events session that's running on your machine. It writes out to a file that you can query to look for the ring buffers events. In there, you can look to see if there are low memory or out of memory events. If you have those, you're looking at memory pressure. Chances are extremely high however that you don't have those and that what everyone else has already told you is true, SQL Server manages it's own memory and it's using every single bit of memory allocated to it and everything is fine with your system.


I'm afraid its actually 2008R2 Grant!


---------------------------------------------------------------------

Post #1577354
Posted Wednesday, June 4, 2014 8:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
george sibbald (6/4/2014)
Grant Fritchey (6/4/2014)
Since this is SQL Server 2014, you can query the system_health extended events session that's running on your machine. It writes out to a file that you can query to look for the ring buffers events. In there, you can look to see if there are low memory or out of memory events. If you have those, you're looking at memory pressure. Chances are extremely high however that you don't have those and that what everyone else has already told you is true, SQL Server manages it's own memory and it's using every single bit of memory allocated to it and everything is fine with your system.


I'm afraid its actually 2008R2 Grant!


Ah, foolish and stupid me assuming we're looking at 2014 because it's in a 2014 forum................


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1577363
Posted Wednesday, June 4, 2014 11:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:53 PM
Points: 54, Visits: 188
HI It is Sql server 2008R2 enterprise edition only. I found the database which is consuming high CPU and memory.
I executed update statistics to improve the performance & to reduce the cpu, memory utilization, But no use. So please guide me what indepth I need to check in the found database, any benchmark values for comparision...
Post #1577439
Posted Wednesday, June 4, 2014 11:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
For tuning:
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

And for memory, there's still no indication there's a problem there.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1577454
Posted Wednesday, June 4, 2014 12:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
Updating the statistics is only going to help if your statistics are out of date and this is causing poor performance because of bad plans generated by those out of date statistics. I wouldn't recommend just willy-nilly running random things to "fix" the problem, especially when it's still not clear that you actually have one. You need to know that you're actually experiencing low memory issues and not just seeing normal behavior. I show how to check this in 2012 in this article. There are some differences in the XML in 2008R2 outlined in this article. If you adjust my query with those differences it should work same as checking the extended events.

Then, if you know that you have memory issues, you can begin to address them. The main thing I'd suggest is monitoring your system to understand which queries are using memory. To get a quick understanding you can query sys.dm_exec_query_stats to see which queries currently in cache are using the most resources. But to really understand it you'll need to set up query monitoring using extended events for trace events.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1577469
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse