Spatial datatypes and index

• Spatial index can be based on only a single column of spatial data type
• The table must have a primary key before you can create a spatial index
• Geography and geometry are spatial data types
• STDistance function is a geography function that returns the shortest distance between a point in a geography instance and a point in another geography instance – benefits from a spatial index

 

DBCC CHECKFILEGROUP

Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

DBCC CHECKFILEGROUP performs the following commands:

• DBCC CHECKALLOC of the filegroup.
• DBCC CHECKTABLE of every table and indexed view in the filegroup.

Nonclustered Indexes on Separate Filegroups

If a nonclustered index in the specified filegroup is associated with a table in another filegroup, the index is not checked because the base table is not available for validation. This is a change in behavior in SQL Server 2005. In earlier versions of SQL Server, the nonclustered index and the base table in the other filegroup are checked. To check both the nonclustered indexes and the base tables, run DBCC CHECKDB.

If a table in the specified filegroup has a nonclustered index in another filegroup, the nonclustered index is not checked because of the following:

• The base table structure is not dependent on the structure of a nonclustered index. Nonclustered indexes do not have to be scanned to validate the base table.
• The DBCC CHECKFILEGROUP command validates objects only in the specified filegroup.

A clustered index and a table cannot be on different filegroups; therefore, the previous considerations apply only to nonclustered indexes.

Partitioned Tables on Separate Filegroups

In versions of SQL Server 2005 earlier than Service Pack 2 (SP2), DBCC CHECKFILEGROUP checks a partitioned table only if the entire table is on the specified filegroup. If the table is spread across multiple filegroups, the entire table is ignored. In SP2 and higher, when a partitioned table exists on multiple filegroups, DBCC CHECKFILEGROUP checks the partition rowsets that exist on the specified filegroup and ignores the rowsets in the other filegroups. Informational message 2594 indicates the partitions that were not checked. Nonclustered indexes not resident on the specified filegroup are not checked.

 

Dedicated Administrator Connection (DAC)

• SQL Server provides a special Diagnostic connection for administrators when standard connections to the server are not possible.
• This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
• This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server.
• The DAC only allows changing the user context to another admin user.
• SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.
• By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.
• Only members of the SQL Server sysadmin role can connect using the DAC.
• The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). For more information about using sqlcmd, see Using sqlcmd with Scripting Variables. You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<instance_name>. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<instance_name>.
  Example

  In this example, an administrator notices that server URAN123 is not responding and wants to diagnose the problem. To do this, the user activates the sqlcmd command prompt utility and connects to server URAN123 using -A to indicate the DAC.

  sqlcmd -S URAN123 -U sa -P <xxx> –A

 

BCP vs. BULK Insert

BCP

• If you are exporting data using BCP, you need SELECT permission on the table
• If you are importing data using BCP, you need SELECT, INSERT, ALTER TABLE (if you want to enforce check constraints/triggers)
• Triggers and check constraints are disabled by default.
• If you want to enforce check constraints/triggers during import, -h flag has to be used, and you need ALTER TABLE permissions.
• this is a command line utility

BULK INSERT

• Cannot export data
• t-sql command and doesn’t need to specify the instance name or login credentials
• To import data by using the BULK INSERT command, you need to have INSERT permission on the table you want to load. You also need to have the ADMINISTER BULK OPERATIONS instance-wide permission. Being a member of the bulkadmin fixed server role grants you exactly that permission, so both permission settings are valid.
• ALTER TABLE permission is needed only if one of the following conditions is true: you are using the -E option (KEEPIDENTITY option) to import identity values, the table has constraints but constraint checking is disabled, or the table has triggers and trigger execution is disabled.
• The BULK INSERT statement runs under the security context of the user executing the command

 

Copy database wizard

http://msdn.microsoft.com/en-us/library/ms188664.aspx

 

Basic High Availability summary points

Failover clustering provides failure transparency. Although the inventory database is unavailable for approximately 10-15 seconds during a failover, the database comes back online within the same instance name. The fi rst step in the failover process is to restart SQL Server on the same node. The instance is restarted on the same node because the cluster fi rst assumes that a transient error caused the health check to fail. If the restart does not respond immediately, the SQL Server group fails over to another node in the cluster (the secondary node). The network name of the server running SQL Server is unregistered from DNS. The SQL Server IP address is bound to the network interface card (NIC) on the secondary node. The disks associated to the SQL Server instance are mounted on the secondary node. After the IP address is bound to the NIC on the secondary node, the network name of the SQL Server instance is registered into DNS. After the network name and disks are online, the SQL Server service is started. After the SQL Server service is started, SQL
Server Agent and Full Text indexing are started. Failover clustering provides automatic failover without affecting transaction performance. Failover clustering provides failure transparency, has a wait of 10-15 seconds on average for a failover to occur. In addition, a cluster failover requires a restart of the Microsoft SQL Server instance. This restart causes all caches to start empty and affects performance until the cache is repopulated.

