A covering index is an index that includes all the columns that are referenced in the WHERE and SELECT clauses. The index "covers" the query, and can completely service the query without going to the base data. This is in effect a materialized view of the query. The covering index performs well because the data is in one place and in the required order. A covering index may improve scalability by removing contention and access from the main table.
Sunday, January 5, 2014
Common issues in DBA
Common Issues
1. Team Job failures - Issues related to backup and maintenance Job failure come under team Job failures.
2. Customer Job failures - Issues related to Customer Application Job failure come under Customer Job failures.
3. Long Running Jobs (Team or Customer) - Those Jobs which are running for longer than the threshold time. It can be Team or Customer Job as explained above.
4. Backup failures - Issues related to failure of all types of Backup Failures like Full, Differential & Transaction Log Backups.
5. Blocking - Issues related to an activity (or a process) being blocked by another process and is hindering its completion.
6. SQL Services stopped - SQL Services like SQL Server service, SQL Agent service, SQL OLAP service, SQL FULL Text service, SQL Reporting service etc.
7. Disk space - Issues related to low disk space or Disk Full on various drives of a server.
8. Database file full - Issues related to Data file(s) of a database reaching its threshold capacity.
9. Log file Full - Issues related to Log file(s) of a database reaching its threshold capacity.
10. Log Shipping Job failures - Log Shipping Backup, Copy or Restore Job failure issues.
11. Log Shipping out of Sync - Log Shipping Copy and Restore is out of Sync where difference of files between them is greater than 90 minutes.
12. Replication Agent Jobs - Replication agents like Distributor, log reader, snapshot, Merge agent Job failures.
13. WebSite Not available - Issues related to Customer websites not available.
14. IIS Service - Issues related to IIS Services like, World Wide Publishing Service, IIS Admin etc being in stopped state.
15. Cluster resources - Cluster resources like SQL Services, disks etc are offline or failed.
1. Team Job failures - Issues related to backup and maintenance Job failure come under team Job failures.
2. Customer Job failures - Issues related to Customer Application Job failure come under Customer Job failures.
3. Long Running Jobs (Team or Customer) - Those Jobs which are running for longer than the threshold time. It can be Team or Customer Job as explained above.
4. Backup failures - Issues related to failure of all types of Backup Failures like Full, Differential & Transaction Log Backups.
5. Blocking - Issues related to an activity (or a process) being blocked by another process and is hindering its completion.
6. SQL Services stopped - SQL Services like SQL Server service, SQL Agent service, SQL OLAP service, SQL FULL Text service, SQL Reporting service etc.
7. Disk space - Issues related to low disk space or Disk Full on various drives of a server.
8. Database file full - Issues related to Data file(s) of a database reaching its threshold capacity.
9. Log file Full - Issues related to Log file(s) of a database reaching its threshold capacity.
10. Log Shipping Job failures - Log Shipping Backup, Copy or Restore Job failure issues.
11. Log Shipping out of Sync - Log Shipping Copy and Restore is out of Sync where difference of files between them is greater than 90 minutes.
12. Replication Agent Jobs - Replication agents like Distributor, log reader, snapshot, Merge agent Job failures.
13. WebSite Not available - Issues related to Customer websites not available.
14. IIS Service - Issues related to IIS Services like, World Wide Publishing Service, IIS Admin etc being in stopped state.
15. Cluster resources - Cluster resources like SQL Services, disks etc are offline or failed.
How to reduce deadlocks
To reduce the chance of a deadlock:
* Minimize the size of transaction and transaction times.
* Always access server objects in the same order each time in application.
* Avoid cursors, while loops, or process which requires user input while it is running.
* Reduce lock time in application.
* Use query hints to prevent locking if possible (NoLock, RowLock)
* Select deadlock victim by using SET DEADLOCK_PRIORITY.
SQL SERVER 2005 has new priority HIGH as well as numeric-priority.
SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar } < numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 } Example: The following example sets the deadlock priority to NORMAL. SET DEADLOCK_PRIORITY NORMAL; GO
To help minimize deadlocks:
* Access objects in the same order.
* Avoid user interaction in transactions.
* Keep transactions short and in one batch.
* Use a lower isolation level.
* Use a row versioning-based isolation level.
* Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
* Use snapshot isolation.
* Use bound connections.
* • Ensure the database design is properly normalized.
* • Have the application access database objects in the same order every time.
* • Keep transactions as short as possible.
* • During transactions, don't allow any user input.
* • Avoid cursors.
* • Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
* • Consider using the NOLOCK hint to prevent locking.
* • Use as low a level of isolation as possible for user connections.
Dead lock—events in profiler
* Deadlock graph
* · Lock: Deadlock
* · Lock: Deadlock Chain
* · RPC:Completed
* · SP:StmtCompleted
* · SQL:BatchCompleted
* · SQL:BatchStarting
* Minimize the size of transaction and transaction times.
* Always access server objects in the same order each time in application.
* Avoid cursors, while loops, or process which requires user input while it is running.
* Reduce lock time in application.
* Use query hints to prevent locking if possible (NoLock, RowLock)
* Select deadlock victim by using SET DEADLOCK_PRIORITY.
SQL SERVER 2005 has new priority HIGH as well as numeric-priority.
SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar } < numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 } Example: The following example sets the deadlock priority to NORMAL. SET DEADLOCK_PRIORITY NORMAL; GO
To help minimize deadlocks:
* Access objects in the same order.
* Avoid user interaction in transactions.
* Keep transactions short and in one batch.
* Use a lower isolation level.
* Use a row versioning-based isolation level.
* Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
* Use snapshot isolation.
* Use bound connections.
* • Ensure the database design is properly normalized.
* • Have the application access database objects in the same order every time.
* • Keep transactions as short as possible.
* • During transactions, don't allow any user input.
* • Avoid cursors.
* • Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
* • Consider using the NOLOCK hint to prevent locking.
* • Use as low a level of isolation as possible for user connections.
Dead lock—events in profiler
* Deadlock graph
* · Lock: Deadlock
* · Lock: Deadlock Chain
* · RPC:Completed
* · SP:StmtCompleted
* · SQL:BatchCompleted
* · SQL:BatchStarting
Joins
* JOIN: Return rows when there is at least one match in both tables
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
* FULL JOIN: Return rows when there is a match in one of the tables
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
* FULL JOIN: Return rows when there is a match in one of the tables
Subscribe to:
Posts (Atom)