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

find session Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 8:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
Hi friends,

Our database server(windows 2008R2 4GB 64-bit) goes up to 98% physical memory and about 3.5G from sqlservr.exe. Max server memory is set to 2GB for sql server. Users are facing performance issues.. Is there a way to find out which process is using up most memory from sql server? Also, are there any options to resolve this other than increasing RAM?

Thanks a lot
Post #1472180
Posted Wednesday, July 10, 2013 8:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
Hello newbie,

During the peak time, the first basic which I would like to check is what is running inside. These are the most basic queries i'll use:

select top 10 * from sys.sysprocesses order by cpu desc

select top 10 * from sys.sysprocesses order by physical_io desc

select top 10 * from sys.sysprocesses order by memusage desc

You might want to check the code associated with these top running spids using following code:

dbcc inputbuffer(<spid>)


Also there is famous piece of code from GlennAlanBerry's brilliance:

SELECT TOP(25) qt.[text] AS [SP Name], total_logical_reads, qs.max_logical_reads,
total_logical_reads/qs.execution_count AS [AvgLogicalReads], qs.execution_count AS [Execution Count],
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.total_logical_writes,
qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = DB_ID() -- Filter by current database
ORDER BY total_logical_reads DESC OPTION (RECOMPILE);
Post #1472187
Posted Wednesday, July 10, 2013 9:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
Thanks. I'm confused with this one.. I set the maximum server memory(in MB): 2147483647 on the database server. But i the task manager sqlservr.exe is using 3349316K memory. So logically SQL server should only use the max set memory of 2G? Why is it using up over 3G and the memory goes to 96%? Please help

Thanks a lot

Post #1472215
Posted Wednesday, July 10, 2013 11:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
newbieuser (7/10/2013)
Thanks. I'm confused with this one.. I set the maximum server memory(in MB): 2147483647 on the database server. But i the task manager sqlservr.exe is using 3349316K memory. So logically SQL server should only use the max set memory of 2G? Why is it using up over 3G and the memory goes to 96%? Please help

Thanks a lot


The default setting is an insanely high value...2048 Terabytes, not 2048 megabytes like you think it is;

for me, that's more ram than i can afford to buy.

could that be contributing to your issue?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472255
Posted Wednesday, July 10, 2013 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
OH really? I thought its in MB?
Post #1472273
Posted Wednesday, July 10, 2013 12:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
newbieuser (7/10/2013)
OH really? I thought its in MB?


yes it is, but that's what's so tricky! it's not obvious!
how much is 2147483647 MegaBytes?
2147483+ Gigabytes!
2147+ TeraBytes!


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472280
Posted Wednesday, July 10, 2013 1:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
thanks I got it fixed now
Post #1472320
Posted Wednesday, July 10, 2013 2:22 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:20 PM
Points: 807, Visits: 725
Are there other applicaitons running on the server? Else I see little reason to see Max Server Memory to 2GB when you have 4GB in the machine. Set it to 3GB.

Whether you actually need more RAM, it's hard to say, but 4GB is not much in a databse server these days. Of course, if you add more RAM, don't forget to change Max Server Memory.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse