1. Use appropriate locking level
you can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to tell SQL Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
This code turns off both row and page locking for the table, thus only table locking is available.
2. Keep all Transact-SQL transactions as short as possible.
3. An often overlooked cause of locking is an I/O bottleneck.
4. To help reduce the amount of time tables are locked, which hurts concurrency and performance, avoid interleaving reads and database changes within the same transaction.
5. Any conditional logic, variable assignment, and other related preliminary setup should be done outside of transactions
6. Encapsulate all transactions within stored procedures
7. If you have a client application that needs to "check-out" data
for more information in detail please review:
Thursday, May 27, 2010
SET DEADLOCK_PRIORITY (Transact-SQL)
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | | @deadlock_var | @deadlock_intvar }
::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
LOW
Specifies that the current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that the current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5.
NORMAL
Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0. It also specifies that the current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. NORMAL is the default priority.
HIGH
Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.
Is an integer value range (-10 to 10) to provide 21 levels of deadlock priority. It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value, but will not be the deadlock victim if the other sessions are running at a deadlock priority value lower than the value of the current session. It also specifies that the current session is eligible to be the deadlock victim if another session is running with a deadlock priority value that is the same as the current session. LOW maps to -5, NORMAL to 0, and HIGH to 5.
@deadlock_var
Is a character variable specifying the deadlock priority. The variable must be set to a value of 'LOW', 'NORMAL' or 'HIGH'. The variable must be large enough to hold the entire string.
@deadlock_intvar
Is an integer variable specifying the deadlock priority. The variable must be set to an integer value in the range (-10 to 10).
Remarks
Deadlocks arise when two sessions are both waiting for access to resources locked by the other. When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the victim is rolled back and deadlock error message 1205 is returned to the client. This releases all of the locks held by that session, allowing the other session to proceed.
Which session is chosen as the deadlock victim depends on each session's deadlock priority:
* If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim. For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll back.
* If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.
SET DEADLOCK_PRIORITY is set at execute or run time and not at parse time.
DECLARE @deadlock_var NCHAR(3);
SET @deadlock_var = N'LOW';
SET DEADLOCK_PRIORITY @deadlock_var;
GO
for more detail :
LOW
Specifies that the current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that the current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5.
NORMAL
Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0. It also specifies that the current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. NORMAL is the default priority.
HIGH
Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.
Is an integer value range (-10 to 10) to provide 21 levels of deadlock priority. It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value, but will not be the deadlock victim if the other sessions are running at a deadlock priority value lower than the value of the current session. It also specifies that the current session is eligible to be the deadlock victim if another session is running with a deadlock priority value that is the same as the current session. LOW maps to -5, NORMAL to 0, and HIGH to 5.
@deadlock_var
Is a character variable specifying the deadlock priority. The variable must be set to a value of 'LOW', 'NORMAL' or 'HIGH'. The variable must be large enough to hold the entire string.
@deadlock_intvar
Is an integer variable specifying the deadlock priority. The variable must be set to an integer value in the range (-10 to 10).
Remarks
Deadlocks arise when two sessions are both waiting for access to resources locked by the other. When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the victim is rolled back and deadlock error message 1205 is returned to the client. This releases all of the locks held by that session, allowing the other session to proceed.
Which session is chosen as the deadlock victim depends on each session's deadlock priority:
* If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim. For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll back.
* If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.
SET DEADLOCK_PRIORITY is set at execute or run time and not at parse time.
DECLARE @deadlock_var NCHAR(3);
SET @deadlock_var = N'LOW';
SET DEADLOCK_PRIORITY @deadlock_var;
GO
for more detail :
Understanding sp_who SQL Server procedure status
sp_who Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.
1. dormant. SQL Server is resetting the session.
2. running. The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.
3. background. The session is running a background task, such as deadlock detection.
4. rollback. The session has a transaction rollback in process.
5. pending. The session is waiting for a worker thread to become available.
6. runnable. The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
7. spinloop. The session's task is waiting for a spinlock to become free.
8. suspended. The session is waiting for an event, such as I/O, to complete.
1. dormant. SQL Server is resetting the session.
2. running. The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.
3. background. The session is running a background task, such as deadlock detection.
4. rollback. The session has a transaction rollback in process.
5. pending. The session is waiting for a worker thread to become available.
6. runnable. The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
7. spinloop. The session's task is waiting for a spinlock to become free.
8. suspended. The session is waiting for an event, such as I/O, to complete.
Understanding sp_who SQL Server procedure
sp_who provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.
exec sp_who
A blocking process, which may have an exclusive lock, is one that is holding resources that another process needs.
In case of parallel processing, subthreads are created for the specific session ID. The main thread is indicated as spid = and ecid =0. The other subthreads have the same spid = , but with ecid > 0.
In SQL Server 2000 and later, all orphaned distributed transactions are assigned the session ID value of '-2'. Orphaned distributed transactions are distributed transactions that are not associated with any session ID.
SQL Server 2000 and later reserves session ID values from 1 through 50 for internal use, and session ID values 51 or higher represent user sessions.
By default user sees only the current session processes or you need VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server.
Following are sp_who columns information
1. spid (smallint) Session ID
2. ecid (smallint) Execution context ID of a given thread associated with a specific session ID. i.e. 0,1,2,3,…n. values >0 represents sub-thread.
3. Status(nchar(30)) Process status.
4. Loginame(nchar(128)) Login name associated with the particular process.
5. Hostname(nchar(128)) Host or computer name for each process.
6. Blk(char(5)) Session ID for the blocking process only, if one exists. Otherwise, this column is zero. This column will return a '-2' for the blocking orphaned transaction.
7. Dbname(nchar(128)) Database used by the process.
8. Cmd(nchar(16)) Database Engine command (Transact-SQL statement, internal Database Engine process, and so on) executing for the process.
9. request_id(int) ID for requests running in a specific session.
Examples
Listing all current processes
USE master;
GO
EXEC sp_who;
GO
Listing a specific user's process
USE master;
GO
EXEC sp_who 'janetl';
GO
Displaying all active processes
USE master;
GO
EXEC sp_who 'active';
GO
Displaying a specific process identified by a session ID
USE master;
GO
EXEC sp_who '10' --specifies the process_id;
GO
you can get more information from http://msdn.microsoft.com/en-us/library/ms174313.aspx
exec sp_who
A blocking process, which may have an exclusive lock, is one that is holding resources that another process needs.
In case of parallel processing, subthreads are created for the specific session ID. The main thread is indicated as spid =
In SQL Server 2000 and later, all orphaned distributed transactions are assigned the session ID value of '-2'. Orphaned distributed transactions are distributed transactions that are not associated with any session ID.
SQL Server 2000 and later reserves session ID values from 1 through 50 for internal use, and session ID values 51 or higher represent user sessions.
By default user sees only the current session processes or you need VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server.
Following are sp_who columns information
1. spid (smallint) Session ID
2. ecid (smallint) Execution context ID of a given thread associated with a specific session ID. i.e. 0,1,2,3,…n. values >0 represents sub-thread.
3. Status(nchar(30)) Process status.
4. Loginame(nchar(128)) Login name associated with the particular process.
5. Hostname(nchar(128)) Host or computer name for each process.
6. Blk(char(5)) Session ID for the blocking process only, if one exists. Otherwise, this column is zero. This column will return a '-2' for the blocking orphaned transaction.
7. Dbname(nchar(128)) Database used by the process.
8. Cmd(nchar(16)) Database Engine command (Transact-SQL statement, internal Database Engine process, and so on) executing for the process.
9. request_id(int) ID for requests running in a specific session.
Examples
Listing all current processes
USE master;
GO
EXEC sp_who;
GO
Listing a specific user's process
USE master;
GO
EXEC sp_who 'janetl';
GO
Displaying all active processes
USE master;
GO
EXEC sp_who 'active';
GO
Displaying a specific process identified by a session ID
USE master;
GO
EXEC sp_who '10' --specifies the process_id;
GO
you can get more information from http://msdn.microsoft.com/en-us/library/ms174313.aspx
Monday, May 24, 2010
Features Supported by the Editions of SQL Server 2008
I got this information from MSDN.
Features Supported by the Editions of SQL Server 2008
Scalability Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Partitioning | Yes | ||||||
Data compression | Yes | ||||||
Resource governor | Yes | ||||||
Partition table parallelism | Yes |
High Availability Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Multi-instance support | 50 | 16 | 16 | 16 | 16 | 16 | 16 |
Online system changes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Log shipping | Yes | Yes | Yes | Yes | |||
Database mirroring2 | Yes (full) | Yes (safety full only) | Witness only | Witness only | Witness only | Witness only | Witness only |
Failover clustering | Operating system maximum1 | 2 nodes | |||||
Dynamic AWE | Yes | Yes | |||||
Failover without client configuration | Yes | Yes | |||||
Automatic corruption recovery from mirror | Yes | Yes | |||||
Database snapshots | Yes | ||||||
Fast recovery | Yes | ||||||
Online indexing | Yes | ||||||
Online restore | Yes | ||||||
Mirrored backups | Yes | ||||||
Hot add memory | Yes | ||||||
Online configuration of P2P nodes | Yes | ||||||
Hot add CPU | Yes | ||||||
Backup compression | Yes |
1 Windows Server 2003 supports a maximum of 8 failover cluster nodes. Windows Server 2008 supports a maximum of 16 failover cluster nodes.
Security Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
C2 audit mode | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
SQL Server auditing | Yes | ||||||
Transparent database encryption | Yes | ||||||
ISV encryption (off-box key management) | Yes |
Replication Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Merge replication | Yes | Yes | Subscriber only¹ | Subscriber only | Subscriber only | Subscriber only | Subscriber only |
Transactional replication | Yes | Yes | Subscriber only¹ | Subscriber only | Subscriber only | Subscriber only | Subscriber only |
Snapshot replication | Yes | Yes | Subscriber only | Subscriber only | Subscriber only | Subscriber only | Subscriber only |
SQL Server change tracking | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Heterogeneous subscribers | Yes | Yes | |||||
Oracle publishing | Yes | ||||||
P2P transactional replication | Yes |
¹If an instance of WorkGroup is used as a Publisher, it supports a maximum of 25 subscriptions to all merge publications, and five subscriptions to all transactional publications. It supports an unlimited number of subscriptions to snapshot publications.
Manageability Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
User instances | Yes | Yes | Yes | ||||
Dedicated admin connection | Yes | Yes | Yes | Yes | Yes (Under trace flag) | Yes (Under trace flag) | Yes (Under trace flag) |
Policy-Based Management | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Policy-Based Management automation | Yes | Yes | Yes | Yes | |||
Policy-Based Management best practices policies | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Performance data collection and warehouse | Yes | Yes | Yes | Yes | |||
Standard performance reports | Yes | Yes | |||||
Plan guides | Yes | Yes | |||||
Plan freezing for plan guides | Yes | Yes | |||||
Distributed partition views | Yes | ||||||
Parallel index operations | Yes | ||||||
Automatic query-to-indexed-view matching | Yes | ||||||
Parallel database backup checksum check | Yes | ||||||
Database mail | Yes | Yes | Yes | Yes | |||
SQL Server Migration Assistant1 | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Maintenance plans | Yes | Yes | Yes | Yes |
Management Tools Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
SQL Server management objects (SMO) | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
SQL Server Configuration Manager | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
SQL CMD (command prompt tool) | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
SQL Server Management Studio | Yes | Yes | Yes | Yes (Basic version) | Yes (Basic Version) | Yes (Basic version) | |
SQL Server Profiler | Yes | Yes | Yes | Yes | |||
SQL Server Agent | Yes | Yes | Yes | Yes | |||
Database Engine Tuning Advisor | Yes | Yes | Yes | Yes | |||
Microsoft Operations Manager Pack | Yes | Yes | Yes | Yes |
Development Tools Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Microsoft Visual Studio Integration | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
SQL query and edit and design tools | Yes | Yes | Yes | ||||
IntelliSense (Transact-SQL and MDX) | Yes | Yes | Yes | No (SSMS is not installed with this edition) | Yes | Yes | |
Version control support | Yes | Yes | Yes | ||||
Business Intelligence Development Studio | Yes | Yes | |||||
MDX edit, debug, and design tools | Yes | Yes |
Programmability Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Common language runtime (CLR) integration | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Native XML support | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
XML indexing | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
MERGE capabilities | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
FILESTREAM support | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Date and Time data types | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Internationalization support | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Full-text search | Yes | Yes | Yes | Yes | Yes | ||
Specification of language in query | Yes | Yes | Yes | Yes | Yes | ||
Service Broker (messaging) | Yes | Yes | Yes | Client only | Client only | Client only | Client only |
XML/A support | Yes | Yes | |||||
Web services (HTTP/SOAP endpoints) | Yes | Yes |
Spatial and Location Services Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Spatial indexes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Geodetic data type | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Advanced spatial libraries | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Standards-based spatial support | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Integration Services SQL Server Standard or Enterprise is required to design and run Integration Services packages. The Integration Services features that are installed by Workgroup, Web, and Express are only for use by the SQL Server Import and Export Wizard.
Feature | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
SQL Server Import and Export Wizard, and the basic Integration Services features required by the wizard | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
SSIS Designer including VSTA scripting | Yes | Yes | |||||
Integration Services service, wizards, and command prompt utilities | Yes | Yes | |||||
Basic tasks and transformations in addition to those used by the Import and Export Wizard | Yes | Yes | |||||
Log providers and logging | Yes | Yes | |||||
Data profiling tools | Yes | Yes | |||||
Additional sources and destinations: Raw File source XML source DataReader destination Raw File destination Recordset destination SQL Server Compact destination SQL Server destination | Yes | Yes | |||||
Advanced sources, transformations, and destinations: Data Mining Query transformation Fuzzy Lookup and Fuzzy Grouping transformations Term Extraction and Term Lookup transformations Data Mining Model Training destination Dimension Processing destination Partition Processing destination | Yes |
Data Warehouse Creation Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Create cubes without a database | Yes | Yes | |||||
Auto-generate staging and data warehouse schema | Yes | Yes | |||||
Attribute relationship designer | Yes | Yes | |||||
Efficient aggregation designers | Yes | Yes |
Data Warehouse Scale and Performance Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Change data capture | Yes | ||||||
Star join query optimization | Yes | ||||||
Scalable read-only AS configuration | Yes | ||||||
Proactive caching | Yes | ||||||
Auto parallel partition processing | Yes | ||||||
Partitioned cubes | Yes | ||||||
Distributed partitioned cubes | Yes |
Multi-Dimensional Analytics Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
SQL Server Analysis Services service | Yes | Yes | |||||
SQL Server Analysis Services backup | Yes | Yes | |||||
General performance/scale improvements | Yes | Yes | |||||
Dimension, attribute relationship, aggregate, and cube design improvements | Yes | Yes | |||||
Personalization extensions | Yes | Yes | |||||
Financial aggregations | Yes | ||||||
Custom rollups | Yes | ||||||
Semi-additive measures | Yes | ||||||
Writeback dimensions | Yes | ||||||
Linked measures and dimensions | Yes | ||||||
Binary and compressed XML transport | Yes | ||||||
Account intelligence | Yes | ||||||
Perspectives | Yes | ||||||
Analysis Services shared, scalable databases | Yes |
Data Mining Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Standard algorithms | Yes | Yes | |||||
Data mining tools: wizards, editors, query builders | Yes | Yes | |||||
Cross validation | Yes | ||||||
Models on filtered subsets of mining structure data | Yes | ||||||
Time series: custom blending between ARTXP and ARIMA models | Yes | ||||||
Time series: prediction with new data | Yes | ||||||
Unlimited concurrent data mining queries | Yes | ||||||
Advanced configuration and tuning for algorithms | Yes | ||||||
Algorithm plug-in API | Yes | ||||||
Parallel model processing | Yes | ||||||
Time series: cross-series prediction | Yes | ||||||
Unlimited attributes for association rules | Yes | ||||||
Sequence prediction | Yes | ||||||
Multiple prediction targets for naïve Bayes, neural network, and logistic regression | Yes |
Reporting Feature Name | Enterprise | Standard | Workgroup | Web | Express | Express Tools | Express Advanced |
Report server | Yes | Yes | Yes | Yes | Yes | ||
Report Designer | Yes | Yes | Yes | Yes | Yes | ||
Report Manager | Yes | Yes | Yes | Yes (Report Manager) | Yes (Report Manager) | ||
Role-based security | Yes | Yes | Yes (Fixed roles) | Yes (Fixed roles) | Yes (Fixed roles) | ||
Ad-hoc reporting (Report builder) | Yes | Yes | Yes | ||||
Word export and enhanced text formatting | Yes | Yes | Yes | Yes | Yes | ||
Enterprise-scale reporting engine | Yes | Yes | Yes | Yes | Yes | ||
IIS-agnostic report deployment | Yes | Yes | Yes | Yes | Yes | ||
Updated management tools | Yes | Yes | Yes | Yes | Yes | ||
Report definition customization extension (RDCE) | Yes | Yes | Yes | Yes | Yes | ||
SharePoint integration | Yes | Yes | |||||
Enhanced SSRS gauges and charting | Yes | Yes | Yes | Yes | Yes | ||
Custom authentication | Yes | Yes | Yes | Yes | |||
Export to Excel, PDF, and images | Yes | Yes | Yes | Yes | Yes | ||
Remote and non-relational data source support | Yes | Yes | |||||
E-mail and file share delivery | Yes | Yes | |||||
Report history, scheduling, subscriptions, and caching | Yes | Yes | |||||
Data source, delivery, and rendering extensibility | Yes | Yes | |||||
Scale out (Web farms) | Yes | ||||||
Infinite click through | Yes | ||||||
Data-driven subscriptions | Yes | ||||||
Reporting Services memory limits | OS Maximum | OS Maximum | 4 GB | 4 GB | 4 GB |
Subscribe to:
Posts (Atom)