Archive

Author Archive

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

Find Index Fragmentation in SQL Server

December 20, 2011 Leave a comment

Below is a script using the DMV’s to find fragmented tables in a SQL Server database instead of the old way using DBCC SHOWCONTIG.  Most DBA’s want to start at using these DMV’s and this script can be customized to your environment

SELECT
    DB_NAME(database_id) as DatabaseName,
    OBJECT_SCHEMA_NAME(ps.object_id) + '.' + OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
    ps.page_count as IndexPageCount,
    ps.avg_fragmentation_in_percent as IndexFragmentation,
    ps.avg_fragment_size_in_pages as IndexFragmentationPages,
    ps.partition_number as PartitionNumber,
    ps.index_type_desc as IndexDesc,
    ps.alloc_unit_type_desc as AllocUnitDesc,
    ps.index_depth as IndexDepth,
    ps.index_level as IndexLevel,
    i.[fill_factor] as [FillFactor],
    CASE i.index_id
        WHEN 1 THEN 1
        ELSE 0
        END as IsClustered
FROM 
sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') ps
INNER JOIN sys.indexes i
    ON ps.object_id = i.object_id 
    AND ps.index_id = i.index_id
WHERE ps.index_type_desc <> 'HEAP'          --Ignore HEAP tables
AND ps.page_count > 30                      --Ingore less than 30 pages
AND ps.avg_fragmentation_in_percent > 30    --Fragmentation Threshold
ORDER BY TableName, IsClustered DESC, IndexName
Categories: indexes

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

Find Tables That Are Missing PK or Clustered Index

November 14, 2011 Leave a comment

I had a need to look for tables that violate company standards that every table must have a Primary Key as well as a Clustered Index. I put together a script that gives you each table name, whether it has a PK or not and whether it has a clustered index or not. You could add a where clause to only show those that violate one of these rules but the script as is shows all tables

SELECT 
    c.name + '.' + b.name as ObjectFullName, 
    'Y' as HasPrimaryKey,
    CASE i.index_id 
        WHEN '0' THEN 'N' 
        WHEN '1' THEN 'Y' 
    END as HasClusteredIndex  
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
INNER JOIN sys.indexes i ON b.object_id = i.object_id and i.index_id IN (0,1)  
WHERE OBJECTPROPERTY(b.object_id, 'IsUserTable') = 1
AND EXISTS --Has PK
(
    SELECT a.name  
    FROM sys.key_constraints a  
    WHERE a.parent_object_id = b.OBJECT_ID  
    AND a.schema_id = c.schema_id  
    AND a.type = 'PK'
)
UNION 
SELECT 
    c.name + '.' + b.name as ObjectFullName, 
    'N' as HasPrimaryKey,
    CASE i.index_id 
        WHEN '0' THEN 'N' 
        WHEN '1' THEN 'Y' 
    END as HasClusteredIndex 
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
INNER JOIN sys.indexes i ON b.object_id = i.object_id and i.index_id IN (0,1)  
WHERE OBJECTPROPERTY(b.object_id, 'IsUserTable') = 1
AND NOT EXISTS --No PK
(
    SELECT a.name  
    FROM sys.key_constraints a  
    WHERE a.parent_object_id = b.OBJECT_ID  
    AND a.schema_id = c.schema_id  
    AND a.type = 'PK'
)
ORDER BY HasPrimaryKey, HasClusteredIndex, ObjectFullName
Categories: sql

Find Tables that can cover more than a SQL Server Page per ROW

November 14, 2011 Leave a comment

I was looking around this morning for a way to see if a table has columns that whose max values would be larger than a 8K SQL Server Page causing 1 row of data to span multiple pages in SQL Server.  Within the database everything is managed using Pages which are 8192 bytes (8 X 1024) or 8KB.  I was playing with some of the catalog views within SQL Server 2008 and came up with the following script and wanted to share it with everyone.  The data you can hold on 1 SQL Server page is 8060 bytes (rest is used internally) so this script gets the MAX length of each column in a table, sums it up and displays a ‘Y’ or ‘N’ flag if that table has the possibility of one row spanning multiple pages:

SELECT
    OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()) AS SchemaName,
    tab.name AS TableName,
    part.rows as NumberRows,
    SUM(col.max_length)  as MaxRowLengthBytes,
    CASE    WHEN SUM(col.max_length) > 8060 THEN 'Y'
            WHEN SUM(col.max_length) <= 8060 THEN 'N'
        ELSE 'UNKNOWN'
    END AS LargerThanPageFlag
FROM sys.tables tab
    INNER JOIN sys.all_columns col
        ON tab.object_id = col.object_id
    INNER JOIN sys.types datatypes
        ON col.system_type_id = datatypes.system_type_id
        AND col.user_type_id = datatypes.user_type_id
    INNER JOIN sys.partitions part
        ON part.object_id = tab.object_id
        AND part.index_id IN (1,0) --Used to get row counts
WHERE tab.is_ms_shipped = 0
GROUP BY OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()), tab.name, part.rows
ORDER BY MaxRowLengthBytes desc
Categories: sql

SQL Server DMV’s

November 2, 2011 Leave a comment

I have been focused a good bit lately into diving into the DMV’s to make my job easier.  Below are some useful ones that I use on a frequent basis.

Dymanic Management Views
sys.dm_exec_sessions (Current sessions)
sys.dm_exec_connections (Current Connections)
sys.dm_exec_requests (Currently executing requests)
sys.dm_exec_query_stats (previously executed SQL that exists in Plan Cache)
sys.dm_exec_cached_plans (Plan Cache)

Dynamic Management Functions
sys.dm_exec_sql_text (Extracts readable SQL from the sql_handle
sys.dm_exec_query_plan (Extracts XML execution plan using plan_handle)

Here are a few examples how these can be used together

--Find Procedures consuming the most logical reads
SELECT 
    DB_NAME(st.dbid) DBName,
    OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName,
    OBJECT_NAME(objectid,st.dbid) StoredProcedure,
    max(cp.usecounts) execution_count,
    sum(qs.total_physical_reads) total_physical_reads,
    sum(qs.total_physical_reads) / max(cp.usecounts) avg_physical_reads,
    sum(qs.total_logical_reads) total_logical_reads,
    sum(qs.total_logical_reads) / max(cp.usecounts) avg_logical_reads
FROM sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    INNER JOIN sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
WHERE DB_NAME(st.dbid) is not null 
and cp.objtype = 'proc'
GROUP BY DB_NAME(st.dbid),
        OBJECT_SCHEMA_NAME(objectid,st.dbid), 
        OBJECT_NAME(objectid,st.dbid)
ORDER BY sum(qs.total_logical_reads) / max(cp.usecounts) desc
 
--Find reusability of plan cache (high usecounts)
SELECT 
    c.cacheobjtype , 
    c.objtype , 
    c.usecounts , 
    c.size_in_bytes , 
    t.dbid , 
    t.text 
FROM sys.dm_exec_cached_plans as c
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t
WHERE c.cacheobjtype = 'Compiled Plan'
ORDER BY c.usecounts DESC
Categories: sql