Database mirroring can fail over to the mirror automatically. However, transactions have to be committed on both the principal and mirror, which affects the performance of applications. Although database mirroring can fail over to the mirror automatically, the mirror database is on a separate instance. During the failover, the server name to reach the inventory database changes and breaks the drop shipping service until connections can be moved and recreated. Database mirroring configured in High Availability operating mode provides automatic detection and automatic failover. Failover generally occurs within 1-3 seconds. By using the capabilities of the Microsoft Data Access Components (MDAC) libraries that ship with Microsoft Visual Studio 2005 and later,  applications can use the transparent client redirect capabilities to mask any failures from users. Database mirroring also contains technology that enables the cache on the mirror to be maintained in a semi-hot state so that application performance is not appreciably affected during a failover.

Replication and log shipping require manual detection as well as manual failover.  Users cannot be connected to a database when it is being restored in Log shipping mode

 

Replication

Transactional replication begins with an initial snapshot being applied to the subscriber to ensure that the two databases are synchronized. As subsequent transactions are issued against the publisher, the replication engine applies them to the subscriber. The incremental transaction flow from publisher to subscriber makes transactional replication a good choice for maintaining a secondary copy of a database for availability or to offload reporting operations. The most common configuration for transactional replication is in a server-to-server environment.

Transactional Replication uses Log reader agent to move transactions from the transaction log on the publisher to the distribution database. The distribution agent then moves transactions from the distribution database to each subscriber.

Transactional replication propagates data in one direction – from publisher to suscriber. You can configure transactional replication with two optional modes—immediate updating subscribers and queued updating subscribers—that enable transactions to be issued against a subscriber. Transactional Replication can be configured in three different architectures – default architecture is to have one publisher with one ore more subscribers. Alternatively, transactional replication can be configured by using either bi-directional or peer to peer architecture.

Immediate Updating Subscriber Option
The Immediate Updating Subscriber option allows changes to be made at the subscriber that are propagated back to the publisher. The change is then picked up by the transactional engine and sent to all other subscribers, but it does not get reapplied to the subscriber that originated the change. To prevent the change from being sent back to the originating subscriber, a timestamp column is required on each table that is participating in replication.

The process of applying changes at a subscriber confi gured with the immediate updating
subscriber option is the following:
1. The application issues the transaction at the subscriber.
2. The trigger is fired.
3. The trigger enlists the Microsoft Distributed Transaction Coordinator (MS DTC) to
connect to the publisher and reissue the transaction.
4. The transaction commits on the publisher.
5. The trigger commits on the subscriber.
6. The transaction commits on the subscriber.

The main issue in high-availability architectures with immediate updating subscribers is that changes must be applied to the publisher. If the publisher is not available, the distributed transaction fails. Because the distributed transaction is being executed from a trigger, the initiating transaction also fails and rolls back. Therefore, the immediate updating subscriber option is an incompatible replication option for high availability.

Queued Updating Subscriber Option
The Queued Updating Subscriber option also allows changes to be made at the subscriber and then propagated back to the publisher, but it does so via a much different mechanism than the immediate updating subscriber option.

The process by which a change is applied to a subscriber using this option is as follows:
1. The application issues a transaction.
2. The trigger is fired.
3. The trigger logs the transaction into a queue (a table within the database).
4. The trigger commits.
5. The transaction commits.
The Queue Reader Agent periodically transfers the queue back to the publisher and issues all the transactions. A change transferred to the publisher is not reapplied to the subscriber that initiated the transaction. This requires a timestamp column on the table.

Because of the asynchronous processing of a queued update, data confl icts can occur. To mitigate data confl icts, when you use the queued updating subscriber option in high- availability architectures, changes are applied to the subscriber only during a failure scenario. This ensures that changes are always made to a single copy of the data, thereby enabling normal application processing to handle confl icting changes. The advantage of the queued updating subscriber option is that when the publisher is brought back online, all the changes that occurred at the subscriber during the outage can be flushed to the publisher automatically. All the planning and processes required to fail back with other architectures is eliminated because the queued updating subscriber option has the built-in capability to bring the publisher up to date again following a failover. The only step
required to fail back to the publisher is to repoint the applications.

The push Distribution Agent requires the following permissions to function properly:

• dbo_owner of the subscription database,
• Read permission of the snapshot folder, and
• member of the publication access list (PAL).

Tracer tokens enable an administrator to gather timing statistics within the replication form publisher to distributor, and also from distributor to subscriber, to monitory point to point latency.

Source: MCTS Training Kit – Mike Hotek

 

Performance Data Warehouse

Data collectors for performance data warehouse is configured using one of the following 4 collector types

• T-SQL Query
• SQL Trace
• Query Activity
• Performance Counter

They are based on SSIS packages and SQL Agent jobs

T-SQL Query collector – allows you to specify SELECT statements to execute, as well as the database to execute the query against. Results stored within a table (output table parameter of data collector). The following are requirements of the result set

• result set does not  contain data collector reserved words e.g. snapshot_id, database_name, snapshot_time etc
• All columns in the result set must have a name
• image, text, xml data types cannot be included
• only a single result set is returned

SQL Trace Collector – supports either the default trace or user-defined trace. Results of the trace are written to a file and Data Collector uses the fn_trace_getTable to extract contents. Results of the data collection are stored in snapshots.trace_info and snapshots.trace_data tables in the performance data warehouse.

Peformance Counter Collector – allows you to define any combination of objects, counters and counter instances. Results of data collection stored in the snapshots.performance_counters table in performance data warehouse

Query Activity Collector – gathers information from sys.dm_exec_requests , sys.dm_exec_sessions, and sys.dm_exec_query_stats

 

DMVs for performance tuning

DMV Categories

Most important DMV Categories for performance tuning are

dm_db_* – General database and index utilization
dm_exec_* – Statistics for queries that are executing, as well as queries that have completed and still have plans in the query cache
dm_io_* – Disk subsystem statistics
dm_os_* – Statistics related to use of hardware resources

Database Statistics
Most common DMVs used for database statistics are

1. sys.dm_db_index_usage_stats – number of times (and the last time) each index was used to satisfy a seek, scan or lookup , as well as the number of time and the last time an update was performed to each index.
2. sys.dm_db_index_operational_stats – this is a function that takes 4 parameters (database_id, object_id, index_id and partition_id). Returns locking, latching and access statistics for each index that can help you determine how heavily an index is being used. Also helps you diagnose contention issues due to locking and latching.
3. sys.dm_db_index_physical_stats – function that takes in 5 parameters – all 4 of above and mode. Returns size and fragmentation statistics for each index and should be the primary source for determining when an index needs to be defragmented.
4. sys.dm_db_missing_index_groups –
5. sys.dm_db_missing_index_group_stats
6. sys.dm_db_missing_index_details

SQL Server logs details of an index miss which can be viewed using sys.dm_db_missing_index_* views (4, 5, 6 above) . Index miss is a situation when an index would be beneficial but doesn’t exist.

Query Statistics
The dm_exec_* views provide information related to connection to an instance as well as query execution.

• dm_exec_connections – one row for each connection to the instance
• dm_exec_sessions – one row for each currently authenticated session- also returns accumulated reads, writes, cpu and query execution duration for session.
• dm_exec_requests – one row for each currently executing request in the instance. Blocking_session_id columns helps to diagnose contention issues.
• dm_exec_query_stats – contains detailed statistics on performance and resources consumed for every query in the query cache.
• dm_exec_sql_text function returns the text of the sql statement associated to the handle that was passed in
• dm_exec_query_plan accepts a plan handle and returns corresponding xml showplan

If you want to see the currently executing queries along with xml plan

SELECT * FROM
sys.dm_exec_requests
CROSS APPLY
sys.dm_exec_query_plan (plan_handle)
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)

IF you want to see the sql statement and xml plan for every query in the query cache

SELECT * FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_query_plan (plan_handle)
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)

DISK SUBSYSTEM STATISTICS
sys.dm_io_virtual_file_stats – statistics about reads and writes for every database file. IOStalls for both reads and writes can be found here.
sys.dm_io_pending_requests – contains one row for each request that is waiting for the disk subsystem to complete an I/O request.

HARDWARE RESOURCES
When a request is sen to the waiting queue, SQL Server sets a value called teh wait type that designates the type of resources that the rqeuest is waiting on. As soon as wait type is set, SQL Server starts a clock. When the resource becomes avaialble, SQL Server stops the clock and records the amount of time that the request had to wait for the resource to become avialable, called teh wait time. SQL Server also sets a clcok when a request enters the running queue, called teh signal wait, which records how long it takes a process to get to the top of the queue and begin executing.

dm_os_wait_stats lists the aggregate amount of signal wait and wait time for each wait type.

Source :MCTS Training Kit – Mike Hotek

 

SQL server start up options

–d –  lists the location and file name for the master data file.
–l  –  lists the location and file name of the master transaction log file.
–e –  lists the name and location of the SQL Server error log. One of the most common changes that you
–T –  enables you to specify trace flags
–m – starts the SQL Server in a single user mode
–f – Starts SQL Server in a minimal configuration with auditing disabled.
-x – diasbles severals monitoring features
-c – shortens the time needed to start up SQL Server from command line