SQL Server services
Depending on the components that you decide to install, SQL Server Setup installs the following services:
- SQL Server Database Services – The service for the SQL Server relational Database Engine.
- SQL Server Agent – Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks.
- Analysis Services – Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
- Reporting Services – Manages, executes, creates, schedules, and delivers reports.
- Integration Services – Provides management support for Integration Services package storage and execution.
- SQL Server Browser – The name resolution service that provides SQL Server connection information for client computers.
- Full-text search – Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.
- SQL Server Active Directory Helper – Publishes and manages SQL Server services in Active Directory.
- SQL Writer – Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.
Increasing Max worker threads – A negative impact
There are many instances when Administrators come to think that increasing Max Worker Threads shall increase performance on a server with thousands of users. This might not be always and preferable not to touch this configuration setting unless we are backed by a lot of data, research and testing.
Increasing the Max Worker threads shall directly influence the MTL sizing as the MTL is computed by the 256[default]+0.5*[Max Worker Threads] MB. Incase the MWT is increased the actual memory that might be needed for the Buffer Pool might be lying unused in the MTL.
Also, increasing the MWT shall increase the Thread contention at the UMS schedulers and this inturn shall result in a negative impact on the performance.
For a better understanding on the UMS architecture and the influence of MWT on it, check out here and also the Books Online.
Query Timeouts – A client side feature only.
Yes thats true.. The feature of a Query timeout is a Client side feature and the Database engine doesnt have any involvement in it except accepting a “Attention event” from the Client.
Once the SQL Server database engine receives a Attention Event, it tries to stop serving to the session at the earliest but not immediately. This would take care of any locks that need to be freed before aborting the query execution.
For a better understanding of this Event and Query Timeouts check out Ken Hendersen’s blog
Concurrency enhancements for the tempdb database
Here is a great link that describes Concurrency enhancements for the tempdb database. This also helps understand the way an object gets a page in the memory.
“During object creation, two (2) pages must be allocated from a mixed extent and assigned to the new object. One page is for the Index Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data.
As part of allocating a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to find out which mixed page is free to be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Appropriate synchronization is maintained to make changes to the PFS and SGAM pages; and that can stall other modifiers for short periods.
When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This results in intense contention on the SGAM page when several mixed page allocations are underway, which can cause the problems documented in the “Symptoms” section of this article. ”
A single GAM page for 4GB of Tempdb file means there is heavy contention for that page. This is the same with the PFS as well where the contention is doubled with allocation and deallocation of pages. This is main reason to have multiple Tempdb files and with the same size. IF any single Tempdb file is bigger again it can be frequently contended for during the round-robin algorithm for allocating a page.
Also, “The auto-grow of tempdb data files can also interfere with the proportional fill algorithm. Therefore, it may be a good idea to turn off the auto-grow feature for the tempdb data files. If the auto-grow option is turned off, you must make sure to create the data files so that they are large enough to prevent the server from experiencing a lack of disk space with tempdb.”
Trace Flags in SQL Server 2008.
A brief description of Traceflags in SQL 2008.
260 – Prints versioning for DLLs for XPs.
1204 – Resources involved in Deadlocks and also Victim details.
1211 – No lock escalation at all.
1222 – Resources involved in Deadlock in XML format and also victims details.
1224 – No lock escalation unless severe memory pressure.
2528 – No parallellization of DBCC Commands.
3205 – No compressed dumps to tapes though file compression is enabled on Tapes.
3226 – No messages on Backups in the Errorlog.
3608 – Doesnt start any DB than Master on Startup.
3625 – Limits error message
4616 – Server level metadata visible to App roles.
6527 – No memory dumps in CLR integration on first error occurance.
7806 – Enable DAC on SQL Express.
Enable/Disable traceflag using DBCC Traceon and DBCC Traceoff. While Server startup use the -T startup option to SQLSrvr.exe. Use DBCC Tracestatus to find the traces enabled on the server.
Dont confuse the functions involving Profiler Traces with those of these Traceflags. The ones that involve Traceflags are DBCC commands.
Problems with Mixed Extents.
SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage
SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s). The logic was also added to SQL Server 2005 in a CU release, KB article 936185.
The MORE INFORMATION section of the article states: “The hotfix that this article describes affects only the tempdb database. Additionally, you do not have to have trace flag 1118 after you install this hotfix. However, you can still use trace flag 1118.”
Some have interpreted this to mean the 1118 trace flag has been deprecated. This is NOT the case and I have asked for the text to be updated. Trace flag 1118 may still be needed along with the other suggestions in section “Troubleshooting contention caused by to DML operations” ofhttp://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx.
The blog that discusses this is here.
SQL Server on Compressed Filesystems.
Many of us know that the filesystem on which the SQL Datafiles exist shouldnt be Compressed. The main reason that every one knows – it takes more effort to get the IO operations completed. Added to that the Windows OS doesnt entertain any async IO operations on a Compressed volume. This makes a huge difference when the application, most probably SQL Server, can give a better performance calling the async IO calls.
More over here by Ken Henderson’s Why you shouldn’t compress SQL Server data and log files
Lightweight Thread Pooling / Fibre Mode
One of the rare tweaks that one would do on a SQl Server when excessive Context switches are found on SQLservr.exe, is to apply the Fibre mode. In this Fibre mode there is a higher probability that SQl Server gets hung or some applications doesnt work as they are supposed to. These applications might not be fibre-safe. Infact, though SQL Server runs in fibre-mode, at the OS level still the process called SQL Server executes in thread mode. It is just that internal to the process the thread is finely used as a set of fibres that are light weight, meaning – not heavy in terms of their possessions so the context switching between fibres doesnt eat much of the resources.
An application thats using datastructures pertaining to a thread like Thread Local Storage, shall not be called “fibre-safe” and are prone to errors in fibre mode.
Ken Henderson’s blog on msdn gives an insight into this in particular detail. One can find it here.
Visitors to this site.
Duty Free!! 