Feb201005

Alex

0

How to find missing indexes in SQL Server 2005 and 2008

SELECT
[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
[Table] = [statement],
[CreateIndexStatement] = ‘CREATE NONCLUSTERED INDEX ix_’
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”)+ISNULL(mid.inequality_columns,”), ‘[', ''), ']‘,”), ‘, ‘,’_’)
+ ‘ ON ‘
+ [statement]
+ ‘ ( ‘ + IsNull(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 [...]

Feb201001

Alex

0

CLEAN UP ONLY ADHOC SQL CACHE

————————————
–    CLEAN UP ONLY ADHOC SQL CACHE
———————————–
declare @size_in_MBs int
select @size_in_MBs = (select size_in_MBs from
(select
objtype,
count(*)as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts)as avg_use_count
from sys.dm_exec_cached_plans
group by objtype) as AXA1
where objtype = ‘Adhoc’)
select @size_in_MBs
if @size_in_MBs > 200
dbcc freesystemcache (’SQL Plans’)

Feb201001

Alex

0

Cache and Performance - AdHoc Queries and Proc Cache

—————————————
–    STORED PROCEDURE CACHE ALLOCATION
—————————————
SELECT CAST(name AS VARCHAR(28)) AS ‘Object’,
single_pages_kb AS ‘Cache in Kb’,
entries_count AS ‘Num of entries’
FROM sys.dm_os_memory_cache_counters
WHERE type in (’CACHESTORE_SQLCP’, ‘CACHESTORE_OBJCP’, ‘CACHESTORE_PHDR’, ‘CACHESTORE_XPROC’)
—————————————
–    DATABASE BUFFER CACHE ALLOCATION
—————————————
SELECT count(*) AS cached_pages_count
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
—————————————
–    CACHED PAGES FOR CURRENT DATABASE
—————————————
SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors [...]

Jan201029

Alex

0

Number of cached pages used by each database in SQL Server 2005

SELECT count(*) AS cached_pages_count
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
See:
http://msdn.microsoft.com/en-us/library/ms173442.aspx

Jan201029

Alex

0

sys.dm_os_memory_cache_clock_hands - Caches and memory pressure

Caches and memory pressure
An alternative way to look at external and internal memory pressure is to look at the behavior of memory caches.
One of the differences of internal implementation of SQL Server  2005  compared to SQL Server 2000 is uniform caching framework. In order to remove the least recently used entries from caches, the framework implements a [...]

Jan201026

Alex

0

Highest IO Queries in SQL Server

SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
substring (qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -    qs.statement_start_offset)/2)
as query_text,
qt.dbid,
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
ORDER BY [Avg IO] DESC

Jan201026

Alex

0

SQL Server Performance Counters Analaysis

Great document.
http://publib.boulder.ibm.com/tividd/td/ITMD/SC23-4852-00/en_US/HTML/mssql511rg02.htm#ToC_94

Jan201026

Alex

0

Dynamic Management Views - SQL Server 2005

http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx

Jan201026

Alex

0

SQL Server queries with DMVs for examining bottlenecks

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1265214,00.html
SELECT * FROM SYS.SYSPERFINFO WHERE
OBJECT_NAME=’SQLSERVER:BUFFER MANAGER’ AND
(COUNTER_NAME=’TARGET PAGES’ OR
COUNTER_NAME=’TOTAL PAGES’ OR
COUNTER_NAME=’DATABASE PAGES’ OR
COUNTER_NAME=’STOLEN PAGES’ OR
COUNTER_NAME=’FREE PAGES’)
Use the following DMV query to determine which SQL Server components are consuming the most amount of memory, and observe how this changes over time:
SELECT TYPE, SUM(MULTI_PAGES_KB) FROM
SYS.DM_OS_MEMORY_CLERKS WHERE
MULTI_PAGES_KB != 0 GROUP BY TYPE
This query will [...]

Jan201015

Alex

0

SQL Server - Performance Monitoring

Link to SQL Server - Performance Monitoring
http://www.sqlservercentral.com/articles/Administration/performancemonitoringbasiccounters/1348/

« Older Entries