Adding NOT NULL columns to existing table

Adding a column with NOT NULL feature to an existing table requires a default value to be inserted.  If the table is large, the operation can take more time and resources.  Importantly, this operation requires exclusive table lock. Before SQL Server 2005, this operation will prevent other users from accessing the table (unless NOLOCK query hint is specified or the session is in READ_UNCOMMITTED mode) until all the data pages were updated with the default value.

With SQL Server 2005,  two (some call it one and a half!)  new transaction isolation levels were introduced which allows the snapshot of the table to be copied into tempdb and accessed by other users. Users using READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation levels can access the data as at the column does not exists. However the cost involved with the operation is still high and It too a reason for page splits.

People generally prefer not to issue NOT NULL clause with the ADD COLUMN statement.  Adding a column with null value and then updating the table with the default value, chunk by chunk is one of the mechanism employed to prevent the issue to some extend.  This method will reduce the locks and increase the    availability of the table. However, it will not prevent the page splits.

With the introduction of SQL Server 2012, we do not have to worry much about it.

Page Splits Part 2: Identifying Page splits

Even though we know theoretically how page split occurs when the database system is on live identifying page splits is a different story altogether. We have little control over the data inserted or updated.  (There are mechanisms to control the clustered key inserted, but it is difficult to control the update)

There is no event in profiler to identify the page split.

Page splits could be identified through Performance Monitor (It is being called with different names:  Performance monitor, System Monitor and Performance – All refers to the same tool named PerfMon.exe ) It is part of control panel and placed under administrative tools.

Page Splits

Introduction

I thought of writing a series about page splits. Initially I started writing about it and it slowly became a huge article. I was in two minds on whether I should write a series, of blogs or go for a big article. Finally, I have made up my mind for the first option and here is the first part of the five part series.


What is page Split?

In SQL Server data is stored into a page. A data page is 8 kb of size. It means when the rows are inserted they need to be inserted into a page. There is an exception when large blob data is inserted sql server can insert the data separately into another page and keep only the pointer (For more details refer: row overflow). Apart from that, a row should fit into the page and cannot split into multiple pages.

The decision on where the data should be inserted is decided by the clustered key. If the table does not have a clustered key, (which is called a "heap") the data inserted always be inserted at the end. On clustered tables the data is logically stored in clustered key order. (I'll discuss later on why the word "logically" is used here. But for the moment, it is safe to assume that the pages are in clustered key order) When a row need to be inserted or updated but it cannot fit into the balance space within the current page, The data is inserted into a new page and is called page split. There are two types of page splits occur in SQL Server. Firstly, the natural growth of data that periodically requires new pages, irrespective of whether it is a heap of clustered table. Secondly, a data insert or update operation happens within an existing page that the page needs to move some of the existing data in order to accommodate the inserted /updated data. Even though technically the first operation too is page split, as the content of the page dos not split in that type of operation. The second operation also called as mid-page split, which is a concern of DBAs.
There are primarily two reasons for mid-page split to occur:

• Data inserts happen not in clustered key order
• Update of variable length columns

Let's analyze each scenario:

MVP Again!

Thanks to those who worked hard to get this again for me.  How can I forget the friends encouraged me.
When looking back,  I need to admit that I can still say that there are many things I can do better and I hope to do it better this time. 

SQL Server database professionals needed

Hi readers,
If you are from Sri Lanka, I need to share this info with you. There are organizations searching for SQL Server database administrators, database developers and similar positions. As of now we do not have a place to share the resume.  But if you are interested in similar positions. Please let me know. I can share your info with others.  Offers include working abroad.

Happy New year!

Will The Performance Improve If The Database Size is Smaller?

Few days back, when I was speaking at SSLUG (SQL Server Sri Lanka User Group) meeting on "managing VLDB" (VLDB stands for Very Large Data Bases) there was a question came on improving the performance of VLDB. There was a participant suggested that by shrinking the database we could improve the performance. I noted it then and answered later, but it could be useful for many if I share in the blog.  So I am sharing it here for the benefit of many.

Argument:  Large databases may take longer time to execute a query because scan operations may have to read more pages.

Answer: If you have an operation which scans the entire database (generally a maintenance or troubleshooting related operation) yes you may have to scan more pages.  However when you run a query against tables, you will touch only the pages allocated to the table and indexes.  If the table is clustered, each page will have a pointer to the next page and all read operations happen based on these pointers.  The performance is better, if you have read forward only. when you have free pages at the end of the database, it will not be touched at all. But if you have free pages  in between, shrink operation moves the pages at the end to the free space available. It will make the physical order different from logical order and forces read backward. This is the major concern for performance.

Argument: Shrink operations reduces the pages occupied by an object

Answer: NO. Please note this point:  Shrink operation does not remove the free space available within a page. Shrink operation does not compress the data either. The free space within a page can occur due to many reasons.

1. If the clustered index was created with a fill factor  between 1-99 generally you expect some space within a page when the index is created/rebuilt.
2. When page split happens due to updates there is some space left in old page. 
3. When a row within a page is deleted their is free space created. (Okay, SQL Server does not initialize every bit of deleted row to 0. But rather the entry is removed from index and row mark within a page. SQL Server treats that area as free space)

Shrink operations work at page level and not at object level or row level.  If there is a free page in the middle and last non-free page will be moved there and that last page will be marked as free. Then it changes the pointers in the pages accordingly At the end, the free pages at the end could truncated if the options are set properly. The internal fragmentation (the fragmentation happens within a page due to page splits etc.)

Then what is the mechanism to remove the fragmentation? The answer is to rebuild the clustered index. As the data is ordered in the clustered index key, it can move the data pages according to the clustered key order.  This is why rebuilding a clustered index is a resource consuming operation.
 




   

SQL Server OLEDB Deprecation

Few days back I sent a tweet update on this matter using my twitter account @preethi_1965
For those who want to hear more on this, here there are few things:

Microsoft has officially announced that they are moving away from OLEDB. 
http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx
http://social.technet.microsoft.com/Forums/en/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f

There could be a couple of reasons behind it.

• SNAC OLEDB does not scale upt o the customer expectation
• ODBC is industry standard and It supports cross platform. To meet the complex market and technology needs, Microsoft needs to Maintain ODBC anyway. 
• Maintaining both OLEDB and ODBC at the same time providing new features is a difficult task.
• Interestingly, Initially Microsoft pushed for OLEDB as it can give a better performance over ODBC.  However, especially in 64 bit platforms, OLEDB didn't takeover.  According to insiders, OLEDB takes shorter paths, less code and provides better performance.    

With that said, SQL Server "Denali" most probably be the last version OLEDB.  As Denali CTP 3 is not yet feature complete, I expect another 6 to 12 months for the RTM to be released. It gives us space of another seven to ten years to change the code which uses OLEDB.  Until then we are rightfully expect Microsoft to support OLEDB.

Just a note, OLEDB is shipped in two forms. SNC(SQL Server Native Client) and MSOLAP. Microsoft is deprecating only the SNC OLEDB. OLEDB for other platforms will be supported continually. SSIS, SSRS and SSAS clients need not to be panicked. Especially SSIS is heavily depending on OLEDB currently and until something else is developed and in place for few years, it will be supported.

I saw an interesting article on  Impact of the SQL Server OLE DB Deprecation on Analysis Services.
Please have a look.

Rename sa login to improve security

Whenever I speak about security, I tell that we should minimally use sa account. SQL server by default keep the sa account disabled.
In addition to this, we can restrict sysadmin rights to few people, and give only what is needed.

Recently I found out that you can even rename sa account. It is a better practice so that people will not even know what the sa account is.
Simply go to security and logins and select sa account in object explorer. Right click there and select “rename” option. You replace the sa name with the new name and you are done. All the database user accounts mapped to sa login and sa owned jobs and objects will work without any issues. This is because even though you have renamed the login name you are still using the same SID 0x01

This means, you have an additional security; earlier Brute force mechanism needs to get only the password of sa. (It already know that they have a user named sa and sa has sysadmin rights. ) Now needs to pass three different options. Finding a valid login name; getting the password; the login should sysadmin have access.

Tool Evaluation: uCertify PrepEngine for 70-432: SQL Server Implementation and Maintenance.

Recently I was asked to evaluate an exam tool. A tool developed for 70-432 exam: MSTS: Implementation and maintenance of SQL Server 2008.

 

Before talking about the tool, Let me introduce my readers to the tool itself. It is “uCertify PrepEngine” from uCertify.com. The tool helps the user to learn and practice for the said exam before taking the actual exam. I know that they provide the preparation tool for various other exams too. 
To use the tool, you have two options: An evaluation version which gives limited usage. If you find it useful, (There is high possibility you may find it useful) you can upgrade it to a licensed edition.

 

 

Let me set this clear first: 100% money back guarantee and braindump free.

• Ucertify supports 100% money back guarantee. In their own words, if you don’t get certified in the first attempt, fax them your score and we will return your money. As simple as that.
• But it does not mean it is another collection of braindump. In fact, it is brain dump free. That means, you can learn the basics and sit for the exam and pass the exam without cheating. You can really be proud of your results.

Doesn’t that sound interesting? Don't you want to download the evaluation version?