aiDeeva.com

Category: Performance Tuning

  • 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.

  • 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)

  • Networking Questions

    What is DNS?

    Domain name system/server is used to translate the IP address into the hostname and hostname into the IP address. DNS is mostly used on the internet and the networks.

    What is DHCP?

    Dynamic host configuration protocol is used to dynamically assign the IP address to the networked computers and devices. DHCP is a network protocol that automatically assigns static and dynamic IP addresses from its own range.

    What is a Router?

    Router is the most important network device that is used to connect two logically and physically different networks. Router defines the short possible route for the data to reach its destination. A router works with built-in intelligent software known as routing table, which helps to determine the route between the two networks.

    What is Gateway?

    A gateway is software or a hardware that is used to connect the local area network with the internet. A gateway is a network entrance point and a router usually works as a gateway.

    What is WLAN?

    WLAN or Wireless local area network is simply a type of network that doesn’t use wired Ethernet connections for networking. WLAN uses wireless network devices such as wireless routers etc.

    What is Subnet Mask?

    A subnet mask is used to determine the number of networks and the number of host computers. Every class of the IP address uses the different range of the subnet mask. Subnet masks allow the IP based networks to be divided into the sub networks for performance and security purposes.

    What is a MAC Address?

    MAC address or Media Access control is a unique identifier of a computer device. The MAC address is provided by the manufacturer of the device. MAC addresses are 12 digital hexadecimal numbers.

    What is an IP Address?

    An IP address is a unique identifier of a computer or network device on the local area network, WAN or on internet. Every host computer on the internet must have a unique IP address. IP addresses on the internet are usually assigned by the local ISPs to which users are connected.

    What is Wifi?

    Wi Fi or wireless fidelity is a base band network technology that is used for the wireless data communication.

    What is WiMax?

    Wi Fi is a next form of the Wi fi. Wi max is a very high speed broadband network technology that is designed for the corporate offices, roaming and home users.

    Name the Seven Layers of OSI Model

    The seven layers of the OSI are Application, Presentation, Sessions, Transport, Network, Data Link and Physical layer.

    What is LDAP?

    Lightweight Directory Access Protocol is used to access the directory services from the Active directory in Windows operation systems.

    What are the standard port numbers for SMTP, POP3, IMAP4, RPC, LDAP, and FTP?

    SMTP – 25, POP3 – 110, IMAP4 – 143, RPC – 135, LDAP, FTP-21, HTTP-80

    What is IPv6?

    IP V6 is a next generation protocol that is used as an expansion of DNS.

    What is UDP?

    UDP or user datagram protocol is a connectionless protocol that is used to transfer the data without any error handling.

    What is Firewall?

    A firewall is usually a software program that is installed on the network server or gateway. The purpose of the firewall is to protect the network resources from the intruders and unauthorized persons.

    What is Virtual Private Network (VPN) and how does it work?

    VPN or virtual private network is used to connect two networks by means internet. VPN uses PPTP (point to point tunneling protocol) and other security procedures to make a secure tunnel on internet.

    Define VOIP

    VOIP or voice over internet protocol is a technology that uses IP based networks such as internet or private networks to transmit the voice communication.

    Define Bluetooth Technology

    Bluetooth is a short range wireless technology that uses radio waves for communication. Many mobile phones, laptops, MP3 players have built in features of the Bluetooth.

    What is a RAS server?

    RAS or remote access server allows you to remote dial in through the desktop computers, laptops and GSM mobile phones.

    What’s a Frame Relay?

    Frame relay is high speed data communication technology that operates at the physical and data link layers of the OSI model. Frame relay uses frames for data transmission in a network.

  • Networking Interview Questions

    What is IPv6?

    Internet Protocol version 6 (IPv6) is a network layer IP standard used by electronic devices to exchange data across a packet-switched internetwork. It follows IPv4 as the second version of the Internet Protocol to be formally adopted for general use.  ipv6 it is a 128 bit size address. This is total 8 octants each octant size is 16 bits separated with “:”, it is in hexa decimal format. These 3 types:

    1. unicast address
    2. multicast address
    3. anycast address

    loopback address of ip v6 is ::1

    What is subnet?

    A subnet allows the flow of network traffic between hosts to be segregated based on a network configuration. By organizing hosts into logical groups, subnetting can improve network security and performance.

    What is Subnet Mask?

    A mask used to determine what subnet an IP address belongs to. An IP address has two components, the network address and the host address. For example, consider the IP address 150.215.017.009. Assuming this is part of a Class B network, the first two numbers (150.215) represent the Class B network address, and the second two numbers (017.009) identify a particular host on this network.

    Subnetting enables the network administrator to further divide the host part of the address into two or more subnets. In this case, a part of the host address is reserved to identify the particular subnet. This is easier to see if we show the IP address in binary format.

    What is Default Gateway?

    a gateway is a device on a network that acts as an entrance to another network. In more technical terms, a gateway is a routing device that knows how to pass traffic between different subnets and networks. A computer will know some routes (a route is the address of each node a packet must go through on the Internet to reach a specific destination), but not the routes to every address on the Internet. It won’t even know all the routes on the nearest subnets. A gateway will not have this information either, but will at least know the addresses of other gateways it can hand the traffic off to. Your default gateway is on the same subnet as your computer, and is the gateway your computer relies on when it doesn’t know how to route traffic.

    The default gateway is typically very similar to your IP address, in that many of the numbers may be the same. However, the default gateway is not your IP address.

    Describe how the DHCP lease is obtained.
    It’s a four-step process consisting of (a) IP request, (b) IP offer, © IP selection and (d) acknowledgement.

    What’s the difference between forward lookup and reverse lookup in DNS?
    Forward lookup is name-to-address, the reverse lookup is address-to-name.

    How can you recover a file encrypted using EFS? Use the domain recovery agent.

    What is LMHOSTS file?
    It’s a file stored on a host machine that is used to resolve NetBIOS to specific IP addresses.

    How can you force the client to give up the dhcp lease if you have access to the client PC?
    ipconfig /release