dosql

• Database and Files Catalog Views:
  sys.backup_devices
  sys.databases
  sys.database_recovery_status
  sys.database_files
  sys.database_mirroring
  sys.master_files

• Object catalog Views:

sys.allocation_units
sys.assembly_modules
sys.check_constraints
sys.columns
sys.computed_columns
sys.default_constraints
sys.events
sys.event_notifications
sys.extended_procedures
sys.foreign_key_columns
sys.foreign_keys
sys.function_order_columns
sys.identity_columns
sys.index_columns
sys.indexes
sys.key_constraints
sys.numbered_procedure_parameters
sys.numbered_procedures
sys.objects
sys.parameters
sys.partitions
sys.procedures
sys.service_queues
sys.spatial_index_tessellations
sys.spatial_indexes
sys.sql_dependencies
sys.sql_expression_dependencies
sys.sql_modules
sys.stats
sys.stats_columns
sys.synonyms
sys.table_types
sys.tables
sys.trigger_event_types
sys.trigger_events
sys.triggers
sys.views

• Security Catalog Views:

Database-Level Views
sys.database_permissions
sys.database_principals
Server-Level Views  
sys.server_permissions
sys.server_principals
sys.server_role_members
sys.database_role_members
sys.master_key_passwords
sys.sql_logins
sys.system_components_surface_area_configuration

The Performance Dashboard reports:

• CPU utilization
• Current user request waiting for resource
• User Activity: User Requests /User Sessions

Count

Elapsed Time (CPU Time, Wait Time)

Cache Hit Ratio

• HIstorical Information:

Waits

IO Stats

• Expensive Queries:

By CPU

By Duration

By Logical Reads

By Physical Reads

By Logical Writes

By CLR Time

• Miscellaneous Information:

Active Traces

Databases 

Start from SQL Server Management Studio:

Database1 -> Reports -> Customer Reports -> {navigate to the install folder} performance_dashboard_main.rdl

Download it here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

Compare to:

1. Replay Markup Language (RML) Utilities for SQL Server
2. DB2 Health Centre
3. Oracle Statspack

Rollup:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN ‘ALL’ ELSE ISNULL(Item, ‘UNKNOWN’) END AS Item, CASE WHEN (GROUPING(Color) = 1) THEN ‘ALL’ ELSE ISNULL(Color, ‘UNKNOWN’) END AS Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Cube: 

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

Compute/Compute By:

This syntax is deprecated in SQL Server 2008.

SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

By default, MARS functionality is not enabled. In order to use MARS when connecting to SQL Server 2005 with SQL Native Client, you must specifically enable it within a connection string. For example:

SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);

• C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis -ga ASPNET
  Start granting ASPNET access to the IIS metabase and other directories used by ASP.NET.
  Finished granting ASPNET access to the IIS metabase and other directories used by ASP.NET.

• C:\Inetpub\AdminScripts>cscript metaacl.vbs IIS://localhost/W2SVC ASPNET RW
  Microsoft (R) Windows Script Host Version 5.6
  Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

-2147024893
IIS://localhost/W2SVC
IIS://localhost/W2SVC

 

Ref: http://support.microsoft.com/kb/326902/

The Management Data Warehouse is a single database which holds various performance related statistics. SQL Server 2008 provides three different system data collection definitions:

1. Disk Usage
2. Query Activity
3. Server Activity

Users can define customized data collection definitions and the information will be saved in custom_snapshots schema.

Setting up MDW using a wizard launched from SQL Server Management Studio:

Management -> Data Collection -> Configure Management Data Warehouse

Compare to:

• SQL Server Health and History Tool (SQLH2)
• Oracle Capacity Planning
• DB2 Health center

• Read Uncommitted
• Read Committed

If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions

• Repeatable Read
• Snapshot

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.
During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.
The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.
A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.
A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

• Serializable

 The lock type:

1. Shared Lock
2. Update Lock
3. Exclusive Lock
4. Intent Locks
5. Schema Locks
6. Bulk Update Locks 

• RID = Lock on a single row in a table identified by a row identifier (RID).
• KEY = Lock within an index that protects a range of keys in serializable transactions.
• PAG = Lock on a data or index page.
• EXT = Lock on an extent.
• TAB = Lock on an entire table, including all data and indexes.
• DB = Lock on a database.
• FIL = Lock on a database file.
• APP = Lock on an application-specified resource.
• MD = Locks on metadata, or catalog information.
• HBT = Lock on a heap or B-Tree index. This information is incomplete in SQL Server.
• AU = Lock on an allocation unit. This information is incomplete in SQL Server.

The lock mode:

• NULL = No access is granted to the resource. Serves as a placeholder.
• Sch-S = Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
• Sch-M = Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
• S = Shared. The holding session is granted shared access to the resource.
• U = Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
• X = Exclusive. The holding session is granted exclusive access to the resource.
• IS = Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
• IU = Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
• IX = Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
• SIU = Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
• SIX = Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
• UIX = Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
• BU = Bulk Update. Used by bulk operations.
• RangeS_S = Shared Key-Range and Shared Resource lock. Indicates serializable range scan.
• RangeS_U = Shared Key-Range and Update Resource lock. Indicates serializable update scan.
• RangeI_N = Insert Key-Range and Null Resource lock. Used to test ranges before inserting a new key into an index.
• RangeI_S = Key-Range Conversion lock. Created by an overlap of RangeI_N and S locks.
• RangeI_U = Key-Range Conversion lock created by an overlap of RangeI_N and U locks.
• RangeI_X = Key-Range Conversion lock created by an overlap of RangeI_N and X locks.
• RangeX_S = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. locks.
• RangeX_U = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.
• RangeX_X = Exclusive Key-Range and Exclusive Resource lock. This is a conversion lock used when updating a key in a range.