Avatar of marrowyung
marrowyung
 asked on

SQL server slow situtation when system are busy

Dear all,

when SQL server is slow when call from web, mainly insert/update/delete operation, in a super busy situation, web system can hangs and no response.

any way from your point of view to solve this ?

someone suggest tibco active space, which site between database and web tier:

http://www.tibco.com/products/automation/in-memory-computing/in-memory-data-grid/activespaces-enterprise-edition

any other think you guys will do to handle this ?

can in memory database engine in SQL 2012/2014/2016 help on this ?
Microsoft SQL ServerDatabases

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
oh, this is the one :):)

sorry, sometime EE has some special effect when searching the group this question belongs to and this searching effect can kill my browser session and I don't know it post a question FOR ME today. usually it should not !

"Further more, there are normally many options in the existing system (SQL Server, IIS) to do performance tuning."

can you share what it is?

". Then even the solutions from above won't work. "

sure! the reason I ask for this as it really success for a company I work with before.

from my point of view, combine SPs together into single SP can scale out transaction per sec, combining views in to a single view can do this too. should it related too ?
ste5an

There is no "more magic" switch.. You need to analyse to problem..
marrowyung

ASKER
yeah, I knew, but from share prospective, I think you can share what you done for that and I think in memory DB/table is one of the way ?

we consider THAT technology before When we are using SQL server 2008/2008 R2, WOa, no in memory DB and tables, right?

we can consider that I think but before that I think group SP/views does help as it make the log even smaller and the many small write/update operation can be finish in one sP call, right?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ste5an

Without knowing your actual problem: in-mem tables may work. But this SQL Server 2012+.

On the otherhand: It's not possible to recommend a best practice without analysis.

The only advice: Check the memory consumption of your SQL Server. How big are your databases? Do they fit into memory? Throwing hardware (memory) at it, is normally pretty cheap.
Also how is tempdb jusage? How many tempdb files do you have? Depending on the size of the databases even here is throwing hardware (SSD) at problem a possible cheap solution.
marrowyung

ASKER
"Without knowing your actual problem: in-mem tables may work. But this SQL Server 2012+."

yes, you are right !

"The only advice: Check the memory consumption of your SQL Server. How big are your databases? Do they fit into memory? Throwing hardware (memory) at it, is normally pretty cheap."

the first way to think should be the one don't need money first.

"Also how is tempdb jusage? How many tempdb files do you have? Depending on the size of the databases even here is throwing hardware (SSD) at problem a possible cheap solution. "

yeah, tried that in my ex employer, but that one seems incorrect as tempdb keep have object put in, and therefore a write operation, SSD is not good for write but read, agree?

So for OLAP solution which keep write little information over and over again, SSD seems not good.

I agree that tempDB on SSD can be good but we also need to think about resize that also with correct number of tempDB file, preferably each tempdb mdf file has a dedicated hard disk channel.

BTW, hardware like SAN and new hardware is not that cheap, SAN is a very expensive thing.

so for developer, I think I should suggest combine SP and views to that transaction log generate by many SP call can be smaller by one SP call, agree? this can also improve the write speed of the whole application ?
Vitor Montalvão

when SQL server is slow when call from web, mainly insert/update/delete operation, in a super busy situation, web system can hangs and no response.
SQL Server is only slow when called from web? If so, it's more possible to be an issue in the website rather than in the database.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
"SQL Server is only slow when called from web?"

all application is on the web and it called SQL server, yes!

"If so, it's more possible to be an issue in the website rather than in the database. "

how can we make sure!

in one of the gambling company here use a SW memory cache tier between web and DB, and it solve it!

This memory tier will write all data to SQL server as a schedule. not keep doing this to SQL server.

but this is all done BEFORE they upgrade all to SQL server 2012 and 2014. I am not sure if this new company I just join, once upgrade to SQL server 2014, can make use of the in memory database table, agree?
Vitor Montalvão

how can we make sure!
Run the same query directly in SSMS and see if it still have performance issues. If not then take the SQL Server out of the equation.
ste5an

And run SSMS locally on the server to avoid network latency required for transmitting rows from the server to SSMS.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
marrowyung

ASKER
"for transmitting rows from the server to SSMS. "

sorry, you mean transmitting from web serve to server and see the diff between running the query locally on the SQL Server itself and compare ?
ste5an

Yes.
marrowyung

ASKER
how about if both web and DB is slow ?

e-commerce world always has a problem when the whole world connect to the web nearly all at the same time, very common here and I don't know how to answer!

this is the situation.

experienced this before ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

Generally is the web server that is usually slow and for that you may need to add more servers to process more requests at same time. How many web servers do you have?
You can also count the number of requests the web has and compare it with the number of sessions running in the database so you'll know if SQL Server is able to handle all the requests performed by the web server.
marrowyung

ASKER
"Generally is the web server that is usually slow and for that you may need to add more servers to process more requests at same time. How many web servers do you have?"

3-4

"You can also count the number of requests the web has and compare it with the number of sessions running in the database "

on the web server, it is by performance monitor of windows?
on DB, it is by SQL profiler?

any max number of session for SQL server, I don't think we have, right ?
Vitor Montalvão

Performance monitor should give you the connection information for both (web and SQL Server).
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
marrowyung

ASKER
wait, once compare, which is the trigger figure we can see it is the web or SQL server problem ?

any URL for that?
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
tks for both of you .