Tuning d'une base SQLServer 2005

zoltix

02/06/2009 à 11:26

On 2 juin, 08:26, "jerome" wrote:

Bonjour,

J'essaie de me servir des outils de tuning de Database Engine Tuning Advi sor
mais je ne trouve pas beaucoup d'informations.
Notamment celle me demandant dechoisir pour le Workload un fichier ou une
table.

Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c'es t
exactement et ce que je dois sélectionner ?

Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

jerome

02/06/2009 à 11:36

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés primaires
afin d'optimiser la base

Merci

"zoltix" wrote in message
news:
On 2 juin, 08:26, "jerome" wrote:

Bonjour,

J'essaie de me servir des outils de tuning de Database Engine Tuning

Advisor

mais je ne trouve pas beaucoup d'informations.
Notamment celle me demandant dechoisir pour le Workload un fichier ou une
table.

Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c'est
exactement et ce que je dois sélectionner ?

Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

zoltix

02/06/2009 à 12:06

On 2 juin, 11:36, "jerome" wrote:

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés pr imaires
afin d'optimiser la base

Merci

"zoltix" wrote in message

news:
On 2 juin, 08:26, "jerome" wrote:

> Bonjour,

> J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
> mais je ne trouve pas beaucoup d'informations.
> Notamment celle me demandant dechoisir pour le Workload un fichier ou u ne
> table.

> Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c' est
> exactement et ce que je dois sélectionner ?

> Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

il y a un wizard pour creer la table mais pour les index il y a plus
simple............

va voir .-----create Missing Index

Regarde mes scripts que j'utilise pour l'optimisation......(Que j'ai
trouvé sur web..;-) ....)

----Info Sur les index
select * from bruprod.screens

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
i.name as idx,
i.type_desc as idxType,
i.is_unique,
i.is_primary_key,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
system_seeks,
system_scans,
system_lookups,
system_updates,
last_system_seek,
last_system_scan,
last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id =
i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

-- index supprimables --list index can be delete

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
i.name as idx,
ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles--Delete
uncessry index
SELECT
'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME
(i.object_id) + '].[' +
i.name + ']'
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'

---Inofos Operationnel
SELECT object_name(s.object_id) as tbl,
i.name as idx,
range_scan_count + singleton_lookup_count as [pages lues],
leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures
sur noeud feuille],
leaf_allocation_count as [page splits sur noeud feuille],
nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
as [écritures sur noeuds intermédiaires],
nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id =
s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

----------Missing Index
SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

-----create Missing Index

SELECT
cast( ('CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
inequality_columns), ']', ''), '[', ''), ', ', '_')
+ ' ON ' + statement + ' (' + COALESCE(equality_columns,
inequality_columns) + ') INCLUDE (' + included_columns + ')') as
varchar(8000))
--,object_name(object_id) as objet
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC--, objet ASC

---Counter for seeing which database take more ressource
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC

--Dectect the More Read/write
SELECT TOP 10
(total_logical_reads + total_logical_writes)/execution_count,
Execution_count,
statement_start_offset,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count
DESC

--Dectect the Proc Take the more power....
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.plan_handle,
qs.sql_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle,qs.sql_handle
order by sum(qs.total_worker_time) desc

--Plan Handle
select * from sys.dm_exec_query_plan
(0x060009007D394C1C40E3238E000000000000000000000000)

-- sql_handle
select * from sys.dm_exec_sql_text
(0x0300FF7F0185511EACCA1B00ED9600000100000000000000)

--Dectect a CPU Bottleneck if high value With mix wih Processor:%
Processor Time
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

---Current Running take a lot ressource
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- *****detect excessive compiles and recompiles. ********
--Perform
-- SQL Server: SQL Statistics: Batch Requests/sec
-- SQL Server: SQL Statistics: SQL Compilations/sec
-- SQL Server: SQL Statistics: SQL Recompilations/sec
---SqlProfiler
--SP:Recompile / SQL:StmtRecompile.

select *
from sys.dm_exec_query_optimizer_info

optimizations
elapsed time

---query gives you the top 25 stored procedures that have been
recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

--inficient Query plan
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--Intra-query parallelism
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)

-------------Memory Bottlenecks
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

----------

DBCC MEMORYSTATUS

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it
represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

--Look for out-of-memory
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

--RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc

--Buffer Pool Failure
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

--
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't
take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation
base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
--Although not required, we recommend locking pages in memory when
using 64-bit operating systems.

sp_configure

--------File Size

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS
[HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN
p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id =
p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2
THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id =
p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and
idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

On 2 juin, 11:36, "jerome" <jerom...@nospam.fr> wrote:

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés pr imaires
afin d'optimiser la base

Merci

"zoltix" <zol...@gmail.com> wrote in message

news:775e4829-651c-422a-ac72-392826b2fe96@o14g2000vbo.googlegroups.com...
On 2 juin, 08:26, "jerome" <jerom...@nospam.fr> wrote:

> Bonjour,

> J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
> mais je ne trouve pas beaucoup d'informations.
> Notamment celle me demandant dechoisir pour le Workload un fichier ou u ne
> table.

> Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c' est
> exactement et ce que je dois sélectionner ?

> Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

il y a un wizard pour creer la table mais pour les index il y a plus
simple............

va voir .-----create Missing Index

Regarde mes scripts que j'utilise pour l'optimisation......(Que j'ai
trouvé sur web..;-) ....)

----Info Sur les index
select * from bruprod.screens

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
i.name as idx,
i.type_desc as idxType,
i.is_unique,
i.is_primary_key,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
system_seeks,
system_scans,
system_lookups,
system_updates,
last_system_seek,
last_system_scan,
last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id =
i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

-- index supprimables --list index can be delete

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
i.name as idx,
ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles--Delete
uncessry index
SELECT
'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME
(i.object_id) + '].[' +
i.name + ']'
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'

---Inofos Operationnel
SELECT object_name(s.object_id) as tbl,
i.name as idx,
range_scan_count + singleton_lookup_count as [pages lues],
leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures
sur noeud feuille],
leaf_allocation_count as [page splits sur noeud feuille],
nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
as [écritures sur noeuds intermédiaires],
nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id =
s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

----------Missing Index
SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

-----create Missing Index

SELECT
cast( ('CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
inequality_columns), ']', ''), '[', ''), ', ', '_')
+ ' ON ' + statement + ' (' + COALESCE(equality_columns,
inequality_columns) + ') INCLUDE (' + included_columns + ')') as
varchar(8000))
--,object_name(object_id) as objet
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC--, objet ASC

---Counter for seeing which database take more ressource
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC

--Dectect the More Read/write
SELECT TOP 10
(total_logical_reads + total_logical_writes)/execution_count,
Execution_count,
statement_start_offset,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count
DESC

--Dectect the Proc Take the more power....
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.plan_handle,
qs.sql_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle,qs.sql_handle
order by sum(qs.total_worker_time) desc

--Plan Handle
select * from sys.dm_exec_query_plan
(0x060009007D394C1C40E3238E000000000000000000000000)

-- sql_handle
select * from sys.dm_exec_sql_text
(0x0300FF7F0185511EACCA1B00ED9600000100000000000000)

--Dectect a CPU Bottleneck if high value With mix wih Processor:%
Processor Time
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

---Current Running take a lot ressource
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- *****detect excessive compiles and recompiles. ********
--Perform
-- SQL Server: SQL Statistics: Batch Requests/sec
-- SQL Server: SQL Statistics: SQL Compilations/sec
-- SQL Server: SQL Statistics: SQL Recompilations/sec
---SqlProfiler
--SP:Recompile / SQL:StmtRecompile.

select *
from sys.dm_exec_query_optimizer_info

optimizations
elapsed time

---query gives you the top 25 stored procedures that have been
recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

--inficient Query plan
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--Intra-query parallelism
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)

-------------Memory Bottlenecks
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

----------

DBCC MEMORYSTATUS

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it
represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

--Look for out-of-memory
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

--RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc

--Buffer Pool Failure
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

--
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't
take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation
base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
--Although not required, we recommend locking pages in memory when
using 64-bit operating systems.

sp_configure

--------File Size

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS
[HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN
p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id =
p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2
THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id =
p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and
idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

Vous avez filtré cet utilisateur ! Consultez son message

On 2 juin, 11:36, "jerome" wrote:

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés pr imaires
afin d'optimiser la base

Merci

"zoltix" wrote in message

news:
On 2 juin, 08:26, "jerome" wrote:

> Bonjour,

> J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
> mais je ne trouve pas beaucoup d'informations.
> Notamment celle me demandant dechoisir pour le Workload un fichier ou u ne
> table.

> Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c' est
> exactement et ce que je dois sélectionner ?

> Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

il y a un wizard pour creer la table mais pour les index il y a plus
simple............

va voir .-----create Missing Index

Regarde mes scripts que j'utilise pour l'optimisation......(Que j'ai
trouvé sur web..;-) ....)

----Info Sur les index
select * from bruprod.screens

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
i.name as idx,
i.type_desc as idxType,
i.is_unique,
i.is_primary_key,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
system_seeks,
system_scans,
system_lookups,
system_updates,
last_system_seek,
last_system_scan,
last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id =
i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

-- index supprimables --list index can be delete

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
i.name as idx,
ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles--Delete
uncessry index
SELECT
'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME
(i.object_id) + '].[' +
i.name + ']'
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'

---Inofos Operationnel
SELECT object_name(s.object_id) as tbl,
i.name as idx,
range_scan_count + singleton_lookup_count as [pages lues],
leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures
sur noeud feuille],
leaf_allocation_count as [page splits sur noeud feuille],
nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
as [écritures sur noeuds intermédiaires],
nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id =
s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

----------Missing Index
SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

-----create Missing Index

SELECT
cast( ('CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
inequality_columns), ']', ''), '[', ''), ', ', '_')
+ ' ON ' + statement + ' (' + COALESCE(equality_columns,
inequality_columns) + ') INCLUDE (' + included_columns + ')') as
varchar(8000))
--,object_name(object_id) as objet
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC--, objet ASC

---Counter for seeing which database take more ressource
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC

--Dectect the More Read/write
SELECT TOP 10
(total_logical_reads + total_logical_writes)/execution_count,
Execution_count,
statement_start_offset,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count
DESC

--Dectect the Proc Take the more power....
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.plan_handle,
qs.sql_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle,qs.sql_handle
order by sum(qs.total_worker_time) desc

--Plan Handle
select * from sys.dm_exec_query_plan
(0x060009007D394C1C40E3238E000000000000000000000000)

-- sql_handle
select * from sys.dm_exec_sql_text
(0x0300FF7F0185511EACCA1B00ED9600000100000000000000)

--Dectect a CPU Bottleneck if high value With mix wih Processor:%
Processor Time
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

---Current Running take a lot ressource
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- *****detect excessive compiles and recompiles. ********
--Perform
-- SQL Server: SQL Statistics: Batch Requests/sec
-- SQL Server: SQL Statistics: SQL Compilations/sec
-- SQL Server: SQL Statistics: SQL Recompilations/sec
---SqlProfiler
--SP:Recompile / SQL:StmtRecompile.

select *
from sys.dm_exec_query_optimizer_info

optimizations
elapsed time

---query gives you the top 25 stored procedures that have been
recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

--inficient Query plan
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--Intra-query parallelism
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)

-------------Memory Bottlenecks
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

----------

DBCC MEMORYSTATUS

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it
represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

--Look for out-of-memory
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

--RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc

--Buffer Pool Failure
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

--
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't
take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation
base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
--Although not required, we recommend locking pages in memory when
using 64-bit operating systems.

sp_configure

--------File Size

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS
[HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN
p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id =
p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2
THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id =
p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and
idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

jerome

02/06/2009 à 17:21

Merci je vais regarder;

J'ai une version d'évaluation de SQL SERVER 2005 (en fait je me sers de la
version workgroup qui n'a pas l'outil de tuning et j'ai donc installé
temporairement une version complète pour faire ce tuning)

Je ne trouve pas le wizard pour créer la table.
Quant au missing index j'ai comme résultat 0 row(s) affected.
Donc soit j'ai fait du bon boulot, soit je n'ai pas tout compris
(je penche pour la deuxième solution)

"zoltix" wrote in message
news:
On 2 juin, 11:36, "jerome" wrote:

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés

primaires

afin d'optimiser la base

Merci

"zoltix" wrote in message

news:
On 2 juin, 08:26, "jerome" wrote:

> Bonjour,

> J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
> mais je ne trouve pas beaucoup d'informations.
> Notamment celle me demandant dechoisir pour le Workload un fichier ou

une

> table.

> Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c'est
> exactement et ce que je dois sélectionner ?

> Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

il y a un wizard pour creer la table mais pour les index il y a plus
simple............

va voir .-----create Missing Index

Regarde mes scripts que j'utilise pour l'optimisation......(Que j'ai
trouvé sur web..;-) ....)

