SQL Server 2008 R2 Cumulative Update 5 released

by Amol 1. March 2011 07:06

Today microsft has released SQL Server 2008 R2 Cumulative Update #5. The build number would be 10.50.1753.0 after applying this updates. This update is available to download at

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2438347&kbln=en-us

Note: This hostfix contains all the updates since initial release SQL Server 2008 R2 RTM.

Tags: ,

General

What is Columnstore Index

by Amol 1. March 2011 06:20

Introdution:

SQL Server 2011 CTP1 has been relesed and it has introduced new INDEX type called Columnstore. Microsoft SQL Server is the first who described columnstore index. Non of the other database products introduced this concept. This newly introduced index will help dramatically increase query performance as columnstore index stores each column in a separate pages rather than traditional index pages which stores multiple rows.  Traditional index pages will looks like as shown in figure below.   

Figure 1

Columnstore index structure is bit different. Instead of storing entire columns in single page it store each column in separate page. Columnstore index is structure is show in figure below.

Figure2

Figure 1 and Figure 2 shows the basic difference between index and columnstore index.
In Figure 1 entire rows (all columns) are stored in page. And in Figure 2 column Col1 to Col4 are stored in different pages.

Benifites of Columnstore Index:

1.      As Columnstore Index stored in separate pages, only required pages are fethced from the disk.
2.      Faster query processing.
3.      Frequently accessed columns remains in memory.
4.      Enhanced query optimization and execution features, improves data warehouse query performance by hundreds to thousands of times in some cases

I will try to post the comparision result between traditinal Index and Columnstore index. Meanwhile you can try downloading SQL Server Denali CTP.

Tags: , ,

SQL Server Denali

Inside SQL Server Stored Procedure

by Amol 28. February 2011 00:21

fn_get_sql() function can be used to get what statement is currently executing for a process. Usually we use DBCC INPUTBUFFER to get know what the process is doing. But this will return the procedure name not the exactly which code of line is being executed. For example, if you have procedure 1 and procedure 1 calls another procedure 2 and procedure 2 calls another procedure 3. And even when procedure 3 is executing, DBCC INPUTBUFFER will return procedure 1 not procedure 3. 

fn_get_sql() will return the exact line of code being executed.

To see how it works, create following stored procedure. This procedure can be used to diagnose performance related issues, long running queries inside the procedures, blocked queries.

IF OBJECT_ID('dbo.WhatsGoingOn') IS NOT NULL
     DROP PROCEDURE dbo.WhatsGoingOn

GO

CREATE PROCEDURE dbo.WhatsGoingOn
(
    @SPID smallint,
    @Wait tinyint = 0
)
AS
BEGIN
SET NOCOUNT ON

      DECLARE @NoLoop bit, @SqlHandle binary(20), @HandleFound bit
      DECLARE @StmtStart int, @StmtEnd int
      DECLARE @Command nvarchar(4000), @WaitStr varchar(8)

      SET @HandleFound = 0
      SET @NoLoop = 0 

      IF @Wait NOT BETWEEN 0 AND 60
      BEGIN
            RAISERROR('@Wait should be between 0 to 60 seconds', 16, 1)
            RETURN -1
      END
      ELSE
      BEGIN
            SET @WaitStr = '00:00:' + RIGHT('00' + CAST(@Wait AS varchar(2)), 2)      END

      WHILE 1 = 1
      BEGIN
            SELECT @SqlHandle = sql_handle,
                   @StmtStart = stmt_start/2,
                   @StmtEnd = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
            FROM master.dbo.sysprocesses
            WHERE spid = @SPID
                  AND ecid = 0

            IF(@SqlHandle = 0x0) OR (@StmtStart = 0 AND @StmtEnd = 0)
            BEGIN
                  IF @HandleFound = 0
                  BEGIN
                        RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
                        RETURN -1
                  END
                  ELSE
                  BEGIN
                        RAISERROR('Query/Stored procedure completed', 0, 1)
                        RETURN 0
                  END
            END
            ELSE
            BEGIN
                  SET @HandleFound = 1
            END 

            SET @Command =
            (
                  SELECT
                  SUBSTRING ( text,
                              COALESCE (NULLIF(@StmtStart, 0), 1),
                              CASE @StmtEnd
                              WHEN -1 THEN DATALENGTH(text)
                              ELSE (@StmtEnd - @StmtStart)
                              END
                            )
                  FROM ::fn_get_sql(@SqlHandle)
            ) 

            RAISERROR(@Command, 0, 1) WITH NOWAIT

            IF @NoLoop = 1
            BEGIN
                  RETURN 0
            END 

            WAITFOR DELAY @WaitStr

       END

END
 

Tags: ,

SQL Server Scripts

Category

Recent Posts

Tag cloud