Last Accesses Stored Procedure

SELECT  a.execution_count ,
    OBJECT_NAME(objectid) Name,
    query_text = SUBSTRING(
    b.text,
    a.statement_start_offset/2,
    ( CASE WHEN a.statement_end_offset = -1
    THEN len(convert(nvarchar(max), b.text)) * 2
    ELSE a.statement_end_offset
    END - a.statement_start_offset)/2
    ) ,
    b.dbid ,
    dbname = db_name(b.dbid) ,
    b.objectid ,
    a.creation_time,
    a.last_execution_time,
    a.*
FROM     sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) like 'sp%'
ORDER BY a.last_execution_time DESC

Insert Rows into Columns in SQL Server

DECLARE @i INT,@SQL VARCHAR(1000),@ColVal varchar(1000);
DECLARE RowDataToColumn CURSOR FOR

SELECT Testing FROM Test1
FOR READ ONLY

OPEN RowDataToColumn
FETCH NEXT FROM RowDataToColumn INTO @colval

SELECT @SQL = 'INSERT INTO Test2 VALUES(',
@i = 0

WHILE @@FETCH_STATUS = 0
BEGIN
if @i = 0 -- First append
SET @SQL = @SQL + '''' + @colval + ''''
else
SET @SQL = @SQL + ', ''' + @colval + ''''

FETCH NEXT FROM RowDataToColumn INTO @colval
SET @i = @i + 1
END
CLOSE RowDataToColumn
DEALLOCATE RowDataToColumn

SET @SQL = @SQL + ')'

EXEC(@SQL) -- This executes insert

Script to collect the wait stats:

-- Isolate top waits for server instance since last restart or statistics clearWITH Waits AS(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rnFROM sys.dm_os_wait_statsWHERE wait_type NOT IN('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))SELECT W1.wait_type,CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,CAST(W1.pct AS DECIMAL(12, 2)) AS pct,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1INNER JOIN Waits AS W2ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage thresholdGO

This dynamic management view collects all the information since the time when the SQL Server services have been restarted. You can also manually clear the wait stats using the following command:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Retrieve Date Using Date Fortmat

DECLARE @StartMonth INT,@StartYear VARCHAR(4),@EndMonth INT,@EndYear varchar(4);
SET @StartMonth = 7
SET @StartYear = 2001
SET @EndMonth = 06
SET @EndYear = 2004

SELECT SpecialOfferID,[Description],StartDate,EndDate
FROM Sales.SpecialOffer
WHERE MONTH(StartDate) = @StartMonth AND YEAR(StartDate) = @StartYear
AND MONTH(EndDate) = @EndMonth AND YEAR(EndDate) = @EndYear

SQL Server Logical Reads - What's it?

--SQL Server Logical Reads - What's it?

Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

--Create table objects

CREATE TABLE TblLogicalReads
(
TranId INT,
TrnData VARCHAR(100),
TrnDate DATETIME
)
GO



--After creating objects, 49999 records will be inserted by following scripts,


DECLARE @cnt BIGINT
SET @cnt  = 1
WHILE (@cnt < 50000)
BEGIN
INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END
GO

--Now we are checking logical reads for the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
TranId,
TrnData,
TrnDate
FROM TblLogicalReads
WHERE TranId = 5

SELECT
TranId,
TrnData,
TrnDate
FROM TblLogicalReads
WHERE TrnDate = '2009-12-26 18:10:47.653'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO


-- Creating indexes on tables to reduce it

CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO



CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO

Task Progress

--This query can be executed with any database context. This is written to show the estimated time in hrs, min and sec and also shows percentage of completion. This is quite helpful to check the backup progress, restore progress, integrity check progress, rebuild index task progress etc.

select
convert (varchar(50),(estimated_completion_time/3600000))+'hrs'+
convert (varchar(50), ((estimated_completion_time%3600000)/60000))+'min'+
convert (varchar(50), (((estimated_completion_time%3600000)%60000)/1000))+'sec'
as Estimated_Completion_Time,
status, command, db_name(database_id), percent_complete
from sys.dm_exec_requests

Restore DB

RESTORE DATABASE [Test] FROM  DISK = N'D:\Testing' WITH  FILE = 1,
MOVE N'Testing' TO N'D:\Data\Test.mdf',
MOVE N'Fg1' TO N'D:\Data\Fg1.ndf',
MOVE N'Fg2' TO N'D:\Data\Fg2.ndf',
MOVE N'Testing_log' TO N'D:\Data\Test.LDF',
NOUNLOAD,  REPLACE,  STATS = 10
GO