Database performance plays significant effect in large user application. We need to examine and identify causes that makes system slow.
One step we can do is to identify query which takes high CPU time. Use transact SQL
sys.dm_exec_sql_text in order to get top CPU expensive queries.
Taken from Microsoft site, https://msdn.microsoft.com/en-us/library/ms181929(v=sql.105).aspx, the code using
sys.dm_exec_sql_text is below:
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;
Above T-SQL gets Top five query by Average CPU Time. Open your SQL Server Management Studio, choose your database and run above query.
After getting those Top queries, you can open SQL Server Database Engine Tuning Advisor to analyze them and apply the Tuning Advisor recommendation to get better performance.
You can take a look at my post about How to use Database Engine Tuning Advisor for your reference.