Performance related help/suggestions required... [Resolved]

It seems, Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes,

which may result in overall slowdown

Ok.

So, Your SQL Server machine has been configured memory limitation for SQL Server.

Minimum Memory for SQL Server is : 512 MB
Maximum Memory of SQL Server is : 1536 MB (1.5 GB)

So, You have configured 1.5 GB of Physical Memory for SQL Server out of 2 GB of total memory!

This may be the root cause:
1. In case your SQL Server was waiting for additional memory/releasing unused allocated memory for the current request!, So, the current request may be waiting for the same!

2. 2 GB of physical memory is very low on multi user environment server!

Is there any way to confirm whether Memory is the root cause for the slow running query/request ?

Yes. Check it out the following script!

Create Table #Counters

(

Counters	Varchar(1000),

Value		BigInt

)



Insert #Counters

Exec('DBCC MEMORYSTATUS')



Select (Value * 8)/1024. [Memory used (MB)] from #Counters where Counters ='Target'



Drop Table #Counters

Run the script and Check the result whenever your find slow running process/queries!

Value in "Memory used (MB)" column may nearest to 1536 MB (what you have configured MAX memory for SQL Server).

Meaning is Your SQL Server engine is waiting for additional memory to process/complete this operation and Already used all the allocated memory!

Kindly let me know, If the problem/root cause is correct!

So, It'll help others when facing this kind of Issues!

I'm sure it would positively help to solve your problem!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Hey

Kindly clarify the followings for my better understanding!

1. Is this performance Issue happening at any specific point of time (or) Its happening dynamically ?
2. How many users are connecting with the SQL Server ? (Through application (or) from sql server instance) ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

1. Performance issue happening dynamically(Not in specific time). suddenly it's working fast and completes within 7 to 8 seconds... suddenly it tooks more than 3 minutes.

2. The sql server machine being used by 10 to 15 members by the connection through some applications and direct sql server instances

I hope it helps you to provide me a perfect solution!

thank you

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Ok.

Your sever may have been configured MIN/MAX server memory for SQL Server.

Let's check on that!

Select name, value_in_use [MB] from sys.configurations where name in ('min server memory (MB)','max server memory (MB)')

Go

Kindly execute the script and give me the result! then we'll proceed further!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Yes. you are correct!

Memory has been configured for SQL Server in our machine.

I got the result for the script!

name				MB

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

min server memory (MB)	         512

max server memory (MB)	         1536

thank you :)

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

I'm happy to say that we identified the root cause and got solution using your scripts/suggestions.

Your way of explanation is/was really appreciable. Thank you :)

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator