Restart needed daily for SQL Performance

Hi,
We have One application server on windows 2008 R2 with Domain Controller in Server-1 (S1), also SQL 2008 with SP4 in Two-node failover cluster(S2&S3). everyday morning SQL server performance is very slow, when we move the SQL to other node or restart active node, SQL performance becomes normal.

Regards
Dinagaran
dinagaran2000Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
bbaoConnect With a Mentor IT ConsultantCommented:
> Problem likely in IIS of application server.

you still need to prove this by monitoring the SQL server.

> 30 mts after starting business operation, Users are unable to get into IIS server itself.
> Lot of query is pending at IIS itself.
> When we restart DB Server everything gets normal

for me, it sounds like the root bottleneck is still at the SQL server as the pending queries on IIS might be caused by the SQL side.

as mentioned earlier, you need to find out what is going on underneath at the SQL side by checking SQL status, processes and memory usage. see below the hints for more details.

http://searchsqlserver.techtarget.com/feature/Built-in-tools-troubleshoot-SQL-Server-memory-usage
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this usually means that the sql server is using TOO much memory
reduce the max memory setting to a lower value (depends on the physical RAM available on the boxes), then it should be better.
0
 
bbaoConnect With a Mentor IT ConsultantCommented:
better further investigate which or which group of process(es) contributes more for the memory usage and optimise the related store procedures if possible.

regarding how to regularly restart the SQL server, you may use Windows Task Scheduler with NET STOP and NET START commands to shut down then start the SQL engine at a given time in morning.

FYI - Manage SQL Server Services from the
Command Line

https://technet.microsoft.com/en-us/magazine/dd421654.aspx
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the configuration of the Server?
And the SQL Server? Specially the min and max server memory configuration.
0
 
dinagaran2000Author Commented:
Server: IBM x3850X6 with 4x15 Core Xeon Processor/ 128 GB Memory
OS: Win2008 R2 Ent 64 bit.
SQL: 2008 . and service pack installed on top is SP4.

Min Server Mem: 1024KB, Max Server.Memory: 124 GB (126976 MB)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
124GB it's a lot. You only left 4GB to OS.
There's any other application installed on the server or it's only dedicated to the SQL Server engine?
0
 
dinagaran2000Author Commented:
Only SQL server. It is dedicated.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please set the max memory to 100GB

are you using the sql server tools on the server itself, or doing all "remotely" (as it should be)
using the ssms on the server itself also uses (most often lots of) memory ...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only SQL server. It is dedicated.
So not even SSAS, SSIS or SSRS, right?

You can run a DBCC MEMORYSTATUS when you note performance issues.
I can't tell you how much free memory you should let for the OS but 4GB really looks not enough for a 64bit system. 8GB or 16GB at minimum I shall say.
0
 
dinagaran2000Author Commented:
Ok. i will set 112 GB for SQL so 16GB will be used for OS. will update shortly.
0
 
dinagaran2000Author Commented:
Even after changing memory problem not solved.  Problem likely in IIS of application server. 30 mts after starting business operation, Users are unable to get into IIS server itself. Lot of query is pending at IIS itself. When we restart DB Server everything gets normal.
 
Suggest Fine tunning of IIS for best performance.

Please note that IIS contains 5 URLs with seperate application pool.
0
 
dinagaran2000Author Commented:
It is working well.
0
 
bbaoIT ConsultantCommented:
glad to know it is working now. what's the main reason you eventually found out?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.