Tag Archives: Performance Tuning

by

How to check Lock Pages In Memory is enabled?

You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.

exec xp_readerrorlog 0, 1, 'locked pages'

To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.

exec xp_readerrorlog 0, 1, 'lock pages in memory'
by

Script to find out most CPU bound Queries

Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage

SELECT highest_cpu_queries.plan_handle, 
(highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) AS AverageCPU, highest_cpu_queries.execution_count, highest_cpu_queries.total_worker_time, highest_cpu_queries.plan_generation_num, highest_cpu_queries.creation_time, highest_cpu_queries.last_execution_time, 
highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads,
q.dbid, q.objectid, q.number, q.encrypted, q.[text]
FROM (SELECT TOP 25 qs.plan_handle, qs.total_worker_time, qs.last_execution_time,
qs.plan_generation_num, qs.creation_time, qs.execution_count, qs.last_physical_reads,
qs.min_physical_reads FROM sys.dm_exec_query_stats qs 
ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY AverageCPU DESC

Above script will return the queries which are completed. You can check the active sessions and CPU details using below query.

SELECT SPID, CPU, s2.text, open_tran, status, program_name,
net_library, loginame FROM sys.sysprocesses 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
where cpu > 5000 and status = 'runnable'
by

Performance Tuning Series

Lock Pages in Memory

You can prevent the Windows operating system from paging out the buffer pool memory of the SQL Server process by locking the memory that is allocated for the buffer pool in physical memory. You lock the memory by assigning the Lock pages in memory user right to the user account that is used as the startup account of the SQL Server service.

Model Database Whenever we create a new database, it will use model as template. Configure model DB for the Auto Shrink OFF, Auto Update/Create Statistics on

Maximum Worker Threads: Based on the load increase the maximum work thread.

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond 4 GB memory limit. The AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

On both 32-bit and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which might have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be granted for the account that runs SQL Server.

 

 

Please note:

  • Turn auto-shrink off.
  • Make sure auto-update of statistics is turned on.
  • If a database is read only, set it to read only.
  • Use triggers very judiciously. They mostly operate in the background making them difficult to monitor and troubleshoot.
  • Be very careful of auto growth settings on the database. 10% auto growth will be fine when the database is 500mb. It makes a huge difference when the system is 50gb. For larger databases, change the setting to grow by a fixed amount rather than a percentage of total database size.
  • Files and FileGroups
  • Other factors that can affect the performance of your system include the way the files and file groups are laid out. You should be creating multiple files for your databases to optimize performance. A baseline for this would be to create one file for the logs, another for the data (defined by the clustered index), and another for non-clustered indexes. Additional files may be necessary to separate out BLOB data or XML data or unusually active tables, each onto its own file, and where possible, onto its own disk. This has been found to be true even on SAN systems because distributing the load takes further advantage of the architecture of the SAN.
  • Data Types
  • Define the data types that you need, not what you think you might need someday. A phone number is a string, not a number. Define the length of field that you need and enforce that length.

.

by

Common cause of the performance issues

Below are the most common reasons for the performance issues.


  • SQL Server Configuration Issues

  • Database/table/schema Design and Configuration Issues

  • CPU/IO/Memory Bottleneck

  • Blocking

  • Network Bottleneck

  • Poor Indexing Strategy (Missing Index, Fragmented Index, Un-Used Index)

  • Out-of-date/missing statistics

  • T-SQL Code

  • Application Code

by

Script to get the last update statistics date

Below script will execute against the sys.Objects,sys.indexes and sys.stats. Script will return last statistics update date and meta data of  statistics. Script will return the result for table and view level statistics.

In this script I have used the function STATS_DATE which will return last updated statistics date.

Syntax
STATS_DATE ( table_id , index_id )

SELECT sysobj.name AS objectname,
sysindex.name AS indexname,
Stats_date(sysindex.[object_id], sysindex.index_id) AS
[Statistics Update Date],
CASE sysstats.auto_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyqueryprocessor,
CASE sysstats.user_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyuser,
CASE sysstats.no_recompute
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedwithnorecomputeoption
FROM sys.objects AS sysobj WITH (nolock)
INNER JOIN sys.indexes AS sysindex WITH (nolock)
ON sysobj.[object_id] = sysindex.[object_id]
INNER JOIN sys.stats AS sysstats WITH (nolock)
ON sysindex.[object_id] = sysstats.[object_id]
AND sysindex.index_id = sysstats.stats_id
WHERE sysobj.[type] IN ( ‘U’, ‘V’ )
ORDER BY Stats_date(sysindex.[object_id], sysindex.index_id);