SQL Server Performance

Impact of NULL values on Performance?

Discussion in 'Performance Tuning for DBAs' started by DBADave, Feb 26, 2004.

  1. DBADave New Member

    I am troubleshooting performance problems related to a third-party software package. In their database all columns are nullable. Do NULL values have a negative impact on performance?

    Thanks, Dave
  2. Chappy New Member

    The act of having nullable columns does not impact performance, but what *might* impact performance is how you are querying the tables with nullable columns. Also nullable values naturally have an impact on indices which may in turn impact performance if not properly thought out.

    Maybe you can supply more information about the sort of problems youre having, and with what operations?
  3. Luis Martin Moderator

    I don't think null values impact on performance.
    I work with third-party software, and all of them had indexs problems.
    I suggest to run profiler and to found larges queries and store procedures.
    Also wait for developers oppinions.



    Luis Martin
    Moderator
    SQL-Server-Performance.com
  4. DBADave New Member

    Most of the performance issues with NULL columns were pre SQL 7. From my notes the following issues existed.

    - Define non-nullable columns first. An internal offset table is organized to access non-nullable columns first
    - A field defined as NULL will lead to deferred updates
    - NULL values degrade SQL Server performance
    - Varchar fields defined as NOT NULL are internally made NULL

    I know the deferred updates issue was resolved with version 7, but I wasn't certain about the other issues.

    Chappy,

    The software package is a project and time tracking system. I'm still waiting for feedback from our users regarding how to recreate the problems. One of the issues involves taking more then one minute to connect to the application. I'm trying to rule-out the WAN.

    Other issues involve slow performance when trying to save data. The only error I have seen in the error log is 1204. The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

    I setup some alerts to notify me when this happens again, but it most likely won't occur until the end of the month, when activity is greatest. I printed several documents yesterday from this web site regarding performance monitoring. I'm reviewing those documents and will be setting up Perfmon counters and running SQL Profiler.

    Thanks, Dave
  5. satya Moderator

    Nulls are one of the most potentially confusing concepts in relational technology.
    Avoid nulls in columns that must participate in arithmetic logic (for example, money values).
    SQL Server checks to ensure that only valid dates and times are placed in columns defined as such. If the column can be unknown, it must be defined to be nullable because the only other option is to define a default for these columns from the valid domain of dates and times.

    Coming to 1204 error, you need to run the statement in batches if it is affecting large number of rows. Another option is to specify a hint like table or page lock so that the number of locks can be reduced. But the server should automatically escalate the row locks.

    BTW what is current setting for locks on SQL Server?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. DBADave New Member

    Satya,

    Locks are currently set to the default of 0. One of our options is to bump this up a bit, but I want to first trap the statement(s) causing the locking issue. Using Profiler, is it fairly easy to identify the statements causing this issue? I still need to determine what Events and Filters to apply in Profiler.

    Do you know of any good resources that further explain the use of NULLs? The only information I have is from a conversation I had with Kalen Delaney several years back regarding SQL 6.5. The information is most likely no longer valid.

    Dave
  7. satya Moderator

    As its a third party application and hard to modify any queries fired against database, the best bet would be to take help from PROFILER for slow running queries. As long as the database holds good/valid indexes on the search arguments and compatible h/w to tackle the load you should be fine.

    I don't have such references to link you about NULLS, and sometime back I referred an article by Craig S Mullin about Nulls.

    A generic articlehttp://builder.com.com/5100-6388-1050429.html about NULLs and I guess you might have come across most of the stuff.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. gaurav_bindlish New Member

    I would monitor the locks in the system when the performance degradation is observed. Form the conversation it seems that the problem is fairly consistent and reproduciblle. In this case getting a feel of what process is causing locking and then looking at the ecexution plan may suggest some new indexes on the underlying table.

    As far as the intial question is concerned, I don't think the perfomance of an operation is affected by NULLs.

    What is the version and service pack of SQL Server under consideration?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. FrankKalis Moderator

    To add to this, I agree that NULL should not have a impact on performance. Since ANSI invented this wheel RDBMS are truely optimized for dealing with NULLS. However it is smart to really think about making columns NULLABLE or not, because as Chappy mentioned you'll run into dealing with three-valued logic.



    Frank
    http://www.insidesql.de
    http://www.familienzirkus.de

Share This Page