Data World

Posts Tagged ‘Performance Tuning’

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”.
Advertisements

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 – Find Fragmented Indexes

Posted by Pramod Singla on March 10, 2017


Problem Statement: Script to find fragmented Indexes.
Solution: No need to explain how killing fragmented indexes could be.Use following script to find the fragmented indexes and script to de-fragment them can be found here.

SELECT Db_name()              AS DBname,
Quotename(s.NAME)      AS CchemaName,
Quotename(o.NAME)      AS TableName,
i.NAME                 AS IndexName,
stats.Index_type_desc  AS IndexType,
stats.page_count       AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
ELSE 90
END                    AS [Fill Factor],
stats.avg_fragmentation_in_percent,
stats.fragment_count,
CASE
WHEN stats.index_level = 0 THEN ‘Leaf Level’
ELSE ‘Nonleaf Level’
END                    AS IndexLevel
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 »

SQL Server Tuning – Find Duplicate Stats

Posted by Pramod Singla on March 10, 2017


Problem Statement: Script to find the duplicate stats.
Solution: SQL server allows to create duplicate statistics but having duplicate statistics are overkill to system.Use following script to find the duplicate statistics.

;WITH all_stats
AS (SELECT s.object_id,
s.NAME AS stats_name,
s.stats_id,
s.has_filter,
s.auto_created,
sc.stats_column_id,
sc.column_id,
c.NAME AS column_name
FROM   sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
AND sc.stats_column_id = 1
INNER JOIN sys.columns AS c
ON c.object_id = s.object_id
AND c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE  objectproperty(s.object_id, ‘IsUserTable’) = 1)
SELECT row_number()
OVER(
partition BY s1.object_id, s1.column_name
ORDER BY s1.column_name) AS stats_no,
Object_name(s1.object_id)    AS table_name,
s1.stats_name                AS stats_name,
s2.stats_name                AS identical_stats_name,
s2.column_name
FROM   all_stats AS s1
INNER JOIN all_stats AS s2
ON s1.object_id = s2.object_id
AND s1.stats_id != s2.stats_id
AND s1.stats_column_id = s2.stats_column_id
AND s1.column_id = s2.column_id
AND s1.has_filter = s2.has_filter
AND s1.auto_created != s2.auto_created

Posted in Performance Tuning, sql server | Tagged: , , | Leave a Comment »