Archive for the ‘Performance Tuning’ Category
Posted by Pramod Singla on March 23, 2017
Problem Statement: Script to find top 10 high memory consuming Queries.
Solution:
SELECT TOP 10 Object_name(qt.objectid)
AS 'SP Name',
Substring(qt.text, ( qs.statement_start_offset / 2 ) + 1,
(
( CASE qs.statement_end_offset
WHEN -1 THEN Datalength(qt.text)
ELSE qs.statement_end_offset
END
-
qs.statement_start_offset ) / 2 ) + 1)
AS statement_text,
total_logical_reads,
qs.execution_count
AS 'Execution Count',
total_logical_reads / qs.execution_count
AS 'AvgLogicalReads',
qs.execution_count / Datediff(minute, qs.creation_time, Getdate())
AS
'Calls/minute',
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.total_logical_writes,
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
qt.dbid,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.dbid = Db_id() -- Filter by current database
ORDER BY total_logical_reads DESC
Posted in DBA, Interview Questions, Performance Tuning, Scripts | Tagged: DMV, memory consuming queries, query tuning, slow running query, sql server tuning | Leave a Comment »
Posted by Pramod Singla on March 12, 2017
Question:How to get a quick overview of SQL Server performance.
Ans: Use activity monitor in SSMS to get the quick overview of SQL server performance.To run the report right click the server name in SSMS>Activity Monitor
Figure 5: Activity Monitor
- This shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.
- Processes:-This shows the list of all the active users which are connected to SQL Server Database Engine. Here, you can right click any of the Session IDs which you think are problematic and can run a SQL Server Profiler Trace to capture all its activities; you can also see the Session Details or can even KILL a process.
- Resource Waits:-This helps to identify potential bottlenecks with respect to Memory, CPU, and Network I/O etc.
- Data File I/O:-This displays disk level I/O information related to all the data and log files of user and system databases
- Recent Expensive Queries:-In this section, database administrators can quickly identify poorly performing queries in an SQL Server Instance. With the feature, which I like the most, you can right click any of the problematic queries and choose the “Edit Query Text” option to edit the query. Moreover, you can also see the execution plan of the poorly performing query by right clicking the problematic query and by selecting the option “Show Execution Plan”.
Posted in Performance Tuning | Tagged: Activity monitor, Performance Tuning | Leave a Comment »
Posted by Pramod Singla on March 12, 2017
Find Slow running queries using the Reports in SSMS
1) High CPU consuming Queries: To run the report right click the server name in SSMS>reports>standard Reports>performance –Top queries by average Time
Figure 1: How to get to the performance reports
Figure 2: Top 10 queries by CPU time
2) Queries with highest IO: To run the report right click the server name in SSMS>reports>standard Reports>performance –Top queries by average IO
Figure 3: Top 10 quries by IO
3) Server Dashboard: To run the report right click the server name in SSMS>reports>standard Reports>Server Dashboard
Figure 4: Server Dashboard
Posted in Performance Tuning | Tagged: cpu intensive queries, memory intensive queries, Performance reports, Performance Tuning, Slow running queries | Leave a Comment »
Posted by Pramod Singla on March 12, 2017
Problem Statement: Script to find top 10 CPU intensive queries.
Solution:
SELECT TOP 10 Object_name(qt.objectid)
AS ‘SP Name’,
Substring(qt.text, ( qs.statement_start_offset / 2 ) + 1,
(
( CASE qs.statement_end_offset
WHEN –1 THEN Datalength(qt.text)
ELSE qs.statement_end_offset
END
–
qs.statement_start_offset ) / 2 ) + 1)
AS statement_text,
total_logical_reads,
qs.execution_count
AS ‘Execution Count’,
total_logical_reads / qs.execution_count
AS ‘AvgLogicalReads’,
qs.execution_count / Datediff(minute, qs.creation_time, Getdate())
AS
‘Calls/minute’,
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.total_logical_writes,
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
qt.dbid,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS qt
OUTER apply sys.Dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.dbid = Db_id() — Filter by current database
ORDER BY totalworkertime DESC
Posted in DBA, Performance Tuning | Tagged: cpu intensive queries, DMV, Performance Tuning | Leave a Comment »
Posted by Pramod Singla on March 10, 2017
Problem Statement: Script to generate the scripts used to de-fragment indexes.
Solution: Use this script to find the fragmented indexes and following script can be used to de-fragment them.
SELECT ‘Alter index ‘ + i.NAME + ‘ on ‘
+ Quotename(s.NAME) + ‘.’ + Quotename(o.NAME) + CASE WHEN
stats.avg_fragmentation_in_percent >=60 THEN ‘ rebuild ‘ ELSE
‘ reorganize’ END
FROM sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) AS
stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE o.OBJECT_ID = stats.OBJECT_ID
AND s.schema_id = o.schema_id
AND i.OBJECT_ID = stats.OBJECT_ID
AND i.index_id = stats.index_id
AND stats.avg_fragmentation_in_percent >= 20
AND stats.page_count >= 1000
ORDER BY stats.avg_fragmentation_in_percent DESC,
stats.page_count DESC
Posted in Performance Tuning, sql server | Tagged: defragment index, DMV | 1 Comment »
