Post reply

find session

  • July 10, 2013 at 8:22 am

    #395113

    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

  • July 10, 2013 at 8:43 am

    #1631258

    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]

    FROMsys.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);

  • July 10, 2013 at 9:41 am

    #1631280

    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

  • July 10, 2013 at 11:17 am

    #1631315

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2013 at 11:52 am

    #1631332

    OH really? I thought its in MB?

  • July 10, 2013 at 12:00 pm

    #1631338

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2013 at 1:23 pm

    #1631375

    thanks I got it fixed now

  • July 10, 2013 at 2:22 pm

    #1631405

    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.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply