Perfmon : Monitoring File Fragmentation

Fragmentation seems like such a simple problem. Take systems offline and DEFRAGMENT the drive with any one of a number of free tools. In the SQL server world, hopefully you're presizing data/log files, eliminating the need for autogrowth and fragmentation in the first place.

Defraggler or Conrig.exe can both show the fragmentation status of files, without performing the defragementation hence you can see if a drive is fragmented.

You can tell if fragmentation is affecting your disk throughput by monitoring the following counters -
LogicalDisk\Split IO/sec or PhysicalDisk\Split IO/sec

Technet : Examining and Tuning Disk Performance

Contig.exe - File Defragmenter

I'm a fan of a free tool by Piriform called Defraggler. It is a visual degragmentation tool (like the OSs used to include!) Recently I had cause to investigate fragmentation on a very overloaded drive with hundreds of thousands of text files.

Defraggler took 45 minutes to build a picture of the drive, a wait I don't want to repeat. Given my primary concern is the sql data files, I wanted to view those results first. Enter 'contig', now on Technet - formerly part of the sysinternals project.

Running Contig without parameters helpfully tells you how to use it -

D:\MSSQL\Data>contig

Contig v1.6 - Makes files contiguous

Copyright (C) 1998-2010 Mark Russinovich

Sysinternals - www.sysinternals.com

Contig is a utility that defragments a specified file or files.

Use it to optimize execution of your frequently used files.

Usage:

contig [-a] [-s] [-q] [-v] [existing file]

or contig [-f] [-q] [-v] [drive:]

or contig [-v] -n [new file] [new file length]

-a: Analyze fragmentation

-f: Analyze free space fragmentation

-q: Quiet mode

-s: Recurse subdirectories

-v: Verbose

Contig can also analyze and defragment the following NTFS metadata files:

$Mft

$LogFile

$Volume

$AttrDef

$Bitmap

$Boot

$BadClus

$Secure

$UpCase

$Extend

To view fragmentation, use the -a switch like this...

D:\MSSQL\Data>contig -a *.mdf

Contig v1.6 - Makes files contiguous

Copyright (C) 1998-2010 Mark Russinovich

Sysinternals - www.sysinternals.com

D:\MSSQL\Data\Accounting.MDF is defragmented

D:\MSSQL\Data\Accounting_UAT.MDF is in 14 fragments

D:\MSSQL\Data\AuditPC.mdf is in 7 fragments

D:\MSSQL\Data\DataStore.mdf is in 34 fragments

D:\MSSQL\Data\FakeDb.MDF is in 5 fragments

D:\MSSQL\Data\Personel.mdf is in 4 fragments

D:\MSSQL\Data\master.mdf is in 3 fragments

D:\MSSQL\Data\model.mdf is in 2 fragments

D:\MSSQL\Data\msdbdata.mdf is in 5182 fragments

D:\MSSQL\Data\northwnd.mdf is in 2 fragments

D:\MSSQL\Data\pubs.mdf is in 2 fragments

D:\MSSQL\Data\Software.mdf is in 4 fragments

D:\MSSQL\Data\Software_UAT.mdf is in 9 fragments

D:\MSSQL\Data\Telecoms.mdf is in 17 fragments

D:\MSSQL\Data\tempdb.mdf is in 42 fragments

D:\MSSQL\Data\tools.mdf is in 5 fragments

D:\MSSQL\Data\Weblogs.MDF is in 89 fragments

Summary:

Number of files processed : 17

Average fragmentation : 176.03 frags/file

Pretty obviously, the above is BAD. Fragmentation of the SQL datafiles on the drive caused by autogrowth. Oh, and the filenames have been changed in the example above for confidentiality. I wouldnt contemplate mixing all those systems in the real world :)

Finding Transactions / Second

The Performance Counters dmv can be used to determine Transactions per second.
Transactions is a cummulative counter however, so here is what I do to interpret it.

Use the script below, replacing 'database_name' as appropriate.
You may wish to change the delay (10s used here) to a more appropriate value (remembering to change the calculation that follows too).

SELECT cntr_value, *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'database_name'
  
WAITFOR DELAY '00:00:10'
  
SELECT cntr_value, *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'database_name'

The queries returned 581820652 and 581821012 respectively.

581821012 - 581820652 = 360

360 Transactions in a 10 second period

Therefore, 360 /10 = 36

36 Transactions per second.

Bookmark : Project Lucy

Quest Software have launched Project Lucy to analyse SQL Profiler trace (.trc) files.

Get their opinion on your servers for free at https://www.projectlucy.com/

Blatent Plug : SQLWorkshops.com - Free Performance Monitoring/Tuning Webcasts

There is a lot of good training material and video blogs for people wishing to further their SQL skills.
Pragmatic Works (for BI) , Cuppa Corner from SQLServerFAQ are my favourites, as well as community webcasts from Quest and Redgate.

Most of  the free stuff simply gets you going. It isn't rocket science, it just saves you an hour or so reading (another manual). Ramesh Meyyappan's SQLWorkshops site is different. 
The videos are still FREE , But they are 'Level 400' (from attending conferences I know this to mean 'the clever stuff'! ) Anyway, if you're interested in performance monitoring and tuning you can download them from http://www.sqlworkshops.com/webcast.
 
Ramesh Meyyappan attended SQLBits V where I saw him present his 'Let's make SQL fly' talk.
After presenting 'Monitoring & Tuning Parallel Query Execution' at SQLBits VI he is back for more on October 2nd to present 'Monitoring & Tuning Parallel Query Execution - Part II at SQLBits VII

* (yes I was encouraged to write this post due to a potential freebie t-shirt, but I do genuinely rate the webcasts)

rich

SQL 101 : Tempdb

This post explains exactly what tempdb is responsible for -

Iain Kick - Not another tempdb post

And what to do to tune it, namely -

1) RAID level (& separate version)
2) Instant File Initialization
3) Trace Flag T1118
4) Pre Size tempdb
5) Split tempdb per processor core

Long Running Queries

A colleague sent me this to show what is currently running.
Look at the WHERE clause to adjust it for the duration threshold you are interested in.

SELECT
    r.session_id
  , p.kpid
  , r.start_time
  , DATEDIFF(SECOND, r.start_time, GETDATE()) as elapsed_time
  , st.text
  , r.status
  , r.command
  , r.cpu_time
  , r.wait_type
  , DB_NAME(r.database_id)
  , p.hostname
  , qp.query_plan
FROM
    sys.dm_exec_requests AS r
    INNER JOIN sys.sysprocesses AS p on r.session_id = p.spid
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE session_id > 50
AND DATEDIFF(SECOND, r.start_time, GETDATE())  > 10 -- duration in seconds
ORDER BY r.start_time

From Measure TSQL Statement Performance , this query provides performance statistics for cached query plans.

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Fast Delete !

Performance wise, a very fast delete can be achieved using a VIEW utilising an ORDER BY clause!!!

Create the view -

CREATE VIEW [dbo].[del_data] as
SELECT TOP(500) * FROM dbo.data WHERE id < 219150348 ORDER BY id
GO

Run a while loop to delete the data effeciently in batches -

WHILE(1=1)
BEGIN
DELETE dbo.del_data
IF @@ROWCOUNT < 500 BREAK
END

Link : SQLCAT - Fast ordered delete

Bookmark : Performance Impact of Profiler Tracing

It's widely known that setting up tracing via sql scripts is far more desirable than using Profiler and watching in real time.

Linchi Shea has taken the trouble of posting on exactly that subject >

Linchi Shea : Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Bookmark : Kevin Kline video on query tuning

SqlServerPedia : Kevin Kline video on query tuning

Gail Shaw's JOIN Performance Testing (of March 2010)

SQL MVP Gail Shaw does some performance testing on comparing join types, with surprising results.
A brief summary of her findings with her final quotes for each page >

IN vs INNER JOIN (IN wins by a nose)

"If all you need is to check for matching rows in the other table but don’t need any columns from that table, use IN. If you do need columns from the second table, use Inner Join."

EXISTS vs IN (little difference)

"IN and EXISTS appear to perform identically both when there are no indexes on the matching columns and when there are, and this is true regardless of whether of not there are nulls in either the subquery or in the outer table."

NOT EXISTS vs NOT IN   (no difference)

