Resource Governor in SQL Server 2008

1 Reply

Resource Governor in SQL Server 2008

SQL Server 2008 Resource Governor allows you to control the resource according to the requirements. This was motive behind this feature is providing predictable response to the user in any situation. In earlier versions, we had a single pool of resources like Memory, CPU, threads etc. You can not priorities the workload versus Resource pool in 2005 and earlier version. Generally, who accesses the system first and starts a process, it can consume the resources without any restrictions. Consider, some kind of BI runaway query is first hit system where the OLTP and OLAP Databases are in the same server. Now the OLTP process has to wait till the OLAP process releases the resource. This was a major concern in earlier versions. So what were the solution then, go for multiple instances and configure the Resource per instance or go for different machine altogether. Both methods were having their own problem. By specifying the resource, if the system is not using that resource still will not released. If you go for another machine, you may have license issue and it’s not a cost effective method.

In SQL Server 2008, these problems are addressed by providing a tool called Resource Governor. You can differentiate the workload by Application Name, Login, Group, by database name etc. Once you have defined the workload, you can configure the resource which can consumed by workload. Probably, you want to give more resource for your OLTP application than the OLAP. You have that kind of flexibility and control here.
The following three concepts are fundamental to understanding and using Resource Governor:
• Resource pools: Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
• Workload groups: Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
• Classification: There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

SQL Server 2008 Sparse column

Leave a reply

SQL Server 2008 Sparse column

One of the major enhancements in database engine of SQL Server 2008 is Sparse column. It improves data retrieval and reduces the storage cost. It also can be used with Filtered Index to improve the performance.

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

How to create sparse column?
Simple , just mention sparse keyword in table creation or alter statement.

CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)

Data Page LSN and Transaction Log LSN

Leave a reply

Data Pages

The SQL Server database page size is 8 KB. Each page contains a header with fields such as Page Number, Object Id, LSN, Index Id, Torn bits, and Types. The actual row data is located on the remaining portion of the page. The internal database structures track the allocation state of the data pages in the database.

Data pages are also referred to as pages.

The Microsoft SQL Server 2000 transaction log operates logically as if it is a serial string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN higher than the LSN of the record before it.

Log Shipping Requirements

2 Replies

Log Shipping Requirements

Log shipping has the following requirements:
* SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition, or a later version, must be installed on all server instances involved in log shipping.
* The servers involved in log shipping should have the same case-sensitivity settings.
* The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

Permissions
You must be a sysadmin on each server instance to enable log shipping. The backup and restore directories in your log shipping configuration must follow these requirements.
* For the backup job, read/write permissions to the backup directory are required on the following:
o The SQL Server service account on the primary server instance.
o The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
* For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
* For the restore job, read/write permission to the copy directory are required by the following:
o The SQL Server service account on the secondary server instance.
o The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.

Network Troubleshooting Dos Commands

14 Replies

Network Troubleshooting Dos Commands

PING
Ping is the most important troubleshooting command and it checks the connectivity with the other computers. For example your system’s IP address is 10.10.10.10 and your network servers’ IP address is 10.10.10.1 and you can check the connectivity with the server by using the Ping command in following format.

At DOS prompt type Ping 10.10.10.1 and press enter

If you get the reply from the server then the connectivity is ok and if you get the error message like this “Request time out” this means the there is some problem in the connectivity with the server.

IPCONFIG
IPconfig is another important command in Windows. It shows the IP address of the computer and also it shows the DNS, DHCP, Gateway addresses of the network and subnet mask.

At DOS prompt type ipconfig and press enter to see the IP address of your computer.

At DOS prompt type inconfig/all and press enter to see the detailed information.

NSLOOKUP
NSLOOKUP is a TCP/IP based command and it checks domain name aliases, DNS records, operating system information by sending query to the Internet Domain Name Servers. You can resolve the errors with the DNS of your network server

HOSTNAME
Hostname command shows you the computer name.

At DOS prompt type Hostname and press enter

NETSTAT
NETSTAT utility shows the protocols statistics and the current established TCP/IP connections in the computer.

NBTSTAT
NBTSTAT helps to troubleshoot the NETBIOS name resolutions problems.

ARP
ARP displays and modifies IP to Physical address translation table that is used by the ARP protocols.

FINGER
Finger command is used to retrieve the information about a user on a network.

TRACERT
Tracert command is used to determine the path of the remote system. This tool also provides the number of hops and the IP address of each hop. For example if you want to see that how many hops (routers) are involved to reach any URL and what’s the IP address of each hop then use the following command.

At command prompt type tracert www.yahoo.com you will see a list of all the hops and their IP addresses.

TRACEROUTE
Traceroute is a very useful network debugging command and it is used in locating the server that is slowing down the transmission on the internet and it also shows the route between the two systems

ROUTE
Route command allows you to make manual entries in the routing table.