Parameter Sniffing

What is it? First we will go through the scripts below;

Create the following index;

USE [AdventureWorks2008R2]

GO

/****** Object:  Index [NNI_SalesOrderDetail_ModifiedDate]    Script Date: 03/06/2012 15:09:41 ******/

CREATE NONCLUSTERED INDEX [NNI_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]

(

     [ModifiedDate] ASC

)

Then create the below stored procedure. It is a very simple SP which has single SELECT statement for Sales.SalesOrderDetail table. There are two parameters for two date values.

VLDBs backup in few seconds

VLDB - Very Large Databases, like several Gigs of databases. (50GB, 100GB, etc) How about managing backups? You may need to spend 20-30 hours to take a full backup of such DBs. Of course SQL Server 2008 has improved backup system than its earlier versions. Another alternative method would be to use third party tools like, LiteSpeed or RedGate backups. But stills you can't make it in few seconds. How about a system to take such a large DB backup in a few seconds? ANSWER is to use SAN snapshot backup.

Reading a deadlock

Deadlock is a common phenomena in databases where users can work concurrently. Deadlock happens because of pessimistic concurrency control mechanism which is essentially use of locks. You can't prevent deadlocks but of course you need to minimize them occurring. There are several ways of capturing deadlocks in SQL Server. They are;

1. Using SQL Server Profiler

2. Using SQL Server Error Log

The objective of the blog post is to read and understand the deadlock graph which is captured in SQL Server Error log.

Pearson Lanka DBA team at SS SLUG

Pearson Lanka DBA team presented six demo's at last meetup of SS SLUG (http://sqlserveruniverse.com/v2/ssslug.aspx). The series of demo's named as WirlWind DBA session. It is really a mind blowing session which each session limited to ten minutes including three minutes Q&A. 

Why so special about these presentations? Yes; it is based on our day to day DBA activities, which we encountered and gathered the knowledge after doing many research. So thought of sharing the valuable information to the community who loves databases. 

There I presented on how to read a deadlock graph which I'm going to do a small write up and publish in future. 

I've the videos of all the presentations and hoping to upload them to youtube. 

Introduction to Parallel Programming and MapReduce

This is a very good article published by Google to undsertand the conecpts and practical usage of Parallel Programming and MapReduce.

http://code.google.com/edu/parallel/mapreduce-tutorial.html

How SQL Server handles multi byte characters?

To store multi byte characters you need to use Unicode data type in SQL Server. SQL Server has four data types for Unicode characters. (nchar, nvarchar, nvarchar(max),ntext)

See below for the MSDN definitions for the above data types; (except ntext)

Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.


nchar, nvarchar

Geospatial data support in SQL Server

I wanted to do some research on geospatial data support in SQL Server after attending Refresh Colombo July meeting.

There Janith did a small presentation on how to finding a bus route in Colombo. Here is his blog explaining how he has done it. 

He has implemented the solution based on MYSQL database and I’m not quite sure about the database design used for the application.

This tended me to look at how we can implement such a thing in MS SQL Server and did some googling around the geospatial data support.