April 8, 2015 at 8:54 pm
Hi friends,
i have a very troublesome production SQL 2005 box getting many performance cocompliant
to get them resolved, i have to understand them but coulcouldn'thout advise or suggestions.
what i did was run server status report, i can get top 10 I/O & top CPU intensive SQL statement, but how to know their occrence, if this statement will be running again, how i can capture them, whether it is from a job or a stored procedures, im going to dig into something that im not very familiar with. so highly appreicate any suggestion or capproach
Thanks,
Jack
April 9, 2015 at 10:41 am
Below are a couple functions that I am working on that will get you the queries that are creating the most CPU pressure and IO pressure.
These are based on this article: Five DMV Queries That Will Make You A Superhero![/url]. I am still testing these but my initial findings are that they are pretty good.
CPU Pressure:
USE <yourdb>
IF OBJECT_ID('<yourdb>.dbo.fn_perf_SprocExecutionByCPUPressure') IS NOT NULL
DROP FUNCTION dbo.fn_perf_SprocExecutionByCPUPressure;
GO
CREATE FUNCTION dbo.fn_perf_SprocExecutionByCPUPressure
(
@records int = 20,
@db varchar(100) = NULL
)
/*
Use:
Returns Stored Procedures ordered by total worker time (CPU pressure)
EXAMPLES:
SELECT * FROM dbo.fn_perf_SprocExecutionByCPUPressure(NULL,NULL); -- Top 20 for all dbs
SELECT * FROM dbo.fn_perf_SprocExecutionByCPUPressure(10,'tpcc'); -- Top 10 for tpcc db
*/
RETURNS TABLE AS
RETURN
(
SELECT TOP (ISNULL(@records,20))
qt.dbid,
db.name,
qt.text AS 'SP Name',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'Execution Count',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.databases db
WHERE db.database_id = qt.dbid
AND (db.name = @db OR @db IS NULL)
ORDER BY qs.total_worker_time DESC
);
GO
I/O:
IF OBJECT_ID('GoogleData.dbo.fn_perf_SprocExecutionByWrites') IS NOT NULL
DROP FUNCTION dbo.fn_perf_SprocExecutionByWrites;
GO
CREATE FUNCTION dbo.fn_perf_SprocExecutionByWrites
(
@records int = 20,
@db varchar(100) = NULL
)
/*
Use:
Returns Stored Procedures ordered by physical reads (read I/O pressure)
EXAMPLES:
SELECT * FROM dbo.fn_perf_SprocExecutionByWrites(100,NULL); -- Top 100 for all dbs
SELECT * FROM dbo.fn_perf_SprocExecutionByWrites(10,'tpcc'); -- Top 10 for tpcc db
*/
RETURNS TABLE AS
RETURN
(
SELECT TOP (ISNULL(@records,20))
qt.dbid,
db.name,
qt.text AS 'SP Name',
qs.total_logical_writes,
qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',
qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.databases db
WHERE db.database_id = qt.dbid
AND (db.name = @db OR @db IS NULL)
ORDER BY qs.total_logical_writes DESC
);
GO
The second function for IO will fail with a divide by zero error is you just cleaned up you cache. I have not addressed that yet.
A viable strategy for collecting this performance data might by to create a query that writes these metrics to a table
(e.g. INSERT INTO <yourtable> SELECT * FROM dbo.fn_perf_SprocExecutionByWrites)... Then create a SQL Agent job runs the query on a regular basis.
-- Itzik Ben-Gan 2001
April 11, 2015 at 9:20 am
Hi Alan,
Thanks for your reply. i will test these scripts.
Moreover, i would also like to know how to determine or capture this high IO/CPU queries if they run again. so i will know how these statements occur and its frequency in specified timeframe.
Is there any method to do so?
Thanks,
Jack
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply