Blogs from Suhas

Internals of Database Snapshot – Part 3: Snapshot and Mirroring

2011-08-15T13:25:32Z

In my earlier posts, we have seen how database snapshots work, and how the Buffer Pool is used when we query Snapshot Databases. In this post, we will see how Database Snapshots work, when they are created against Mirrored Databases. Let us assume we have a mirroring setup, as described in the below image. SQLServer1 hosts the Principal Database (MyDB) while the same database on SQLServer2 is the Mirror Database. It is possible to create a Snapshot of the Mirrored Database (MyDB on SQLServer2), in...(read more)

Internals of Database Snapshot – Part 2: Snapshot and the Buffer Pool

2011-08-02T12:46:32Z

In my earlier post, we have seen what Database Snapshots are, and how the Database Snapshots are maintained by the SQL Server Database Engine. In this post we will see how the data and index pages, belonging to the Snapshot, are managed in the Buffer Pool. Before we start drilling down further, let me take a while to explain how we will pull up details about pages in the Buffer Pool. We have a DMV and a few Catalog Views that we can use: sys.dm_os_buffer_descriptors : This DMV returns information...(read more)

Internals of Database Snapshot

2011-07-31T08:09:30Z

In this post, we will explore the internals of Database Snapshot. Most of this information is already available in the Internet, in the posts authored by the SQL Server Development Team and the CSS Escalation Team; however, through this post we will attempt to consolidate all of these information for the benefit of everybody. All credit for this post goes to my colleague, Mohammad Sufian , without whose help, this post may have not been possible. So, what are Database Snapshots? According to the...(read more)

Advanced Performance Tuning – 4 :: Designing for better performance contd…

2011-07-15T14:46:01Z

Few months back, I was working with one of my customers, who was engaged by his client to design a Survey Module. Nothing very fancy about the module, but the kind of reports that were to be delivered made it really tricky. Let me explain the requirements first: When customers click on a Survey Link, they are redirected to a web page. This page has 20 questions; for each question, customers are supposed to provide a rating between 1 and 5, both inclusive. Of course, customers can skip any question...(read more)

Advanced Performance Tuning – 3 :: Designing for better performance

2011-07-14T16:09:16Z

If you have read my earlier post , you might be saying “Heck! This guy loves to post questions without providing proper answers!” Believe me, that is not my intention. My intention here is to help you understand common mistakes that people make, so that you can avoid them in your projects. The biggest challenge for a SQL Server Developer is to write efficient code. The expectations are that the code must complete in the least possible time, produce correct results and use minimum resources possible...(read more)

Advanced Performance Tuning – 2 :: Which side of the operator

2011-07-13T13:14:40Z

If you have read my earlier blog post on the Importance of data-types , you might be wondering, “What the heck is going on here?” I had already written in that post the reason behind the slow performance – the EmpCountry column in the Employee table is CHAR(2) which cannot be directly compared with NCHAR(2); hence, SQL Server is forced to CONVERT the EmpCountry column values to NCHAR(2) before it can perform the comparison. Again, SQL Server does not know what the column values will look like after...(read more)

Transaction Log Backup Size in Bulk Logged Recovery Model

2010-12-29T11:29:00Z

Assume that I have a database in Bulk Logged Recovery Model, and I perform a bulk operation that is minimally logged. Now, if I take a Log Backup, should not my Log Backup File Size be smaller than if I had performed the same operation in Full Recovery Model? I have seen many people confused by this question. When I say, “No, please do not expect this”, I am thrown back another question – what, then, is the purpose of having the Bulk Logged Recovery Model? Let’s first have a look at an extract from...(read more)

Advanced Performance Tuning – 1 :: Importance of data-types

2010-09-12T10:10:00Z

Why did we develop DBMS systems? Not just for storing data – but also to be able to search for and retrieve stored data, and we define what our search criteria should be. This was probably true a couple of decades back, but we have come a long way now. With the huge advancement of technology and the computing powers being doubled probably a few hundred times, we are now not satisfied with just being able to retrieve stored data; we want to retrieve data faster, we want our operations to...(read more)

INF: How to re-setup Database Mirroring In SQL Server

2010-08-25T17:08:04Z

One of my friends was asking this to me offline the other day: "I have Database in SQL Server, which is both Mirrored as well as Log Shipped. Log backups happen every 15 minutes; Mirroring happens in Synchronous mode and does not involve a witness. I want to break Mirroring (Remove Mirroring) and then want to re-setup the Mirroring configuration after about 30 minutes; what is the easiest way to achieve this?" I constructed a small diagram for better understanding: I said, "What is...(read more)

INF: Automatic Identity Range Management with Merge Replication in Microsoft SQL Server 2005 / 2008

2010-03-22T06:41:37Z

I have been working with SQL Server Replication for over 4 years now; and till now each case that I work on gives me something new to learn. At the same time, every case amazes me by the intricate design that has gone into implementation of SQL Server Replication. With the wonderful GUI design, replication seems so easy to configure and monitor; however, when I start troubleshooting an issue and I look into the metadata to understand the cause of the issue, I am faced with new challenges every day...(read more)

INF: A missing exception handler can cause blocking and eventual application hang.

2010-03-18T05:45:24Z

Very recently, I had the opportunity to help troubleshoot a Blocking issue that one of our customer's was experiencing. In my experience, this is a very common scenario that I have seen. However, while troubleshooting this issue, and more so, while we wrote code to reproduce the issue at our end, we gained a lot of knowledge on how an inaccurate exception handling in code can affect the overall performance of the system in a negative way. I wanted to share my new gained knowledge with my readers...(read more)

Is NFR set?

2010-03-12T20:01:08Z

Hi Friends, After a long break of about 8 months, I am back again!! Many things changed recently, and I was quite busy adopting to the changes – the most challenging was, of course, adopting myself to new parenthood. However, I am back again, with some new topics – topics that I have learnt or experienced during the long absence. I was working on a case very recently – my customer was trying to setup Transactional Replication, and while applying the snapshot, he was receiving an error message: "Cannot...(read more)

Revealing the secrets - Truncating a Transaction Log File

2009-07-18T15:34:46Z

In my earlier blog post on Shrinking the Transaction Log files in SQL Server , I had mentioned that a VLF can be truncated only when all transactions, that have at least one command written into the specified VLF have either been committed or rolled back. While this is true, I understand that it might create a bit of confusion around how the truncation actually happens behind the covers. In his comment, David writes: Also, I have read in the documentation (Transaction Log Physical Architecture) that...(read more)

Step-by-step guide to configure Database Mirroring between SQL Server Instances in a Workgroup

2009-07-13T16:34:00Z

This post is again in response to requests from customers who want to know how to configure Database Mirroring between instances of SQL Server 2005 in a WORKGROUP. As you might have noticed, while configuring Database Mirroring using the GUI, it requires us to enter FQDNs of the servers; hence using the GUI, we can configure Database Mirroring between instances of SQL Server in a domain environment. All credit goes to Varun for creating this step-by-step guide. I take the liberty to post it...(read more)

How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account

2009-07-12T10:33:00Z

Hi Friends, This post comes as a response to requests from many of our customers, who want to know the step by step process on how to configure SQL Server Database Mail to send emails using their Windows Live Mail Account or their Gmail Account. If you are using SQL Server 2005 or higher, you might have noticed that there is now a “Database Mail” (DB Mail) option under “Management” . This is very different from the “SQL Mail” that we had on previous versions (it still exists under Management\Legacy...(read more)

Database Corruption Part 5 :: Dealing with Log File Corruption

2009-07-11T18:15:00Z

Hi Friends, I am back again after a long time. Last month was the end of yet another financial year, and all of us were very busy with the processes involved with each year-end. Now that all those are completed, I could find some time for my next blog post on database corruption. Here, I will be targeting issues related to corruption in the database log files. As we all know, the Transaction Log File(s) of a database (*.ldf files) are essential for maintaining transactional consistency in the databases...(read more)

Registry check-pointing on a Windows Cluster

2009-05-26T23:07:00Z

Over the last few years that I have been in Microsoft, I have come across multiple issues where database administrators have mistakenly made inappropriate changes to the clustered SQL Server registry hives. Later, when SQL Server restarts (either due to manual intervention or during a failover), SQL Server can no longer come online on the cluster. Additionally, the registry changes cannot be reverted back; and SQL Server remains in the failed state despite all efforts to bring it online. This...(read more)

Lock pages in memory now available for 64 bit Standard Edition of SQL Server

2009-05-21T08:42:00Z

Great news!!! Due to over-whelming customer demand for the " Lock pages in memory " support for the Standard Edition of SQL Server 2005 and 2008 , Microsoft has released Cumulative Updates that will help SQL Server 64 bit Standard Edition to "lock pages" . This feature was already available on the Enterprise and Developer Edition. Enabling "Lock pages in memory" enables the SQL Server to use the AWE APIs, thereby avoiding potential performance issues due to trimming...(read more)

Database Corruption Part 4 :: Recovering from a failed disk

2009-05-18T19:22:38Z

It was a nice day a few days back - nice sunny day, with moderate temperatures. I got up early and after spending some time reading my favorite articles, made my way to office. In office, I realized I hadn't much work lying ahead; so sat down wondering how to account for my day. Suddenly the phone on my desk rang - tring! tring! "Hi, this is Suhas. How can I help you?" "I... I lost my disk...!" "What!" "My disk crashed. It had my database..." "You mean...(read more)

Server Side Encryption in SQL Server

2009-05-15T17:31:00Z

Hi Friends, After 3 posts on Database Corruption, I hope you would not mind if I divert my attention a bit and post some blogs related to some other topics. This topic that I going to write about is a very interesting topic; so I thought of taking some time out to write a few lines regarding this. In this post, I will be writing about enabling Server Side Encryption in SQL Server. All of us know that to enable Server Side Encryption, we need a certificate. However, there is a very strange issue...(read more)

Database Corruption Part 3 :: Differences between CHECKTABLE and CHECKDB

2009-05-14T15:56:12Z

In this post, we would have a look at the differences between DBCC CHECKTABLE and DBCC CHECKDB. We would not go deep into how each works – that has already been very beautifully documented by the SQL Server Storage Engine Team in their blog posts; we would rather have a quick look at the differences between the two commands. DBCC CHECKTABLE: DBCC CHECKTABLE performs a physical consistency check on a single table or indexed view and all its non-clustered and XML indexes, unless the NOINDEX option...(read more)

Database Corruption Part 2 :: How to attach a database using the Create-Replace method

2009-05-12T19:15:17Z

Ok, so, as promised, here’s my next post on Database Corruption. In this post, we will have a look at how to attach a database that you have detached and can now no longer attach back to SQL Server. This may happen if the database was already corrupt before it was detached; or if the database files have become corrupt after the detach. There are quite a few names that have been given to this method. “Create-Copy method”, “Copy-Create method”, “Copy-Replace method” and “Create-Replace method” are...(read more)

Database Corruption Part 1 :: Introduction to Database Corruption in SQL Server

2009-04-08T19:41:00Z

Hello Everybody, I think you will all agree with me that database corruption is one of the nightmares in the life of a DBA. We store precious data in databases and each day the dataset grows bigger and bigger. A database corruption issue not only puts the data at risk, it also threatens to take a toll on businesses and revenues. Throughout my career in Microsoft, I have seen many corruption issues; and while working with the DBAs for troubleshooting such corruption issues, I have felt the apprehension...(read more)

Property Owner is not available for Database '[DBName]'.

2009-04-05T19:51:00Z

Hi Friends, This issue that I am talking about is a very common issue that I have seen. I have, in my experience, seen many users experiencing this issue; yet, most people do not know how to troubleshoot or fix this issue. Moreover, I have seen this issue on SQL Server 2005; although not tested, I am sure you might encounter this issue in SQL Server 2008 as well. The issue is, you right-click on the database and choose properties. Now, instead of the database properties opening up, you get...(read more)

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

2009-04-05T18:55:00Z

Hi Friends, The last month has been a very busy month for all of us, and its not over yet. However, with the daily work, comes a few instances when we do encounter really interesting issues. End of the last month, I encountered an issue, that is really very interesting. The issue that I am talking about is very uncommon; I have not seen many people facing it; although it is very easy to encounter this scenario. Also, this issue can be encountered in SQL Server 2005; although I have not tested...(read more)