Archive

Archive for November, 2012

Convert Varbinary to Varchar data type

November 27, 2012 Leave a comment

 

Covert varbinary data type to Varchar

Script:

 

SELECT
thread_address,CONVERT(VARCHAR(MAX),thread_address,1) thread_address_converted
FROM
sys.dm_os_threads

 

 

 

 

Categories: SQL Scripts

FORCE DATABASE OFFLINE

November 21, 2012 Leave a comment

Recently I get into a situation where one of Database file freeze and stopped responding to any of the queries executed on the objects using the file

On killing the select statement it goes into rollback state with WAIT TYPE DISKIO_SUSPEND.

DISKIO_SUSPEND: Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.

Resolution: Forcefully take database offline.

ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK IMMEDIATE

Bring the database Online

ALTER DATABASE database-name SET ONLINE

 

 

Categories: Recovery

Reading Error Log (Parameterized)

November 15, 2012 Leave a comment

Read specific date, time and text error log. This help while troubleshooting issue on a highly transaction SQL Server.

SET DATEFORMAT DMY

DECLARE @StartDateTime Varchar(30)=’15/11/2012 23:40:00′
DECLARE @EndDateTime Varchar(30)=’29/11/2012 23:59:00′
DECLARE @SearchString nvarchar(MAX)=”

DECLARE @ReadCurrentSQLErrorLog TABLE
(LogDate DATETIME,
ProcessInfo VARCHAR(20),
Text VARCHAR(500)
)

INSERT INTO @ReadCurrentSQLErrorLog
EXEC master.dbo.xp_readerrorlog 0

SELECT * FROM @ReadCurrentSQLErrorLog
WHERE LogDate >= CONVERT(datetime,@StartDateTime)
AND LogDate < CONVERT(datetime,@EndDateTime)
AND text like ‘%’+@SearchString+’%’
ORDER BY LogDate DESC

You can also use
xp_readerrorlog 0, 1,NULL, NULL, ’12-11-2012 15:40:00′, ’12-11-2012 23:40:00′, N’asc’

Categories: SQL Scripts

RECOMPILE in SQL Server

November 8, 2012 Leave a comment

Before we get into the Subject we need to understand the definition of RECOMPILE.

From BOL

RECOMPILE Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuse them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. For more information, see Recompile a Stored Procedure. RECOMPILE is also useful when you create plan guides.

“SQL Server internally decided to use a new plan and generates a new plan based on multiple factors ”

Multiple ways to FORCE RECOMPILE

  1. Passing Parameter Values
    CREATE PROCEDURE <Procedure Name> <Parameter>
    WITH RECOMPILEAS

    BEGIN

    ……

    END

  2. Fixed Parameter Values

    CREATE PROCEDURE <Procedure Name> <Parameter @AC_NO INTEGER= NULL>

    WITH RECOMPILE

    AS

    BEGIN

    ……

    END

  3. SQL Statement Query HintOPTION(RECOMPILE)

As we have learnt what recompile does let’s do a demo to prove what is said. In this demo I am using [AdventureWorks] database.

  • SQL statement without RECOMPILE (Passed multiple parameter values to check how many plans are generated and cached)

    DECLARE @P NUMERIC(10,2)

    SELECT @P=SUM(LINETOTALFROM [SALES].[SALESORDERDETAIL] A
    JOIN [SALES].[SALESORDERHEADER] B
    ON A.SALESORDERID=B.SALESORDERID WHERE A.ORDERQTY=1 –-2 –3
    GROUP BYA.SALESORDERID


Executing the T-SQL again with the same parameters to check the reuse of plan

Execute the SP or T-SQL using Recompile and see if you get the cached plan. The plan will not be cached for next time use.

Categories: Uncategorized