Archive

Archive for November, 2013

Viewing index fragmentation

November 7, 2013 Leave a comment

All tables in a given database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),null,null,null,null );

Single given table in a database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),OBJECT_ID('[table_name]'),null,null,'DETAILED' );
Advertisements

I/O pressure

November 7, 2013 Leave a comment
-- Read
  SELECT --TOP 20 
	qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',
    qs.execution_count AS 'Execution Count',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',  
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes,  
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid 
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_physical_reads DESC

-- Write
   SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
    qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',  
    qs.execution_count AS 'Execution Count', 
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_logical_writes DESC

 

CPU Pressure

November 7, 2013 Leave a comment
SELECT TOP 50 
      SUM(qs.total_worker_time) AS total_cpu_time, 
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements, 
      qs.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

Most “expensive” queries

November 7, 2013 Leave a comment
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(qt.TEXT)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2)+1),
 qs.execution_count,
 qs.total_logical_reads, qs.last_logical_reads,
 qs.total_logical_writes, qs.last_logical_writes,
 qs.total_worker_time,
 qs.last_worker_time,
 qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
 qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
 qs.last_execution_time,
 qp.query_plan
 FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 ORDER BY qs.total_logical_reads DESC

Transaction log file information

November 7, 2013 Leave a comment

General T-Log info

all databases

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.master_files where type_desc = 'LOG';

given database

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.database_files where type_desc = 'LOG';

detailed transaction log info

select * from ::fn_dblog(null, null);

Check VLFs

November 7, 2013 Leave a comment

given database

DBCC LogInfo;

all databases on the server

declare @v_sql nvarchar(max);
select @v_sql = coalesce(@v_sql + CHAR(13) + char(10), '') + N'
use [' + name + '];' + CHAR(13) +
'DBCC LogInfo;'
from sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');
print @v_sql
exec sp_executesql @v_sql
Categories: Handy Scripts Tags:

Shrink transaction log file(s)

November 7, 2013 Leave a comment

Sometimes there is an urgent need to shrink transaction log file/s of a single or multiple database/s which has/have grown to an extent that threatens to leave to free space on the logical drive, which would lead to entire database server operation freeze up.

The “quick-and-dirty” solution in such situation is to quickly shrink the t-log file/s. There are two methods of doing this. First one would simply attempt the file shrink operation. Second option is to back this/these file/s up first. Shrinking transaction log files without prior backing them up (first method) might or might not┬ádo the job – depending on what part of this/these file/s is marked “used”. If the tail of the transaction log file (latter VLFs) is marked as used than the first method wouldn’t work, i.e. the files won’t get shrank in size and won’t release the used space back to the operating system, so the problem would persist. To take care of that, the second method is more preferable, given the┬áconstraints of time and free space elsewhere to accommodate the t-log backup file/s. In addition, it’s more safe to backup transaction log file/s prior to shrinking them in case of production database/s running in full recovery mode – just in case of DRP point-in-time scenario.

First method:

–single database

DBCC SHRINKFILE([TLog_LogicalFileName], 1)

–generic script – all databases

sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
AND (SELECT recovery_model FROM master.sys.databases WHERE name = ''?'') = 1
AND (SELECT is_read_only FROM master.sys.databases WHERE name = ''?'') = 0
BEGIN
declare @LogFile nvarchar(2000)
USE [?]
SELECT @LogFile = name
FROM sys.database_files
WHERE type = 1
DBCC SHRINKFILE (@LogFile, truncateonly)
END'

Second method:

(try to stick to it, at least for all production databases as well as any databases running in full recovery mode)

–single database

USE [Database_Name]
GO
BACKUP LOG [Database_Name] TO DISK = N'[path]\[file_name].trn'
WITH NOFORMAT, INIT, NAME = N'[Database_Name] - Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, [COMPRESSION*], COPY_ONLY, STATS = 1;
DBCC SHRINKFILE([TLog_LogicalFileName], 1)
GO

* [COMPRESSION] option is available in both Enterprise and Standard editions of SQL Server 2008R2, but only in Enterprise edition of SQL Server 2008

–generic, all databases

declare @SQL nvarchar(max);
declare @v_TLog_dir nvarchar(max);
declare @v_compression_YN nchar(1);
BEGIN
set @v_TLog_dir = 'C:\Temp';
set @v_compression_YN = 'Y';
select @SQL = coalesce(@SQL + char(13) + char(10),'') + N'
Use [' + d.name + '];' + CHAR(13) +
'BACKUP LOG ' + d.name + ' TO DISK = N''' + @v_TLog_dir + '\' + mf.name + '.trn'' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD' +
case @v_compression_YN
when 'Y' then
', COMPRESSION, COPY_ONLY;'
when 'N' then
', COPY_ONLY;'
end + CHAR(13) +
'DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1) WITH NO_INFOMSGS;'
FROM sys.databases d INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name not in ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')
--AND d.recovery_model_desc = 'FULL'
AND d.is_read_only = 0
AND mf.type_desc = 'LOG'
ORDER BY d.name;
--print @SQL;
exec sp_executesql @SQL;
END;

062311_1657_SQLServerse2.png

NOTE: It’s highly recommended to comment out “exec sp_executesql @SQL;” and uncomment “–print @SQL;” lines respectively to first review the result. Then the lines could be swapped or, simply, the output copied over to new query window and ran there.