Archive

Archive for the ‘Uncategorized’ Category

Backup/Restore Time Estimations

January 19, 2012 Leave a comment

Often times I have seen people run through the GUI in SSMS to perform backup or restore operations only to see the spinning wheel and not knowing how much longer or what percentage complete the command is at.  I stumbled across this script a few years ago and use it quite often to determine time left for a command and percentage complete.  It also works for other operations such DBCC CHECKDB.


use master
go

SELECT
percent_complete,
start_time ,
command,
b.name AS DatabaseName,
DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,
(estimated_completion_time/1000/60) AS MinutesToFinish
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b
ON a.database_id = b.database_id
WHERE command like '%restore%'
or command like '%Backup%'
AND estimated_completion_time > 0

Categories: Uncategorized

TEMPDB Temp tables vs Permanent tables

December 8, 2011 Leave a comment

I was testing some code on a client site this past week where we were populating temp tables and when I unit tested it, it ran in 15 minutes. Then we decided to make them permanent tables within TEMPDB and the process ran for over an hour. I posted some questions to the SQL Server Community and was able to get a resolution thanks to Paul White (http://sqlblog.com/blogs/paul_white) and Jorge Segarra (www.sqlchicken.com)and below was the original script

--15 minutes
CREATE TABLE #TWPA
(
	AccountEntity  VARCHAR(3) NOT NULL,
	AccountProductCD  VARCHAR(3) NOT NULL,
	AccountNum  VARCHAR(21) NOT NULL,
	PartyID VARCHAR(11) NOT NULL,
	CONSTRAINT PK_#TWPA PRIMARY KEY CLUSTERED
	(
		AccountEntity,
		AccountProductCD,
		AccountNum,
		PartyID
	)
)
GO
INSERT INTO #TWPA

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows

GO


--Over an hour
CREATE TABLE tempdb.dbo.TWPA
(
	AccountEntity  VARCHAR(3) NOT NULL,
	AccountProductCD  VARCHAR(3) NOT NULL,
	AccountNum  VARCHAR(21) NOT NULL,
	PartyID VARCHAR(11) NOT NULL,
	CONSTRAINT PK_#TWPA PRIMARY KEY CLUSTERED
	(
		AccountEntity,
		AccountProductCD,
		AccountNum,
		PartyID
	)
)
GO
INSERT INTO tempdb.dbo.TWPA

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows


GO
</code>

The resolution is the permanent table in TEMPDB was not using minimal logging like the temp table did.  To resolve this I had to put WITH (TABLOCKX) into the insert statement and it ran in 15 minutes just like the temp table version

<code>
INSERT INTO tempdb.dbo.TWPA WITH (TABLOCKX)

	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table1		--9,109,553 rows
	UNION     
	SELECT AccountEntity, AccountProductCD, AccountNum, PartyID 
	FROM Table2		--137,696,784 rows


GO
Categories: Uncategorized

Find Worst Offending SQL from Cache

November 17, 2011 Leave a comment

I was performing an performance tuning analysis on a SQL Server for a client with very minimal permissions but high expectations (undesirable I know) so I was looking for a way to target what needed to be looked at for tuning opportunities and I decided to go look into the procedure cache to get the previously executed SQL statements in a way I could determine what the worst offenders where.  I wanted to share the script I came up with to do this

--Get Worst Offenders
select top 1000
      DB_NAME(txt.dbid) as DBName,
      cp.cacheobjtype, 
      cp.objtype,
      OBJECT_NAME(txt.objectid, txt.dbid) as SPName,
      SUBSTRING (txt.text,(r.statement_start_offset / 2) + 1,((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(txt.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) as QueryText,
      r.total_worker_time / r.execution_count as AverageWorkerTime,
      r.total_elapsed_time / r.execution_count as AverageDuration,
      r.total_logical_reads / r.execution_count as AverageLogicalReads,
      r.total_physical_reads / r.execution_count as AveragePhysicalReads,
      r.total_logical_writes / r.execution_count as AverageLogicalWrites,
      r.execution_count as ExecutionCount,
      r.total_worker_time as TotalWorkerTime,
      r.total_elapsed_time as TotalDuration,
      r.total_logical_reads as TotalLogicalReads,
      r.total_physical_reads as TotalPhysicalReads,
      r.total_logical_writes as TotalLogicalWrites,
      r.sql_handle as sql_handle,
      r.plan_handle as plan_handle
from sys.dm_exec_query_stats r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as txt
INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = r.plan_handle

--Only if it is a compiled object or it will be NULL
--WHERE DB_NAME(txt.dbid) = '<DatabaseName>' --Only if it is a compiled object (Proc or Trigger) or it will be NULL
--WHERE objtype = 'Prepared' --Use to find cetain Object Types (AdHoc, Prepared, Proc, etc)


--ORDER BY AverageDuration desc
Categories: Uncategorized

Welcome to my blog

I have just created my first blog for my love for the SQL Server technology.  I will be posting various SQL related topics, scripts, and links to useful information regarding SQL Server technology

Categories: Uncategorized