July 30, 2013 at 4:26 pm
I am trying to figure out how to monitor performance of the database before and after deploying an index. Are there any simple ways of doing this?
July 31, 2013 at 1:00 am
I am using this query to find top 15 poor queries, modify as per your request and run it before and after index creation and see everything going as per your needs
SELECT TOP 15
DB_NAME(qt.dbid) 'DBName',qt.objectid,
qs.execution_count ,
SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) 'Query_Text',
qs.total_worker_time 'Total_CPU_Time',
qs.total_worker_time/qs.execution_count 'Avg_CPU_Time',
qs.total_physical_reads 'Tot_Phy_Read',
qs.total_physical_reads/qs.execution_count 'AVG_Phy_Read',
qs.total_logical_reads 'Tot_Logic_Read',
qs.total_logical_reads/qs.execution_count 'AVG_Logic_Read',
qs.total_logical_writes 'Tot_Logic_Write',
qs.total_logical_writes/qs.execution_count 'AVG_Logic_Write',
qs.total_elapsed_time 'Total_Duration',
qs.total_elapsed_time/qs.execution_count 'Avg_Duration,[Plan])'
--cast(qp.query_plan as varchar(max))
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE DB_NAME(qt.dbid) not in ('Master','MSDB','Model','WFM40','ReportServer') and
(
qs.execution_count > 50 OR
qs.total_worker_time/qs.execution_count > 100 OR
qs.total_physical_reads/qs.execution_count > 1000 OR
qs.total_logical_reads/qs.execution_count > 1000 OR
qs.total_logical_writes/qs.execution_count > 1000 OR
qs.total_elapsed_time/qs.execution_count > 1000
)
and SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) is not null
ORDER BY
qs.total_worker_time/qs.execution_count DESC
Run this to clear the cache to get actual results but dont try this on production
DBCC FREESYSTEMCACHE('All')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Regards
Durai Nagarajan
July 31, 2013 at 1:04 am
The usual way would be to monitor specific queries and evaluate the execution plans of those queries. You run the query, tune the indexes as appropriate (keeping in mind there may be other queries that might need that index), and then test the query again and compare results.
You set a baseline, make a change, rerun the baseline and compare.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2013 at 7:20 am
You can pretty quickly set up extended events to capture the query and it's performance and then recapture it after adding the index. You can also capture the execution plans before and after through extended events. That would be one of the easiest ways. You can simply do the same thing through SSMS manually as you modify the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply