What should you know more about NOLOCK?

As you may know already, NOLOCK is used in some queries to avoid, SQL Server to acquire shared locks on the records being red. The main reason to use nolock is to eliminate the performance overhead, especially for large data sets, due to shared locks. Though you have the gain in performance wise, it also gives you dirty records (uncommitted data).

This article explains another behavior of nolock.

Let’s create a sample table with test data to illustrate the behavior.

Shortcut to view tables

As a DBA I need to see the list of tables available in a particular database frequently. Traditionally, this can be done by traversing through the object explorer. This may be the popular method among GUI lovers. But for me it is a pain. I hope it is the same for most of the DBAs.

BIOS settings for Virtualization

This is not directly related to SQL Server, but this configuration would be needed for SQL Server Virtualization as well. Windows Server 2008 R2 comes with the built-in feature called Hyper-V to support for virtualization, meaning to manage virtual machines.

There are couple of configuration settings you need to enable in order to use Virtualization.

Does SPID unique in sys.sysprocesses?

Sys.sysprocesses contains connection information for each connection made to the SQL Server. As you see it, it looks like SPID is unique. But actual it is not, because of KPID. KPID is Windows thread id. Each SQL Server task is assigned a Windows thread. SQL Server task is the unit of execution for SQL Server. The queries submitted to SQL Server may have parallel tasks like parallelism operator. In this case, SPID has several KPIDs, which duplicate SPID in the sys.sysprocesses view.

Windows Powershell

Last few days I spent most of the time on my personal development to learn and understand about Windows Powershell; Microsoft new scripting language. I’ve not being used any scripting language extensively. But now I understand how much power will bring to a DBA by learning this kind of a scripting language. 

You may be thinking why do you need a scripting language while having most powerful user friendly GUIs in Windows. Ok. I also thought the same way before I learning Powershell.

There are couple of reasons why you should use Powershell. I’m sure there are many more;

Configure Windows Firewall for SQL Server 2008

You will find this article very useful if your planing to install SQL Server 2008 on Windows 2008 server. Once you have installed SQL Server and if you try to log into the server remotely, it will fail. I too have faced this issue recently. Configuration of Windows Firewall comes into action at this time. 

How to retrieve server information?

How do you get information (HW/SW) in a server? Have you ever wanted to get that information? Ok. You might say you can use, “dxdiag” in command prompt and it will give you ample of information. Of course you can use “dxdiag” but I’m going to present you a different way of getting that information.