November 29, 2013 at 9:25 am
Hi,
Our ERP is very badly design... We have long running transactions that block simple SELECT..
If I activate the RCSI on the database reads will never be blocked... (i don't mind they getting wrong results as long as they don't block...).
Is ALTER DATABASE [Database] SET READ_COMMITTED_SNAPSHOT ON; enough or do I have to change the ERP connection to use a transaction isolation level by default?
Thanks,
Pedro
December 1, 2013 at 6:35 am
RSCI will increase a lot the usage of the tempdb. You should have ready tempdb.
Did you make indexes analysis? Aren't there a lot of missing indexes?
How about queries, are they mostly covered by indexes? .. and many other aspects ...
In this post you'll see lots of reasons for bad performing at sql server databases.
http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
December 2, 2013 at 1:04 am
IgorMi (12/1/2013)
RSCI will increase a lot the usage of the tempdb. You should have ready tempdb.Did you make indexes analysis? Aren't there a lot of missing indexes?
How about queries, are they mostly covered by indexes? .. and many other aspects ...
In this post you'll see lots of reasons for bad performing at sql server databases.
http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx
Regards
IgorMi
Tempdb is not a problem... It's on its own RAID10.
The problem aren't missing indexes, the problem is the application that begins does the following:
// somewhere in the VB6... (yep VB6, not .NET)
1. Begins a transaction
2. Does some queries to get some data
3. Processes data from 1 to see accounting data to analyse and process
4. Gets the accounting data with ROWLOCK, XLOCK...
BADABUM!!! no one can read this data anymore until the transaction is released... (at least this is what happening right now, some users just want to get the accounts list and it's locked..., in SSMS the query returns 5 rows and gets blocked, the table has 14 rows...)
5. Takes a lot of time to process the data... locking it for more than 5 minutes..
6. Commits the transaction
This is why I want to check if RCSI avoids this.. I made a simple test with two SSMS windows and it worked...
I'll check it today in the APP if there locking problem gets reduced...
Of course if two people start the accounting process one will still be locked since it's SELECT WITH(ROWLOCK,XLOCK) behaves like a writer, and writers get blocked by writers in RCSI...
Pedro
PS: We can't change the app code right now and "just like that"... I just want our client no to be locked as many times as he gets currently...
Developers will make the code change.... but I'll make a final review to get rid of those XLOCK,ROWLOCK.. Apparently it's been on the app for ages...
December 2, 2013 at 6:30 am
PiMané (12/2/2013)
IgorMi (12/1/2013)
RSCI will increase a lot the usage of the tempdb. You should have ready tempdb.Did you make indexes analysis? Aren't there a lot of missing indexes?
How about queries, are they mostly covered by indexes? .. and many other aspects ...
In this post you'll see lots of reasons for bad performing at sql server databases.
http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx
Regards
IgorMi
Tempdb is not a problem... It's on its own RAID10.
The problem aren't missing indexes, the problem is the application that begins does the following:
// somewhere in the VB6... (yep VB6, not .NET)
1. Begins a transaction
2. Does some queries to get some data
3. Processes data from 1 to see accounting data to analyse and process
4. Gets the accounting data with ROWLOCK, XLOCK...
BADABUM!!! no one can read this data anymore until the transaction is released... (at least this is what happening right now, some users just want to get the accounts list and it's locked..., in SSMS the query returns 5 rows and gets blocked, the table has 14 rows...)
5. Takes a lot of time to process the data... locking it for more than 5 minutes..
6. Commits the transaction
This is why I want to check if RCSI avoids this.. I made a simple test with two SSMS windows and it worked...
I'll check it today in the APP if there locking problem gets reduced...
Of course if two people start the accounting process one will still be locked since it's SELECT WITH(ROWLOCK,XLOCK) behaves like a writer, and writers get blocked by writers in RCSI...
Pedro
PS: We can't change the app code right now and "just like that"... I just want our client no to be locked as many times as he gets currently...
Developers will make the code change.... but I'll make a final review to get rid of those XLOCK,ROWLOCK.. Apparently it's been on the app for ages...
Hi Pedro,
I'm glad it worked well with a simple scenario.
I meant the tempdb to be ready in case of multi-user usage of the database. RAID 10 is very good for tempdbs.
Do you have more files in your tempdb? It's essential... see about for e.g. here: https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/, and this link is useful http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
Capacity planing is also very important - have the tempdb files with appropriate sizes and autogrowths.
It'll be good sharing the experience from your changes.
Thanks and regards
IgorMi
Igor Micev,My blog: www.igormicev.com
December 2, 2013 at 7:10 am
IgorMi (12/2/2013)
PiMané (12/2/2013)
IgorMi (12/1/2013)
RSCI will increase a lot the usage of the tempdb. You should have ready tempdb.Did you make indexes analysis? Aren't there a lot of missing indexes?
How about queries, are they mostly covered by indexes? .. and many other aspects ...
In this post you'll see lots of reasons for bad performing at sql server databases.
http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx
Regards
IgorMi
Tempdb is not a problem... It's on its own RAID10.
The problem aren't missing indexes, the problem is the application that begins does the following:
// somewhere in the VB6... (yep VB6, not .NET)
1. Begins a transaction
2. Does some queries to get some data
3. Processes data from 1 to see accounting data to analyse and process
4. Gets the accounting data with ROWLOCK, XLOCK...
BADABUM!!! no one can read this data anymore until the transaction is released... (at least this is what happening right now, some users just want to get the accounts list and it's locked..., in SSMS the query returns 5 rows and gets blocked, the table has 14 rows...)
5. Takes a lot of time to process the data... locking it for more than 5 minutes..
6. Commits the transaction
This is why I want to check if RCSI avoids this.. I made a simple test with two SSMS windows and it worked...
I'll check it today in the APP if there locking problem gets reduced...
Of course if two people start the accounting process one will still be locked since it's SELECT WITH(ROWLOCK,XLOCK) behaves like a writer, and writers get blocked by writers in RCSI...
Pedro
PS: We can't change the app code right now and "just like that"... I just want our client no to be locked as many times as he gets currently...
Developers will make the code change.... but I'll make a final review to get rid of those XLOCK,ROWLOCK.. Apparently it's been on the app for ages...
Hi Pedro,
I'm glad it worked well with a simple scenario.
I meant the tempdb to be ready in case of multi-user usage of the database. RAID 10 is very good for tempdbs.
Do you have more files in your tempdb? It's essential... see about for e.g. here: https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/, and this link is useful http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
Capacity planing is also very important - have the tempdb files with appropriate sizes and autogrowths.
It'll be good sharing the experience from your changes.
Thanks and regards
IgorMi
Tempdb has 4 files, its a 2 socket 6 core machine, and for now 4 files work just fine.
Since the 60GB raid10 is just for the tempdb each file has 12GB with no growth, just the log.
Pedro
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply