Home » Performance Tuning » Page 3

Performance Tuning

sql server DBA 999

SQL Server Memory

SQL Server divides the total memory dedicated to it into two parts referred to as Buffer Pool and Memory to Leave.
Buffer Pool is the larger of the two and is used for most memory operations like stored procedure query plan generation, caching data and creating indexes.
Memory to Leave is dedicated to executing linked server queries, extended stored procedure calls and OLE automation procedure calls. By default, Memory to Leave is 384 MB and the balance of total memory available to SQL Server will determine the size of Buffer Pool.
An important counter of the performance of buffer cache is the Buffer Cache Hit Ratio performance counter. It indicates the percentage of data pages found in the buffer cache … Read the rest

sql server DBA 999

SQL Server Tempdb Management

Some Facts about TempDB
The tempdb is a temporary workspace which is normally used for:
• Storage of explicitly created temporary tables.
• Worktables that hold intermediate results created during query processing and sorting.
• Materialized static cursors.
SQL Server records only enough information in the tempdb transaction log to roll back a transaction but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in the tempdb. In addition, there is no need to log information to redo any transactions because the tempdb is re-created every time you restart SQL Server.
Therefore, it does not have any transactions to roll forward or roll back. When SQL Server starts, the tempdb is re-created by using … Read the rest

sql server DBA 999

SQL Server Optimistic Concurrency

SQL Server Concurrency Terminology
Optimistic concurrency control inside the SQL Server 2005/2008 database engine technically means that row versioning is used instead of a pessimistic locking approach. I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control by using hints like (nolock).
Applications have used optimistic concurrency long before row versioning was introduced in SQL Server 2005/2008. In fact, the rowversion data type (a.k.a. timestamp) exists specifically to facilitate optimistic concurrency control.

Choosing the SQL Server concurrency control features (transaction isolation levels, transactions, locking hints) that are most appropriate for the task at hand without getting tripped up on terminology. For example, SQL … Read the rest

sql server DBA 999

SQL Server Disk I/O Specific issues

If you suspect that your SQL Server instance is having Disk I/O issues, these queries can help furnish proof that you are under I/O pressure, and they can help pinpoint what is causing the problem.
Before you run determine that you have I/O issues, please verify that by running Top Wait Stats rollup query.


-- Always look at Avg Disk Sec/Read and Avg Disk Sec/Write in PerfMon for each Physical Disk

-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';

-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';… Read the rest