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.

How to Move a Publisher Database

Recently I faced an incident where I needs to MOVE a database which is a publisher, to a different drive. The reason was the insufficient storage. The challenge was to intact the replication settings of the database after the move. So that, I don't have to recreate the replication which is a tedious also a boring task.

As many of you aware, there are several methods can be applied to move a database. Such as, attach / detach method, move database files while the server is shutdown, etc. However none of these methods are a solution in my case, because of the replication factor.

The version of the SQL server I am using is 2008.

Solution

Took a full backup of the database and then restored with “WITH MOVE” option to the new drive.  

Troubleshooting High-CPU Utilization for SQL Server

The objective of this FAQ is to outline the basic steps in troubleshooting high CPU utilization on  a server hosting a SQL Server instance.

The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. The Performance tab will show the high utilization as shown below:

Click on below link to read the entire article.

sql-server-performance.com

Mysterious system table: master.sys.sysdbreg

Have you ever seen the system table, "master.sys.sysdbreg" in your SQL Server. I've seen this unintentionally while working on something else. I was analyzing a query plan in XML view. Suddenly I saw this system table. See below portion of execution plan.

How to identify objects used in a stored procedure

Recently there was a requirement from a project team to identify all objects belong to a stored procedure. Actually they wanted only the tables used in a stored procedure.You can use sp_depends with the SP and then you will be able to get all the dependent objects. But the actual requirement was much more complex than that because they wanted to analyze a trace they created for all the SPs captured in the trace.

Sp_depends and sys.sysdepends are provided in SQL Server 2008 for backward compatibility purpose only. So not recommending to use in new developments.

Refer below links for more information.


sp_depends


sys.sysdepends

SQLOS and Windows OS – Part I

If you’re a SQL Server professional you may have already familiar with SQLOS. At least you might have heard it but may not know much about it. However everyone familiar with the Windows OS but again may not know much technical aspect of Windows. Windows is a general purpose OS designed to install application programs and manage them. E.g. you can install MS Office into your computer only after configuring Windows OS.

Windows OS

When you execute an application it creates a process in Windows OS. Until such time it is in inactive state (stored in storage). Let’s consider SQL Server. It is just another application as far as Windows is concerned. SQL Server process will create in Windows as soon as you start SQL Server. You can simply see the processes in Windows Task Manager. (See Figure 1.1)

NEW-STYLE DATABASES FOR NEW-STYLE PROBLEMS

Old-style database systems solve old-style problems; we need new-style databases to solve new-style problems. While the need for conventional database management systems isn't going away, many of today’s problems require a configurable database system. Even without a crystal ball, it seems clear that tomorrow’s systems will also require a significant degree of reconfigurability. As programmers and engineers, we learn to select the right tool to do a job; selecting a database is no exception. We need to operate in a mode where we recognize that there are options in data management, and we should select the right tool to get the job done as efficiently, robustly, and simply as possible.

Above is the excerpt from the article published in ACM. It is an interesting one and take sometime to look at it.

Beyond Relational Databases

How to identify which CPU(s) is using for a query

If a query uses parallelism when executing, it may be interesting to see actually which CPU(s) are getting involved.

The below query gives you that information;

SELECT     t.session_id
          ,t.request_id

          ,t.scheduler_id

          ,t.task_state

          ,s.cpu_id

          ,r.database_id

          ,r.sql_handle

          ,w.last_wait_type

FROM sys.dm_os_tasks t

INNER JOIN sys.dm_os_schedulers s

     ON t.scheduler_id=s.scheduler_id

INNER JOIN sys.dm_exec_requests r

     ON t.session_id=r.session_id

INNER JOIN sys.dm_os_workers w

     ON t.task_address=w.task_address 

WHERE t.session_id=51 -- put the SPID

Sample Databases for Microsoft SQL Server 2008R2

The below link can be used to download SQL Server 2008 R2 sample databases. 

Download

OSTRESS Tool

OSTRESS is a Microsoft tool comes with RML utilities package and it uses to stress SQL Server. This is especially useful when you want to troubleshoot SQL Server while SQL Server is under heavy load.

Use below link to download RML Utilities


SQL Server (x64)


RML Utilities


After installing RML utilities, you can use RML cmd prompt to execute scripts with OSTRESS.

Tools for System Internals

While googling on some SQL Server internals, I found a site which has lots of tools to get vital information about your system. I've used some of them and they are very useful.  


One of such tools is "Coreinfo.exe".
"Coreinfo is a command-line utility that shows you the mapping between logical processors and the physical processor, NUMA node, and socket on which they reside, as well as the cache’s assigned to each logical processor. It uses the Windows’ GetLogicalProcessorInformationfunction to obtain this information and prints it to the screen, representing a mapping to a logical processor with an asterisk e.g. ‘*’. Coreinfo is useful for gaining insight into the processor and cache topology of your system. "

Rename a constraint

If you need to rename a constraint after creating with typo or due to any other reason, you could follow the below steps.

--creating test table with PK constraint
CREATE TABLE dbo.RenameConstraintTest
(
ID int CONSTRAINT PKC_RenameConstraintTest PRIMARY KEY CLUSTERED
)
GO

--check the existance of the constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO

--rename the constraint
EXEC sp_rename N'[dbo].[RenameConstraintTest].[PKC_RenameConstraintTest]', N'PKC_RenameConstraintTest2', N'INDEX'
GO

--check the old constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO
--check the renamed constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest2'
GO