Archive for the ‘Performence Tuning’ Category

Performance boost up by Optimize for ad hoc workloads

December 1, 2013 Leave a comment

Performance boost up by Optimize for ad hoc workloads

I have faced a serious issue where the sales calculation is taking more than 7 hours whereas as per IBM it should be completed within 3 hours. The client is using Varicent sales application ( IBM software)to make the sales report. We are using 64 GB RAM and 8 core processor but still sql server faced the memory pressure. I am start to investigate the memory pressure and observed the cache memory issue. As per the application tendency it is using the lots ad hoc query. I am resolve the issue after enable the optimize for ad hoc workloads and time is comes down to 2 hours 30 minute. I have mentioned the details of optimize for ad hoc workloads option so we can determine how is it help us to improve the performance.

In SQL Server 2008 and later, we can use optimize for ad hoc workloads, which is a server-level setting, i.e. it will affect every single database on the server (as opposed to forced parameterization, which is database-specific).

With this setting enabled, SQL Server does not cache the plan of an ad hoc query the first time it is encountered. Instead, a plan-stub is cached that just indicates that the query has been seen before. Only if the query is seen a second time is the plan cached. This won’t reduce the number of compiles for ad hoc queries, but it will make it less likely that the plan cache will grow as much, since the initial stub takes up very little memory. As such,
it reduces the chances that other plans which could be reusable will be discarded due to memory pressure.

To enable the optimize for ad hoc workloads setting, use sp_configure, as shown

EXEC sp_configure ‘show advanced options’,1
EXEC sp_configure ‘optimize for ad hoc workloads’,1

Categories: Performence Tuning

Procedure need to follow to determine the SAN issue.

December 1, 2013 Leave a comment

Procedure need to follow to determine the SAN issue.
Normally everything is fine, however over the past few weeks we have been having huge lag spikes in sql server. It’s clear that SQL Server is waiting on disk I/O. But I keep getting told that it is because SQL Server is asking for abnormally high I/O. Which isn’t the case?
IO issue identification due to SAN:
Identify I/O bottlenecks by examining the latch waits. Run the following DMV query to find I/O latch wait statistics.
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats
where wait_type like ‘PAGEIOLATCH%’ and waiting_tasks_count > 0
order by wait_type

Identify the number of pending I/Os that are waiting to be completed for the entire SQL Server instance:
SELECT SUM(pending_disk_io_count) AS “Number of pending I/Os” FROM sys.dm_os_schedulers

Capture the details about the stalled I/O count reported by the above query.
SELECT * FROM sys.dm_io_pending_io_requests
To identify the stalled I/O per each database files in the SQL Server instance, run the below query:
SELECT DB_NAME(database_id) AS “Database”,file_id, io_stall_read_ms, io_stall_write_ms, io_stall FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Note: The values are cumulative since SQL Server started. You can use sample_ms column output to compare the output between two instances of the output and find which file is the cause of the stalled I/O. (sample_ms is the number of milliseconds since the computer was started). Once you identify the file and the drive with high stall IO, next step is to capture the IO latency
–virtual file latency
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
–avg bytes per IOP
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [io_stall_write_ms] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
— WHERE [vfs].[file_id] = 2 — log files
— ORDER BY [Latency] DESC
— ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;

First see which database files have the most IO bottleneck by running this query(Query by Glenn Berry)
SELECT DB_NAME(fs.database_id) AS [Database Name] ,
mf.physical_name ,
io_stall_read_ms ,
num_of_reads ,
CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,
io_stall_write_ms ,
num_of_writes ,
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] ,
io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,
num_of_reads + num_of_writes AS [total_io] ,
CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
+ num_of_writes ) AS NUMERIC(10,
1)) AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
 Then run this query to see the top ten events your server is waiting on(query by Jonathan Kehayias). You will also find similar query from Glenn Berry diagnostic queries.
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
100.0 * ( wait_time_ms – signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 — remove zero wait_time
AND wait_type NOT IN — filter out additional irrelevant waits
ORDER BY wait_time_ms DESC
Once you have this information at hand it would be much easier to troubleshoot the problem.

Once you identify the file and the drive with high stall IO, next step is to capture logical disk counters using Perfmon (Windows Performance monitor).
Logical Disk
Avg. Disk sec/read
Avg. Disk sec/write
Avg. Disk sec/transfer
%Disk time
Disk Read Bytes/sec
Disk Write Bytes/sec
Split IO/sec
IO Data Bytes/sec
IO Read Data Bytes/sec
IO Write Data Bytes/sec
Remember to capture the counters for respective drive or process not in _Total
Average Disk Sec/Transfer on a SQL Server machine is typically less than 15ms. If this value climbs, it may indicate that the I/O subsystem is not keeping up with I/O demand. To gauge I/O properly, here are the values for reference:
Any number Less than 10ms – very good
Between 10 – 20ms – okay
Between 20 – 50ms – slow, needs attention
Greater than 50ms – Serious I/O bottleneck
If this value is above the recommended value, check if SQL Server is flooding the IO subsystem – in case of poorly written IO intensive query and explore the opportunity of tuning such queries, an addition index or creating missing or updating existing statistics or re-writing the problematic query might help reduce the IO.

Categories: Performence Tuning

CPU pressure and optimal number of CPUs to handle the workload

December 1, 2013 Leave a comment

CPU pressure and optimal number of CPUs to handle the workload



AS a DBA we are faced the question from a customer asks :I am running a DSS/ OLAP like SQL job and it takes x amount of time, how can I reduce the time so the SQL job completes sooner, can I add more CPUs ? if yes, how many ? And we are getting high CPU alert also so here need to analysis the problem can be resolved by adding the CPU.


I am follow the below query step by step to decide the CPU pressure stage.



You can see the top wait stats and the percentage of waits at the current point in time by running the following query by Tim Ford and Glenn Berry.






wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,


FROM sys.dm_os_wait_stats

WHERE wait_type





) — filter out additional irrelevant waits

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2 ON W2.rn <= W1.rn





HAVING SUM(W2.pct) – W1.pct < 95; — percentage threshold;


Note: SOS_SCHEDULER_YIELD is a fairly common wait type and occurs when there is CPU pressure. SQL Server runs multiple threads and tries to allow all threads to run without problems. However, if all the threads are busy on each scheduler and can’t let other threads run, it will yield itself for another thread, which in turn creates the SOS_SCHEDULER_YIELD wait type.


To see an average of current tasks and current waiting tasks you can use the following query:


SELECT AVG(current_tasks_count) AS [Avg Current Task],

AVG(runnable_tasks_count) AS [Avg Wait Task]

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255


–query we can see how many processors SQL Server is using:


FROM sys.dm_os_schedulers 
AND is_online = 1


The following query will show current tasks running, CPU time, wait type, etc.:


SELECT s2.text, 
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS s2  
WHERE status <> 'background'


Query to find out how many CPU on an average will help to scale the workload better.


When concurrent users apply simultaneous DSS/OLAP like CPU intensive workload, there could be CPU pressure. We can conclude presence of CPU pressure  by use of the above command .


In this case one can run the below query to find out how many CPU on an average will help to scale the workload better. It might be more informative to collect the below information in short time intervals (many samples) than just once to understand during which time of the workload application the CPU pressure was the most. Single sample will lead to average additional CPUs necessary for the entire workload duration.


  1. Reset Wait Stats

dbcc sqlperf(‘sys.dm_os_wait_stats’, clear)–example provided by

2. Apply workload (you can find sample workload query at the end of this article, you need to execute the sample workload query simultaneously in many sessions to simulate concurrent user tasks).

3. Run the below query to find Additional CPUs Necessary it is important to run the query right after the workload completes to get reliable information.

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) /si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type =‘SOS_SCHEDULER_YIELD’

–example provided by http://www.sqlworkshops.coms

The SQL Server 2008 query:

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / si.os_quantum *scheduler_count), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type =‘SOS_SCHEDULER_YIELD’

–example provided by


How did you ensure that SQL server performance has been degraded due to memory pressure?

Let’s take a look at sys.dm_os_ring_buffers to understand the scenario .

Running this query against the system:

SELECT * FROM sys.dm_os_ring_buffers AS dorb;

There are only four columns, but the final column is XML and that’s where the good information can be found. There are a number of different ring buffers but we’re only interested in the type called RING_BUFFER_RESOURCE_MONITOR. This records changes to memory allocations, which is a great way to see when you’re running out of memory because a message that your memory is low is a pretty good indicator that you might be seeing memory pressure.

The information presented in the MemoryNode and the MemoryRecord are useful for attempting to figure out what went wrong, but the key points are up in the ResourceMonitor element with the Notification, IndicatorsProcess, and IndicatorsSystem values. First, the notification tells us that this was RESOURCE_MEMPHYSICAL_LOW message that was captured in the ring buffers. That means that physical memory was low. The next two indicators let us know what was low. If the IndicatorsProcess returns 0 and the IndicatorsSystem returns a value then the problem was system wide. But, in our case the IndicatorsProcess has a value and IndicatorsSystem is returning 0. This means that this alert was for a single process that ran suffered from low memory, not the entire system. The values break down as follows:

Value Meaning 1 High Physical Memory 2 Low Physical Memory 4 Low Virtual Memory

Categories: Performence Tuning

SQL Script to Update Usage for all databases and rectify inaccuracies in the catalog view

February 5, 2013 26 comments
Categories: Performence Tuning

Database statistics update issue along with a resolution

December 24, 2012 Leave a comment

In our environment we execute the sp_updatestats job on a daily basis at 5 am, but we have faced severe performance issue due to inaccuracy of statistics and as a result query is unable to use the optimal execution plan.

Finding the state of your statistics


Up-to-date statistics help ensure that the most appropriate index is chosen to obtain the underlying data. They also help ensure that the correct index access mechanism is chosen, for example, seek or lookup. Running the SQL script given in the following listing will identify the current state of your statistics.


SELECT AS SchemaName

, AS TableName

, AS IndexName

, STATS_DATE(,s.indid) AS ‘Statistics Last Updated’

, s.rowcnt AS ‘Row Count’

, s.rowmodctr AS ‘Number Of Changes’

, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS

DECIMAL(28,2)) * 100.0)

AS DECIMAL(28,2)) AS ‘% Rows Changed’

FROM sys.sysindexes s

INNER JOIN sys.tables st ON st.[object_id] = s.[id]

INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]

WHERE > 100

AND s.indid > 0

AND s.rowcnt >= 500

ORDER BY SchemaName, TableName, IndexName

Reason :

The system stored procedure sp_updatestats does not provide the ability to update your statistics with the FULLSCAN option. When we are faced with an urgent performance issue updating statistics with the “WITH FULLSCAN” option can be exactly what SQL Server needs to find the optimal execution plan and update the statistics


We can use rhe EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’ at specific database level to update the statistics correctly or we can schedule the Rebuild job daily basis if it is permitted by business owner or did not conflict with any other job.

Categories: Performence Tuning

Troubleshoot SQL server performance issue due to user-defined scalar functions

December 24, 2012 Leave a comment

We have faced a strange issue in Database server where it took 20 hours time to upload the 1 million rows. The issue is not related with Database locking and resource consuming issue. The database server has 16 CPU and 32 GB ram RAM. To fixed such kind of issue first we have to look out Inefficient query plan, When generating an execution plan for a query, the SQL Server optimizer attempts to choose a plan that provides the fastest response time for that query. The most significant input the optimizer uses in evaluating the cost of various alternative query plans is the cardinality estimates for each operator, which you can see in the Show plan (EstimateRows and EstimateExecutions attributes). By the below step I am able to figure out that there is a need to use some other process to upload the batch instead of Function. It is a user-defined scalar function and UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It’s the row by row processing that seems to slow SQL Server the most.  It does not create any issue for a small number of rows, but this penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. No matter how simple a UDF there’s a large performance penalty paid when they’re used. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. It turns a Seek into a Scan.

Categories: Performence Tuning