December 7, 2011 at 10:11 am
Background:
We have a 3rd party ERP system (Amtech), database is SQL Server 2008 R2. Our ERP vendor
99.1% of the time I use SSMS Query Editor to execute SELECT statements to (1) lookup data in response to information requests and (2) develop queries for Access reports. This covers 99.9% of my usage. Occasionally I will execute an UPDATE or DELETE statement. Occasionally, while the results are 'sitting there' someone in the ERP system is doing work that will update the records locked by my SELECT statement. If they wait 'a minute or more' they contact me that their "PC has frozen". I execute another SELECT statement to release the locks. I don't know how often my work 'freezes' their processing, could be for seconds or more, and they don't contact me. I would prefer they are not impacted at all.
Googling and reading information I have discovered - I think - SET TRANSACTION ISOLATION LEVEL SNAPSHOT will eliminate this problem. It is not critical that the results take into account any data changes in progress.
I need more information before (1) using this functionality and (2) if using it, how do I implement it...
1. Is this setting at the database level (all users) or my connection to the database?
2. How do I see the current setting? I looked in Query Options > Advanced > SET TRANSACTION LEVEL and SNAPSHOT is not in the drop down list. Either I cannot set it here, or the ALLOW_SNAPSHOT_ISOLATION is OFF, therefore, not included.
3. Assuming this is OFF ... Since the ERP Vendor set up our SQL Server is there a reason it is OFF that I should not change it? Is this a question for our vendor?
4. If it is OFF and it is OK to turn it ON, what steps do I take to turn it on?
* I hope I've covered most or all the questions I need to ask.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
December 7, 2011 at 10:40 am
Have you considered Read Committed Snapshot? It just needs turning on at a database level. Snapshot needs enabling at a database level and then the queries have to explicitly select it (SET TRANSACTION ISOLATION LEVEL SNAPSHOT)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2011 at 11:10 am
Thanks for the suggestion Gail.
Read up on Read Committed Snapshot and it sounds good.
Since I'm working with our LIVE database I want to be sure all the bases are covered to avoid messing up anything...
I contacted our vendor asking them if there is any reason we can't set it to Read committed Snapshot.
Thanks again for your help.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
December 12, 2011 at 7:19 am
I inquired with our ERP system vendor about changing database level setting and they do NOT recommend the change.
Of course my mind wants to understand. Not knowing 'what goes on under the hood' - both the SQL Server database setting and how their programming handles data changes I don't know if your recommendation is valid IF the programming does a good job of insuring data integrity or, in general, it is not good practice to set the database level to SNAPSHOT in an environment like ours. Given that we occasionally encounter data errors - a record is NOT updated although related records show that it should be* - I am suspicious of coding 'shortcuts'.
* Example: Transactions indicate an inventory quantity change, but, the "Quantity On Hand" field in another record was not updated. We have found about a dozen such records over the past 4 years. I have wondered if the issue is a SQL Server 'hiccup' or the code is (pardon my directness) ... sloppy.
So, I believe the solution for me when I am simply looking up data is to enter:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
when I open a query window/tab.
One question - what would happen if I needed to update data, i.e. UPDATE ... SET Quantity_On_Hand = ...
and didn't change the ISOLATION LEVEL?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
December 12, 2011 at 7:44 am
Ms Dynamics Nav doesn't save the totals, ever. It always recalc on demand.
On lower transaction systems that solution works pretty good. The advantage is that the qty can never be wrong.
On An amazon level I don't know how well this would work. And saving the value (assuming no programming error) would likely be my preferred option.
December 12, 2011 at 8:13 am
Yes! Standard Rule Of Thumb (IMO) for Quantity On Hand (QOH) "Don't store totals, always calculate on demand." And, you're right that the consideration is response time, i.e. when the number of records becomes very large.
I have developed 2 ERP systems in my career and figured out a solution for the 'large number of records issue'. It is standard practice to periodically perform a count of inventory to verify the reported Quantity is in fact correct. The result of this action is a TICKET document, which translated, in my systems, to a table (INVT_TICKETS). Since the TICKET is the valid quantity the coding finds the last TICKET(s) and calculates using all transactions dated after the TICKET date to arrive at the QOH.
Alas, unfortunately, as I mentioned, our ERP system is developed and maintained by a 3rd party. (1) They have a table that includes a QOH field. (2) One must calculate balances from the beginning because their version of a TICKET is a transaction record, like any other transaction record, it indicates the change Quantity.
Thanks for your comment - it would help anyone developing an 'Inventory' solution to consider possible solutions.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply