SQL Server Performance Tuning – 4

SQL Server Performance Tuning – 4

1. What are the lockable resources in SQL Server?

The following table shows the resources that the Database Engine can lock

Resource	Description
RID	A row identifier used to lock a single row within a heap.
KEY	A row lock within an index used to protect key ranges in serializable transactions.
PAGE	An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENT	A contiguous group of eight pages, such as data or index pages.
HoBT	A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
TABLE	The entire table, including all data and indexes.
FILE	A database file.
APPLICATION	An application-specified resource.
METADATA	Metadata locks.
ALLOCATION_UNIT	An allocation unit.
DATABASE	The entire database.

2. What is Dynamic Locking?

Microsoft SQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan.

3. What are the advantages of Dynamic Locking in SQL Server?

Dynamic locking has the following advantages:

• Simplified database administration. Database administrators do not have to adjust lock escalation thresholds.
• Increased performance. The Database Engine minimizes system overhead by using locks appropriate to the task.
• Application developers can concentrate on development. The Database Engine adjusts locking automatically.

4. What is a latch?

A latch can be defined as an object that ensures data integrity on other objects in SQL Server memory, particularly pages. They are a logical construct that ensures controlled access to a resource and isolationism when required for pages in use. In contrast to locks, latches are an internal SQL Server mechanism that isn’t exposed outside the SQLOS.

5. What are the types of latches?

Roughly we can classify latches in 2 categories.

• buffer latches
• non-buffer latches

6. What is buffer latch?

• Buffer (BUF) latch:

The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction. These are indicated in the sysprocesses table by the PAGELATCH waittypes.

7. What is non buffer latch?

• Non-buffer (Non-BUF) latch:

The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrancy protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.

8. What is Optimistic and pessimistic locking?

Pessimistic locking locks resources as they are acquired, holding the locks for the duration of the transaction. Pessimistic locking is more likely to cause deadlocks. A deadlock occurs with two transactions when each blocks access to resources needed by the other.

Optimistic locking assumes that conflicts between transactions are unlikely but might occur. Transactions are allowed to execute without locking resources. The only time resources are checked for a conflict is when data changes are made. If a conflict occurs, the transaction is rolled back.

9. What is Row versioning?

Microsoft introduced row versioning as an alternative to shared locks in SQL Server 2005. With row versioning, rows are read into tempdb at the beginning of a transaction and the transaction uses that copy of those rows throughout the duration of the transaction. Row versioning protects the transaction from:

• Dirty reads
• Nonrepeatable reads
• Phantoms

Even when row versioning is used, SQL Server still takes an exclusive lock on a row before updating it.

Row versioning allows for optimum concurrency (multiple users accessing data at the same time), while providing good protection.

10. How LRU K algorithm is associated with SQL Server?

SQL Server uses LRU (Least Recently Used) algorithm to keep track of pages that are aged in the buffer pool. It increments a counter every time a page is referenced and decrements the counter every time the lazy writer process sweeps the page. Any worker thread will check the memory status of Buffer Pool to make sure that there are healthy numbers of free pages to honor incoming new requests.

Note that: SQL Server will always keep a minimum number of free pages on the free list, so that it can serve incoming requests without any significant delay. The amount of free space is calculated based on Buffer Pool and amount of incoming requests.

11. What is Halloween protection?

“Halloween protection” in database systems refers to a solution for a problem that can occur in update queries. The problem occurs when an update itself affects the rows selected for update.  For example, imagine a company wants to give every employee a 10% pay increase. If the update query is unlucky enough to walk the salary index then the same row may be selected, updated, the update moves the row ahead in the index, and then the row will be redundantly selected again and again.  This problem is corrected by isolating the rows chosen from the effects of the update itself.

12. What is the shortcut key to invoke Task manager?

Open Task ManagerCtrl+Shift+Esc

13. What is the formula that SQL Server uses to calculate max worker threads?

If ‘Max. Worker threads’ to 0 then SQL Server will decide the worker thread count based on formula below:

For 32 bit operating system:

Total available logical CPU’s <= 4 :     max worker threads = 256

Total available logical CPU’s > 4 :        max worker threads = 256 + ((logical CPUS’s - 4) * 8)

For 64 bit operating system:

Total available logical CPU’s <= 4 :     max worker threads = 512

Total available logical CPU’s > 4 :        max worker threads = 512 + ((logical CPUS’s - 4) * 16)

14. What is the Query below gives the amount of worker threads created at the moment in your SQL Server?

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

15. What are statistics in SQL Server?

Statistics refers to the statistical information about the distribution of values in one or more columns of a table or an index. The SQL Server Query Optimizer uses this statistical information to estimate the cardinality, or number of rows, in the query result to be returned, which enables the SQL Server Query Optimizer to create a high-quality query execution plan. For example, based on these statistical information SQL Server Query Optimizer might decide whether to use the index seek operator or a more resource-intensive index scan operator in order to provide optimal query performance. In this article series, I am going to talk about statistics in detail.

16. What are various types of SQL server database statistics?

Index statistics: Created automatically when an index (both clustered and non-clustered) is created. These will have the same name as the index and will exist as long as the index exists.

