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
62
63
64
65
66
67
| -- File I/O requests taking longer than ... seconds to complete in the 5 most recent SQL Server Error Logs
DECLARE @LONGIO TABLE (LogDate datetime2, ProcessInfo sysname, LogText nvarchar(2000));
INSERT INTO @LONGIO
EXEC xp_readerrorlog 0, 1, N'taking longer than ';
INSERT INTO @LONGIO
EXEC xp_readerrorlog 1, 1, N'taking longer than ';
INSERT INTO @LONGIO
EXEC xp_readerrorlog 2, 1, N'taking longer than ';
INSERT INTO @LONGIO
EXEC xp_readerrorlog 3, 1, N'taking longer than ';
INSERT INTO @LONGIO
EXEC xp_readerrorlog 4, 1, N'taking longer than ';
SELECT LogDate, ProcessInfo, LogText
FROM @LONGIO
ORDER BY LogDate DESC;
-- cumulative
WITH T0 AS
(
SELECT LogDate, LogText
FROM @LONGIO),
T1 AS
(
SELECT *,
CAST(SUBSTRING(LogText,
CHARINDEX('SQL Server has encountered', LogText) + 27,
CHARINDEX(' occurrence(s) of I/O', LogText) -
CHARINDEX('SQL Server has encountered', LogText) - 27) AS INT)
AS NB_OCCUR,
CAST(SUBSTRING(LogText,
CHARINDEX('I/O requests taking longer than', LogText) + 32,
CHARINDEX(' seconds to complete', LogText) -
CHARINDEX('I/O requests taking longer than', LogText) - 32) AS INT)
AS LONG_SECOND,
SUBSTRING(LogText,
CHARINDEX('to complete on file [', LogText) + 21,
CHARINDEX('] in database ', LogText) -
CHARINDEX('to complete on file [', LogText) - 21)
AS DATABASE_FILE,
SUBSTRING(LogText,
CHARINDEX('] in database ', LogText) + 14,
CHARINDEX('. The OS file handle ', LogText) -
CHARINDEX('] in database ', LogText) - 14)
AS DATABASE_ID_OR_NAME
FROM T0),
T2 AS
(
SELECT LogDate, NB_OCCUR, LONG_SECOND, DATABASE_FILE,
DATABASE_ID_OR_NAME
FROM T1),
T3 AS
(
SELECT MIN(LogDate) AS PERIOD_BEGIN, MAX(LogDate) AS PERIOD_END,
SUM(NB_OCCUR) AS IO_OCCUR,
1.0 * SUM(NB_OCCUR * LONG_SECOND) / SUM(NB_OCCUR) AS AVG_LEMGTH_TIME_S,
COUNT(*) AS FREQUENCY,
DATABASE_FILE, DATABASE_ID_OR_NAME
FROM T2
GROUP BY DATABASE_FILE, DATABASE_ID_OR_NAME)
SELECT T3.*, vs.*
FROM T3
LEFT OUTER JOIN sys.master_files AS mf
ON CASE WHEN DATABASE_ID_OR_NAME LIKE 'id [0-9]%'
THEN CAST(SUBSTRING(DATABASE_ID_OR_NAME, 4, LEN(DATABASE_ID_OR_NAME) - 3) AS INT)
ELSE DB_ID(LEFT(DATABASE_ID_OR_NAME, LEN(DATABASE_ID_OR_NAME) - 1))
END = mf.database_id
AND DATABASE_FILE = mf.physical_name
OUTER APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs; |
