RAM Required to run SQL Server + Web Application + SSIS packages
-
Production Server with following processes running:
- REST API hosted in IIS
- SQL Server with Data - Majorly for Time sheet and Other application data, which is having more than 14 Lac records and increasing rapidly every week.
- 6 SSIS Packages running almost every hour (4 Packages - Every 15 Min. , 1 Package Daily Once, 1 Package Every 5 mins.)
- 7 Task Schedulers running at the OS Level every 45 mins.
Currently, Server has RAM of 28 GB which is running one site on iis (REST API) + Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64)
Application runs smoothly, it also generates the reports in expected time. However on Fridays and Mondays it gets little slow due to lot of transactions by user (Approx. 1700+) on server.
Can anyone suggest,is there any way i can improve SQL Server Performance? by increasing size of RAM or any other way.
PS: I also, have Indexes built on the tables which is being hit multiple time, also has nolock in every query. and periodically am rebuilding the indexes as well.
- parth83.rawal - Monday, May 22, 2017 6:25 AM
Improving performance can probably be achieved by optimising every single query run against the database. 28GB RAM for a production SQL Server box seems very light, so it is very likely that increasing would help.
Using NOLOCK means that your queries may return uncommitted results, duplicate rows or may fail to return rows which should have been selected. Are your users happy to accept these risks?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Yes, the design of the pages are such that Nolock will have hardly have impact on the users. Anyhow currently i am not looking at the optimization of the queries as such. because before i enter that area i want to make sure that i have server with proper RAM size. If you can help me identify the RAM size which i can confirm with the customer will really help me.
- parth83.rawal - Monday, May 22, 2017 5:13 PM
You're not exactly listening. If you are constrained to only 28GB, then you DO need to make sure that your queries are as tight as a drum when it comes to performance. And I love it.... "will have hardly have impact on the users". If you didn't use WITH NOLOCK to make up for poor queries, you would never have an impact on users thanks to the problems it can cause.
I can't speak for anyone else but my personal opinion is that it's an absolute worst practice to put an application/web server on the same box as SQL Server.
As for the memory, 28GB is probably enough for such a small database as 14 LAC rows but, as you say, it's growing rapidly. I don't know how much memory your application takes for each open session but having 1700+ concurrent users may become a problem especially if that grows. Again, consider moving your application/web server to a separate box. Take SSIS with it. If this is going to become a "bread'n'butter" system for the company you work for, seriously consider that and upgrade to at least 64GB in the short term.
As for your queries currently "running smoothly and generating reports in the expected time, consider this. With only 14 LAC rows, your reports should be almost instantaneous. If they are not, then you very likely have a serious problem brewing on the back burner regardless of what your current expected times are.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, May 22, 2017 10:09 PM
Agree with Jeff on Moving SQL Server \Application to separate box.
Check your current RAM usage ,are you seeing any bottleneck ?
-
1700+ user transactions - what does it mean?
Ped day, per hour, simultaneous?
_____________
Code for TallyGenerator -
What's the storage subsystem like? Where are the database files themselves?
Thomas Rushton
blog: https://thelonedba.wordpress.com -
Have you looked at the wait statistics to understand what is causing things to run slower? It may not be memory. It could be your disk. It could be your queries. It could be indexes. It could be statistics or any number of other things. Without a good assessment of what is causing things to slow down, focusing on a single solution, more memory, won't get the job done if the issue is somewhere else. I'd suggest capturing the wait statistics and a locking and blocking report as well as query metrics so that you can identify precisely why things are slow, then address those issues directly.
And, on large scale systems, SQL Server is a very bad kindergartener, it doesn't play well with others. Get the web site on another server so that SQL Server isn't contending with resources.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Tuesday, May 23, 2017 6:54 AM
And so that any vulnerabilities in the web site don't automatically expose the entire SQL instance as well.
p.s. Nolock can cause incorrect results. Are your users aware that their reports may be in error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Tuesday, May 23, 2017 7:00 AM
And why bother with Nolock on all the queries. If you really don't mind getting dirty data, just use read uncommitted for the connections. It works the same way as nolock except, when your business people find out that they're getting incorrect data, you can quickly and easily fix it without having to rewrite every query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Another thought - what is your max memory for your SQL instance set to?
SSIS operates on different memory than the SQL Instance as would the web instance. You leave that running long term and your SQL instance will try to eat up all of the memory slowing down all of your other systems.The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I am totally Overwhelmed by the responses i have got and things i have learn from you guys.
To answer few of the Questions:
1. Yes, I can understand that if i write poor queries and expect no lock with improve the performance. I am fundamentally wrong
2. I have attached my Wait Results
3. Actually, We have suggested for 2 application server and 1 database server with load balance. But customer does not want to invest in such a huge hardware cost at the moment. And I acknowledge that it is us who have to convince customer about the hardware sizing. after so much effort they have agreed to increase RAM size and for it i have to submit a report which will they see and take decisions.Hence i have posted the question here.
4. I will go ahead and check for 64 GB RAM at the server level.Request all of you to see the image and see if you can further suggest something.
- VastSQL - Tuesday, May 23, 2017 12:58 AM
Even i agree with jeff 🙂 These timesheet have direct impact on billing to customer and salary to the employee
RAM usage seems fine to me, however will post a screenshot on Friday /Monday where usage is at it's peak. - bmg002 - Tuesday, May 23, 2017 2:02 PM
Another thought - what is your max memory for your SQL instance set to? - Deault Max. 2147483647
SSIS operates on different memory than the SQL Instance as would the web instance. You leave that running long term and your SQL instance will try to eat up all of the memory slowing down all of your other systems. -- I think we do not have any option, other than assigning processors
-
Default max - means "take everything".
You need to define how much memory you need for OS, Web server, SSIS and allow the remaining memory for SQL Server to take.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply