Data World

Archive for the ‘Performance Tuning’ Category

SQL Server Tuning – Top 10 high memory consuming Queries

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  
Advertisements

Posted in DBA, Interview Questions, Performance Tuning, Scripts | Tagged: , , , , | Leave a Comment »

SQL Server Tuning: Quick overview of SQL Server performance using Activity Monitor

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: , | Leave a Comment »

SQL Server Tuning: Find Slow running queries using the Reports in SSMS

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: , , , , | Leave a Comment »

SQL Server Tuning : Top 10 CPU Intensive Queries

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: , , | Leave a Comment »

SQL Server Tuning – De-fragment Indexes

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: , | 1 Comment »