SQL server slow situtation when system are busy

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 ?

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

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?

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.

"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 ?

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.

"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?

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.

And run SSMS locally on the server to avoid network latency required for transmitting rows from the server to SSMS.

"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 ?

Yes.

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 ?

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.

"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 ?

Performance monitor should give you the connection information for both (web and SQL Server).

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

any URL for that?

tks for both of you .