SQL server slow situtation when system are busy

marrowyung
marrowyung used Ask the Experts™
on
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 ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
To your questions: Those options can help.

But: Without prior analysis it's not predicatable whether it works or not. Further more, there are normally many options in the existing system (SQL Server, IIS) to do performance tuning. And last, but not least: It could be also caused by an architectural mistake, which prohibits scaling. Then even the solutions from above won't work.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
ste5anSenior Developer

Commented:
There is no "more magic" switch.. You need to analyse to problem..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
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?
ste5anSenior Developer

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.
ste5anSenior Developer

Commented:
And run SSMS locally on the server to avoid network latency required for transmitting rows from the server to SSMS.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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 ?
ste5anSenior Developer

Commented:
Yes.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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ãoIT Engineer
Distinguished Expert 2017

Commented:
Performance monitor should give you the connection information for both (web and SQL Server).
marrowyungSenior Technical architecture (Data)

Author

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

any URL for that?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
The performance counter should immediately tell you if it's web or SQL Server.
You can search in the internet for web and SQL Server performance counters.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks for both of you .

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial