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

BigQuery by Google

by Amol 27. February 2011 16:29

BigQuery is a web service that enables you to do interactive analysis of massively large datasets. Scalable and easy to use, BigQuery lets developers and businesses tap into powerful data analytics on demand.

Features

  • Speed - Analyze billions of rows in seconds
  • Scale - Terabytes of data, trillions of records
  • Simplicity - SQL-like query language, hosted on Google infrastructure
  • Sharing - Powerful group- and user-based permissions using Google accounts
  • Security - Secure SSL access
  • Flexibility - REST APIs, JSON RPC, Google Apps Script

Uses

  • Ad-hoc analysis
  • Standardized reporting
  • Data exploration
  • App prototyping

Read more on Google

Tags:

General

Considering time by excluding overlapping and gaps

by Amol 26. February 2011 19:06

If we need to deal with schedules, we often require calculating total period by excluding overlap and schedule gaps.  Here is the scenario. Below table contains one schedule. According the schedule total hours should be 6 hours by excluding gaps and overlaps.

9:00 AM

10:00 AM

11:00 AM

12:00 PM

1:00 PM

2:00 PM

3:00 PM

4:00 PM

5:00 PM

 

 

 

 

 

 

 

 

 

1hr

 

 

 

 

 

 

 

 

1hr

1hr

1hr

 

 

 

 

 

 

 

 

 

 

1hr

 

 

 

 

 

 

 

 

 

 

1hr

1hr

 

 

 

1hr

 

 

 

 

 

 

Below script is used to populate some sample data according the above schedule and script to get the actual hours (you can say working hours). 

To get the total working hours, first need to calculate total hours between very first time and last time.  Then subtract the gaps between schedules if there are any.

IF OBJECT_ID('XYZ') IS NOT NULL

      DROP TABLE XYZ

GO

 

CREATE TABLE XYZ

(

id int identity(1,1),

ST smalldatetime NOT NULL,

ET smalldatetime NOT NULL

)

GO

 

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 9:00AM', '2010-01-01 10:00AM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 9:00AM', '2010-01-01 12:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 1:00PM', '2010-01-01 2:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 3:00PM', '2010-01-01 5:00PM')

INSERT INTO XYZ (ST, ET)

VALUES ('2010-01-01 11:00AM', '2010-01-01 12:00PM')

 

GO

 

WITH Gaps(Gap) AS 

(

    SELECT COALESCE(SUM(DATEDIFF(MINUTE,ST,ET)), 0) 

    FROM (

            SELECT MAX(P1.ET) AS ST, P2.ST AS ET

            FROM XYZ AS P1

            INNER JOIN XYZ AS P2 ON (P1.ST < P2.ST)

            GROUP BY P2.ST

            HAVING MAX(P1.ET) < P2.ST    

         ) gaps

)

SELECT ( 

            COALESCE(DATEDIFF(MINUTE, MIN(ST), MAX(ET)), 0) 

            - (SELECT Gap FROM Gaps)

       ) / 60.0 TotalHrs

FROM XYZ

Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud