SQL Server lent

Bonjour,

En terme d'optimisation, je suis au tout début pour comprendre ce qui se passe. J'ai des sessions avec la commande BRKR TASK qui est en mode suspended dans Activity monitor sur la base de données SA. J'ai des lenteurs et plein d'alerte sur serveur windows

Avez-vous eu une idée?

Merci

Sans autre détails aucune réponse....

1) version / edition =>

Code:

SELECT @@VERSION;

2) VM ou machine physique ?

Code:

EXEC xp_readerrorlog 0, 1, N'System Manufacturer';

3) Mémoire disponible sur la machine ?

Code:

EXEC xp_readerrorlog 0, 1, N'Detected ', N' RAM.';

4) Nombre de CPU / coeurs ?

Code:

EXEC xp_readerrorlog 0, 1, N'SQL Server detected ';

5) Quantité de cache SQL Server ?

Code:

1
2
3
4
SELECT CAST((1.0 * total_physical_memory_kb / POWER(1024, 2)) AS DECIMAL(16,3)) AS PC_RAM,
       CAST((1.0 * committed_target_kb / POWER(1024, 2)) AS DECIMAL(16,3)) SQL_RAM 
FROM   sys.dm_os_sys_memory
       CROSS JOIN sys.dm_os_sys_info;

6) le cache est-il suffisant ?

Code:

SELECT system_low_memory_signal_state, system_memory_state_desc FROM sys.dm_os_sys_memory;

7) quelle est la durée de vie moyenne des pages en cache ?

Code:

1
2
3
4
SELECT counter_name, cntr_value AS DURATION_SECOND
FROM   sys.dm_os_performance_counters 
WHERE  object_name LIKE  '%:Buffer Manager%' 
       AND counter_name = 'Page life expectancy';

7) Quel est le volume global des données bases ?

Code:

SELECT CAST(SUM(size) / 131072 AS DECIMAL(16, 3)) AS SIZE_MB FROM sys.master_files WHERE type <> 1;

8) Vos bases sont-elles en verrouillage pessimiste ou optimiste ?

Code:

SELECT name, database_id, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases WHERE database_id > 4;

9) Quelle est la latence des disques ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
WITH 
disk_activity AS
(SELECT LEFT(mf.physical_name, 2) AS Drive, 
        SUM(num_of_reads) AS num_of_reads,
        SUM(io_stall_read_ms) AS io_stall_read_ms, 
        SUM(num_of_writes) AS num_of_writes,
        SUM(io_stall_write_ms) AS io_stall_write_ms, 
        SUM(num_of_bytes_read) AS num_of_bytes_read,
        SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
 FROM   sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
        INNER JOIN sys.master_files AS mf
           ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
 GROUP  BY LEFT(mf.physical_name, 2))
