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'
Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage
(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,
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'
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.
- 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.
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
- Network Bottleneck
- Poor Indexing Strategy (Missing Index, Fragmented Index, Un-Used Index)
- Out-of-date/missing statistics
- T-SQL Code
- Application Code