Bookmark : CPU Pressure

Nagaraj Venkatesan has provided a great article on finding CPU Pressure from a DMV query.

Link : Finding CPU Pressure using waitstats

Bookmark : SQL Server DMV Starter Pack E-Book Published

The SQL Server DMV Starter Pack is a download comprising of an E-Book & Scripts for 28 DMV based queries. It has been put together by Glenn Berry, Louis Davidson and Tim Ford and is available as another freebie from Redgate

The 28 queries it provides, are -

Connections, Sessions, Requests, Queries

1: Are you Connected?
2: Session Ownership
3: Current expensive, or blocked, requests
4: Query Stats – Find the "top X" most expensive cached queries
5: How many single-use ad-hoc Plans?
6: Ad-hoc queries and the plan cache
7: Investigate expensive cached stored procedures
8: Find Queries that are waiting, or have waited, for a Memory Grant

Transactions

9: Monitor long-running transactions
10: Identify locking and blocking issues

Databases and Indexes

11: Find Missing Indexes
12: Interrogate Index Usage
13: Table Storage Stats (Pages and Row Counts)
14: Monitor TempDB

Disk I/O

15: Investigate Disk Bottlenecks via I/O Stalls
16: Investigate Disk Bottlenecks via Pending I/O

Operating System

17: Why are we Waiting?
18: Expose Performance Counters
19: Basic CPU Configuration
20: CPU Utilization History
21: Monitor Schedule activity
22: System-wide Memory Usage
23: Detect Memory Pressure
24: Investigate Memory Usage Across all Caches
25: Investigate memory use in the Buffer Pool

Other Useful DMVs

26: Rooting out Unruly CLR Tasks
27: Full Text Search
28: Page Repair attempts in Database Mirroring

Redgate : Download SQL Server DMV Starter Pack

Bookmark : Who is Active? script

An excellent tool for DBAs, 'Who is Active'.
The script itself is quite impressive and is a lot more helpful than sp_who !

Create it in the master database, and run it by >

exec sp_WhoIsActive

Link : Adam Machanic : Who Is Active v9.57

Free Training on SQL Server DMVs

On Wednesday 3rd March , Courtesy of Quest ... http://www.vconferenceonline.com/shows/spring10/quest/register/multireg.asp

Dynamic Management Objects : sys.dm_exec_query_stats

Sys.dm_exec_query_stats is a dmv (dynamic management view) which stores summary information about queries in the query cache.

Sys.dm_exec_sql_text(sql_handle) is a function that returns the executed command from sql_handle.

Putting them together with CROSS APPLY (APPLY lets you join the output of a function), you can see what is being run and how often.

SELECT t.text , s.*
FROM sys.dm_exec_query_stats  s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t 
WHERE t.text NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

This provides you 35 columns summarising query activity and includes counts, times, reads, writes statistics.

SQLDenis has provided a great query, which i've slightly adapted to order by the the most commonly executed queries.
It tells you where the sql is called from (ProcedureName) or replaces it with 'Ad-hoc' if not called from a procedure.

SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcedureName,execution_count,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

SQLDenis's original post >http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/finding-out-how-many-times-a-table-is-be-2008

MSDN Reference : http://msdn.microsoft.com/en-us/library/ms189741.aspx

Dynamic Management Objects : sys.dm_db_index_usage_stats

Sys.dm_db_index_usage_stats is a really helpful view that returns information on index usage.

Using AdventureWorks2008, we can list the indexes belonging to the TransactionHistory table ...

select * from sys.indexes
where object_id = object_id('production.transactionhistory')

Here we find out how they have been used ...

select i.name, s.* from sys.dm_db_index_usage_stats s
inner join sys.indexes i
on s.object_id = i.object_id
and s.index_id = i.index_id
where database_id = DB_ID('adventureworks2008')
and s.object_id = object_id('production.transactionhistory')

The view returns counts of seeks, scans and lookup operations as well as timestamps for the latest ones ...



This view is utilised by my Unused index and Index Sizes script as well as this simple example showing index usage in the current database.

Louis Davidson has an excellent article here >
http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx

The MSDN reference is here >
http://msdn.microsoft.com/en-us/library/ms188755.aspx

Data in the Buffer Cache

I found this script here > http://itknowledgeexchange.techtarget.com/sql-server/tag/syspartitions/
(Thank you Denny Cherry)

In my case, the script shows very low percentages, as our data moves very fast, expiring the cache all the time...

SELECT sys.tables.name TableName,
sum(a.page_id)*8 AS MemorySpaceKB,
SUM(sys.allocation_units.data_pages)*8 AS StorageSpaceKB,
CASE WHEN SUM(sys.allocation_units.data_pages) <> 0 THEN SUM(a.page_id)/CAST(SUM(sys.allocation_units.data_pages) AS NUMERIC(18,2)) END AS ‘Percentage Of Object In Memory’
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id = sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
AND sys.allocation_units.container_id = sys.partitions.hobt_id)
OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id = sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
AND sys.tables.is_ms_shipped = 0
WHERE a.database_id = DB_ID()
GROUP BY sys.tables.name

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

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

DMV Performance Counters - Buffer Cache hit ratio example

Demonstrates the dmv, sys_dm_os_performance_counters.
Returns a single value, i.e. the buffer cache hit ratio.
This represents how well pages stay in buffer cache.
The closer the result is to 100%, the better.

Corrected from version here

This version -

1. includes the necessary join
2. will run on any server (wildcarded the server name)

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x
     FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio'
      AND object_name like '%:Buffer Manager%') a
JOIN
  (SELECT *, 1 x
  FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
      AND object_name like '%Buffer Manager%') b
ON a.x = b.x