----Info Sur les index
select * from bruprod.screens

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
i.name as idx,
i.type_desc as idxType,
i.is_unique,
i.is_primary_key,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
system_seeks,
system_scans,
system_lookups,
system_updates,
last_system_seek,
last_system_scan,
last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

-- index supprimables --list index can be delete

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
i.name as idx,
ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles--Delete
uncessry index
SELECT
'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME
(i.object_id) + '].[' +
i.name + ']'
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'

---Inofos Operationnel
SELECT object_name(s.object_id) as tbl,
i.name as idx,
range_scan_count + singleton_lookup_count as [pages lues],
leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures
sur noeud feuille],
leaf_allocation_count as [page splits sur noeud feuille],
nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
as [écritures sur noeuds intermédiaires],
nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

----------Missing Index
SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

-----create Missing Index

SELECT
cast( ('CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
inequality_columns), ']', ''), '[', ''), ', ', '_')
+ ' ON ' + statement + ' (' + COALESCE(equality_columns,
inequality_columns) + ') INCLUDE (' + included_columns + ')') as
varchar(8000))
--,object_name(object_id) as objet
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC--, objet ASC

---Counter for seeing which database take more ressource
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC

--Dectect the More Read/write
SELECT TOP 10
(total_logical_reads + total_logical_writes)/execution_count,
Execution_count,
statement_start_offset,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count
DESC

--Dectect the Proc Take the more power....
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.plan_handle,
qs.sql_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle,qs.sql_handle
order by sum(qs.total_worker_time) desc

--Plan Handle
select * from sys.dm_exec_query_plan
(0x060009007D394C1C40E3238E000000000000000000000000)

-- sql_handle
select * from sys.dm_exec_sql_text
(0x0300FF7F0185511EACCA1B00ED9600000100000000000000)

--Dectect a CPU Bottleneck if high value With mix wih Processor:%
Processor Time
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

---Current Running take a lot ressource
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- *****detect excessive compiles and recompiles. ********
--Perform
-- SQL Server: SQL Statistics: Batch Requests/sec
-- SQL Server: SQL Statistics: SQL Compilations/sec
-- SQL Server: SQL Statistics: SQL Recompilations/sec
---SqlProfiler
--SP:Recompile / SQL:StmtRecompile.

select *
from sys.dm_exec_query_optimizer_info

optimizations
elapsed time

---query gives you the top 25 stored procedures that have been
recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

--inficient Query plan
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--Intra-query parallelism
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)

-------------Memory Bottlenecks
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

----------

DBCC MEMORYSTATUS

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it
represents the largest consumer which is normal
select
@total_alloc sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

--Look for out-of-memory
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

--RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc

--Buffer Pool Failure
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

--
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't
take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation
base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
--Although not required, we recommend locking pages in memory when
using 64-bit operating systems.

sp_configure

--------File Size

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS
[HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN
p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2
THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and
idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

Merci je vais regarder;

J'ai une version d'évaluation de SQL SERVER 2005 (en fait je me sers de la
version workgroup qui n'a pas l'outil de tuning et j'ai donc installé
temporairement une version complète pour faire ce tuning)

Je ne trouve pas le wizard pour créer la table.
Quant au missing index j'ai comme résultat 0 row(s) affected.
Donc soit j'ai fait du bon boulot, soit je n'ai pas tout compris
(je penche pour la deuxième solution)

"zoltix" <zoltix@gmail.com> wrote in message
news:26d665e1-dabe-4995-b021-d98716a6ba0d@o14g2000vbo.googlegroups.com...
On 2 juin, 11:36, "jerome" <jerom...@nospam.fr> wrote:

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés

primaires

afin d'optimiser la base

Merci

"zoltix" <zol...@gmail.com> wrote in message

news:775e4829-651c-422a-ac72-392826b2fe96@o14g2000vbo.googlegroups.com...
On 2 juin, 08:26, "jerome" <jerom...@nospam.fr> wrote:

> Bonjour,

> J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
> mais je ne trouve pas beaucoup d'informations.
> Notamment celle me demandant dechoisir pour le Workload un fichier ou

une

> table.

> Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c'est
> exactement et ce que je dois sélectionner ?

> Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

il y a un wizard pour creer la table mais pour les index il y a plus
simple............

va voir .-----create Missing Index

Regarde mes scripts que j'utilise pour l'optimisation......(Que j'ai
trouvé sur web..;-) ....)

