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; |
Partager