September 8, 2015 at 12:23 pm
Hello,
We have some issues in our sql server 2012 SP1 system. when trying to move a big folders from application which is a document management application , the system hang and have to restart the application servers again. By checking the database, it has high CPU/memory wait and 5 blocked sessions. CPU and memory usage is not high. memory is 50% and CPU is 20-30%. Our environment is sql server 2012 Se Sp1 64-bit on WIndows server 2008 R2. CPU 8, memory 16G. Virtual Machine. and also a monitoring system keeps sending high CPU singal wait alert email.
How to resolve this issue?
Thanks for your help!
September 8, 2015 at 3:25 pm
ying.wang (9/8/2015)
Hello,We have some issues in our sql server 2012 SP1 system. when trying to move a big folders from application which is a document management application , the system hang and have to restart the application servers again. By checking the database, it has high CPU/memory wait and 5 blocked sessions. CPU and memory usage is not high. memory is 50% and CPU is 20-30%. Our environment is sql server 2012 Se Sp1 64-bit on WIndows server 2008 R2. CPU 8, memory 16G. Virtual Machine. and also a monitoring system keeps sending high CPU singal wait alert email.
How to resolve this issue?
Thanks for your help!
There is nowhere near enough details posted here. There are so few details posted I don't even know where to start to help you provide enough information to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2015 at 4:19 pm
I especially don't understand the "move big folders from the application" part of the problem. That sounds like file manipulation which is not usually something done within the SQL Server database. You'll have to supply some more details.
As to the blocked processes, you identify the process that is doing the blocking and understand what resource it is holding that's causing the blocks. Understand why it's holding that resource. Look to see what you can do to reduce the time it's holding that resource.
Without seeing a lot more detail, that's about as much a response as I can provide.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2015 at 9:11 am
Hello,
The documents are stored on application side not database server but some meta data is stored in database. when moving a big folder like 4G forlder in the application, some data operations occur in database. From what I have found using a database performance monitoring tool, there are locks, blocked sessions which causes resource issues and cause application hang. the wait type is major memory/cpu waits. But memory/cpu usage are not high at all(memory is 55% and CPU is 30% at most). From the performance monitoring tool, it frequently shows the database has CPU pressure which is high critital signal waits percentage(sometimes 95%).
I am not sure if it is application bug which causes issue or database server or application server capacity issue. we have already monitored the application servers resource and did not find any issues. Do I need to add more CPU or Memory or change to some faster CPU or what?
the blocker is this session with the following sql:
(@P1 bigint)
SELECT OwnerID,
ParentID,
DataID,
Name,
OriginOwnerID,
OriginDataID,
UserID,
GroupID,
UPermissions,
GPermissions,
WPermissions,
SPermissions,
ACLCount,
PermID,
Deleted,
DataType,
Reserved,
SubType,
CreatedBy
FROM DTreeCore
WHERE DataID=@P1;
I donot see any issue in this sql but it seems it blocked more than 50 sessions and causes dead lock and resource issue.
By the way, we can reproduce the issue in test/dev environment.
Thanks for your help.
September 9, 2015 at 9:36 am
Hello,
we have gotten feedback from application vendor saying that it is not a application bug but it is depened on the server capacity and different environment. Some copmanies can handle 500M foler move only but some companies can handle very big folder moving.
Thanks
September 9, 2015 at 9:57 am
ying.wang (9/9/2015)
Hello,we have gotten feedback from application vendor saying that it is not a application bug but it is depened on the server capacity and different environment. Some copmanies can handle 500M foler move only but some companies can handle very big folder moving.
Thanks
Then get with your vendor and figure out what you need to do. It still isn't at all clear what you mean by moving folders or how that affects sql server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 10:17 am
It actually sounds like the process takes out locks in the database while the files move. I would classify that as a bug in the application code.
However, I agree. You need to talk with the vendor. Fair warning though, a shocking number of vendors are really horrible at SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2015 at 10:34 am
Grant Fritchey (9/9/2015)
Fair warning though, a shocking number of vendors are really horrible at SQL Server.
Gosh Grant the way you say that makes me think you have the opinion that some of then aren't horrible at SQL Server. I would like to meet one of them someday. They would make a nice addition to my collection of rarely seen items. :-D:-D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 11:06 am
Sean Lange (9/9/2015)
Grant Fritchey (9/9/2015)
Fair warning though, a shocking number of vendors are really horrible at SQL Server.Gosh Grant the way you say that makes me think you have the opinion that some of then aren't horrible at SQL Server. I would like to meet one of them someday. They would make a nice addition to my collection of rarely seen items. :-D:-D
Fair point. I guess this would be something of a unicorn hunt.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply