Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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 ?
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 ?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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 ?
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 ?
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?
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.
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 ?
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?
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 ?
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 ?
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 ?
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.
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 ?
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 ?
wait, once compare, which is the trigger figure we can see it is the web or SQL server problem ?
any URL for that?
any URL for that?
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.
You can search in the internet for web and SQL Server performance counters.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.