Category Archives: Performance-Scripts

Useful scripts to troubleshoot performance issues

Number of Logins and Sessions

Performance-Scripts

.


/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry

DESCRIPTION:  List number of logins and their sessions

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
WHERE session_id > 50    -- filter out system SPIDs
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

.

CPU Usage

Performance-Scripts

.



/*

 SQL Server 2008 and R2 Diagnostic Information Queries
 Glenn Berry
 June 2012
 Last Modified: June 19, 2012
 http://sqlserverperformance.wordpress.com/
 http://sqlskills.com/blogs/glenn/
 Twitter: GlennAlanBerry

DESCRIPTION:  List CPU usage for of all files for all databases

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000
 Get total buffer usage by database for current instance  (Query 18)
 This make take some time to run on a busy instance

Update Log:

*/

select
DB_NAME(database_id) AS [Database Name]
, COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
from sys.dm_os_buffer_descriptors WITH (NOLOCK)
where database_id > 4 -- system databases
and database_id <> 32767 -- ResourceDB
group by DB_NAME(database_id)
order by [Cached Size (MB)] DESC OPTION (RECOMPILE);
 
/*
Tells you how much memory (in the buffer pool)
is being used by each database on the instance
*/

.

Location of Data files and Tlog

InTheFileSystem, Performance-Scripts, Reports

. 


/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry


DESCRIPTION:  List location of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

SELECT

DB_NAME([database_id])AS [Database Name]

,   [file_id]

, name

, physical_name

, type_desc

, state_desc

,  CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

.

IO Usage by data file

Performance-Scripts

. 

/* SQL Server 2008 and R2 Diagnostic Information Queries
 Glenn Berry
 June 2012
 Last Modified: June 19, 2012
 

DESCRIPTION:  List IO usage for of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues and identify the busiest DB

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

--Calculates average stalls per read, per write, and per total input/output for each database file  
SELECT
DB_NAME(fs.database_id) AS [Database Name]
, mf.physical_name
, io_stall_read_ms
, num_of_reads
, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms]
, io_stall_write_ms
, num_of_writes
, CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms]
, io_stall_read_ms + io_stall_write_ms AS [io_stalls]
, num_of_reads + num_of_writes AS [total_io]
, CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]

FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

-- Helps you determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location

.

Last Autogrowth Event

InsideDatabases, InTheFileSystem, Performance-Scripts

. 


/*
DESCRIPTION:    Queries the default trace to find out last AUTOGROWTH event

The default trace should be enabled by, well, default

Check if it is enabled:  select status, path from sys.traces where is_default = 1

How far back does it report?

The default trace creates up-to 5 files, 20 mb in size. So depending on how busy the server is, it could be 20 mins or days



Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:
 
*/

 
 
Use master
go
 
declare @curr_tracefilename varchar(500) ;  
declare @base_tracefilename varchar(500) ;  
declare @indx int ;
 
select @curr_tracefilename = path from sys.traces where is_default = 1 ;  
 
set @curr_tracefilename = reverse(@curr_tracefilename);
 
select @indx = patindex('%\%', @curr_tracefilename) ;
 
set @curr_tracefilename = reverse(@curr_tracefilename) ;
 
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;  
 
 
select  
 loginname
 , spid
 --,convert(int, EventClass) as EventClass,  
,DatabaseName
,[Filename]
,Duration
, StartTime
, EndTime
, (IntegerData*8.0/1024) as ChangeInSize
, e.name
, RIGHT(@curr_tracefilename,@indx) as curr_tracefilename
 
from ::fn_trace_gettable(@base_tracefilename, default ) a
INNER JOIN sys.trace_events e    ON eventclass = trace_event_id

where  
            e.name = 'Log File Auto Grow'
        OR  e.name = 'Log File Auto Shrink'
        OR  e.name = 'Data File Auto Grow'
        OR  e.name = 'Data File Auto Shrink'
 
order by StartTime desc ;  
GO

.

Statistics last Updated

index-fragmentation, Performance-Scripts 

/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry

DESCRIPTION: Statistics last udpate

 


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

-- When were Statistics last updated on all indexes?  (Query 48)
SELECT o.name, i.name AS [Index Name],  
      STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
      s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);

Check Fragmentation Levels

index-fragmentation, Performance-Scripts

Fragmentation levels.  Look for the pages count over 1,000 clause. 


/*
DESCRIPTION:
Check fragmentation levels
 
CONFIGURATION
None

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008

Does not work
MSSQL2000
 
*/

 USE yourdatabase

SELECT t.name as TableName, b.name as IndexName, b.index_id, ps.avg_fragmentation_in_percent, PAGE_COUNT AS PageCounts
, STATS_DATE( b.object_id , b.index_id ) AS LastUpdatedDate
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.tables AS t on ps.OBJECT_ID = t.OBJECT_ID
AND ps.index_id = b.index_id
WHERE 1=1
and ps.database_id = DB_ID()
and page_count > 1000        -- ignore small tables
and b.index_id > 0            -- ignore heaps
and ps.avg_fragmentation_in_percent > 10  -- ignore small fragmentation
ORDER BY t.name, b.name

If receives error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near (. you will have to fill in with the actual Database id and plug in that value. See below 


USE [yourdatabase]
SELECT DB_ID() AS [Database ID];
GO

How long backups will take

Backups, Performance-Scripts, restore

Very simple script that queries a DMV to get an estimate of backup duration 

/*
DESCRIPTION:    Queries the dm_exec requests and gives you an estimate of how long a backup/restore request is going to take
Configuration:    No Configuration needed
 
Compatibility list:
MSSQL2005 MSSQL2008  MSSQL2008R2 MSSQL2012

DOES NOT WORK MSSQL2000
Update Log:
*/
USE MASTER
SELECT
session_id as SPID
, r.command
, a.text AS Query
, start_time
, percent_complete
, dateadd(second,estimated_completion_time/1000, getdate())
                       as estimated_completion_time
, estimated_completion_time/1000/60 as time_left_mins
, r.blocking_session_id
, r.wait_type
FROM SYS.DM_EXEC_REQUESTS r
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(r.sql_handle) a
WHERE 1=1
and r.command in
('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE LOG')