Sql Server 2008 Tips_2

SQL Server 2008 Tips By The Technocrats Use the Query Governor to Control Excessive Query Execution The query governor does not allow the execution of any query that has a running time that exceeds a specified query cost. The query cost is the estimated time, in seconds, required to execute a query, and it is estimated prior to execution based on an analysis by the query engine. By default, the query governor is turned off, meaning there is no maximum cost. To activate the query governor, complete the following steps: 1. In the Server Properties dialog box, go to the Connections page. 2. Select the option Use Query Governor To Prevent Long-Running Queries. 3. In the box below the option, type a maximum query cost limit. The valid range is 0 through 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum query cost limit. 4. Click OK. With sp_configure, the following Transact-SQL statement will activate the query governor: exec sp_configure "query governor cost limit", You can also set a per-connection query cost limit in Transact-SQL using the following statement: set query_governor_cost_limit Note Before you activate the query governor, you should use the Query view to estimate the cost of current queries you are running on the server. This will give you a good idea of a value to use for the maximum query cost. You can also use the Query view to optimize queries.

Manage Access and Roles with Transact-SQL (T-SQL) Commands SQL Server provides different commands for managing database access and roles. Here is a summary of the commands you can use. Adding a User to the Current Database:

CREATE USER user_name [ { { FOR | FROM } { LOGIN login_name | CERTIFICATE certificate_name | ASYMMETRIC KEY asym_key_name } ] [ WITH DEFAULT_SCHEMA = schema_name ] Renaming a User or Changing Default Schema:

ALTER USER user_name WITH < set_item > [ ,...n ]

< set_item > ::= NAME = new_user_name | DEFAULT_SCHEMA = schema_name Removing a User from a Database:

DROP USER user_name Listing Server Role Members:

sp_helpsrvrolemember [[@rolename =] 'role'] Managing Database Standard Roles:

CREATE ROLE role_name [ AUTHORIZATION owner_name ] ALTER ROLE role_name WITH NAME = new_name DROP ROLE role_name sp_helprole [[@rolename =] 'role'] Managing Database Role Members:

sp_addrolemember [@rolename =] 'role', [@membername =] 'security_account' sp_droprolemember [@rolename =] 'role', [@membername =] 'security_account' sp_helprolemember [[@rolename =] 'role'] Managing Application Roles:

CREATE APPLICATION ROLE application_role_name WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ] ALTER APPLICATION ROLE application_role_name WITH <set_item> [ ,...n ] <set_item> ::= NAME = new_application_role_name | PASSWORD = 'password' | DEFAULT_SCHEMA = schema_name DROP APPLICATION ROLE rolename

Understand the Replication Models in SQL Server 2008 The architecture for the replication process is extensive. This ensures that the architecture is versatile enough to meet the needs of almost any replication situation. Unfortunately, this versatility also makes replication tricky to configure. To make the replication go smoothly, you should do a bit of planning, which involves selecting a specific replication model and performing any necessary preliminary tasks before you start configuring replication. The main decision to make when you select a replication model involves the physical layout of the publisher, distributor, and subscriber databases. Common replication models that you might want to use include: Peer-to-peer model Allows replication between identical participants in the topology. The advantage of this model is that it permits roles to move between replicated nodes dynamically for maintenance or failure management. The disadvantage is the additional administration overhead involved with moving roles. Central publisher model Maintains the publisher and distributor databases on the same server, with one or more subscribers configured on other servers. The advantages of this model are manageability and ease of maintenance. The disadvantages include the extra workload and resource usage on the publication server. Central publisher with remote distributor model Maintains the publisher and distributor databases on different servers, with one or more subscribers configured on other servers. The advantage of this model is that the workload is more evenly distributed. The disadvantage is that you have to maintain an additional server. Central subscriber model A single subscriber database that collects data from several publishers. For example, if you have ServerA, ServerB, and ServerC, ServerA and ServerB act as central publishers and ServerC acts as the central subscriber. In this configuration, when updates are distributed from ServerA and ServerB, they are collected on ServerC. A central subscriber can then republish the combined data to other servers. To use this model, all tables used in replication must have a unique primary key; otherwise, the replication model will not work properly. Publishing subscriber model Relays the distribution of data to other subscribers; you can use this with any of the other models. For example, if you have two geographically separated sites, a publisher can replicate data to servers at site A and then have a publishing subscriber at site B that distributes the data to servers at site B. The central publisher model is the most commonly used replication model. Unfortunately, you will often find that the extra load on the publication server will slow down server performance. To reduce the server load, you should put the distributor on its own server. Be aware, however, that doing this will not entirely eliminate the workload on the publication server. The publisher and distributor still need to communicate, and they still need to pass data back and forth.

Monitor SQL Server Performance and Activity with Built-In Functions In addition to having the use of log files and Transact-SQL statements, you will find a set of built-in functions that return system information. Here’s an overview of key built-in functions and their usages. The values returned by these functions are cumulative from the time SQL Server was last started.

Built-In Functions for Monitoring SQL Server Performance and Activity Function

Description

Example

@@connections

Returns the number of connections or attempted connections

select @@connections as 'Total Login Attempts'

@@cpu_busy

Returns CPU processing time in milliseconds for SQL Server activity

select @@cpu_busy as 'CPU Busy', getdate() as 'Since'

@@idle

Returns SQL Server idle time in milliseconds

select @@idle as 'Idle Time', getdate() as 'Since'

@@io_busy

Returns I/O

select @@io_busy as 'IO Time',

processing time in milliseconds

getdate() as 'Since' for SQL Server

@@pack_received

Returns the number of input packets read from the network by SQL Server

select @@pack_received as 'Packets Received'

@@pack_sent

Returns the number of output packets written to the network by SQL Server

select @@pack_sent as 'Packets Sent'

@@packet_errors

Returns the number of network packet errors for SQL Server connections

select @@packet_errors as 'Packet Errors'

@@timeticks

Returns the number of microseconds per CPU clock tick

select @@timeticks as 'Clock Ticks'

@@total_errors

Returns the number of disk read/write errors encountered by SQL Server

select @@total_errors as 'Total Errors', getdate() as 'Since'

@@total_read

Returns the number of disk reads by SQL Server

select @@total_read as 'Reads', getdate() as 'Since'

@@total_write

Returns the number of disk writes by SQL Server

select @@total_write as 'Writes', getdate() as 'Since'

fn_virtualfilestats

Returns input/output statistics for data and log files

select * from fn_virtualfilestats(null,null)

How to Quickly Create a Copy of a Table using Transact-SQL The easiest way to create a copy of a table is to use a Transact-SQL command. Use SELECT INTO to extract all the rows from an existing table into the new table. The new table must not exist already. The following example will copy the Customers table under the Salesschema to a new table called CurrCustomers under the BizDev schema: SELECT * INTO BizDev.CurrCustomers FROM Sales.Customers You can also create the new table from a specific subset of columns in the original table. In this case, you specify the names of the columns to copy after the SELECT keyword. Any columns not specified are excluded from the new table. The following example copies specific columns to a new table:

SELECT CustName, Address, Telephone, Email INTO BizDev.CurrCustomers FROM Sales.Customers

Use New Cmdlets in SQL Server PowerShell to Mange SQL Server 2008 When you are working with the SQL Server PowerShell, the available cmdlets are different than when you are working with the standard Windows PowerShell. The reason for this is that the set of registered snap-ins is different. Additional SQL Server–specific cmdlets are available, and some standard PowerShell cmdlets might not be available. In the original implementation of SQL Server PowerShell, the following additional cmdlets are included: Convert-UrnToPath Converts a SQL Server Management Object Uniform Resource Name (URN) to a SQL Server provider path. The URN indicates a management object’s location within the SQL Server object hierarchy. If the URN path has characters not supported by PowerShell, the characters are encoded automatically. Decode-SQLName Returns an unencoded SQL Server identifier when given an identifier that has been encoded. Encode-SQLName Encodes special characters in SQL Server identifiers and name paths to formats that are usable in PowerShell paths. The characters encoded by this cmdlet include \:/%<>*?[]|. If you don’t encode these characters, you must escape them using the single quote (') character. Invoke-PolicyEvaluation Evaluates management policies applied to SQL Server instances. By default, this command reports compliance but does not enforce compliance. To enforce compliance, set –AdHocPolicyEvaluationMode to Configure. Invoke-SQLCmd Runs a Transact-SQL or XQuery script containing commands supported by the sqlcmd utility. By default, this cmdlet doesn’t set any sqlcmd variables by default or return message output. (Many sqlcmd commands aren’t supported.) As the set of available cmdlets and cmdlet options change as new versions of SQL Server PowerShell are released, you can use the following techniques to discover new cmdlets and determine how they are used: To view a list of all cmdlets, type get-command at the shell prompt. To get detailed information about a cmdlet, type get-help CmdletName –detailed where CmdletName is the name of the cmdlet you want to examine. To get detailed information about the SQL Server provider, which provides the SQL Server functionality for PowerShell, type get-help sqlserver | more.