tag:blogger.com,1999:blog-123275062024-03-05T13:39:03.815+05:30dbthoughtsAll 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:30Lock Escalation Point
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:30Supplement to page Splits: Change in SQL Server 2012
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:30My Investigation On Heap Table: Why It Shows Data Not In Order?
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:30SS SLUG August 2012 meetup is at Pearson Lanka
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:30SQL Server Universe is online again
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:30New certification Paths
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:30Page Splits Part 4: Cost and Ways of Controlling
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:30Page Split 3.5: Advantage of Extended Events in SQL Server 2012
[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:30Page Splits Part 3 – Identifying through Extended events
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:30Adding 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 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:30Page 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 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:30Page 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 pagePrithiviraj 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:30MVP 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. 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:30SQL 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. 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:30Happy New year!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:30Will 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 blogPrithiviraj 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:30SQL 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://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:30Rename sa login to improve securityWhenever 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 andPrithiviraj 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:30Tool 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 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:30UNION Vs UNION ALLThis 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:30Removing unnecessary files using powershellNow 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 lotPrithiviraj 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:30Speaking again at SSSLUGI 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:30Resources on Denali
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:30Denali CTP3, Juneau CTP3 & SQL Server 2008 R2 sp1 Available 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 ViewsPrithiviraj 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:30Understanding Page SplitsNote: 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 isPrithiviraj kulasinghanhttp://www.blogger.com/profile/15899411508678919419noreply@blogger.com0