SELECT (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS SINCE,
       Drive AS DISK_DRIVE,
       CASE 
           WHEN num_of_reads = 0 THEN 0 
           ELSE (io_stall_read_ms/num_of_reads) 
       END AS READ_LATENCY,
       CASE 
           WHEN io_stall_write_ms = 0 THEN 0 
          ELSE (io_stall_write_ms/num_of_writes) 
       END AS WRITE_LATENCY,
       CASE 
          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
          ELSE (io_stall/(num_of_reads + num_of_writes)) 
       END AS GLOBAL_LATENCY,
       CASE 
          WHEN num_of_reads = 0 THEN 0 
          ELSE (num_of_bytes_read/num_of_reads) 
       END AS AVG_BYTES_PER_READ,
       CASE 
          WHEN io_stall_write_ms = 0 THEN 0 
          ELSE (num_of_bytes_written/num_of_writes) 
       END AS AVG_BYTES_PER_WRITE,
       CASE 
          WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
          ELSE ((num_of_bytes_read + num_of_bytes_written)
		         /(num_of_reads + num_of_writes)) 
       END AS AVG_BYTES_PER_TRANSFER
FROM   disk_activity AS tab
ORDER  BY GLOBAL_LATENCY;

10) quels sont les principales attentes de SQL Server ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
WITH 
W 
AS 
(SELECT wait_type, wait_time_ms/ 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
 FROM   sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] NOT IN (
       N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
       N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
       N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'CXCONSUMER',
       N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
       N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
       N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
       N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', 
       N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
       N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 
       N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
       N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',
       N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',
       N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
       N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
       N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
       N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
       N'PREEMPTIVE_OS_DELETESECURITYCONTEXT', N'PREEMPTIVE_OS_REPORTEVENT',
       N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
       N'PREEMPTIVE_OS_WRITEFILE', N'PREEMPTIVE_OS_WRITEFILEGATHER',
       N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
       N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
       N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
       N'POPULATE_LOCK_ORDINALS',
       N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
       N'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
       N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
       N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
       N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
       N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
       N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
       N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SOS_WORK_DISPATCHER',
       N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SOS_WORKER_MIGRATION', N'VDI_CLIENT_OTHER',
       N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
       N'STARTUP_DEPENDENCY_MANAGER',
       N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
       N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
       N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
       N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
       AND waiting_tasks_count > 0)
SELECT MAX (W1.wait_type) AS WAIT_TYPE,
       CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS WAIT_PERCENT,
       CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS AVG_WAIT_S,
       CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS WAIT_S,
       MAX (W1.WaitCount) AS WAIT_COUNT
FROM   W AS W1
       INNER JOIN W AS W2
          ON W2.RowNum <= W1.RowNum
GROUP  BY W1.RowNum, W1.wait_type
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99
OPTION (RECOMPILE);

11) Quel est le top 50 des requêtes les plus consommatrices de ressources ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT TOP 50 DB_NAME(dbid) AS DATABASE_NAME, 
       execution_count, total_worker_time, total_elapsed_time, 
       SUBSTRING(text, 
                 (statement_start_offset /2 ) + 1,  
                 ((CASE statement_end_offset   
                      WHEN -1 THEN DATALENGTH(text)  
                      ELSE statement_end_offset 
                   END - statement_start_offset) / 2) + 1) AS SQL_QUERY
FROM   sys.dm_exec_query_stats
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE  dbid < 32767 AND DB_NAME(dbid) NOT IN ('master', 'model', 'msdb')
ORDER BY total_worker_time DESC;

12) efficacité d'indexation

Code:

1
2
3
4
5
6
7
8
9
10
SELECT DB_NAME(database_id) AS DATABASE_NAME, database_id, object_id, index_id, 
       CASE 
	      WHEN index_id = 0 THEN 'Table'
		  WHEN index_id = 1 THEN 'Clustered B Tree'
		  WHEN index_id BETWEEN 1 AND 999 THEN 'Unclustered B Tree'
		  WHEN index_id = 0 THEN 'Other'
       END AS INDEX_TYPE,
	   user_seeks, user_lookups, user_scans, user_updates
FROM   sys.dm_db_index_usage_stats
ORDER  BY user_seeks, user_lookups, user_scans, user_updates;

etc...

A +

Bonjour,

J'ai lancé les requêtes sur mon appli et j'avoue que certains chiffres sont assez parlant par contre pour d'autres indicateurs, c'est le flou. J'ai récupéré les requêtes et je les analyserai plus tard.

S'il y a d'autres mesures, je les prends avec plaisir

Merci encore:

Comment j'exploite des données sur la requête donnée par l'efficacité des index?

postez tout ce que vous avez comme résultats je vais vous expliquer ce qui cloche. Il sera sans doute nécessaire de faire d'autres investigations...

A +

J'avais une requête qui posait problème. Il me reste du temps pour comprendre et encore j'ai juste 5 serveurs. J'ai mis de coté les requêtes. Par contre, les index missing qui sont dans le rapport ne sont pas tous judicieux. Merci. Je rouvrirai une discussion je pense sur les index. Merci