Factors That Impact Performance

 

Factors That Impact Performance

Application Architecture

Application Design

Transactions and Isolation Levels

Transact-SQL Code

Hardware Resources

SQL Server Configuration

What does database performance means?

What does database performance means?

Database performance enhancement refers to below terms.

Response time: Refers to the interval between the time when a request is submitted and when the first character of the response is received.

Throughput: Refers to the number of transactions that can be processed in a fixed unit of time.

Scalability: Refers to how the throughput and/or the response time changes as we add more hardware resources. In simple terms, scalability means that if you are hitting a hardware bottleneck, you can alleviate it simply by adding more resources.

 

SQL Server and Networking

How do you test proper TCP/IP configuration Windows machine?

Windows NT: IPCONFIG/ALL, Windows 95: WINIPCFG, Ping or ping ip.add.re.ss

What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.

How do you define testing of network layers?

Reviewing with your developers to identify the layers of the Network layered architecture, your Web client and Web server application interact with. Determine the hardware and software configuration dependencies for the application under test.

What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

How many layers of TCP/IP protocol combined of?

Five. (Application, Transport, Internet, Data link, Physical).

How many bits IP Address consist of?

An IP Address is a 32-bit number.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode.

As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs.

What is the difference between them (Ethernet networks and token ring networks)?

With Ethernet, any devices on the network can send data in a packet to any location on the network at any time. With Token Ring, data is transmitted in ‘tokens’ from computer to computer in a ring or star configuration. Token ring speed is 4/16 Mbit/sec , Ethernet - 10/100 Mbit/sec.

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also.

What is the system function to get the current user's user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What is a traditional Network Library for SQL Servers?

Named Pipes.

Table Locking enhancement - SQL Server 2008

In SQL Server 2008 ALTER TABLE statement, you can find a new option called SET LOCK_ESCALATION. This is one of the enhancements in Locking in SQL Server 2008. This option can have three value, Auto, Table, Disable

FROM BOL
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
Specifies the allowed methods of lock escalation for a table.

AUTO
This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.
• If the table is partitioned, lock escalation will be allowed to the heap or B-tree (HoBT) granularity. After the lock is escalated to the HoBT level, the lock will not be escalated later to TABLE granularity.
• If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

TABLE
Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

DISABLE
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

Note : If you use partitions then After you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO. This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table.

Resource Governor in SQL Server 2008

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

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

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

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

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.