none
Dirty Reads in Procedure

    Question

  • Hi All,

    Please Let me know which one is better in terms of performance:-

    1.If I am doing dirty Read many time in one procedure 

    for example:- select column1 from table1 (nolock)where id = 5

    2. Doing dirty read at one time and fetch all column which is needed and put into table variable

    for Example:- select column1,column2 from table1 (nolock)where id = 5

    and put all this in table variable 

    which one is perform better?

    I think 1st option is better because we are doing dirty read and nothing to impact searching data many time 

    and we have shortage of ram also.

    Thanks,

    Saturday, April 12, 2014 11:45 AM

Answers

  • My main question is we have shortage of memory after then i m putting the data in table variable then some of memory is occupied by table variable but same thing i can do without putting the data in variable then if i am hitting one table many time for different column then whats issue.

    Table variable data is not necessarily in memory.  Like all table data, pages may be on disk, in memory, or both.  However, data must be in memory before SQL Server can use it so less frequently used pages are removed from cache to make room for immediate memory needs (e.g. reading a page from disk that is not already in memory).

    After you run an INSERT...SELECT query to load the table variable you could have 2 copies of the data in memory - one in the table variable and another in the permanent table.  The implication from a performance perspective is that if you don't have enough RAM to hold both copies, whichever copy is used most frequently will stay in memory while the other is removed from cache.  You will observe more I/O and performance degradation if both copies are frequently used.

    Performance and concurrency are closely related.  Rather than ask about which of the suggested approaches is best, I suggest you provide more information about the problem stored procedure.  The fact that you are reading the same row in the stored procedure, but with different column lists, seems a bit odd but I don't know your application.  Perhaps the correct answer is neither the table variable nor the NOLOCK hint.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, April 19, 2014 1:10 PM

All replies

  • Dirty read is not recomended when you are working with mission critical transaction as it returns rows that are uncommited. It can return inconsistent data.

    However it is better to read one time inconsistency data rather than multiple times. And what is the use of reading multiple time ?


    Regards, RSingh

    Saturday, April 12, 2014 11:59 AM
  • I concur with Singh and would advise not to use NOLOCK query hint you might get incorrect value,If anyhow you want to use use at your own risk.For me both options seems incorrect

    A simple way to not use NOLOCK and still not get blocked is to use Optimistic Isolation level preferably Read committed snapshot  Isolation level.Main drawaback is this isolation level heavily utilities tempdb an your tempdb needs to be on separate drive with drive having optimum performance.

    Understanding RCSI


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, April 12, 2014 12:23 PM
  • Best is not to use dirty reads, unless you know what you are doing!

    And I can tell from your question that you don't. Normally, I would read both columns in the same query, but depending on available indexes, there could be situations where it is better to read them one by one. NOLOCK has very little do with it.

    And that is the answer: you should not use NOLOCK. Performance is one thing, but correctness is a lot more important.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 12, 2014 12:23 PM
  • Take a look at two transaction isolation level like SNAPSHOT IL for example, read the link

    http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Saturday, April 12, 2014 3:14 PM
  • As suggested NOLOCK is not good practice.

    Nonetheless if you decide to use it, option 2 is better.

    > it returns rows that are uncommited

    Are you worried about user A conflicting with user B?

    Have you noticed any performance improvement?

    I have found NOLOCK useful in certain applications such as getting midday count of website activities. In such an application it does not matter if 1001 (dirty) returned incorrectly instead of 1000.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, April 13, 2014 12:18 PM
  • You don't say why you are using NOLOCK at all, is it because you know there is blocking or just for performance reasons?

    Whether or not you use NOLOCK, the answer to whether you want to select just once or many times - depends on many things.

    But if several of the selects result in table scans, you probably want to do one select, not several.

    NOLOCK has very little to do with it.

    Josh

    Sunday, April 13, 2014 7:27 PM
  • Company has no issue with inconsistent data  but ,they don't want to degrade the performance .

    "We don't care if we get the wrong data, as long as we get it fast!".

    My answer to your question is: do what is simplest for coding. Which you choose will not matter for performance, at least not with the example you have posted. Your question is a typical example of asking the wrong thing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 18, 2014 6:39 PM
  • My main question is we have shortage of memory after then i m putting the data in table variable then some of memory is occupied by table variable but same thing i can do without putting the data in variable then if i am hitting one table many time for different column then whats issue.

    Table variable data is not necessarily in memory.  Like all table data, pages may be on disk, in memory, or both.  However, data must be in memory before SQL Server can use it so less frequently used pages are removed from cache to make room for immediate memory needs (e.g. reading a page from disk that is not already in memory).

    After you run an INSERT...SELECT query to load the table variable you could have 2 copies of the data in memory - one in the table variable and another in the permanent table.  The implication from a performance perspective is that if you don't have enough RAM to hold both copies, whichever copy is used most frequently will stay in memory while the other is removed from cache.  You will observe more I/O and performance degradation if both copies are frequently used.

    Performance and concurrency are closely related.  Rather than ask about which of the suggested approaches is best, I suggest you provide more information about the problem stored procedure.  The fact that you are reading the same row in the stored procedure, but with different column lists, seems a bit odd but I don't know your application.  Perhaps the correct answer is neither the table variable nor the NOLOCK hint.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, April 19, 2014 1:10 PM