----Info Sur les index
select * from bruprod.screens

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
i.name as idx,
i.type_desc as idxType,
i.is_unique,
i.is_primary_key,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
system_seeks,
system_scans,
system_lookups,
system_updates,
last_system_seek,
last_system_scan,
last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

-- index supprimables --list index can be delete

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
i.name as idx,
ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles--Delete
uncessry index
SELECT
'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME
(i.object_id) + '].[' +
i.name + ']'
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'

---Inofos Operationnel
SELECT object_name(s.object_id) as tbl,
i.name as idx,
range_scan_count + singleton_lookup_count as [pages lues],
leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures
sur noeud feuille],
leaf_allocation_count as [page splits sur noeud feuille],
nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
as [écritures sur noeuds intermédiaires],
nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

----------Missing Index
SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

-----create Missing Index

SELECT
cast( ('CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
inequality_columns), ']', ''), '[', ''), ', ', '_')
+ ' ON ' + statement + ' (' + COALESCE(equality_columns,
inequality_columns) + ') INCLUDE (' + included_columns + ')') as
varchar(8000))
--,object_name(object_id) as objet
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC--, objet ASC

---Counter for seeing which database take more ressource
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC

--Dectect the More Read/write
SELECT TOP 10
(total_logical_reads + total_logical_writes)/execution_count,
Execution_count,
statement_start_offset,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count
DESC

--Dectect the Proc Take the more power....
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.plan_handle,
qs.sql_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle,qs.sql_handle
order by sum(qs.total_worker_time) desc

--Plan Handle
select * from sys.dm_exec_query_plan
(0x060009007D394C1C40E3238E000000000000000000000000)

-- sql_handle
select * from sys.dm_exec_sql_text
(0x0300FF7F0185511EACCA1B00ED9600000100000000000000)

--Dectect a CPU Bottleneck if high value With mix wih Processor:%
Processor Time
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

---Current Running take a lot ressource
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- *****detect excessive compiles and recompiles. ********
--Perform
-- SQL Server: SQL Statistics: Batch Requests/sec
-- SQL Server: SQL Statistics: SQL Compilations/sec
-- SQL Server: SQL Statistics: SQL Recompilations/sec
---SqlProfiler
--SP:Recompile / SQL:StmtRecompile.

select *
from sys.dm_exec_query_optimizer_info

optimizations
elapsed time

---query gives you the top 25 stored procedures that have been
recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

--inficient Query plan
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--Intra-query parallelism
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)

-------------Memory Bottlenecks
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

----------

DBCC MEMORYSTATUS

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it
represents the largest consumer which is normal
select
@total_alloc sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

--Look for out-of-memory
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

--RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc

--Buffer Pool Failure
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

--
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't
take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation
base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
--Although not required, we recommend locking pages in memory when
using 64-bit operating systems.

sp_configure

--------File Size

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS
[HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN
p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2
THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and
idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

Vous avez filtré cet utilisateur ! Consultez son message

Merci je vais regarder;

J'ai une version d'évaluation de SQL SERVER 2005 (en fait je me sers de la
version workgroup qui n'a pas l'outil de tuning et j'ai donc installé
temporairement une version complète pour faire ce tuning)

Je ne trouve pas le wizard pour créer la table.
Quant au missing index j'ai comme résultat 0 row(s) affected.
Donc soit j'ai fait du bon boulot, soit je n'ai pas tout compris
(je penche pour la deuxième solution)

"zoltix" wrote in message
news:
On 2 juin, 11:36, "jerome" wrote:

Une table mais quelle table ?

Je dois créer une table ?
si oui quelle forme, sur quelle base, etc...
si non je sélectionne quelle table ?

Le but recherché est de voir s'il ne manque pas d'index ou de clés

primaires

afin d'optimiser la base

Merci

"zoltix" wrote in message

news:
On 2 juin, 08:26, "jerome" wrote:

> Bonjour,

> J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
> mais je ne trouve pas beaucoup d'informations.
> Notamment celle me demandant dechoisir pour le Workload un fichier ou

une

> table.

> Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c'est
> exactement et ce que je dois sélectionner ?

> Merci par avance.

moi je préfère sur une table mais faut faire attention a la taille et
au performance global du serveur. Car avec un fichier ce n'est pas
toujours facile de rechercher une info et de consolider.

A+

il y a un wizard pour creer la table mais pour les index il y a plus
simple............

va voir .-----create Missing Index

Regarde mes scripts que j'utilise pour l'optimisation......(Que j'ai
trouvé sur web..;-) ....)

----Info Sur les index
select * from bruprod.screens

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
i.name as idx,
i.type_desc as idxType,
i.is_unique,
i.is_primary_key,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
system_seeks,
system_scans,
system_lookups,
system_updates,
last_system_seek,
last_system_scan,
last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

-- index supprimables --list index can be delete

SELECT
SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
i.name as idx,
ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles--Delete
uncessry index
SELECT
'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME
(i.object_id) + '].[' +
i.name + ']'
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN
(sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id )
ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
t.type_desc = 'USER_TABLE'

---Inofos Operationnel
SELECT object_name(s.object_id) as tbl,
i.name as idx,
range_scan_count + singleton_lookup_count as [pages lues],
leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures
sur noeud feuille],
leaf_allocation_count as [page splits sur noeud feuille],
nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
as [écritures sur noeuds intermédiaires],
nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

----------Missing Index
SELECT object_name(object_id) as objet, d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC, object_id

-----create Missing Index

SELECT
cast( ('CREATE INDEX nix$' + lower(object_name(object_id)) + '$'
+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns,
inequality_columns), ']', ''), '[', ''), ', ', '_')
+ ' ON ' + statement + ' (' + COALESCE(equality_columns,
inequality_columns) + ') INCLUDE (' + included_columns + ')') as
varchar(8000))
--,object_name(object_id) as objet
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY s.user_seeks DESC--, objet ASC

---Counter for seeing which database take more ressource
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC

--Dectect the More Read/write
SELECT TOP 10
(total_logical_reads + total_logical_writes)/execution_count,
Execution_count,
statement_start_offset,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count
DESC

--Dectect the Proc Take the more power....
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.plan_handle,
qs.sql_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle,qs.sql_handle
order by sum(qs.total_worker_time) desc

--Plan Handle
select * from sys.dm_exec_query_plan
(0x060009007D394C1C40E3238E000000000000000000000000)

-- sql_handle
select * from sys.dm_exec_sql_text
(0x0300FF7F0185511EACCA1B00ED9600000100000000000000)

--Dectect a CPU Bottleneck if high value With mix wih Processor:%
Processor Time
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

---Current Running take a lot ressource
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- *****detect excessive compiles and recompiles. ********
--Perform
-- SQL Server: SQL Statistics: Batch Requests/sec
-- SQL Server: SQL Statistics: SQL Compilations/sec
-- SQL Server: SQL Statistics: SQL Recompilations/sec
---SqlProfiler
--SP:Recompile / SQL:StmtRecompile.

select *
from sys.dm_exec_query_optimizer_info

optimizations
elapsed time

---query gives you the top 25 stored procedures that have been
recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

--inficient Query plan
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

--Intra-query parallelism
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc',
default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API
cursor (TSQL cursors always have fetch buffer of 1)

-------------Memory Bottlenecks
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

----------

DBCC MEMORYSTATUS

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as
[Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it
represents the largest consumer which is normal
select
@total_alloc sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation
base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with
zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on
(cc.cache_address =ch.cache_address)
/*
--uncomment this block to have the information only
for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int')
as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int')
as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

--Look for out-of-memory
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

--RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)')
as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc

--Buffer Pool Failure
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

--
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't
take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation
base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
--Although not required, we recommend locking pages in memory when
using 64-bit operating systems.

sp_configure

--------File Size

SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS
[HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
AS [RowCount]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN
p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]

, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2
THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and
p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date

FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and
idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

WOLO Laurent

14/06/2009 à 16:27

Commence par faire une capture des données avec le profiler sql server que
vous aller enregistrer soit dans une table soit dans un fichier et vous
fournirer ces données lorsque cela sera nécessaire.

"jerome" a écrit dans le message de
news:

Bonjour,

J'essaie de me servir des outils de tuning de Database Engine Tuning
Advisor
mais je ne trouve pas beaucoup d'informations.
Notamment celle me demandant dechoisir pour le Workload un fichier ou une
table.

Avant de faire une bêtise quelqu'un pourrait-il m'expliquer ce que c'est
exactement et ce que je dois sélectionner ?

Merci par avance.

Tuning d'une base SQLServer 2005

5 réponses

Veuillez sélectionner un problème