Solved

Restart needed daily for SQL Performance

Posted on 2015-02-23
13
18 Views
Last Modified: 2016-02-22
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
0
Comment
Question by:dinagaran2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40627539
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
 
LVL 37

Assisted Solution

by:bbao
bbao earned 500 total points
ID: 40627581
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40627952
What's the configuration of the Server?
And the SQL Server? Specially the min and max server memory configuration.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dinagaran2000
ID: 40628554
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40628563
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
 

Author Comment

by:dinagaran2000
ID: 40630185
Only SQL server. It is dedicated.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40630231
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40630322
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
 

Author Comment

by:dinagaran2000
ID: 40634942
Ok. i will set 112 GB for SQL so 16GB will be used for OS. will update shortly.
0
 

Author Comment

by:dinagaran2000
ID: 40652627
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
 
LVL 37

Accepted Solution

by:
bbao earned 500 total points
ID: 40653089
> 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
 

Author Closing Comment

by:dinagaran2000
ID: 41475525
It is working well.
0
 
LVL 37

Expert Comment

by:bbao
ID: 41476459
glad to know it is working now. what's the main reason you eventually found out?
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question