"On non-nullable columns, the behaviour and performance of NOT IN and NOT EXISTS are the same, so use whichever one works better for the specific situation."

LEFT OUTER JOIN vs NOT EXISTS

"If you need to find rows that don’t have a match in a second table, and the columns are nullable, use NOT EXISTS. If you need to find rows that don’t have a match in a second table, and the columns are not nullable, use NOT EXISTS or NOT IN."

Finding Page Splits

Finding Page splits by using undocumented function fn_dblog (this queries the transaction log) -

SELECT *
FROM ::fn_dblog(NULL, NULL)
WHERE operation = 'LOP_DELETE_SPLIT'

ref ; http://killspid.blogspot.com/2006/07/using-fndblog.html

You can summarise them like this -

Select COUNT(1) AS NumberOfSplits, AllocUnitName , Context
From fn_dblog(NULL,NULL)
Where operation = 'LOP_DELETE_SPLIT'
Group By AllocUnitName, Context
Order by NumberOfSplits desc 

ref - Identifying Page Splits


Other ways to monitor  page splits -

• DBCC SHOWCONFIG
• Extended events (SQL 2008+ only)

Recommended links about Page Allocation

MS CSS Sql Server Engineers : How It Works: SQL Server Page Allocations

Recommended links about Page Splits

Tony Rogerson : What is a page split and why does it happen?
SQL Server Performance : At what point should I worry about page splits?
Michelle Ufford : Page Splitting & Rollbacks
Michelle Ufford : sys.fn_physLocCracker (SQL 2008 Undoumented function)

Bookmark : Schemabinding

If you are going to oncur the row by row processing overhead of a scalar user defined function (UDF) , you may as well investigate SCHEMABINDING...

MSDN : Improving query plans with the SCHEMABINDING option on T-SQL UDFs

Improving SQL Performance

Excellent document on MSDN on Improving SQL Performance...

http://msdn.microsoft.com/en-us/library/ms998577.aspx


Found it via Jose Barreto's Blog (full of excellent SQL content btw) ...

http://blogs.technet.com/josebda/archive/2009/03/19/book-chapter-on-improving-sql-server-performance.aspx

I/O Delays

" SQL Server has encountered n occurrence(s) of I/O requests taking longer than 15 seconds to complete on file d:\path\datafile.mdf "

This is SQL's way of saying the I/O Subsystem is not coping with the data throughput.

" When you see this message the first action should still be to have a look at the physical disk counters in sysmon to ensure that the disks are servicing IOs in a reasonable period of time. If those appear to fine then start looking at what filter drivers might be installed on your system, and if there are any known issues with them, or disable them if you don’t need them. "

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx

SQL 2005 : Index Performance

Index Types from Fastest > Slowest

[1] Non-Clustered Covering with Included non-key columns
[2] Non-Clustered Covering
[3] Clustered
[4] Non Clustered Non Covering
[5] No Index

therefore, for a Non-Clustered Index with Included non-Key columns >

CREATE INDEX nci_IndexName
ON TABLE (keycolumn_1,keycolumn_2)
INCLUDE (nonkeycolumn_1,nonkeycolumn_2)

Using Missing Indexes DMVs to generate index suggestions

I came across this today.
It uses the missing_indexes dmvs to recommend where indexes could be added.
Have modified it to include the table schema.

SELECT     'CREATE NONCLUSTERED INDEX NewNameHere ON ' + sys.schemas.name + '.' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL 
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, 
mid.included_columns
FROM         sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN   sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
INNER JOIN   sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
INNER JOIN   sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
INNER JOIN   sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id 
WHERE     (migs.group_handle IN
(SELECT     TOP 100 PERCENT group_handle
FROM          sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND sys.objects.type = 'U'

Original Piece


MSDN : Using Missing Index Information to Write CREATE INDEX Statements


Brian Knight's blog on the Missing Index DMV

Forcing Query Plan Choice with USE PLAN hint

USE PLAN can be used to bypass SQL's choice of query plan.
Microsoft white paper here...
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

Finding the Fragmentation of an Index and fixing it

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/finding-fragmentation-of-an-index-and-fi

(Does what is says on the tin really)

Syscacheobjects (Sql query plan reuse)

Viewing the contents of the Sql cache (and how many times plans have been reused).

select cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects