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

Posted by Jasminej under Sql Server on 7/26/2012 | Points: 10 | Views : 1214 | Status : [Member] | Replies : 6
we have SQL Server 2008 (Std edition) installed in my machine.
we have 2 GB of RAM in my machine
we have 100 of Databases on it.

In one of the database, I'm using a query which having 3 tables joined on it.

This query already been written in proper way!. Already reviewed by our seniors...

This query executing in "7 seconds" sometimes... and The same query takes more than "2.5 Minutes" to complete the executing... But, the final result is only 50 to 60 records

Each table have less than 1 million records on it...

1. what would be the reason to take these much of time variations...?
2. what are the ways to identify root cause...?

I already have discussed with my seniors...

and, I'm expecting more reasons/explanations regarding this! instead of reading/passing other Links ....

Experts! Kindly explain with monitoring scripts in SQL Server itself!

thanks in advance for your valuable reply and explanation!




Responses

Posted by: Pandians on: 7/26/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

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

Posted by: Pandians on: 7/26/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Jasminej on: 7/26/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Pandians on: 7/26/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Jasminej on: 7/26/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Jasminej on: 7/27/2012 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response