Column statistics: Created manually by the DBA using the ‘CREATE STATISTICS’ command, or automatically if the “Auto Create Statistics” option is set to “True”. Column statistics can be created, modified and dropped at will.

17. What kind of information is stored by Statistics?

Statistics contain two different types of information about the data; density and distribution. Density is simply the inverse of the count of distinct values for the column or columns. The distribution is a representation of the data contained in the first column of the statistic. This information is stored in a histogram; the histogram contains up to 200 steps with a lower and upper limit and contains the count of values that fall between both limits.

18. What is “Auto Create Statistics” properties of a SQL Server database?

This database property allows SQL Server to automatically create stats for a single non-indexed column if they are missing when it is used in a where or join condition. This ensures the optimizer will have the necessary information to choose a plan. The statistics automatically created by SQL Server will start with _WA_ in their name.

19. What is “Auto UpdateStatistics” properties of a SQL Server database?

This database property allows SQL Server to automatically update the statistics when they are deemed outdated. The update occurs before executing a query if certain conditions are met, or after the query is executed if Auto Update Statistics Asynchronously is used instead.

20. What conditions trigger “Auto UpdateStatistics” process?

The three conditions that will trigger an update if one is met are:

• Table had 0 rows and increases to one or more rows.
• Table had less than 500 rows and there is an increase of 500 rows or more since the last update
• Table has over 500 rows and there is an increase of 500 + 20% of the table size since the last update

21. What’s New in SQL Server 2012 with Respect to Statistics?

Starting with SQL Server 2012, SQL Server Query Optimizer creates and maintains temporary statistics in tempdb database for the read-only database or read-only snapshot database or readable secondaries in the AlwaysOn cluster in order to ensure your queries perform better. These temporary statistics are created and maintained by SQL Server Query Optimizer only; though you can delete them when not needed. These statistics are suffixed with “_readonly_database_statistic” to differentiate it from the regular statistics.

22. What is the impact of stale statistics over database performance?

Since SQL Server optimizer use the statistics to create execution plans for the SQL Server queries. It is very important to update the statistics time to time to make sure that the SQL Server uses the best optimized plan to execute the SQL Server queries.

23. How to check when the statistics were updated for a table.

We can use stats_date() function to check the statistics updated date.

24. How can we update the statistics on a table?

To update statistics for a particular table we can use below command.

UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN

25. What is a scheduler in SQL Server?

(SOS Scheduler)– The object that manages thread scheduling in SQL Server and allows threads to be exposed to the CPU (described in sys.dm_os_schedulers). It does not control things but lets the workers work with each other and relies on their cooperation (co-operative scheduling mode). Each scheduler /master (one per logical CPU) accepts new tasks and hands them off to workers. SOS Scheduler allows one worker at a time to be exposed to the CPU.

26. What are the components of SQL Server scheduler?

Scheduler

A schedule has three important component, Processor,Waiter List and Runnable queue.

A Processor is the actual processor which processes the thread one at a time.

Waiter List is the list of threads waiting for resources.

Runnable Queue is a queue of threads which has all the resource it need to process but waiting for its turn to get into the processor.

Scheduler put the thread in to Runnable Queue then move the thread to Processor and migrate to Waiter List. Each thread keeps going through these three components until the thread works completes.

27. What is the difference between preemptive and non-preemptive scheduling?

Preemptive Scheduling is when a computer process is interrupted and the CPU’s power is given over to another process with a higher priority. This type of scheduling occurs when a process switches from running state to a ready state or from a waiting state to a ready state.

Non-Preemptive Scheduling

Non-Preemptive Scheduling allows the process to run through to completion before moving onto the next task.

28. What is SQLIO tool?

SQLIO is a generic tool for testing your disk subsystem. It allows you to specify whether to test random/sequential, data block sizes, queue depth, threading, etc. Despite its name, SQLIO is not part of SQL Server, yet it can be an invaluable tool for determining whether the disk drives slated to support SQL Server can meet the system’s I/O needs. The tool lets you push the limits of your disk subsystem in order to determine the hardware’s I/O capacity and to identify performance-related issues before deploying SQL Server. You can use the tool to test a variety of I/O types and sizes against various disk subsystems, including direct attached storage, network attached storage, and storage area networks.

29. What is SQLIOSim tool?

SQLIOSim is a tool that tries to emulate the patterns that SQL Server would expose to your system. The sqliosim utility simulates SQL Server read, write, sort, backup, checkpoint and read-ahead patterns for both user and system activity. The tool creates sample data and log files in order to perform SQL Server reliability and integrity tests on the disk subsystem. Because the utility runs independently of the SQL Server engine, you can use it before installing SQL Server in order to ensure that new hardware can handle your expected loads.

30. What are the performance related benefits which are available in SQL Server enterprise edition?

There are some key enterprise edition only performance benefits across RAM, Parallelism, Query Plans and DISK I/O that will lead to better performance on high end systems.

1. Lock Pages in Memory
2. Advanced Scanning (aka Merry-go-round scan)
3. Larger Read Ahead Buffering on Storage Devices
4. Large Page Extensions
5. Parallel index Operations

31. How merry-go-round scanning is associated with SQL Server?

In SQL Server Enterprise Edition, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.