tag:blogger.com,1999:blog-123275062026-07-24T11:26:46.243+05:30All about - databases and related technologies - things I work with - SQL Server, other relational & non relational databases, PowerShell and scripting languages.Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.comBlogger108125tag:blogger.com,1999:blog-12327506.post-64530857946158097642012-10-04T12:43:00.000+05:302012-10-04T12:43:49.758+05:30

This week, at our office we had a discussion over lock escalation.  We were discussing about a large query which returns hundreds of thousands of rows.  The query touches some key tables where critical inserts are happening to those tables.  Even though the numbers of rows are large, the table has millions of rows. We were worried about the locks it is going to place. 

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com1tag:blogger.com,1999:blog-12327506.post-60738935760290943302012-09-29T13:29:00.003+05:302012-09-29T13:49:55.930+05:30

On one sentence, Page splits does not always make a 50/50 split in SQL Server 2012! Recently I was preparing for a training and checking on page splits. Suddenly I thought that it is better to tell people about the numberof page splits can occur in a single statement and the amount of activities it can take. I found something new … well its new to me,  as my subsequent

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com1tag:blogger.com,1999:blog-12327506.post-16371211986768404722012-09-16T18:37:00.004+05:302012-09-17T00:15:41.198+05:30

Recently, while I was looking at DatabaseLog table in AdventureWorks database, I found something wired. Before going further, let us look at some important information about this table. I publish only the information of my concern. First of all, this table does not have a clustered index. It has DatabaseLogID which is an identity column which is also the primary key, non-clustered. The next

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-21241409478280287322012-08-09T07:07:00.001+05:302012-08-09T07:08:26.736+05:30

As most of you have noticed or even received invitation, this month's user group meeting is happening at a different place.  Pearson Lanka (formally known as eCollege) is hosting the meeting at their facility. yes it is where i am working.  (not only me, around eight other great DBAs including Dinesh Asanka MVP, Susantha Bathige too.)   There are couple of reasons

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-10948210581775792362012-07-04T07:28:00.002+05:302012-07-04T11:06:28.860+05:30

As some of you know SQLServerUniverse.com, the site managed by SQL Server Sri Lanka User Group was down for a couple of months.  Unfortunately, the crash was so serious, we have to rebuild it from almost scratch. Gogula (http://dbantics.wordpress.com Twitter @gogula) did an amazing job in that and the site is online now. Right now the site does not have all the features. We

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-14118898319304304732012-04-15T12:17:00.000+05:302012-04-16T07:08:53.575+05:30

Interestingly,  I got an email on 11th from a friend of mine inquiring about the certifications Microsoft is offering.  I know that Microsoft was planning to bring new set of certifications.  So While writing the reply when I checked the site, I found out the Microsoft has just updated the info. Microsoft has done a major restructuring of the certification program related to

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-41249780257827133492012-04-14T12:56:00.000+05:302012-04-14T12:56:00.637+05:30

This is the final part of the series. I was not initially planned to write this series in a week interval, but due to various reasons it got dragged. Cost of Page Splits Many writers have written many times about this, so I like to summarize them It needs to take the last page and delete half of the content In order to do that this and maintain the consistency, the activity is

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-45745997663905143732012-04-12T10:23:00.002+05:302012-04-14T06:15:42.405+05:30

[Updated 13th April 2012 12:27 am] Note: Jonathan Kehayias (Blog)  pointed out that some of his work is being referenced but not cited. Since it is a series, I was under the impression that all references could be published at the end; but Jonathan updated me that it should be there with each post. So it is mentioned at the end of this post. Regret for the anxiety

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com2tag:blogger.com,1999:blog-12327506.post-58660320749050956422012-04-11T22:12:00.001+05:302012-04-13T10:46:17.159+05:30

My sincere apologies to all in delaying producing part 3 of the series.  I got into a set of urgent tasks which prevented me from updating my blog. SQL server has one more mechanism which allows us to identify page splits.  This feature called extended events introduced with SQL server 2008 and According to Microsoft sources,  eventually, it will replace SQL trace. SQL

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com3tag:blogger.com,1999:blog-12327506.post-90092100895826006702012-03-17T18:36:00.002+05:302012-03-17T18:38:40.352+05:30

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

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-73364330002514398052012-03-12T07:44:00.002+05:302012-04-13T11:18:24.328+05:30

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

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-39750237478404849942012-02-19T23:08:00.001+05:302012-04-13T11:17:49.262+05:30

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

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-35344581954225344512012-01-11T06:27:00.000+05:302012-01-11T06:27:03.159+05:30

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. 

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-43239966083182934072012-01-03T09:35:00.001+05:302012-01-03T09:35:26.804+05:30

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.

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com2tag:blogger.com,1999:blog-12327506.post-83408637149242657082012-01-01T00:00:00.000+05:302012-01-03T09:08:20.311+05:30

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-91610532583277633562011-11-02T07:38:00.000+05:302011-11-02T07:38:26.919+05:30

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

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-75270902451330336312011-09-14T12:58:00.000+05:302011-10-24T17:07:50.220+05:30

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://

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-15771772553129449862011-09-08T20:55:00.007+05:302011-09-08T21:13:36.954+05:30

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

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-82391762257882413872011-09-04T23:16:00.000+05:302011-09-04T23:16:19.766+05:30

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

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-73535932721141141492011-08-30T20:07:00.001+05:302011-08-30T20:08:23.582+05:30

This issue came up during one of the training I conducted recently. There is a popular belief that UNION will ALWAYS perform a unique filter on the data.  While it is true that UNION Clause will ALWAYS RETURN unique set of data, filtering operation either sort or distinct select is not guaranteed. Take this example: SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID,

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-21875760709983832872011-08-28T16:46:00.000+05:302011-08-28T16:46:01.059+05:30

Now a days some people (including me) used to save whole heap of files into their hard disks. Word documents, excel sheets, powerpoint presentations, pictures  taken, pictures received from others and white papers (some times they are word documents), code files (including SQL),   Then you have Acrobat reader files, e-books, video and audio files.  Most of them take a lot

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-6610470533804849332011-08-24T09:26:00.000+05:302011-08-30T20:14:44.900+05:30

I was scheduled to speak at this user group meeting (That's today.) I am ready with the presentation. However, after having a small hickup on some personal issues, I asked Dinesh Asanka to do it. He too was ready with the presentation, but at our office he got something. He presence was needed at office at that time. So, I am back to square one is doing the presentation. I am speaking on SQL

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-80140466527028840002011-08-11T06:42:00.001+05:302011-08-11T06:44:40.300+05:30

onYesterday, Microsoft has announced the release of Product Guide v1 for SQL Server Code Name “Denali” Community Technology Preview 3 (CTP3). This is a single download that organizes Denali related content for easier viewing. It is available for download at http://go.microsoft.com/fwlink/?LinkID=225814.  The SQL Server Code Name “Denali” CTP3 Product Guide includes useful

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-41513823773978492232011-07-14T08:53:00.001+05:302011-07-14T08:53:11.149+05:30

  Let me give a different order: SQL Server 2008 R2 sp1 Yes, it is finally available.  Check out the links below. SQL Server 2008 R2 SP1 SQL Server 2008 R2 SP1 Express SQL Server 2008 R2 SP1 Feature Packs You can download the files based on the processor you have.  In addition to bug fixes some interesting enhancements too are added to this service pack. Dynamic Management Views

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0tag:blogger.com,1999:blog-12327506.post-8694956452412659622011-07-13T21:00:00.001+05:302011-07-13T21:04:55.031+05:30

Note: I thought of writing about page splits. Initially I started writing a blog and it slowly became a huge article. I was having 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 article. My plan is to write different aspects of page splits in coming days… In SQL Server data is

Prithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0