Session has expired in ASP.NET

What are the root cause to loss the session values in ASP.NET applications?

Cause 1: Session will expire in InProc session state

Yes, Session will lost the values in InProc session state when the Web garden(IIS) value is greater than 1.

Why?   InProc session management won’t work because each worker process will be maintaining its own session state. So if the user is browsing your Web site and if we have 2 worker processors, then your user has a 1 in 2 chance of losing his session state as IIS round-robins his subsequent requests among the 2 available worker processes. If you use an out-of-process session state, then we can be sure that all 2 worker processes are consulting the same single resource as the place to store and retrieve session data.

How to check the Web garden value in my IIS server?

I. First we need to start the IIS Manager.

We can use two ways to start the Internet Information Services Manager..

First one,

From the Start menu, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
To start IIS Manager from the Run dialog box

Second option,

1. From the Start menu, click Run.
2. In the Open box, type inetmgr, and click OK.

II. Check the Web garden settings

In IIS Manger just expand the local computer, expand Application Pools, right click the application pool, and then click Properties.

Click the Performance tab, and under Web garden settings check the Maximum number of worker processes value  is 1 or greater than 1

If the value is greater than 1, Please change the value to 1 in order to fix this cause.

STRETCH IT CHALLENGE

It’s time to test you

Improving SQL Server Performance

 

1. SQL Sever optimization tips
2. Database settings optimization tips
3. Table design optimization tips
4. Application design optimization tips
5. Backup/Restore optimization tips
6. Bulk copy optimization tips
7. Cursor optimization tips
8. DTS optimization tips
9. Index optimization tips
10. File/File groups optimization tips
11. Full-Text search optimization tips
12. Operating system optimization tips
13. Transact – SQL optimization tips
14. Miscellaneous – SQL optimization tips
15. Replication optimization tips
16. Stored Procedure optimization tips
17. SQL Server 2000 optimization tips
18. SQL Server 6.5 optimization tips
19. Analysis services optimization tips

 

Chapter: 1

SQL Server Optimization Tips

• You can increase the ‘min memory per query’ option to improve the performance of queries that use hashing or sorting operations, if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.
  The SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default ‘min memory per query’ option is equal to 1024 Kb.

*****

• You can increase the ‘max async IO’ option if your SQL Server works on a high performance server with high-speed intelligent disk subsystem (such as hardware-based RAID with more than 10 disks).
  This option specifies the maximum number of outstanding asynchronous disk I/O requests that the entire server can issue against a file. By the way, the ‘max async IO’ SQL Server option is no longer supported in SQL Server 2000.

*****

• You can change the ‘network packet size’ option to the appropriate value.
  This option can improve performance on networks whose base topology supports larger packets than TCP/IP’s default of 4096 bytes. For example, if client sends or receives large amounts of data, a larger packet size can improve performance, because it results in fewer network reads and writes. The default value for the ‘network packet size’ option is 4096 bytes. Microsoft does not recommend changing this option, because for most applications, the default packet size of 4096 bytes is best.

*****

• You can change the ‘fill factor’ option to the appropriate value.
  The ‘fill factor’ option specifies how full SQL Server will make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed), you can set the ‘fill factor’ option to 100. When the table’s data modified very often, you can decrease the ‘fill factor’ option to 70 percent, for example.

*****

• You can increase the ‘recovery interval’ value.
  The ‘recovery interval’ option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option. SQL Server issues a checkpoint using the ‘recovery interval’ option. Microsoft does not recommend changing this option in general case, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the ‘recovery interval’ option to 5 and continuing monitoring.

*****

• You can set the ‘priority boost’ SQL Server options to 1.
  You can set this option to 1, if you want from SQL Server to work with a higher priority than other processes on the same computer. The default value is 0. Setting ‘priority boost’ to 1 can degrade the performance of other applications running on the same computer with SQL Server. So, you should set the ‘priority boost’ SQL Server options to 1 only if you have dedicated server to SQL Server. In other case, do not change this option.

*****

• Set the ‘max worker threads’ options to the maximum number of the user connections to your SQL Server box.
  The default setting for the ‘max worker threads’ option is 255. If the number of user connections will be less than the ‘max worker threads’ value, a separate operating system thread will be created for each client connection, but if the number of user connections will exceed this value the thread pooling will be used. For example, if the maximum number of the user connections to your SQL Server box is equal to 50, you can set the ‘max worker threads’ options to 50, this frees up resources for SQL Server to use elsewhere. If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the ‘max worker threads’ options to 500, this can improve SQL Server performance because thread pooling will not be used.

*****

• You can specify the ‘min server memory’ and ‘max server memory’ options.
  These options can be used to specify the fixed amount of memory to allocate to SQL Server. In this case, you should set the ‘min server memory’ and ‘max server memory’ to the same value (equal to the maximum amount of physical memory that SQL Server will use), and set the ’set working set size’ SQL Server option to 1. This can improve performance because SQL Server will not dynamically allocate memory. You can also change these options when SQL Server works on the same computer with other applications. In this case, the ‘min server memory’ options is used to allow SQL Server works when other applications pretend to use all available memory, and the ‘max server memory’ options is used to allow other applications work when SQL Server tried to use all available resources.

*****

• You can specify the ’set working set size’ SQL Server option to reserve the amount of physical memory space for SQL Server.
  Unlike SQL Server 6.5, SQL Server 7.0/2000 can automatically allocate memory (can take more memory if SQL Server need it, and can give memory back to operation system). This is one of the main advantages in comparison with previous versions, but dynamic memory allocation takes some time. If you know the maximum amount of physical memory that SQL Server will use, you can specify this amount by setting ‘min server memory’ and ‘max server memory’ to the same value (equal to the maximum amount of physical memory that SQL Server will use) and set the ’set working set size’ option to 1.

*****

Chapter: 2

Database Settings Optimization Tips

• You can turn off the ‘auto create statistics’ database option.
  When this database option is set to true, statistics are automatically created on columns used in a predicate. By default, this database option is set to true. Because auto creation statistics results in some performance degradation, you can turn off this database option and create statistics manually during off-peak times by using the CREATE STATISTICS statement. By the way, in most cases, it will not provide some performance benefits.

*****

• You can turn off the ‘auto update statistics’ database option.
  When this database option is set to true, existing statistics are automatically updated when the statistics become out-of-date. By default, this database option is set to true. Because auto update statistics results in some performance degradation, you can turn off this database option and update statistics manually during off-peak times by using the UPDATE STATISTICS statement. By the way, in most cases, it will not provide some performance benefits.

*****

• Turn off the ‘autoclose’ database option.
  When this option is turned on, the database’s resources are freed after the last user exits. When the new user will connect to database, the database should be reopened, which takes some time. So, do hot set this database option to true on your production server. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.

*****

• Turn off the ‘autoshrink’ database option.
  When this database option is set to true, the database files will be periodically shrink. Auto shrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.

*****

• You can turn on the ‘read-only’ database option to prevent users to modify the database’s data.
  By default, this database option is set to false. If you have data that should not be modified, you can place it into another database and set for this database the ‘read-only’ option to true. It can increase the speed of your queries. If you need to allow permissions management (for example, prevent some users to select data from some tables), you should create another filegroup and make only this filegroup read-only, because when the ‘read-only’ database option is set to true, the database’s system tables will be also read-only and this will prevent the permissions management.

*****

• You can turn on the ’select into/bulkcopy’ database option to allow SELECT INTO statements and nonlogged bulk copies.
  The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:
  1. The database option ’select into/bulkcopy’ is set to true.
  2. The target table is not being replicated.
  3. The TABLOCK hint is specified.
  4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.
  By default, this database option is set to false.

*****

• You can turn off the ‘trunc. log on chkpt.’ database option to prevent the transaction log from truncating on checkpoint.
  This option can be set if the transaction log grows very quickly to prevent the transaction log from filling rapidly and running out of disk space. If you set the ‘trunc. log on chkpt.’ database option to true, the transaction log cannot be backed up, so you cannot restore your data to the point of failure (only to the time when the last full backup was made). So, the general recommendation about this option is allow it to be turned off, and make the transaction log backup periodically to truncate the log. By default, this database option is set to true when using SQL Server Desktop Edition, and set to false for all other editions.
  Note. You can set the above database options by using the sp_dboption system stored procedure or Enterprise Manager. If you want to set the above database options for the newly created database, you should set these options for the model database.

*****

Chapter: 3

Table Design Optimization Tips

• Normalize your tables to the third normal form.
  A table is in third normal form (3NF) if it is in second normal form (2NF) and if it does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form. The normalization is used to reduce the total amount of redundant data in the database. The less data there is, the less work SQL Server has to perform, speeding its performance.

*****

• Consider the de normalization of your tables from the forth or fifth normal forms to the third normal form.
  Normalization to the forth and fifth normal forms can result in some performance degradation, especially when you need to perform many joins against several tables. It may be necessary to de normalize your tables to prevent performance degradation.

*****

• Consider horizontal partitioning of the very large tables into the current and the archives versions.
  The less space used, the smaller the table, the less work SQL Server has to perform to evaluate your queries. For example, if you need to query only data for the current year in your daily work, and you need all the data only once per month for the monthly report, you can create two tables: one with the current year’s data and one with the old data.

*****

• Create the table’s columns as narrow as possible.
  This can reduce the table’s size and improve performance of your queries as well as some maintenance tasks (such as backup, restore and so on).

*****

• Try to reduce the number of columns in a table.
  The fewer the number of columns in a table, the less space the table will use, since more rows will fit on a single data page, and less I/O overhead will be required to access the table’s data.

*****

• Try to use constraints instead of triggers, rules, and defaults whenever possible.
  Constraints are much more efficient than triggers and can boost performance. Constraints are more consistent and reliable in comparison to triggers, rules and defaults, because you can make errors when you write your own code to perform the same actions as the constraints.

*****

• If you need to store integer data from 0 through 255, use tinyint data type.
  The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint data types accordingly. For example, if you design tables for a small company with 5-7 departments, you can create the departments table with the DepartmentID tinyint column to store the unique number of each department.

*****

• If you need to store integer data from -32,768 through 32,767, use smallint data type.
  The columns with smallint data type use only two bytes to store their values, in comparison with four bytes and eight bytes used to store the columns with int and bigint data types respectively. For example, if you design tables for a company with several hundred employees, you can create an employee table with the EmployeeID smallint column to store the unique number of each employee.

*****

• If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type.
  The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table with a BookID int column to store the unique number of each book.

*****

• Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647.
  The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.

*****

• Use smalldatetime data type instead of datetime data type, if you need to store the date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute.
  The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types. For example, if you need to store the employee’s hire date, you can use column with the smalldatetime data type instead of datetime data type.

*****

• Use varchar/nvarchar columns instead of text/ntext columns whenever possible.
  Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, it can take more time to get the text/ntext values.

*****

• Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
  The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

*****

• Consider setting the ‘text in row’ SQL Server 2000 table’s option.
  The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the ‘text in row’ table option by using the sp_tableoption stored procedure.

*****

• If you work with SQL Server 2000, use cascading referential integrity constraints instead of triggers whenever possible.
  For example, if you need to make cascading deletes or updates, specify the ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

*****

Chapter: 4

Application Design Optimization Tips

• Use stored procedures instead of passing ANSI-compliant SQL to the database.
  This can reduce network traffic because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

*****

• Design the application to run queries asynchronously.
  This can improve performance of your application because one query will not wait for the next before it can run.

*****

• Consider using Microsoft Transaction Server (MTS) for object pooling.
  This can improve performance of your application because MTS allows COM objects to be pooled.

*****

• If most of the users have modern power computers (‘fat’ clients), consider design application to make client data caching.
  By doing so, you can reduce the load of your SQL Server because when users will need to access the data they will use local desktop resources, not SQL Server resources.

*****

• Consider designing the application to take advantage of the n-tier application model.
  By using the n-tier application model, you can increase application’s performance and scalability.

*****

• Try to restrict the result sets by using the WHERE clause in your SELECT statements.
  This can results in good performance benefits because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

*****

• Try to restrict the result sets by returning only the particular columns from the table, not all table’s columns.
  This can result in good performance benefits because SQL Server will return to client only particular columns, not all table’s columns. This can reduce network traffic and boost the overall performance of the query.

*****

• Try to restrict the result sets by using the select statements with the TOP keyword.
  This can improve performance of your application because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

*****

• Use SQL Server cursors to allow your application to fetch a small subset of rows instead of fetching all table’s rows.
  SQL Server cursors allow application to fetch any block of rows from the result set, including the next n rows, the previous n rows, or n rows starting at a certain row number in the result set. Using SQL Server cursors can reduce network traffic because the smaller result set will be returned.

*****

• Use ADO or OLE DB for accessing data from the applications that need high performance.
  This can improve performance of your application in comparison with using DAO or ODBC. OLE DB is a low-level COM API for accessing data and ADO is an application-level interface that uses OLE DB. Microsoft recommends to use OLE DB for developing tools, utilities, or low-level components that need high performance and use ADO for general-purpose access programs in business applications (Accounting, Human Resources, and Customer Management).

*****

• When you connect to SQL Server, use ‘Microsoft OLE DB Provider for SQL Server’ instead of ‘Microsoft ODBC Driver for SQL Server’.
  Because native OLE DB provider is faster than ODBC provider, you should use OLE DB provider whenever possible.

*****

• Set a lock time-out so that queries used in your application will not run indefinitely.
  You can use the SET LOCK_TIMEOUT command to allow an application to set a maximum time that a statement waits on a blocked resource. When the LOCK_TIMEOUT setting will be exceed, the blocked statement will be canceled automatically, and error message 1222 “Lock request time-out period exceeded” will be returned to the application. Your application should have an error handler that can trap error message 1222.

*****

• Avoid using both OLTP and OLAP transactions within the same database.
  Because OLTP transactions are optimal for managing changing data and OLAP transactions are optimal for data queries that do not change data try to relegate OLTP and OLAP transactions to their own databases.

*****

• Try to avoid using Refresh method when you call stored procedures from the ADO Command object.
  This can improve performance of your application because using Refresh method produces extra network traffic. You should explicitly create the stored procedure parameters using ADO code, instead of using the Refresh method to identify the parameters of a stored procedure.

*****

• Avoid creating transactions using ADO’s methods.
  Try to create transactions inside a stored procedure on the SQL Server. By doing so, you can reduce network traffic and boost overall application performance.

*****

Chapter: 5

Backup/Restore Optimization Tips

• Try to perform backup to the local hard disk first, and copy backup file(s) to the tape later.
  When you perform backup, some SQL Server commands cannot be made, for example: during backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink database, you cannot run CREATE INDEX statement and so on. So, to decrease the backup operation’s time, you can perform backup to the local hard disk first, and then copy backup file(s) to the tape, because tape device usually much more slow than hard disks. The smaller backup operation’s time is, the less impact there will be on the server when the backup occurs.

*****

• Perform backup on multiple backup devices.
  Using multiple backup devices forces SQL Server to create a separate backup thread for each backup device, so the backups will be written to all backup devices in parallel.

*****

• Perform backup on a physical disk array, so the more disks in array the more quickly the backup will be made.
  This can improve performance because a separate thread will be created for each backup device on each disk in order to write the backup’s data in parallel.

*****

• Perform backups during periods of low database access.
  Because backup is very resource effective, try to schedule it during CPU idle time and slow production periods.

*****

• Use full backup to minimize the time to restore databases.
  The full backups take the longest to perform in comparison with differential and incremental backups, but are the fastest to restore.

*****

• Use incremental backup to minimize the time to backup databases.
  The incremental backups take the fastest to perform in comparison with full and differential backups, but are the longest to restore.

*****

• Use differential backup instead of incremental backup when the users update the same data many times.
  Because a differential backup captures only those data pages that have changed after the last database backup, you can eliminate much of the time the server spends rolling transactions forward when recovering transaction logs from the incremental backups. Using differential backup, in this case, can improve the recovery process in several times.

*****

• Try to separate your database to different files and file groups to backing up only appropriate file/filegroup.
  This can results in smaller backup operation’s time. The smaller backup operation’s time is, the less impact there will be on the server when the backup occurs.

*****

• Use Windows NT Performance Monitor or Windows 2000 System Monitor to check a backup impact on the total system performance.
  You can verify the following counters: SQL Server Backup Device: Device Throughput Bytes/sec to determine the throughput of specific backup devices, rather than the entire database backup or restore operation; SQL Server Databases: Backup/Restore Throughput/sec to monitor the throughput of the entire database backup or restore operation; Physical Disk: % Disk Time to monitors the percentage of time that the disk is busy with read/write activity; Physical Disk Object: Avg. Disk Queue Length to determine how many system requests on average are waiting for disk access.

*****

• To decrease the backup operation’s time consider backing up more often.
  The more often you will make backup, the smaller they will be, and the less impact there will be on the server when the backup occurs. So, to avoid locking users for a long time during everyday work, you can perform backup more often.
  Note. The more often you will make backup, the less data you will lost if the database becomes corrupt.

*****

• Place a tape drive on another SCSI bus as disks or a CD-ROM drive.
  The tape drives perform better if they have a dedicated SCSI bus for each tape drive used. Using separate SCSI bus for a tape drive can results in maximum backup performance and prevents conflicts with other drive array access. Microsoft recommends using dedicated SCSI bus for the tape drives whose native transfer rate exceeds 50 percent of the SCSI bus speed.

*****

• Use SQL Server 2000 snapshot backups for the very large databases.
  The SQL Server 2000 snapshot backup and restore technologies work in conjunction with third party hardware and software vendors. The main advantages of snapshot backups and restores are that they can be done in a very short time, typically measured in seconds, not hours, and reduce the backup/restore impact on the overall server performance. The snapshot backups accomplished by splitting a mirrored set of disks or creating a copy of a disk block when it is written and required the special hardware and software.

*****

Chapter: 6

Bulk Copy Optimization Tips

• Use nonlogged bulk copy whenever possible.
  The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:
  1. The database option ’select into/bulkcopy’ is set to true.
  2. The target table is not being replicated.
  3. The TABLOCK hint is specified.
  4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.

*****

• Use native mode bulk copy whenever possible.
  This can improve performance in comparison with the character mode.

*****

• Try to use BULK INSERT command instead of bcp or DTS to load data into SQL Server.
  The BULK INSERT command is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot bulk copy data from SQL Server to a data file.

*****

• Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.
  The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

• Specify the number of the first and the last row to bulk copy, if you need to bulk copy not all the rows from the specified data file.
  This can results in good performance benefits, because the total amount of data copied will be less.

*****

• Specify the number of rows per batch of data copied, if the transaction log was filled before the bulk copy is complete.
  Because each batch is copied to the server as one transaction, SQL Server commits or rolls back the transaction for every batch. When you bulk copy large data files, the transaction log can be filled before the bulk copy is complete. In this case, enlarge the transaction log, allow it to grow automatically or specify the number of rows per batch of data copied.

*****

• Try to increase the packet_size option.
  The packet_size option specifies the number of bytes, per network packet, sent to and from the server. The packet_size can be from 4096 to 65535 bytes with the default of 4096. Increased packet size can enhance performance of bulk copy operations. Try to set the packet_size option to 8192 bytes and continue monitoring.

*****

• Use the ORDER hint, if the clustered index exists on the table and the data file is sorted according to the clustered index.
  This can significantly improve performance of the bulk copy operation; because SQL Server will load data in the clustered index order without any reorders operations.

*****

• If you create a new table and bulk copy data into it, try to bulk load data first and only after that create any indexes.
  This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

• If you load data into an empty table with the existing nonclustered indexes, try to drop the nonclustered indexes, bulk load data and only after that re-create the nonclustered indexes.
  This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

• If you load data into a nonempty table with the existing clustered and/or nonclustered indexes, and the amount of data added is large, it can be faster to drop all indexes on the table, perform the bulk copy operation, and then re-create the indexes after the data is loaded.
  Check the time needed to load data with dropping/re-creating indexes and without dropping/re-creating indexes on your test server before run bulk copy operation on the production server.

*****

• If your SQL Server box has multiple CPUs, try to divide loaded data into two or more sources and run multiple instances of BCP on separate clients to load data in parallel.
  Because SQL Server allows data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement, try to use parallel data loads whenever possible. To bulk copy data into SQL Server in parallel, you must provide all the following conditions:
  1. The database option ’select into/bulkcopy’ is set to true.
  2. The TABLOCK hint is specified.
  3. The target table does not have any indexes.

*****

• Specify the TABLOCK hint, if you bulk copy data into an empty table from a single client.
  This can improve performance of the bulk copy operation, because this causes a table-level lock to be taken for the duration of the bulk copy operation.

*****

• Try to avoid using CHECK_CONSTRAINTS and FIRE_TRIGGERS hints.
  Using these hints can significantly degrade performance of the bulk copy operation, because for each row loaded the constraints and insert triggers defined on the destination table will be executed.

*****

Chapter: 7

Cursor Optimization Tips

• Try to avoid using SQL Server cursors, whenever possible.
  SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

• Do not forget to close SQL Server cursor when its result set is not needed.
  To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

*****

• Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.
  To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

*****

• Try to reduce the number of records to process in the cursor.
  To reduce the cursor result set, use the WHERE clause in the cursor’s select statement. It can increase cursor performance and reduce SQL Server overhead.

*****

• Try to reduce the number of columns to process in the cursor.
  Include in the cursor’s select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.

*****

• Use READ ONLY cursors, whenever possible, instead of updatable cursors.
  Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

*****

• Try to avoid using insensitive, static and keyset cursors, whenever possible.
  These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

*****

• Use FAST_FORWARD cursors, whenever possible.
  The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

*****

• Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.
  If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.

*****

Chapter: 8

DTS Optimization Tips

• Avoid using DTS if it is possible to use Transact-SQL distributed query such as a SELECT INTO statement to transfer data.
  Using Transact-SQL is the fastest method to move data between SQL Server tables, so try to use Transact-SQL statements to move data whenever possible.

*****

• Try to use BULK INSERT statement instead of bcp or DTS to load data into SQL Server from the text file.
  The BULK INSERT statement is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot export data from SQL Server to a data file.

*****

• Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.
  The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

• Try to avoid using data pump operations that use ActiveX transformation scripts.
  Because data pump operations that use ActiveX transformation scripts can be up to two to four times slower than copy operations, you should avoid using ActiveX transformation scripts, whenever possible.

*****

• If you cannot avoid using ActiveX scripts in your data pump operations, try to use VBScript instead of JScript or PerlScript.
  Microsoft recommends using VBScript because scripts written in VBScript run approximately 10 percent faster than scripts written in JScript, which in turn run approximately 10 percent faster than scripts written in PerlScript.

*****

• When the transformation contains many columns (more than 20) and the data pump operations use ActiveX transformation scripts, use ordinal values to refer to columns in ActiveX transformations instead of referring to columns by name.
  Use this tip only when the transformation contains many columns. If the transformation contains less than 20 columns, use columns name to refer to columns in ActiveX transformations to make the script easier to read.

*****

• Try to use many-to-many copy column transformations instead of one-to-one copy column transformations.
  The DTS Import/Export Wizard uses many-to-many column mappings. By default, the DTS Designer assigns one-to-one column mappings to transformations in order to improve readability. Because a single many-to-many copy column transformation is faster then many one-to-one copy column transformations, you can manually set your Transform Data task or Data Driven Query task to use many-to-many column mappings. For example, to set Transform Data task to use many-to-many column mappings in SQL Server 2000 DTS Designer, you can do the following:
  1. Choose appropriate Transform Data task in the DTS Designer.
  2. Right-click this task and select Properties.
  3. On the Transformations tab click ‘Select All’ button.
  4. Click ‘Delete All’ button.
  5. Click ‘New’ button and choose ‘Copy Column’ in the Create New Transformation window.
  6. Specify the transformation options and click ‘OK’ button.

*****

• You can increase the DTS package priority.
  To increase the DTS package priority in SQL Server 2000, try the following:
  1. Run SQL Server Enterprise Manager.
  2. Expand a server group; then expand a server.
  3. Expand Data Transformation Services and choose appropriate package.
  4. Right-click package and select ‘Design Package’.
  5. From the top menu of the DTS Designer click Package and select Properties.
  6. On the General tab increase the package priority by moving the slider bar to the right one or two steps (the slider bar has three position, and the default setting is at the second setting).

*****

• You can increase the Transform Data task or Data Driven Query task priority.
  To increase the Transform Data task priority in SQL Server 2000 DTS Designer, you can do the following:
  1. Choose appropriate Transform Data task in the DTS Designer.
  2. Right-click this task and select Workflow Properties.
  3. On the Options tab increase the task priority by moving the slider bar to the right one or two steps (the slider bar has five position, and the default setting is at the third setting).

*****

• Try to use ‘Microsoft OLE DB Provider for SQL Server’ instead of ‘Microsoft ODBC Driver for SQL Server’ and ‘Microsoft OLE DB Provider for Oracle’ instead of ‘Microsoft ODBC Driver for Oracle’.
  Because native OLE DB provider is faster than ODBC provider, you should use OLE DB provider whenever possible.

*****

• Try to avoid DTS lookups.
  Because DTS lookups slow down performance, try to use the Transact-SQL statements to perform the same functions.

*****

• Check the ‘Use fast load’ advanced option of the Transform Data task.
  When you enable this option, the high-speed bulk-copy processing will be used. To enable this option in SQL Server 2000 DTS Designer, you can do the following:
  1. Choose appropriate Transform Data task in the DTS Designer.
  2. Right-click this task and select Properties.
  3. On the Options tab check the ‘Use fast load’ option.

*****

• Increase the ODBC query time-out value to improve query performance on large SQL Server 2000 DTS packages stored in a repository.
  Change this registry setting from the default value (10 seconds): HKEY_LOCALMACHINE\SOFTWARE\Microsoft\Repository\Engine\ODBCQueryTimeout The ODBC query time-out values are measured in seconds.

*****

• When using SQL Server 2000 Transform Data task, try to use ‘Copy Column’ method to transfer data.
  This is the fastest way to transfer data using Transform Data task, so it should be used whenever possible.

*****

Chapter: 9

Index Optimization Tips

• Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
  These column(s) are best candidates for index creating. You should analyze your queries very attentively to avoid creating not useful indexes.

*****

• Keep your indexes as narrow as possible.
  Because each index take up disk space try to minimize the index key’s size to avoid using superfluous disk space. This reduces the number of reads required to read the index and boost overall index performance.

*****

• Drop indexes that are not used.
  Because each index take up disk space and slow the adding, deleting, and updating of rows, you should drop indexes that are not used. You can use Index Wizard to identify indexes that are not used in your queries.

*****

• Try to create indexes on columns that have integer values rather than character values.
  Because the integer values usually have less size then the characters values size (the size of the int data type is 4 bytes, the size of the bigint data type is 8 bytes), you can reduce the number of index pages which are used to store the index keys. This reduces the number of reads required to read the index and boost overall index performance.

*****

• Limit the number of indexes, if your application updates data very frequently.
  Because each index take up disk space and slow the adding, deleting, and updating of rows, you should create new indexes only after analyze the uses of the data, the types and frequencies of queries performed, and how your queries will use the new indexes. In many cases, the speed advantages of creating the new indexes outweigh the disadvantages of additional space used and slowly rows modification. However, avoid using redundant indexes, create them only when it is necessary. For read-only table, the number of indexes can be increased.

*****

• Check that index you tried to create does not already exist.
  Keep in mind that when you create primary key constraint or unique key constraints SQL Server automatically creates index on the column(s) participate in these constraints. If you specify another index name, you can create the indexes on the same column(s) again and again.

*****

• Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.
  Because every table can have only one clustered index, you should choose the column(s) for this index very carefully. Try to analyze all your queries, choose most frequently used queries and include into the clustered index only those column(s), which provide the most performance benefits from the clustered index creation.

*****

• Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
  In comparison with a clustered index, which can be only one for each table, each table can have as many as 249 nonclustered indexes. However, you should consider nonclustered index creation as carefully as the clustered index, because each index take up disk space and drag on data modification.

*****

• Create clustered index on column(s) that is not updated very frequently.
  Because the leaf node of a nonclustered index contains a clustered index key if the table has clustered index, then every time that a column used for a clustered index is modified, all of the nonclustered indexes must also be modified.

*****

• Create clustered index based on a single column that is as narrow as possibly.
  Because nonclustered indexes contain a clustered index key within their leaf nodes and nonclustered indexes use the clustered index to locate data rows, creating clustered index based on a single column that is as narrow as possibly will reduce not only the size of the clustered index, but all nonclustered indexes on the table also.

*****

• Avoid creating a clustered index based on an incrementing key.
  For example, if a table has surrogate integer primary key declared as IDENTITY and the clustered index was created on this column, then every time data is inserted into this table, the rows will be added to the end of the table. When many rows will be added a “hot spot” can occur. A “hot spot” occurs when many queries try to read or write data in the same area at the same time. A “hot spot” results in I/O bottleneck.
  Note. By default, SQL Server creates clustered index for the primary key constraint. So, in this case, you should explicitly specify NONCLUSTERED keyword to indicate that a nonclustered index is created for the primary key constraint.

*****

• Create a clustered index for each table.
  If you create a table without clustered index, the data rows will not be stored in any particular order. This structure is called a heap. Every time data is inserted into this table, the row will be added to the end of the table. When many rows will be added a “hot spot” can occur. To avoid “hot spot” and improve concurrency, you should create a clustered index for each table.

*****

• Don’t create index on column(s) which values has low selectivity.
  For example, don’t create an index for columns with many duplicate values, such as “Sex” column (which has only “Male” and “Female” values), because in this case the disadvantages of additional space used and slowly rows modification outweigh the speed advantages of creating a new index.

*****

• If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
  The order of the columns in a composite (multi-column) index is very important. This can increase the chance the index will be used.

*****

• If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.
  The order of the columns in a composite (multi-column) index is very important. The index will be used to evaluate a query only if the leftmost index key’s column are specified in the WHERE clause of the query. For example, if you create composite index such as “Name, Age”, then the query with the WHERE clause such as “WHERE Name = ‘Alex’” will use the index, but the query with the WHERE clause such as “WHERE Age = 28″ will not use the index.

*****

• If you need to join several tables very frequently, consider creating index on the joined columns.
  This can significantly improve performance of the queries against the joined tables.

*****

• Consider creating a surrogate integer primary key (identity, for example).
  Every table must have a primary key (a unique identifier for a row within a database table). A surrogate primary key is a field that has a unique value but has no actual meaning to the record itself, so users should never see or change a surrogate primary key. Some developers use surrogate primary keys, others use data fields themselves as the primary key. If a primary key consists of many data fields and has a big size, consider creating a surrogate integer primary key. This can improve performance of your queries.

*****

• Consider creating the indexes on all the columns, which referenced in most frequently used queries in the WHERE clause which contains the OR operator.
  If the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed, then the table or clustered index scan will be made. In this case, creating the indexes on all such columns can significantly improve your queries performance.

*****

• If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.
  A covering index is an index, which includes all of the columns referenced in the query. So the creating covering index can improve performance because all the data for the query is contained within the index itself and only the index pages, not the data pages, will be used to retrieve the data. Covering indexes can bring a lot of performance to a query, because it can save a huge amount of I/O operations.

*****

• Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.
  Because fragmented data can cause SQL Server to perform unnecessary data reads and the queries performance against the heavy fragmented table can be very bad, you should periodically rebuild all indexes to reduce fragmentation. Try to schedule the DBCC DBREINDEX statement during CPU idle time and slow production periods.

*****

• Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.
  The DBCC INDEXDEFRAG statement is a new SQL Server 2000 command, which was not supported in the previous versions. Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term and thus will not block running queries or updates. So, try to use the DBCC INDEXDEFRAG command instead of DBCC DBREINDEX, whenever possible.

*****

• Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
  The SORT_IN_TEMPDB option is a new SQL Server 2000 feature, which was not supported in the previous versions. When you create an index with the SORT_IN_TEMPDB option, SQL Server uses the tempdb database, instead of the current database, to sort data during the index creation. Using this option can reduce the time it takes to create an index, but increases the amount of disk space used to create an index.

*****

• Use the SQL Server Profiler Create Trace Wizard with “Identify Scans of Large Tables” trace to determine which tables in your database may need indexes.
  This trace will show which tables are being scanned by queries instead of using an index.

*****

Chapter: 10

File/Filegroups optimization tips

• Set a reasonable size of your database.
  First of all, before database creation, you should estimate how big your database will be. To estimate the reasonable database size, you should estimate the size of each table individually, and then add the values obtained.

*****

• Set a reasonable size for the transaction log.
  The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The smaller the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimation database size is equal to 10Mb, you can set the size of the transaction log to 4-5Mb, but if the estimated database size is over 500Mb, the 50Mb may be enough for the size of the transaction log.

*****

• Leave the Autogrow feature on for the data files and for the log files.
  Leave this feature to let SQL Server to automatically increase allocated resources when necessary without DBA intervention. The Autogrow feature is necessary when there is no DBA in your firm or when your DBA doesn’t have a lot of experience.

*****

• Set a reasonable size of the autogrow increment.
  Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable size for the Autogrow increment to avoid automatically growing too often. Try to set the initial size of the database, and the size of the Autogrow increment, so that automatic growth will occur once per week or less.

*****

• Don’t set the autoshrink feature.
  Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on.

*****

• Set the maximum size of the data and log files.
  Specify the maximum size to which the files can grow to prevent disk drives from running out of space.

*****

• Create a user-defined filegroup and make it the default filegroup.
  It’s a good decision in most cases to store and manage system and user objects separately from one another, so the user objects will not compete with system objects for space in the primary filegroup. Usually, a user- defined filegroup is not created for small databases, for example, if the database is less than 100Mb.

*****

• Create a user-defined filegroup and create some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables.
  LOAD TABLE and DUMP TABLE are no longer supported in SQL Server 7.0 (and higher), but you can place a table in its own filegroup and can backup and restore only this table. So you can group user objects with similar maintenance requirements into the same filegroup.

*****

• If you have several physical disk arrays, try to create as many files as there are physical disk arrays so that you have one file per disk array.
  This will improve performance, because when a table is accessed sequentially, a separate thread is created for each file on each disk array in order to read the table’s data in parallel.

*****

• Don’t create many data and log files on the same physical disk array.
  Leaving the autogrow feature on for the data and for the log files can cause fragmentation of those files if there are many files on the same physical disk array. In most cases, it’s enough to have 1-2 database files on the same physical disk.

*****

• For heavily accessed tables, place these tables in one filegroup and place the table’s indexes in a different filegroup on different physical disk arrays.
  This will improve performance, because separate threads will be created to access the tables and indexes.

*****

• For heavily accessed tables with text/image columns, place this table in one filegroup and place text/image columns in a different filegroup on different physical disks.
  You can use CREATE TABLE statement with TEXTIMAGE_ON keyword to place text/image columns in a different filegroup. See the SQL Server BOL for details.

*****

• Place the log files on other physical disk arrays than those with the data files.
  Because logging is more write-intensive, it’s important that the disk arrays containing the SQL Server log files have sufficient disk I/O performance.

*****

• If one of your join queries is used much more often than others, place the tables used in this query in different filegroups on different physical disk arrays.

*****

• If you have read-only tables, place these tables in different filegroups on different physical disk arrays and use the ALTER DATABASE statement to make just this filegroup READ ONLY.
  This not only increases read performance, it prevents any data changes and allows you to control permissions to this data.

*****

• Use the Windows NT Performance Monitor to determine the appropriate number for the data and log files on your server by checking the Disk Queue Length counter.
  Consider reducing the number of files and filegroups you have for your databases if the Disk Queue length on your server averages above 3, and continue monitoring once you have made your changes to ensure that your disk I/O is optimum over the long term.

*****

Chapter: 11

Full-Text search optimization tips

• Set the virtual memory size to at least 3 times the physical memory installed in the computer, and set the SQL Server ‘max server memory’ server configuration option to half the virtual memory size setting (1.5 times the physical memory).
  Because working with full-text search is very resource expensive, you should have enough physical and virtual memory.

*****

• Set the “Maximize Throughput for Network Applications” option.
  This can increase full-text search performance, because Windows NT will allocate more RAM to SQL Server than to its file cache. To set this option, you can do the following:
  1. Double-click the Network icon in Control Panel.
  2. Click the Services tab.
  3. Click Server to select it, and then click the Properties button.
  4. Click Maximize Throughput for Network Applications, and then click OK.
  5. Restart the computer.

*****

• Make full-text index population during periods of low database access.
  Because full-text index population takes some time, these updates should be scheduled during CPU idle time and slow production periods.

*****

• Assign a very large table (a table that has millions of rows) to its own full-text catalog.
  This can improve performance, and can be used to simplify administering and monitoring.

*****

• You can boost the resource usage for the full-text search service (increase the “System Resource Usage” option for the full-text search service).
  Run SQL Server Enterprise Manager, expand a server group then expand a server. Expand “Support Services”, then right-click the “Full-Text Search” and select “Properties”. Choose the “Performance” tab and increase the “System Resource Usage” option for the full-text search service.
  Note. Don’t set the “System Resource Usage” option to the “Dedicated” value (right border of the “System Resource Usage” slider bar), because it can negatively affect your SQL Server’s performance.

*****

• Reduce the full-text unique key size.
  To create a full-text index, the table to be indexed must have a unique index. Try to select a numeric column as the full-text unique key to increase the speed of full-text population. If the table to be indexed does not have numeric unique index, consider creating numeric unique index.

*****

• If you have several physical disks, create several Pagefile.sys files, so that each Pagefile.sys file will be placed on its own physical disk.
  Spreading paging files across multiple disk drives and controllers improves performance on most disk systems because multiple disks can process input/output requests concurrently.

*****

• If you use SQL Server 2000, consider using the Change Tracking with scheduled or background update index option versus Incremental Population.
  The Change Tracking with scheduled propagation should be used when CPU and memory can be used at scheduled times and changes between the scheduled times are not significant. The Change Tracking with background update index option should be used when CPU and memory are available and the value of an up-to-date index is high.

*****

• Consider using a full population when a large percentage of records were changed or added at once.

*****

• If you work with SQL Server 7.0, consider using an incremental population when not a large percentage of records were changed or added at once.
  Using an incremental population instead of a full population decreases the population time and results in good performance benefits.

*****

• If you have several physical disks, place the database files separately from the full-text catalog files.
  So, you can improve the speed of full-text queries, because multiple disks can process input/output requests concurrently.

*****

• Upgrade to SQL Server 2000, in order to enhance full-text search performance and if you need to work with full-text search in clustered environment.
  The full text search is not available in SQL Server 7.0 clustered environment.

*****

• If you work with SQL Server 2000, consider using the new top_n_by_rank parameter with CONTAINSTABLE or FREETEXTTABLE.
  It can be used to restrict the number of rows returned. The top_n_by_rank parameter specifies that only the n-highest ranked matches, in descending order, will be returned.

*****

• Try to use the CONTAINS or FREETEXT predicates instead of the CONTAINSTABLE or FREETEXTTABLE functions to simplify the query’s text.
  Because qualifying rows returned by the CONTAINSTABLE or FREETEXTTABLE rowset functions must be explicitly joined with the rows in the original SQL Server table, the queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates.

Chapter : 12

Operating System Optimization Tips

• Set a reasonable size of your PAGEFILE.SYS file(s).
  Microsoft recommends that the Windows NT PAGEFILE.SYS file(s) be set to physical RAM + 12 MB for the initial size and physical RAM + half of physical RAM for the maximum size. Microsoft recommends that the Windows 2000 PAGEFILE.SYS file(s) be set to 1.5 times the amount of physical RAM.
  If you used additional SQL services such as Full-Text Search service, the size of PAGEFILE.SYS file(s) should be increased.
  To increase the size of PAGEFILE.SYS file(s), you can do the following:
  1. Double-click the Control Panel System applet and select the Performance tab.
  2. Click the “Virtual Memory” button.
  3. Set appropriate size of the PAGEFILE.SYS file(s).
  4. Restart the computer.

*****

• Create another pagefile.sys files on every separate physical drives (Except drive contains the Windows NT system directory).
  Spreading paging files across multiple disk drives and controllers improves performance on most disk systems because multiple disks can process input/output requests concurrently.

*****

• If you have a lot of RAM, you can configure your Windows NT server to never page out drivers and system code to the pagefile that are in the pageable memory area.
  Run regedit and choose:
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management
  Set DisablePagingExecutive to 1 and reboot the server box.

*****

• Set the “Maximize Throughput for Network Applications” option.
  This can increase SQL Server performance, because Windows NT will allocate more RAM to SQL Server than to its file cache.
  To set this option, you can do the following:
  1. Double-click the Network icon in Control Panel.
  2. Click the Services tab.
  3. Click Server to select it, and then click the Properties button.
  4. Click Maximize Throughput for Network Applications, and then click OK.
  5. Restart the computer.

*****

• You can increase performance by disable last access update files date.
  Run regedit and choose:
  HKLM\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate
  Add key NtfsDisableLastAccessUpdate as REG_DWORD and set it to “1″.

*****

• Use minimum protocols on the server box (only TCP/IP, for example).
  Because each protocol uses RAM and CPU, you can remove unused protocols to release resources for SQL Server using.

*****

• Use minimum services on the server box.
  Try to remove from startup IIS service, FTP server service, Gopher, SMTP, WINS, DHCP, Directory Replicator and so on, if you do not need to use these services. You can start these services manually when you will need them.

*****

• When multiple transport protocols are installed, set the most frequently used protocol to the first place in the binding list.
  If you installed several protocols, Windows NT negotiates network connections in the order that the protocols are prioritized in the network services binding list. So, the first protocol in the binding list will be used before the other installed protocols. You can improve the overall performance by setting the most frequently used protocol to the first place in the binding list.

*****

• Use as few counters in Performance Monitor, as possible.
  Because each Performance Monitor counter uses some server resources, it is a great idea to use as few counters in Performance Monitor, as possible.

*****

• Do not use Open GL screen savers on your server box.
  Because Open GL screen savers use a lot of system resources, it is a great idea to not use them on a server box.

*****

• Use as few types of Audit Policy events, as possible.
  Because each type of Audit Policy events uses some server resources, it is a great idea to use as few types of Audit Policy events, as possible. Try to not use “File and Objects Access” and “Process Tracking” Audit Policy events, because they most resource expensive in comparison with other Audit Policy events.

*****

• Set the performance boost for the foreground applications to “None”.
  This ensures that background applications (SQL Server, for example) will get higher priority than foreground applications.
  To set the performance boost for the foreground applications to “None”, you can do the following:
  1. Double-click the Control Panel System applet and select the Performance tab.
  2. On the Application Performance box drag the arrow to set the boost to “None”.

*****

• You can increase the I/O Page Lock Limit to increase the performance of the reads and writes operations.
  Run regedit and choose:
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management
  Set IoPageLockLimit to the maximum bytes you can lock for I/O operations.

*****

Chapter: 13

Transact – SQL Optimization Tips

• Try to restrict the queries result set by using the WHERE clause.
  This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

*****

• Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns.
  This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table’s columns. This can reduce network traffic and boost the overall performance of the query.

*****

• Use views and stored procedures instead of heavy-duty queries.
  This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

*****

• Try to avoid using SQL Server cursors, whenever possible.
  SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

• If you need to return the total table’s row count, you can use alternative way instead of SELECT COUNT(*) statement.
  Because SELECT COUNT(*) statement make a full table scan to return the total table’s row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2 So, you can improve the speed of such queries in several times.
  See this article for more details:

*****

• Try to use constraints instead of triggers, whenever possible.
  Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.

*****

• Use table variables instead of temporary tables.
  Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

*****

• Try to avoid the HAVING clause, whenever possible.
  The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

*****

• Try to avoid using the DISTINCT clause, whenever possible.
  Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.

*****

• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
  This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

*****

• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
  This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

*****

• Use the FAST number_rows table hint if you need to quickly return ‘number_rows’ rows.
  You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.

*****

• Try to use UNION ALL statement instead of UNION, whenever possible.
  The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

*****

• Do not use optimizer hints in your queries.
  Because SQL Server query optimizer is very clever, it is very unlikely that you can optimize your query by using optimizer hints, more often, this will hurt performance.

*****

Chapter: 14

Miscellaneous – SQL Optimization Tips

• Try to perform backup at the local hard disk first, and copy backup file(s) to the tape later.
  When you perform backup, some SQL Server commands cannot be made, for example: during backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink database, you cannot run CREATE INDEX statement, you cannot make SELECT INTO, bulk load and so on. So, to improve backup performance, you can perform backup at the local hard disk first, and then copy backup file(s) to the tape.

*****

• Use nonlogged bulk copy whenever possible.
  The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:
  1. The database option ’select into/bulkcopy’ is set to true.
  2. The target table is not being replicated.
  3. The TABLOCK hint is specified.
  4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.

*****

• Use native mode bulk copy whenever possible.
  This can improve performance in comparison with the character mode.

*****

• Try to use BULK INSERT command instead of bcp or DTS to load data into SQL Server.
  The BULK INSERT command is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot bulk copy data from SQL Server to a data file.

*****

• Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.
  The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

• Keep transactions as short as possible.
  This can be used to prevent deadlocks.

*****

• Consider the horizontally partitioning the very large tables into the current and archives versions.
  This can improve performance of your select statements.

*****

• Normalize your database’s tables to the third normal form.
  A table is in third normal form (3NF) if it is in second normal form (2NF) and if it does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form.

*****

• Consider the denormalization of your database’s tables from the forth or fifth normal forms to the third normal form.
  Normalization to the forth and fifth normal forms can results in some performance degradation, so it can be necessary to denormalize your database’s tables to prevent performance degradation.

*****

• Create the table’s columns as narrow as possible.
  So, you can reduce the table’s size, this can improve performance of your queries and some maintenance tasks (such as backup, restore and so on).

*****

• Use varchar/nvarchar columns instead of text/ntext columns whenever possible.
  Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, then it can take more time to get the text/ntext values.

*****

• Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
  So, you can reduce the table’s size, this can improve performance of your queries and some maintenance tasks (such as backup, restore and so on).

*****

• Use char/nchar data type instead of varchar/nvarchar if the column’s size will be 4 bytes or less.
  The char data type is a fixed-length data type and varchar data type is variable-length data type. So, by using char data type, you can increase the probability of in-place update instead of delete/insert or deferred update. The in-place update is the most effective method of modification, when it is used the data changed on its physical place. When the row’s size is changed, the delete/insert modification method can be used. This results in some performance degradation.

*****

• If you need to delete all tables rows, consider using TRUNCATE TABLE instead of DELETE command.
  Using the TRUNCATE TABLE is much fast way to delete all table’s rows, because it removes all rows from a table without logging the individual row deletes.

*****

• You can increase the speed of sorting operation up to 20 percent, if you will use Binary sort order instead of Dictionary sort order, which is used by default.
  Binary sort order is the simplest and fastest sort order, but it is used not often, because binary sort is not case-insensitive and it is based on the numeric values (from 0 through 255) of the characters in the installed character set.

*****

• Don’t use Enterprise Manager to access remote servers over a slow link or to maintain very large databases.
  Because using Enterprise Manager is very resource expensive, use stored procedures and T-SQL statements, in this case.

*****

• Try to install SQL Server on a standalone server box, not on a primary or backup domain controller.
  Because domain controllers have extra overhead, you should install SQL Server on a standalone server box to dedicate all of the server’s power to SQL Server.

*****

Chapter: 15

Replication Optimization Tips

• Avoid publishing unnecessary data.
  Try to restrict the amount of published data. This can results in good performance benefits, because SQL Server will publish only the amount of data required. This can reduce network traffic and boost the overall replication performance.

*****

• Place the published database log and distribution database log on the separate disk drives.
  Because logging is more write-intensive, it is important that the disk arrays containing the SQL Server log files have sufficient disk I/O performance.

*****

• Do not configure the distribution database to expand or shrink automatically.
  Microsoft recommends to set a fixed size for the distribution database. Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable initial size of the distribution database.

*****

• Place the distribution component of replication on its own dedicated server.
  This topology is used for performance reasons when the level of replication activity increases or the server resources become constrained. It reduces Publisher loading, but it increases overall network traffic. This topology requires separate Microsoft SQL Server installations, one for the Publisher and one for the Distributor.

*****

• Run the Snapshot Agent as infrequently as possible.
  The Snapshot Agent bulk copies data from the Publisher to the Distributor, which results in some performance degradation. So, try to schedule it during CPU idle time and slow production periods.

*****

• Avoid using continuous replication.
  Try to schedule replication to occur at regular intervals instead of using continuous replication.

*****

• Avoid replicating text, ntext and image columns.
  These data types require more storage space and processing than other column data types.

*****

• Replicate the execution of stored procedures when a large number of rows are affected.
  For example, instead of replicating a very large number of insert, update and delete statements, you can create stored procedure, which will contain all these statements, and replicate to subscriber only the execution of this stored procedure. This can reduce network traffic and boost the overall replication performance.

*****

• Set the “Maximize Throughput for Network Applications” option.
  This can increase SQL Server performance, because Windows NT will allocate more RAM to SQL Server than to its file cache. To set this option, you can do the following:
  1. Double-click the Network icon in Control Panel.
  2. Click the Services tab.
  3. Click Server to select it, and then click the Properties button.
  4. Click Maximize Throughput for Network Applications, and then click OK.
  5. Restart the computer.

*****

• Specify the ‘min server memory’ options.
  This option is used to set a minimum amount of memory allocated to SQL Server. Microsoft recommends that the ‘min server memory’ options be set to at least 16 MB of memory to avoid low memory availability during replication activities, if the server is a remote Distributor or a combined Publisher and Distributor. You can also change these options when SQL Server works on the same computer with other applications. In this case, the ‘min server memory’ option is used to allow SQL Server works when other applications pretend to use all available memory.

*****

• If you work with SQL Server 2000 in a central publisher with remote distributor topology (when the distribution component of replication resides on its own dedicated server) and Publisher connected with the Distributor over slow LAN or WAN, consider compressing the snapshot files.
  This is a new SQL Server 2000 replication feature, which allows you to decrease network traffic by compressing snapshot files.

*****

• Try to enable pull or anonymous subscriptions to increase the Distributor performance.
  This can increase the Distributor performance, because Distribution Agent processing will be moved from the Distributor to Subscribers.

*****

• Increase the MaxBcpThreads property of the Snapshot Agent.
  This property specifies the number of bulk copy operations that can be performed in parallel. By increasing this value, bulk copy operations can run faster, because they will be perform in parallel. To increase the MaxBcpThreads value in the Snapshot Agent profile, you can do the following:
  1. Run SQL Server Enterprise Manager.
  2. Expand a server group; then expand a server.
  3. Expand Replication Monitor; then expand the Agents and click the Snapshot Agents folder.
  4. Right-click appropriate publication and select Agent Profiles…
  5. Click the New Profile button to create the new profile with the appropriate MaxBcpThreads value.
  6. Choose the newly created profile.
  Note. Do not set this property too high, it can results in some performance degradation, because SQL Server will have to spend extra time managing the extra threads. Increase this property to 2 and continue monitoring.

*****

• Set the OutputVerboseLevel property of the Distribution Agent, the Log Reader Agent, the Merge Agent, and the Snapshot Agent to 0.
  This property specifies whether the output should be verbose. There are three available values:
  0 – only error messages are printed
  1 – all of the progress report messages are printed
  2 – all error messages and progress report messages are printed
  The default value is 2. You can increase performance by printed only error messages.
  To set the OutputVerboseLevel value to 0, you can do the following:
  1. Run SQL Server Enterprise Manager.
  2. Expand a server group; then expand a server.
  3. Expand Replication Monitor; then expand the Agents and click the appropriate agent folder.
  4. Right-click appropriate publication and select Agent Properties…
  5. On the Steps tab, double-click the Run agent step, and then add the -OutputVerboseLevel 0 in the Command text box.

*****

• You can minimize the performance affect of history logging by selecting 1 for the HistoryVerboseLevel property of the Distribution Agent, the Log Reader Agent, the Merge Agent, and the Snapshot Agent.
  This property specifies the amount of history logged during distribution operation (for a Distribution Agent), during a log reader operation (for a Log Reader Agent), during a merge operation (for a Merge Agent), or during a snapshot operation (for a Snapshot Agent).
  To set the HistoryVerboseLevel value to 1, you can do the following:
  1. Run SQL Server Enterprise Manager.
  2. Expand a server group; then expand a server.
  3. Expand Replication Monitor; then expand the Agents and click the appropriate agent folder.
  4. Right-click appropriate publication and select Agent Properties…
  5. On the Steps tab, double-click the Run agent step, and then add the -HistoryVerboseLevel 1 in the Command text box.

*****

• If you work with SQL Server 2000 consider using the -UseInprocLoader agent property.
  If this option was set, the in-process BULK INSERT command will be used when applying snapshot files to the Subscriber. You cannot use this property with character mode bcp, this property cannot be used by OLE DB or ODBC Subscribers.
  To set the UseInprocLoader property, you can do the following:
  1. Run SQL Server Enterprise Manager.
  2. Expand a server group; then expand a server.
  3. Expand Replication Monitor; then expand the Agents and click the Distribution Agents or Merge Agents folder.
  4. Right-click appropriate publication and select Agent Properties…
  5. On the Steps tab, double-click the subscription agent step, and then add the -UseInprocLoader property in the Command text box.

*****

• Increase the Log Reader Agent ReadBatchSize parameter.
  This parameter specifies the maximum number of transactions read out of the transaction log of the publishing database. The default value is 500. This option should be used when a large number of transactions are written to a publishing database, but only a small subset of those are marked for replication.

*****

• If you work with transactional replication, increase the Distribution Agent CommitBatchSize parameter.
  This parameter specifies the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default value is 100.

*****

• Create an index on each of the columns that is used in the filter’s WHERE clause.
  If you do not use indexes on columns used in filters, then SQL Server must perform a table scan.

*****

• If you work with merge replication, use static instead of dynamic filters.
  Because SQL Server requires more overhead to process the dynamic filters than static filters, for best performance you should use static filters, whenever possible.

Chapter: 16

Stored Procedure Optimization Tips

• Use stored procedures instead of heavy-duty queries.
  This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

*****

• Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
  This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

*****

• Call stored procedure using its fully qualified name.
  The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

*****

• Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
  The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

*****

• Don’t use the prefix “sp_” in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
  The prefix “sp_” is used in the system stored procedures names. Microsoft does not recommend using the prefix “sp_” in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with “sp_” in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix “sp_” in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

*****

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

*****

• Use sp_executesql stored procedure instead of temporary stored procedures.
  Microsoft recommends using the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

*****

• If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
  The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

*****

• Try to avoid using temporary tables inside your stored procedure.
  Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

*****

• Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
  Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

*****

• Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
  The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

*****

• Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
  To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the “Stored Procedures” category called “SP: Recompile”. You can also trace the event “SP: StmtStarting” to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

*****

Chapter: 17

SQL Server 2000 Optimization Tips

• For the very large databases, use distributed partitioned views to partition tables horizontally across multiple servers.
  This is a new SQL Server 2000 feature, which is available when using SQL Server 2000 Enterprise Edition only. Due to distributed partitioned views, SQL Server 2000 now on the first place in the TBC-C tests.

*****

• Use indexed views to increase the speed of your queries.
  The result set of the indexed view is persist in the database and indexed for fast access. Because indexed views depend on base tables, you should create indexed views with SCHEMABINDING option to prevent the table or column modification that would invalidate the view. Furthermore, using views instead of heavy-duty queries can reduce network traffic and can be used to facilitate permission management.

*****

• Consider using the WITH SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
  Using this option can reduce the time it takes to create an index, but increases the amount of disk space used to create an index.

*****

• You can specify whether the index keys are stored in ascending or descending order.
  For example, using the CREATE INDEX statement with the DESC option (descending order) can increase the speed of queries, which return rows in the descending order. By default, the ascending order is used.

*****

• Consider creating index on computed columns.
  In SQL Server 2000, you can create indexes on computed columns. To create index on computed column, the computed column must be deterministic, precise, and cannot has text, ntext, or image data type.

*****

• Consider setting the ‘text in row’ table option.
  The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the ‘text in row’ table option by using the sp_tableoption stored procedure.

*****

• Use table variables instead of temporary tables.
  The table variable is a new SQL Server 2000 feature. The table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.

*****

• Use cascading referential integrity constraints instead of triggers, whenever possible.
  For example, if you need to make cascading deletes or updates, you can specify ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

*****

• If you work with SQL Server 2000 Enterprise Edition use SAN (System Area Network) protocols instead of LAN (Local Area Network) or WAN (Wide Area Network).
  SANs are more reliable than LANs or WANs and support high levels of messaging traffic by lowering CPU loads and message latency.

*****

• Use user-defined functions to encapsulate code for reuse.
  The user-defined functions (UDFs) contain one or more Transact-SQL statements that can be used to encapsulate code for reuse. Using UDFs can reduce network traffic.

*****

• Set the ‘awe enabled’ server configuration option to 1 if you work with SQL Server 2000 Enterprise or Developer edition and have more than 4 gigabytes (GB) of physical memory.
  Because SQL Server 2000 can support up to a maximum of 64 gigabytes (GB) of physical memory, you can purchase the appropriate server box and get all advantages of it hardware platform.

*****

• Use the DBCC CHECKCONSTRAINTS statement if you need to check the integrity of a specified constraint or all constraints on a specified table.

*****

• Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.
  DBCC INDEXDEFRAG statement is an online operation. Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term and thus will not block running queries or updates. So, try to use the DBCC INDEXDEFRAG command instead of DBCC DBREINDEX, whenever possible.

*****

• You can use INSTEAD OF triggers to perform enhance integrity checks on the data values.
  The INSTEAD OF trigger is a new SQL Server 2000 feature. These triggers can be used to enforce business rules when constraints cannot be used.
  Note. Because triggers are more resource expensive, use constrains instead of triggers, whenever possible.

*****

Chapter: 18

SQL Server 6.5 Optimization Tips

• Set a reasonable size of your database.
  Because SQL Server 6.5 database cannot automatically grow, you should estimate how big the database will be. To estimate the reasonable database size, you should previous estimate the size of each table individually, and then add the values obtained.

*****

• Set a reasonable size for the transaction log.
  Because SQL Server 6.5 transaction log cannot automatically grow, you should estimate how big the transaction log will be. The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The less the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimation database size is equal to 10Mb, you can set the size of the transaction log to 4-5Mb, but if the estimation database size is over 500Mb, the 50Mb can be enough for the size of the transaction log.

*****

• You can place a database device on a raw partition.
  It can increase the speed of your queries and modify operations on 20 percents and more.

*****

• Move tempdb database on its own database device to simplify administering and monitoring.
  This is the description:
  1. Create new device with appropriate size (tempdb_dev for example).
  2. Uncheck “Default device” option for the master database device (this option is enable for the master database device by default).
  3. Set “Default device” option for the tempdb_dev device.
  4. From the Enterprise Manager (or sp_configure) set tempdb to be in RAM (set value to 1).
  5. Stop and restart MSSQLServer service.
  6. From the Enterprise Manager (or sp_configure) set tempdb to not be in RAM (set value to 0).
  7. Stop and restart MSSQLServer service.

*****

• You can put the tempdb database to be in RAM.
  If your queries contain subqueries, or GROUP BY, or ORDER BY clause, you can increase their performance by placing the tempdb database into RAM.

*****

• Create separate device for the transaction log.
  In SQL Server 6.5, any database (except the master database) can span multiple devices. If you want to ensure recoverability and reduce contention, you should place the transaction log on a separate device.

*****

• For heavily accessed table with text/image columns place this table on a one device and place text/image columns on a different device on separate physical disks.
  It can be useful to simplify administering and monitoring.

*****

• Place a table on one physical device and its nonclustered indexes on a different physical device.
  This will improve performance, because separate threads will be created to access the tables and indexes.

*****

• Split a large, heavily-used table across database devices on two separate disk controllers.
  This will improve performance, because when a table is accessed sequentially, a separate thread is created for each physical device on each disk array in order to read the table’s data in parallel.

*****

• If one of the join queries is used most often, place the tables used in this query on different devices on separate physical disks.

*****

• Create separate physical device and place some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables.
  You can group user objects with similar maintenance requirements into the same physical device. It can be useful to simplify administering and monitoring.

*****

• If you have several physical disk arrays, try to create as many physical devices as there are physical disk arrays so that you have one physical device per disk array.
  This will improve performance, because when a table is accessed sequentially, a separate thread is created for each physical device on each disk array in order to read the table’s data in parallel.

*****

• You can backup and restore a single table.
  It can be used to improve the speed of backup process and decrease the backup size. In SQL Server 7.0 and higher, the LOAD TABLE statement is no longer supported.

*****

• If you create default constraint for some fields, you should define these fields as NOT NULL.
  It can be used to store storage space.

*****

• Place all nullable fields to the end of the fields list (after fields with fixed length), so than more often this field will contain NULL value, the closer to the end of the record it should be placed.
  It can be used to store storage space.

*****

• If you use OR logical operation to find rows from a MS SQL 6.5 table, and there is index on the field for which values you use OR operation, then you can improve performance by writing two queries and combine their result sets by using the UNION ALL statement.
  If you use OR logical operation to find rows from a MS SQL 6.5 table, and there is index on the field for which values you use OR operation, then MS SQL 6.5 can use worktable with dynamic index on searchable field instead simple index search. So, if the table is very big, it can take a lot of time. You can increase the speed of this query by divide it into to select statement and union this statements with UNION ALL operator. For each query the appropriate index will be used, and this way can increase the speed of the new select statement in several times in comparison with the first one.

*****

Chapter: 19

Analysis Services Optimization Tips

• You can use the Usage-Based Optimization Wizard to optimize partition performance based on the history of queries previously sent to the cube.
  To run the Usage-Based Optimization Wizard run Analysis Manager, right-click the appropriate cube and choose ‘Usage-Based Optimization’, then complete the steps in the wizard.

*****

• Try to separate the OLTP server with the OLAP server if these servers very hard used by many users.
  In this case, placing the OLAP server on its own dedicated server can boost the overall performance of the queries.
  Note. When you use HOLAP or ROLAP cubes, placing cubes on a dedicated server can decrease performance if the data warehouse and Analysis Services will be connected via slow link.

*****

• Try to use the MOLAP or HOLAP cubes instead of the ROLAP cubes, if you have enough amount of disk space.
  The MOLAP and HOLAP cubes provide batter performance in comparison with ROLAP cubes, but can use more disk space to store the cube’s data.

*****

• Set the ‘read only’ data warehouse database option to true.
  This can reduce the time needed to process the cube and increase the speed of queries against the data.

*****

• If the data warehouse database is a read only database, you can create as many indexes as needed to for your Analysis Services queries.
  If all of your Analysis Services queries are covered by the indexes, only indexes will be used to get the queries data, and the overall performance of these queries will be greatly maximized.

*****

• Create all indexes in the data warehouse database with a ‘fill factor’ option of 100.
  Using a ‘fill factor’ option of 100 ensures that the index pages will be as full as possible. This can increase the speed of queries and reduce the amount of space used to store the index data.

*****

• Make a cube process during periods of low users activity.
  Because a cube process can take much time, this operation should be scheduled during CPU idle time and slow production periods.

*****

• Declare the foreign key relationships between the fact table and the dimension tables and create the indexes for every foreign key in the dimension tables.
  This can greatly maximize the overall performance of the queries against the indexed data.

*****

• Increase the level of aggregation for your cubes to boost the performance of the Analysis Services queries.
  The larger the level of cube’s aggregation will be, the faster the queries will be executed, but the more amount of disk space will be used and the more time it will take to process the cube. So, you should make some monitoring to get the best value for the level of aggregation for your cubes.

*****

• Consider placing cubes on their own server to distribute the load, if these cubes are large and very busy.
  Placing each cube on its own dedicated server can distribute the workload among these servers and boost the overall performance.

*****

• If your OLAP server does not have CPU bottleneck, try to increase the “Maximum number of threads” Analysis Services option.
  By default, the “Maximum number of threads” value is equal to two times the number of CPUs in the server computer. If you decide to increase this value, run System Monitor to check that there is no CPU bottleneck. To increase the “Maximum number of threads” option, run Analysis Manager, right-click the server name and choose ‘Properties’, then go to the ‘Environment’ tab.

*****

• If you have a dedicated server for Analysis Services, increase the “Minimum allocated memory” Analysis Services option.
  By default, the “Minimum allocated memory” value is equal to one-half of the server computer’s memory. Because allocation memory takes some time, if you will increase this value, you can avoid frequently automatic memory allocation. To increase the “Minimum allocated memory” option, run Analysis Manager, right-click the server name and choose ‘Properties’, then go to the ‘Environment’ tab.

*****

• If the OLAP server has a lot of RAM, increase the “Read-ahead buffer size” Analysis Services option.
  This option indicates the maximum amount of data placed into memory during each read of the database. The larger this value will be, the less disk read operation will be required to read the cube’s data. The default value is 4 Mb. Try to increase this value to 8 Mb and continue monitoring. To increase the “Read-ahead buffer size” option, run Analysis Manager, right-click the server name and choose ‘Properties’, then go to the ‘Processing’ tab.

*****

• If the OLAP server has a lot of RAM, increase the “Process buffer size” Analysis Services option.
  This option indicates how much data is processed in memory before an I/O is performed. The larger this value will be, the fewer the I/O operations will be required. The default value is 4 Mb. Try to increase this value to 8 Mb and continue monitoring. To increase the “Process buffer size” option, run Analysis Manager, right-click the server name and choose ‘Properties’, then go to the ‘Processing’ tab.

*****

• If you install Analysis Services for SQL Server 2000 Enterprise Edition, you can create multiple partitions in a cube.
  Using multiple partitions allows the source data and aggregate data of a cube to be distributed among multiple server computers. This can boost performance, because the workload will be distributed across multiple I/O devices.

*****

Source : www.mssqlcity.com

Microsoft Students to Business

Realize your dreams with the Student to Business Program.

Are you a graduating student looking for formative work experience in the IT field? Are you a Microsoft partner hoping to recruit students with the skills to meet the demands of today´s technology industry? Are you a professor at a University teaching Microsoft technology competencies? Welcome to the S2B program, a community initiative involving Microsoft, State Governments, Microsoft Partners and leading universities.

Our goal is to help students enhance their career opportunities by providing them with training opportunities, helping them obtain globally recognized certifications and providing them with the platform to showcase their ideas to the world

Great ideas need not be confined to the garage.

The mission of the Academic Projects Program is to inspire students in India to innovate, solve key computing challenges and make them more productive in their IT jobs from day one. The program will be a learning experience beyond compare.

Who can sign up?
If you are a student working on the Microsoft platform or using any of the Microsoft technologies sign up your project with us.
Why sign up?
Not only will we take you through an experience of how software is built in the industry but also provide you with the opportunity to showcase you at a State/National Level Project Fair.And did we mention that the top 25 teams will get a cash prize of INR 10000/- each.
Why register your academic project with Microsoft?

• Leverage Microsoft expertise to help you produce a world-class solution.
•  Gain access to the latest Microsoft technologies (students registering for the program will be able to download the latest software from the software section of the site).
• Receive Microsoft merit certificates (all teams that complete the project will get a certificate).
• Enhance your CV before you start work in the IT industry.
• Get an opportunity to showcase your project at State level and National level expo.
• The Top 25 teams will win a cash prize of INR 10000/- each.

How to get Started?
All you need to get started is a smart idea (we have a bunch of good project topics that you can find in the Projects Pool and 2-3 teammates and a project mentor. If you are already working on a project with an industry partner as part of your academic requirement, you can submit the same project to us. You do not need to work on a fresh project for this program. If you do not have a project mentor, you can still register your project as a hobbyist project. If you are already working on a project with an industry partner as part of your academic requirement, you can submit the same project to us. You do not need to work on a new project for this program. Your project should be targeted for the Microsoft platform. There is no requirement to use any specific programming languages or software. This means that you can submit a project written in Java or PHP as well, as long as the project runs on Microsoft Windows operating system. You can also look at interoperatibility between Microsoft and other platforms as project topics. Since different colleges across the country have different schedules we have two tracks that you can choose for doing the project. If your college project starts in the odd semester you can participate in the one year program. However, if you college project only starts in even semester or if you have missed submitting the project in the one year program you can participate in the six month program. Both programs provide the same benefits only the deadlines are different

Academic Year (2007 – 2008)

	One year program	Six months program
Registrations	17th August to 15th November	15th November to 31st January
Synopsis(idea) submission (2 pages)	15th November	1st February
Project Report (20 – 100 pages)	15th February	28th February
Mentor Feedback	28th February	28th February
Microsoft Review Results	15th March	15th March
Project Fair for top teams	April	April

Click here to register your project

Templates for submitting synopsis, detailed report and collecting mentor feedback will soon be available under the Downloads section of the website.

Get Project Topics Here.

General Questions

How is S2B different from MSAPP (Microsoft Academic Projects Program)?

MSAPP is just one part of Students2Business. S2B is about helping students be more successful and have a great head start in their careers. The S2B program will provide the following -

• Technical training- Agenda about student trainings being done in partnership with State governments, online webcasts, academic developer conference, other Microsoft events and links to resources that we think are the most important to students.
• Soft Skills trainings- We aspire to provide some good content to help students improve their communication, leadership and management skills. These as all of you know are things skills without which you cannot reach the top.
• Academic Projects- This is the equivalent of the MSAPP program. However this time we want you to submit the projects that you are working on to us instead of working on project topics given by us. The objective is to identify the great work that happens in our academic institutions from all of you and provide you with a platform to showcase this to the world. The best projects will be invited to showcase their projects at state and national level expos. Various deadlines for the academic projects are available on the website.
• Industry Projects- Look out at this space for internships and projects with Microsoft and our partners. Since this is the first time that we are doing this, we might not have a lot of opportunities to start with but we think that the number of projects that we are able to provide to you from here will grow exponentially once our partners see the smart students that they can reach through S2B.
• Software Access– Access to the latest tools and technologies from Microsoft that you need to design a world class project.

Registration and Team Formation

I cannot find my college in the list. Can I register for the program?

If you cannot find your college in our list, you have an option to request to add your college in our list and complete your registration process. We periodically review all the request that we have received and will add your college to the database if it meets our criteria. You will then have full access to all the features of the website. In case for some reason we do not accept your college, your registration will not be completed and we will send you an email informing you of the same. In this case you will need to re-register to the site.

Projects

What is the difference between Academic Projects and Industry Projects?

The objective of academic projects is to provide students with an opportunity to submit their projects that they are already working on to Microsoft and aims to take the students through the different stages involved in working on software projects. On the other hand the objective of industry projects is to provide students with an opportunity to work on real world projects and internships defined by Microsoft or its partners. We will be posting up industry projects/internships as and when they become available to us.

What kind of projects can I submit to Microsoft?

You can submit any project that you are already working on. There is no need to work on a new project just to take part in S2B program. The only requirement is that there should be one Microsoft technology being used. This means you can submit a Java project of a PHP website if they run on Windows. The aim of our academic project is to focus more on the idea and less on the tools that are being used for implementing it.

How do we sign up/register a academic project?

You require the follow the following steps to register a academic project –

• 1. On the S2B site, on the left hand side you fill find a “Signup” button to signup using your Live ID. If you do not have a Live ID you will have an option of creating one.
• 2. If you have not previously registered to the site then once you sign in you will have to register with us by providing additional information (college details, email address etc)
• 3. You will then have to confirm your registration by clicking on the link in the email that S2B will send to you email address.
• 4. Once you are registered, please click the Academic Projects and you will now see a sub-menu which will allow you to start a new project.
• 5. Click the “Start New Project” link to form your team and register your project with us. You can register multiple projects with us.

have got an invitation from a friend to join a project. What do I do next?

If a friend of yours has invited you to his/her project, you will get an email asking you to accept the invitation. Once you click this, you will be directed to the S2B website where you will need to sign in using your Live ID (in case you do not have a Live ID then you will require to create one as part of the registration process). In case you have not registered to the S2B portal before you will have to complete the registration process. Once you complete the registration process you will be added to the team and you will be able to see the project in your “My Projects” section under Academic Projects.

How many students are required to do project? Can I work alone on my project?

The minimum number of members required for a project is different for different types of projects. For a academic project there can be between 2-4 members in a team and hobbyist project can have between 1-4 members.

Do I require a mentor for registering my project?

There are two types of projects under “Academic Projects”

• Academic Project – Project done as part of course requirement
• Hobbyist Project – Project done outside the academic course

If you want to register an academic project then a mentor is required who can evaluate the project. This should not be a problem in any project that you do as part of academic requirements should have some faculty member who can be your mentor. For hobbyist project no mentors are required.

What is the difference between Academic Project and Hobbyist Project?

Academic Project is a project that you do as part of requirement of your course study. Typically this will be either a final year project or a pre-final year mini-project that is required by most universities. Any other project that you do to learn about new technologies or participate in competitions at college or inter-college level will fall under hobbyist category. Certificates will be provided for both category of projects if you complete all the stages of the project.

If I choose a hobbyist project, does my chance of coming at an expo decrease?

No, projects under both the category have an equal chance for being selected for the expo.

Will Microsoft provide me with a mentor/guide for the project? Will there be someone to whom I can ask my doubts?

No guide/mentor will be provided to anyone as part of the academic or industry projects. In case you have doubts you can post them at our forum http://www.microsoft.com/samvaad . We have a very strong and vibrant student community who can help you if you get stuck while working on the project.

Will I received a feedback on my project?

Based on the mentor evaluations we will be short listing projects for detailed review. Only these projects will be provided with feedback.

When will I get the certificates for this year (2007-08)?

Projects will be given out after the project expos are complete. We are working on a system such that you can download an print your own certificate so as to avoid delay or loss of certificates. Link to print your certificate will be provided to you towards the end of this academic year.

Do I get a participation certificate?

Certificates will be given to students who complete all the stages of the project. No credit will be given to any individual/team who does not complete the project and they will not get any participation certificates.

Does my chance for an expo decrease if I choose an existing project topic?

If you choose one of the existing definitions and submit the same to S2B the chance for being invited to expo is a little less since we want to showcase innovative ideas. If you do well and complete the project, you will get a certificate but unless the implementation is really different it will stand a less chance for being selected. The project topics are given to students so that they can see the kind of projects that we want to see and use them as an idea to build on. So if you want to compete for a place in the expo we suggest that you either work on a cool implementation and explore areas to extent the current project definition or submit a totally different idea.

Will I receive money/stipend for working on projects?

No stipend is provided for working on academic projects. Information about stipend/prize provide in case of industry project will be provide with each project.

Can I do more than one project at a time?

Yes you can do multiple projects (including academic, hobbyist and industry) at the same time. Each project that you do can have a different team and you will get a certificate for all the projects that you complete successfully.

My teammates have not received an invitation. What can I do?

You can send a reminder/invitation to you team members from the project home page. Once you choose the team members to whom you want to send a reminder press the save/submit button or else the invitations will not go. Also ask your friends to check their junk mail.

Can we change the team members, and project title after first initial registration?

Yes, you can change you team members till the time you lock your team. You need to lock the team to start submitting project documents to us.

Where do I submit the project documents?

You can upload the project documents from the project home page.

If we miss the deadline can we submit the documents to you over email?

In case you miss the deadlines for submission, you cannot send the documents to us over email. In this scenarios your project will be consider to be incomplete and you will not be get any credit for that project.

Do we have to use the project templates provided by you?

You have to use the project template provided by us. In case your college required the projects to be documents in another format then we request you to submit both the files in a zip folder. Having the project report in our template makes it easy for our experts to review the projects and ensure that all the information that we are looking for is covered by the students.

Will Microsoft provide us with a mentor for academic projects?

No Microsoft will not be providing you with a mentor for any projects.

Software

What software will I get if I register for Students2Business?

If you college has subscribed to MSDN Academic Alliance (www.msdnaa.net) and has not deployed ELMS then once you are verified we will provide you with the most commonly used software that comes as part of MSDN AA. This includes most of our Operating Systems, Servers and Developer Tools like Windows XP, Windows Vista, SQL Server 2005 and Visual Studio 2005. In case you college does not subscribe to MSDN AA then unfortunately we cannot provide you access to the whole set of our offering but we will provide you access to basic software that we think are the most required for working on projects.

What is a verified member?

Any student who registers with us is called a registered member. You can become a verified member by submitting to us a proof of your being a student with the college that you choose while signing up. Any student who has been successfully verified is known as a verified member. The main benefit of being a verified member of the website is access to download software from the S2B program which is not available to registered members.

How do I become a verified member?

To become a verified a member you need to send a scanned copy of your 10th standard mark sheet and your college id card/bonafide certificate to s2bindia@microsoft.com. Please mention the subject as “Verification request for Student2Business Portal”. If your college has an active MSDN AA subscription then once you are verified, you will have access to software available as part of MSDN AA.

To make a big idea take-off, you require to right tools

We at Microsoft know that how you execute on your idea and the tools that you use are as important as the idea itself. We have the tools and products that enable people to gain productivity, to enhance their productivity, their creativity and their imagination. On this website you will find all the tools that you require to execute seamless and transform your idea to reality.

In case your college has MSDN AA (www.msdnaa.net) and you are a verified member of the student2business.co.in website then you will be able to access the software available to you as part of the academic alliance program. In case your college is not a member of the academic alliance we will provide you links to freely available Microsoft tools which you can use while working on your projects.

For more information, contact the DPE Academic Team at the Microsoft office in Delhi, Mumbai or Bangalore

Delhi

Microsoft Corporation (I) Pvt. Ltd.
9th Floor, Tower A, DLF Cyber Greens
DLF Cyber Citi, Sector 25A
Gurgaon 122 002
Tel. +91-124-4158000 / 2567000
Fax. +91-124-4158888

Mumbai

Microsoft Corporation (I) Pvt. Ltd.
4th Floor, Windsor Building, Off CST Road
Kalina, Santacruz (East)
Mumbai 400 098
Tel. +91-22-67724000
Fax. +91-22-67724500

Bangalore

Microsoft Corporation (I) Pvt. Ltd.
Signature Building, Ground & 1st floor
Embassy Golf Links Business Park
Off. Intermediate Ring Road, Domlur
Bangalore 560 071
Tel. +91-80-22121212
Fax. +91-80-22121000

You can also interact with us through our forum located at http://forums.microsoft.com/samvaad or write to us at s2bindia@microsoft.com.We recommend that you first post your query or search for an answer at the forums and only if you cannot find that answer there that you mail us. Please use the option to call us as the last resort only.

A Las Vegas casino prevented from getting hacked. Find out How.

(Or A Developer’s Fantasy come true)

I don’t know if I will be able to disclose everything here but first
bookmark this.
What is so special about this code?
The Code Room has 3 videos. Watch them in your office or home in the still of the night.
Like you, I was an innocent geek developer, until I became part of a crack team. We were asked to team up with programmers & technologists, and challenged to do the impossible. Again and again.
First, we were locked up inside an intercity warehouse with only a desk,a laptop, and a few partially charged batteries…and asked to complete an ecommerce website.
Then, the crack team was asked to create a mobile app for allowing a mobile phone in a mall to talk to a particular store using Bluetooth. And… finally the most dangerous mission… detecting a security breach inside a casino and catching the hackers. And… finally the most dangerous mission… detecting a security breach inside a casino and catching the hackers.
In each case, we succeeded — against all odds. (I can hear you saying you would have done even better…but would you?
See the videos and decide for yourself).
But now I feel threatened by the hackers at the casinos. So I’ve decided not to take chances, but pass on the code to fellow developers like you.
Promise me that our to-do-or-die “spirit for challenges” will always fly high.
Pass this clue to every developer you know who dreams of being part of a crack team.
I will write to you… again.
Until then,
Agent 9
Member, Crack Team Code Room

---

©2007 Microsoft Corporation. All rights reserved. Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries. Review our Privacy Statement: If you prefer not to receive future promotional e-mails of this type from Microsoft, please send an email at erase@microsoft.com. We will promptly update your preferences; however, you may still receive previously initiated promotional communications from Microsoft.

.NET Interview Questions and Answers

• · Differences

1. Difference between Classic ASP and ASP.Net?
Answer:

§ ASP is Interpreted language based on scripting languages like Jscript or VBScript.

§ ASP has Mixed HTML and coding logic.

§ Limited development and debugging tools available.

§ Limited OOPS support.

§ Limited session and application state management.

§ Poor Error handling system.

§ No in-built support for XML.

§ No fully distributed data source support.
while

§ ASP.Net is supported by compiler and has compiled language support.

§ Separate code and design logic possible.

§ Variety of compilers and tools available including the Visual studio.Net.

§ Completely Object Oriented.

§ Complete session and application state management.

§ Full proof error handling possible.

§ Full XML Support for easy data exchange.

§ Fully distributed data source support.

2. What’s the difference between Response.Write() and Response.Output.Write()?
Answer: Response.Outout.Write allows us to write the formatted out put.

3. Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?
Answer:

§ A DataSet can represent an entire relational database in memory, complete with tables, relations, and views, A Recordset can not.

§ A DataSet is designed to work without any continuing connection to the original data source; Recordset maintains the contentious connection with the original data source.

§ There’s no concept of cursor types in a DataSet, They are bulk loaded, while Recordset work with cursors and they are loaded on demand.

§ DataSets have no current record pointer, you can use For Each loops to move through the data. Recordsets have pointers to move through them.

§ You can store many edits in a DataSet, and write them to the original data source in a single operation. Recordset can have a single edit at a time.

§ Dataset can fetch source data from many tables at a time, for Recordset you can achieve the same only using the SQL joins.

4. What is the difference between an abstract method & virtual method?
Answer: An Abstract method does not provide an implementation and forces overriding to the deriving class (unless the deriving class also an abstract class), where as the virtual method has an implementation and leaves an option to override the it in the deriving class. Thus Virtual method has an implementation & provides the derived class with the option of overriding it. Abstract method does not provide an implementation & forces the derived class to override the method.

5. What are the different types of assemblies? Explain.
Answer: Assemblies can be static or dynamic. Static assemblies can include .NET Framework types (interfaces and classes), as well as resources for the assembly (bitmaps, JPEG files, resource files, and so on). Static assemblies are stored on disk in portable executable (PE) files. You can also use the .NET Framework to create dynamic assemblies, which are run directly from memory and are not saved to disk before execution. You can save dynamic assemblies to disk after they have executed.

6. What are the difference between Structure and Class?
Answer:

§ Structures are value type and Classes are reference type.

§ Structures can not have contractors or destructors. Classes can have both contractors and destructors.

§ Structures do not support Inheritance, while Classes support Inheritance.

7. What are the difference between const and readonly?
Answer:

§ A const can not be static, while readonly can be static.

§ A const need to be declared and initialized at declaration only, while a readonly can be initialized at declaration or by the code in the constructor.

§ A const’s value is evaluated at design time, while a readonly’s value is evaluated at runtime.

8. Differences between dataset.clone and dataset.copy
Answer: dataset.clone copies just the structure of dataset (including all the datatables, schemas, relations and constraints.), However it doesn’t copy the data. On the other hand dataset.copy, copies both the dataset structure and the data.

9. Describe the difference between inline and code behind.
Answer: Inline code written along with the html and design blocks in an .aspx page. Code-behind is code written in a separate file (.cs or .vb ) and referenced by the .aspx page.

10. What is Difference between Namespace and Assembly?
Answer: Namespace is a logical design-time naming convenience, whereas an assembly establishes the name scope for types at run time.

11. What is the difference between early binding and late binding?
Answer: Calling a non-virtual method, decided at a compile time is known as early binding. Calling a virtual method (Pure Polymorphism), decided at a runtime is known as late binding.

12. What is the difference between User Control and Custom Control?
Answer: Custom Controls are compiled code (Dlls), easier to use, difficult to create, and can be placed in toolbox. Drag and Drop controls. Attributes can be set visually at design time. Can be used by Multiple Applications (If Shared Dlls), Even if Private can copy to bin directory of web application add reference and use. Normally designed to provide common functionality independent of consuming Application. User Controls are similar to those of ASP include files, easy to create, can not be placed in the toolbox and dragged – dropped from it. A User Control is shared among the single application files.

13. What is the difference between ASP Session State and ASP.Net Session State?
Answer: ASP session state relies on cookies, Serialize all requests from a client, does not survive process shutdown, Can not maintained across machines in a Web farm.

14. What is the difference bettween DataReader and DataSet?
Answer: DataReader represents only one database record at a time. You must call the Read() method to fetch each new record from the underlying database table into memory. Each time you call Read() again, the previously fetched record is lost. DataSet is on the other hand, enables you to represent the results of a database query in your server’s memory. Because a DataSet provides you with a memory-resident representation of data, you can work with the results of a database query as a whole. DataReader must remain connected to a database table. A DataReader is tied down to its underlying data source. The DataSet object is central to supporting disconnected, and distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It might be helpful to think of a DataReader as a forward-only recordset. A DataSet, on the other hand, is similar to a disconnected, client-side, static recordset. DataSets also require more overhead to create and populate than DataReaders.

•  Abbreviations

1. CLR = Common Language Runtime

2. CLS = Common Language Specifications

3. CTS = Common Type Specifications

4. GC = Garbage Collector.

5. WSDL = Web Services Description Language.

6. MSIL = Microsoft Intermediate Language.

7. CIL = Common Intermediate Language – MSIL.

8. JIT = Just In Time.

9. PE = Portable Executable – A file format.

10. COFF = Common Object File Format – A file format.

11. GAC = Global Assembly Cache.

12. DDL = Data Definition Language.

13. DML = Data Manipulation Language.

14. CAS = Code Access Security.

15. RCW = Runtime Callable Wrapper.

16. COM = Component Object Model.

17. CCW = COM Callable Wrapper.

18. DOM = Document Object Model.

19. DNA = Distributed interNet Applications Architecture.

20. GUID = Globally Unique Identifier.

21. MS-DTC = Microsoft Distributed Transaction Coordinator.

22. OLTP = Online Transaction Processing.

23. OLAP = Online Analytical Processing.

24. RAD = Rapid Application Development.

25. SMTP = Simple Mail Transfer Protocol.

26. SOAP = Simple Object Access Protocol.

27. TCP = Transport Control Protocol.

28. TLB = Type Library.

29. UDF = Uniform Data Format.

30. UDDI = Universal Description, Discovery and Integration.

• True/False

1. A Web service can only be written in .NET?
Answer: False (Java also)

2. To test a Web service you must create a windows application or Web application to consume this service?
Answer: False, the webservice comes with a test page and it provides HTTP-GET method to test it.

• Simple Questions

1. What is IL? (What is MSIL or CIL, What is JIT?)
MSIL is the CPU –independent instruction set into which .Net framework programs are compiled. It contains instructions for loading, storing initializing, and calling methods on objects.

2. What is CLR?
Answer: CLR also known as Common Language Run time provides a environment in which program are executed, it activate object, perform security check on them, lay them out in the memory, execute them and garbage collect them.

3. What is CTS?
Answer: The common type system is a rich type system, built into the common language runtime, which supports the types and operations found in most programming languages.

4. What is CLS?
Answer: CLS also known as Common Language Specification defines the rules which all language must support, in order to be a part of .Net framework. The Common Language Specification is a set of constructs and constraints that serves as a guide for library writers and compiler writers. It allows libraries to be fully usable from any language supporting the CLS, and for those languages to integrate with each other. The Common Language Specification is a subset of the common type system. The Common Language Specification is also important to application developers who are writing code that will be used by other developers.

5. Which class does the web page belong to in ASP.Net?
Answer: System.Web.UI.Page

6. Which class deals wit the user’s locale information?
Answer: System.Web.UI.Page.Culture

7. What data type does the RangeValidator control support?
Answer: Integer, Date and String.

8. What is the difference between Server.Transfer and Response.Redirect?
Answer: Server.Transfer, transfers the control of a web page, posting a form data, while Response.Redirect simply redirects a page to another page, it can not post a form data to another page. Server.Transfer is more efficient over the Response.Redirect, because Response.Redirect causes a round trip to server as the page is processed once again on the client and a request is made to server there after.

9. Can you give an example of what might be best suited to place in the Application_Start and Session_Start subroutines?
Answer: All the global declarations or the variables used commonly across the application can be deployed under Application_Start. All the user specific tasks or declarations can be dealt in the Session_Start subroutine.

10. What is viewState?
Answer: ViewState is a .Net mechanism to store the posted data among post backs. ViewState allows the state of objects to be stored in a hidden field on the page, saved on client side and transported back to server whenever required.

11. What is the lifespan for items stored in ViewState?
Answer: Items stored in a ViewState exist for the life of the current page, including the post backs on the same page.

12. Can we disable ViewState, If, yes how?
Answer: ViewState can be disabled by using “EnableViewState” property set to false.

13. What’s a bubbled event?
Answer: When a complex control like datalist or datagrid, which contains a child control, using an itemcommand can listen to the events raised by the child control in the main control. The process of listening to the child control in the main or parent control is called as event bubbling.

14. What is an assembly?
Answer: Assemblies are the building blocks of the .NET framework. They are the logical grouping of the functionality in a physical file.

15. What are different types of Assemblies?
Answer: Single file and multi file assembly. Assemblies can be static or dynamic. Private assemblies and shared assemblies

16. Which method do you invoke on the DataAdapter control to load your generated dataset with data?
Answer: DataAdapter’s fill () method is used to fill load the data in dataset.

17. Can you edit data in the Repeater control?
Answer: No, it just reads the information from its data source

18. Which template is to be provided in the Repeater control in order to display a data? Which template will display every other row in another color?
Answer: ItemTemplate, AlternatingItemTemplate

19. What are the most important property and most important method of a Repeater control?
Answer: The DataSource property and DataBind() method.

20. How many classes can a single .NET DLL contain?
Answer: It can contain many classes.

21. What are the advantages of an assembly?
Answer: Increased performance. Better code management and encapsulation. It also introduces the n-tier concepts and business logic.

22. What is the purpose of an Assembly?
Answer: An assembly controls many aspects of an application. The assembly handles versioning, type and class scope, security permissions, as well as other metadata including references to other assemblies and resources. The rules described in an assembly are enforced at runtime

23. What a static assembly consist of in general?
Answer: In general, a static assembly consist of the following four elements:

§ Assembly Manifest, which contains the assembly metadata.

§ Type Metadata.

§ MSIL code that implements the types.

§ A set of resources.

From above all only the manifest is required, however the other types and resources add the additional functionality to the assembly.

24. What is GAC or Global Assembly Cache?
Answer: Global Assembly Cache (GAC) is a common place to share the .NET assemblies across many applications. GAC caches all strong named assembly references within it. All System assemblies that come with the .NET framework reside in the GAC.

25. How to view an assembly?
Answer: We can use the tool “ildasm.exe” known as “Assembly Disassembler” to view the assembly.

26. What is Authentication and Authorization?
Answer: Authentication is the process of identifying users. Authentication is identifying/validating the user against the credentials (username and password) and Authorization performs after authentication. Authorization is the process of granting access to those users based on identity. Authorization allowing access of specific resource to user.

27. What are the types of Authentication? Describe.
Answer: There are 3 types of Authentication. Windows, Forms and Passport Authentication.

§ Windows authentication uses the security features integrated into the Windows NT and Windows XP operating systems to authenticate and authorize Web application users.

§ Forms authentication allows you to create your own list/database of users and validate the identity of those users when they visit your Web site.

§ Passport authentication uses the Microsoft centralized authentication provider to identify users. Passport provides a way to for users to use a single identity across multiple Web applications. To use Passport authentication in your Web application, you must install the Passport SDK.

28. What are the types of comment in C#?
Answer: There are 3 types of comments in C#.
Single line (//), Multi line (/* */)and Page Comments (///).

29. What is an ArrayList?
Answer: The ArrayList object is a collection of items containing a single data value.

30. What is a HashTable?
Answer: The Hashtable object contains items in key/value pairs. The keys are used as indexes, and very quick searches can be made for values by searching through their keys.

31. What is SortedList?
Answer: The SortedList object contains items in key/value pairs. A SortedList object automatically sorts items in alphabetic or numeric order.

32. What is a Literal Control?
Answer: The Literal control is used to display text on a page. The text is programmable. This control does not let you apply styles to its content!

33. What is CAS or Code Access Security?
Answer: CAS is the part of the .NET security model that determines whether or not a piece of code is allowed to run, and what resources it can use when it is running. For example, it is CAS that will prevent a .NET web applet from formatting a hard disk.

34. What is Side-by-Side Execution
Answer: The CLR allows any versions of the same-shared DLL (shared assembly) to execute at the same time, on the same system, and even in the same process. This concept is known as side-by-side execution.

35. What are the different types of Caching?
Answer: There are three types of Caching:

§ Output Caching: stores the responses from an asp.net page.

§ Fragment Caching: Only caches/stores the portion of page (User Control)

§ Data Caching: is Programmatic way to Cache objects for performance.

36. What are the different types of Validation Controls?
Answer: There are six types of validation controls available:

§ RequiredFieldValidator

§ RangeValidator

§ RegularExpressionValidator

§ CompareValidator

§ CustomValidator

§ ValidationSummary

37. How to Manage State in ASP.Net?
There are several ways to manage a state.

§ ViewState

§ QueryString

§ Cookies

§ Session

§ Application

38. What base class all Web Forms inherit from?
Answer: System.Web.UI.Page.

39. What method do you use to explicitly kill a user’s Session?
Answer: HttpContext.Current.Session.Abandon().

40. What are the layouts of ASP.NET Pages?
Answer: GridLayout and FlowLayout. GridLayout positions the form object on absolute x and y co-ordinates of the screen. FlowLayout positions the form objects relative to each other.

41. What is the Web User Control?
Answer: Combines existing Server and HTML controls by using VS.Net. to create functional units that encapsulate some aspects of UI. Resides in Content Files, which must be included in project in which the controls are used.

42. What is the Composite Custom Control?
Answer: combination of existing HTML and Server Controls.

Tricky Questions

1. If I’m developing an application that must accommodate multiple security levels though secure login and my ASP.NET web application is spanned across three web-servers (using round-robin load balancing) what would be the best approach to maintain login-in state for the users?
Answer: You can use the security state maintained using a database. (Use Authentication mode as database)

2. What’s the difference between Codebehind=”MyCode.aspx.cs” and src=”MyCode.aspx.cs” mce_src=”MyCode.aspx.cs”?
Answer: Visual Studio uses the Codebehind attribute to distinguish the page source or programming logic from the design. Also the src attribute will make the page compile on every request. That is the page will not be compiled in advance and stored in the bin as a dll instead it will be compiled at run time.

3. Suppose you want a certain ASP.NET function executed on MouseOver over a certain button or textbox. Where do you add an event handler?
Answer: Every web control has a ability to add the attributes on client side which will execute on client side and run a client side script like a javascript function.
btnSubmit.Attributes.Add(“onMouseOver”,”someClientCode();”) //For on mouse over of a button
TextBox1.Attributes.Add(“onFocus”,“ClearText();”) //For on focus of a text box

4. Explain what a diffgram is and a good use for one?
Answer: The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. For reading database data to an XML file to be sent to a Web Service.

5. What base class do all Web Forms inherit from?
Answer: The Page class.

6. Name two properties common in every validation control?
Answer: ControlToValidate and Text property.

7. What tags do you need to add within the Datagrid tags to bind columns manually?
Answer: Set AutoGenerateColumns Property to false on the Datagrid tag

8. What tag do you use to add a hyperlink column to the DataGrid?
Answer: asp:HyperLinkColumn

9. What is the transport protocol you use to call a Web service?
Answer: SOAP (Simple Object Access Protocol) is the preferred protocol.

10. Where on the Internet would you look for Web services?
Answer: http://www.uddi.org

11. Which control would you use if you needed to make sure the values in two different controls matched?
Answer: CompareValidator Control

12. What are the assembly entry points? An Assembly can have how many entry points at a time?
Answer: An assembly can have only one entry point from DllMain, WinMain or Main.

13. What does an assembly contain?
Answer:
• Manifest – The metadata describing the information below.
• Assembly name – Aids in versioning and visibility scope.
• Version information – The version number is integrated into the assembly’s identity.
• Types – Boundaries and scopes of methods, classes, properties, events, attributes.
• Locale – Information describing language/culture.
• Cryptographic Hash – Public key encoded hash acting as version/security check.
• Security Permissions – The permissions within the assembly determine the permissions that can be granted for all aspects of the assembly contents.

14. What does an assembly manifest contains?
Answer: It contains assembly name, version number (major.minor.build.revision) and culture Information. It also specifies the strong name information, which is useful for shared assemblies, and list of files, an assembly contains. It also provides information for type references in an assembly and other referenced assemblies.

15. Which tool is used to deploy an assembly, so as the assembly is available to all the application?
Answer: The GacUtil.exe is the tool, which allows us to add any assembly to the windows GAC.

16. How many catch statements can be associated with single try statement?
Answer: There can be a zero or more catch statement for each try statement. So it has not limit to the number of catch statement per try statement.

17. What is Console and System a Class/a Data Member/a routine/a namespace or a type?
Answer: Console is a class and System is namespace.

18. How many values can be returned from a method in C#?
Answer: Only one value can be returned from method, however you can use ref or out variable to change more than one value in called method.

19. How to declare a variable named this in C#, with data type string?
Answer: string @this;

20. Can we change the dimension of Array at run time like Array [3, 4]?
Answer: Yes, We can change only the first position of array dimension.

21. What keyword is used to accept a variable number of parameter in a method?
Answer: “params” keyword is used as to accept variable number of parameters.

22. What is a Namespace? What is the use of a namespace?
Answer: Namespaces are logical grouping of classes and other types in hierarchical structure. Namespaces are useful to avoid collision or ambiguity among the classes and type names. Another use of the namespace is to arrange a group of classes for a specific purpose.

23. What does a keyword using works for?
Answer: Using is just a convention or a short-cut method which allows us to access the classes in a namespace by referencing it once. So when ever we want use the classes or methods in them, we can avoid typing the entire namespace hierarchy. However it is not a good practice when there are likely chances to have name ambiguity or collision of class names.

24. What is Enums in C#?
Answer: Enums or Enumerators are used to declare a set of related constants (default start with 0); they are only available with primitive data types like int and short etc.

25. What is Delegates?
Answer: Delegates are a type-safe, object-oriented implementation of function pointers and are used in many situations where a component needs to call back to the component that is using it. Delegates are generally used as basis of events, which allow any delegate to easily be registered for as event.

26. Which are the namespaces that are imported automatically by Visual Studio in ASP.Net?
Answer: There are 7 namespaces which are imported automatically.

§ System

§ System.Collections

§ System.IO

§ System.web

§ System.web.UI

§ System.web.UI.HTMLControls

§ System.web.UI.WebControls

27. Which namespaces are used for data access?

§ System.Data

§ System.Data.OleDB

§ System.Data.SQLClient

28. What do you mean by boxing and un-boxing?
Answer: C# provides us with Value types and Reference Types. Value Types are stored on the stack and Reference types are stored on the heap. The conversion of value type to reference type is known as boxing and converting reference type back to the value type is known as un-boxing.
e.g.
int x = 10;
object o = x ; // Implicit boxing
object o = (object) x; // Explicit Boxing
x = o; // Implicit Un-Boxing
x = (int)o; // Explicit Un-Boxing

29. What are the different methods available under sqlcommand class to access the data?

§ ExecuteReader –Used where one or more records are returned – SELECT Query.

§ ExecuteNonQuery – Used where it affects a state of the table and no data is being queried – INSERT, UPDATE, DELETE, CREATE and SET queries.

§ ExecuteScalar – Used where it returns a single record(a single value normally) – SQL Functions like MIN(), NAX()

30. What are the different types of Session state management options available with ASP.NET?
Answer: ASP.NET provides In-Process & Out-of-Process state management,
Also known as “In-Proc” and “Out-Proc”. In-Proc stores the session in memory of the web server, that is on the same server the ASP.Net page is.
On the other hand Out-Proc session state management stores the session data on external data source, which can be a SQL Server or Server State Service. Out-of-Process state management requires the objects stored in session, must be serializable.

31. What is Remoting? Give Example.
Answer: Remoting is a means by which one operating system process, or program, can communicate with another process. The two processes can exist on the same computer or on two computers connected by a LAN or the Internet. Web services are probably the best known type of remoting, but they are not the only option.

32. What is Marshalling?
Answer: Marshaling is a process of making an object in one process (the server) available to another process (the client). There are two ways to achieve the marshalling.

i. Marshal by value: the server creates a copy of the object passes the copy to the client. When a client makes a call to an object marshaled by value (MBV), the server creates an exact copy and sends that copy to the client. The client can then use the object’s data and executable functionality directly within its own process or application domain without making additional calls to the server. Objects that the application accesses frequently are best remoted using MBV.

ii. Marshal by reference: the client creates a proxy for the object and then uses the proxy to access the object. When a client makes a call to an object marshaled by reference (MBR), the .NET framework creates a proxy in the client’s application domain and the client uses that proxy to access the original object on the server. Large objects that the application accesses relatively infrequently are good candidates for MBR.

33. What is a Static class? What are its features?
Answer: Static class is a class which can be used or accessed without creating an instance of the class.
Important Features:

i. Static class only contains static members and a private constructor.

ii. Static class cannot be instantiated.

iii. Static classes are sealed by default and therefore cannot be inherited.

34. What is sealed class? What are its features?
Answer: Sealed classes are those classes which can not be inherited and thus any sealed class member can not be derived in any other class. A sealed class cannot also be an abstract class.
In C# structs are implicitly sealed; therefore, they cannot be inherited.

35. Can we declare a method as sealed?
Answer: In C# a method can’t be declared as sealed. However when we override a method in a derived class, we can declare the overridden method as sealed. By declaring it as sealed, we can avoid further overriding of this method.
E.g.
using System;
class MyClass1
{
public int x;
public int y;
public virtual void Method() {
Console.WriteLine(“virtual method”); }
}
class MyClass : MyClass1
{
public override sealed void Method() {
Console.WriteLine(“sealed method”); }
}
class MainClass
{ public static void Main() {
MyClass1 mC = new MyClass();
mC.x = 110;
mC.y = 150;
Console.WriteLine(“x = {0}, y = {1}”, mC.x, mC.y);
mC.Method(); }
}

36. What is a DataSet ?
Answer: A DataSet is an in memory representation of data loaded from any data source.

37. What is a DataTable?
Answer: A DataTable is a class in .NET Framework and in simple words a DataTable object represents a table from a database.

38. If you want to view an Assembly how to you go about it? What is ILDASM?
Answer: You can use the MSIL Disassembler (Ildasm.exe) to view Microsoft intermediate language (MSIL) information in a file. If the file being examined is an assembly, this information can include the assembly’s attributes, as well as references to other modules and assemblies. This information can be helpful in determining whether a file is an assembly or part of an assembly, and whether the file has references to other modules or assemblies.

39. Where is version information stored of an assembly?
Answer: The version number is stored in the assembly manifest along with other identity information, including the assembly name and public key, as well as information on relationships and identities of other assemblies connected with the application.

40. Is versioning applicable to private assemblies?
Answer: No

41. How to create a shared assembly or add an assembly to GAC?
Answer: There are several ways an assembly can be added to GAC.

i. Use .msi installer designed to work with the global assembly cache.

ii. Use GACUtil.exe provided by the .NET Framework SDK.

iii. Use Windows Explorer to drag assemblies into the cache.

42. What is strong Name?
Answer:

43. What is reflection?
Answer: All .NET compilers produce metadata about the types defined in the modules they produce. This metadata is packaged along with the module (modules in turn are packaged together in assemblies), and can be accessed by a mechanism called reflection. The System.Reflection namespace contains classes that can be used to interrogate the types for a module/assembly. Reflection is ability to find information about types contained in an assembly at run time.

44. How can I produce an assembly?
Answer: Simply compile your class/module with the following command.
C#.Net – CSC /t:library yourclassname.cs
VB.Net – VBC /t:library yourmodulename.vb

45. What is an Application Domain? How they get created?
Answer: An Application Domain can be thought of as a lightweight processes controlled by the .Net runtime. Application Domains are usually created by hosts like Windows Shell, ASP.NET and IE. When you run a .NET application from the command-line, the host is the Shell. The Shell creates a new Application Domain for every application.

46. Do I have any control over the garbage collection algorithm?
Answer: Yes, we have a limited control over the GC algorithm, For example, the System.GC class exposes a Collect method – this forces the garbage collector to collect all unreferenced objects immediately.

47. What is a life span of a static variable?
Answer: A static variable’s life span is till the class is in memory.

48. What is a Page Life Cycle of an ASP.Net page?
Answer: There are various stages described as under.

§ Init

§ LoadViewState

§ LoadPostBackData

§ Load

§ RaisePostBackDataChangedEvent

§ RaisePostBackEvents

§ Pre-Render

§ SaveViewState

§ Render

§ Unload

49. Can the action attribute of a server-side <form>tag be set to a value and if not how can you possibly pass data from a form to a subsequent Page?
Answer: No, Assigning value will not work because will be overwritten at the time of rendering. We can assign value to it by register a startup script which will set the action value of form on client-side. On other hand we can use Server.Transfer or Response.Redirect.

50. How do you turn off cookies in one page of your asp.net application?
Answer: We may not use them at the max, However to allow the cookies or not, is client side functionality.

51. Which method do you use to redirect to user to another page without performing a round trip to Client?
Answer: Server.Transfer(“AnotherPage.aspx”).

52. How many namespaces are in .NET version 1.1?
Answer: 124.

53. Should Validation occur on Client/Server Side for Date Input?
Answer: Both. Client-side reduces extra round-trip. Server-Side ensures prevention against hacking and failure against automated requests.

54. What are the web form events?
Answer:

i. The first event that occurs in the life of a Web Form is the Init event. This is raised so that we can have initialization code for the page. The controls on the page are not yet created at this point. This event is raised once for each user of the page.

ii. The Load event follows the Init event. Subsequently, it is raised each time the page is requested. When this event is raised, all child controls of the Web Form are loaded and accessible. You should be able to retrieve data and populate the controls so that they can render themselves on the page when sent back to the client.

iii. The PreRender event happens just before the page is rendered and sent back to the client. We don’t often handle this event; however, it depends on the situation.

iv. The last event in the life of a Web Form is the Unload event. This happens when the page is unloaded from memory. Final cleanup should be done here.

• OOPS

1. What is a class?
Answer: Class is concrete representation of an entity. It represents a group of objects, which hold similar attributes and behavior. It provides Abstraction and Encapsulations.

2. What is an Object? What is Object Oriented Programming?
Answer: Object represents/resembles a Physical/real entity. An object is simply something you can give a name. Object Oriented Programming is a Style of programming that represents a program as a system of objects and enables code-reuse.

3. What is Encapsulation?
Encapsulation is binding of attributes and behaviors. Hiding the actual implementation and exposing the functionality of any object. Encapsulation is the first step towards OOPS, is the procedure of covering up of data and functions into a single unit (called class). Its main aim is to protect the data from out side world.

4. What is Abstraction?
Answer: Hiding the complexity. It is a process of defining communication interface for the functionality and hiding rest of the things.

5. What is Overloading?
Answer: Adding a new method with the same name in same/derived class but with different number/types of parameters. It implements Polymorphism.

6. What is Overriding?
Answer: A process of creating different implementation of a method having a same name as base class, in a derived class. It implements Inheritance.

7. What is Shadowing?
Answer: When the method is defined as Final/sealed in base class and not override able and we need to provide different implementation for the same. This process is known as shadowing, uses shadows/new keyword.

8. What is Inheritance?
Answer: It is a process of acquiring attributes and behaviors from another object (normally a class or interface).

9. What is an Abstract class?
Answer: An abstract class is a special kind of class that cannot be instantiated. It normally contains one or more abstract methods or abstract properties. It provides body to a class.

10. What is an Interface?
Answer: An interface has no implementation; it only has the signature or in other words, just the definition of the methods without the body.

11. What is Polymorphism?
Answer: Mean by more than one form. Ability to provide different implementation based on different number/type of parameters.

12. What is Pure-Polymorphism?
Answer: When a method is declared as abstract/virtual method in a base class and which is overridden in a base class. If we create a variable of a type of a base class and assign an object of a derived class to it, it will be decided at a run time, which implementation of a method is to be called.
This is known as Pure-Polymorphism or Late-Binding.

13. What is a Constructor?
Answer: A special Method Always called whenever an instance of the class is created.

14. What is a Destructor?
Answer: A special method called by GC. just before object is being reclaimed by GC.

15. How a base class method is hidden?
Answer: Hiding a base class method by declaring a method in derived class with keyword new. This will override the base class method and old method will be suppressed.

16. What Command is used to implement properties in C#?
Answer: get & set access modifiers are used to implement properties in c#.

17. What is method overloading?
Answer: Method overloading is having methods with same name but carrying different signature, this is useful when you want a method to behave differently depending upon a data passed to it.

18. Can constructors have parameters?

19. What are Static Assembly and Dynamic Assembly?
Answer: Static assemblies can include .NET Framework types (interfaces and classes) as well as resources for the assembly (bitmaps, JPEG files, resource files, and so forth). Static assemblies are stored on disk. Dynamic assemblies run directly from memory and are not saved to disk before execution.

20. Describe the functionality of an assembly.
Answer: It is the smallest unit that has version control. All types and resources in the same assembly are versioned as a unit and support side by side execution. Assemblies contain the metadata and other identities which allow the common language runtime to execute. They are the boundaries providing the type check. They the unit where security permissions are requested and granted.

21. What is serialization?
Answer: Serialization is the process of converting an object into a stream of bytes. De-serialization is the opposite process of creating an object from a stream of bytes. Serialization/De-serialization is mostly used to transport objects (e.g. during remoting), or to persist objects (e.g. to a file or database). There are two separate mechanisms provided by the .NET class library for serialization – XmlSerializer and SoapFormatter and BinaryFormatter. Microsoft uses XmlSerializer for Web Services, and uses SoapFormatter/BinaryFormatter for remoting.

•  SQL: Queries

1. Write a query to select the second highest salary from a table.
Answer: SELECT max(salary) AS salary2 FROM orders WHERE salary < (SELECT max(salary) AS salary1 FROM orders)

2. Write a query to select the 5^th highest salary from a table.
Answer: SELECT min(salary) AS high5 FROM employee WHERE salary IN(SELECT DISTINCT TOP 5 salary FROM orders ORDER BY salary DESC)

3. How to find duplicate records with the number they are duplicated?
Answer: SELECT Id, count (*) as number_records from table group by id having count (*) > 1.

• SQL: Questions

1. What is the difference between Delete and Truncate command in SQL?
Answer: Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate.

2. What is Magic Table in SQL?
Answer: The insert and Delete commands are known as magic tables in SQL.

3. Can Primary key is a Foreign Key on the same table?
Answer: Yes, Consider a category table in a e-commerce web site.
Category_Id, Category_Name, Parent_Category_ID. In this table all the parent categories are also categories. When we create a self join category id will be treated as foreign key to the same table.

4. What is Normalization? What are it’s rules?
Answer: Normalisation is the technique in the database design wher ethe idea is to reduce the redundancy of non key data items across the table.

§ Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.

§ Rule 2: A field should have the same meaning in each row of the table.

§ Rule 3: Each table should represent at most one entity.

§ Rule 4: Multiple instances of an entity should be represented by multiple rows in a table.

§ Rule 5: Joins should be based only on primary and foreign-key equality.

§ Rule 6: Make sure keys are linked correctly.

5. What are the advantages and disadvantages of Normalization?
Answer: There are several advantages of normalization as under:

§ Faster sorting and index creation.

§ A larger number of clustered indexes.

§ Narrower and more compact indexes.

§ Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements

§ Fewer null values and less opportunity for inconsistency, which increase database compactness.
Beside the above benefits there are few disadvantages as well:

§ Increased amount of Normalization increases the amount of complexity of joins between tables and that hinders the performance.

6. What are the conditions to achieve the normalization?
Answer: There are few conditions to achieve the normalization:

§ There should be a unique row identifier.

§ A table should store only data for a single type of entity. For e.g. details for book’s publisher and book’s author should be saved under different table.

§ A table should avoid columns which can be null-able.

§ A table should avoid duplication of data and columns.

7. What is a Stored Procedure? State its advantage.
Answer: A stored procedure is a set of pre-compiled SQL commands (query statements), which are stored in the server. It is faster then the loose SQL statements processed on client, as it is pre-compiled. It can execute more then one SQL commands once as they are bundled in a single entity. We can use control statements within the stored procedure, which will allow us to repeat some SQL command. It can send return values depending upon the result. Stored procedures are used to reduce network traffic.

8. What is a Trigger?
Answer: Triggers are a special type of stored procedure, which gets invoked upon a certain event. They can be performed upon an INSERT, UPDATE and DELETE.

9. What is a Clustered Index?
Answer: The data rows are stored in order based on the clustered index key. Data stored is in a sequence of the index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index. A clustered index usually provides faster access to data than does a non-clustered index

10. What is a Non-Clustered Index?
Answer: The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. In a non-clustered index, the physical order of the rows in the table is not same as the logical (indexed) order of the key values.

11. Describe the three levels of data abstraction?
The are three levels of abstraction:

§ Physical level: The lowest level of abstraction describes how data are stored.

§ Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.

§ View level: The highest level of abstraction describes only part of entire database.

12. What is DDL (Data Definition Language)?
Answer: A data base schema which is specified by a set of definitions expressed by a special language is called DDL. Data Definition Language (DDL) is used to define and manage all the objects in an SQL database.

13. What is DML?
Answer: It is a special language used to manipulate the Data. Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL.

14. What is a PRIMARY KEY?
Answer: The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.

15. What is a FOREIGN KEY?
Answer: A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

16. What is a UNIQUE KEY?
Answer: A UNIQUE KEY is one or more columns that must be unique for each row of the table.

17. What is the difference between UNIQUE and PRIMARY KEY?
Answer: The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <NULL> value is also restricted.

18. What is a VIEW?
Answer: A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.

19. What is a ROWID?
Answer: ROWID is the logical address of a row, and it is unique within the database.

20. What is INDEX?
Answer: INDEX is a general term for an SQL feature used primarily to speed up execution and impose UNIQUENESS upon data. You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table. The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify.

21. What is a cursor?
Answer: An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.

22. The Difference between ‘Count’ and ‘Count (*)’?
Answer: ‘Count’: Counts the number of non-null values. ‘Count (*)’: Counts the number of rows in the table, including null values and duplicates.

Microsoft Silverlight

Dear .NET peoples and Microsoft lovers,

 Microsoft has released new amazing technology is called as Microsoft Silverlight.

Silverlight is a cross-browser, cross-platform plug-in for delivering the next generation of Microsoft .NET–based media experiences and rich interactive applications for the Web.

But do u know How did the name came?

                                             please read follow these lines…

source : Communitycredit 

(David Silverlight(communitycredit) met with Bill gates in Bill home)

When I met with Bill Gates a few months back, I thought that he was just kidding around. I remember the day well. I visited Bill at his home after he called me over to race one of those 34 sports cars he has in his garage. His house is as amazing as you may have heard about, when you walk in, the music is customized to your tastes… Weird Al was playing (White and Nerdy) in the background,  Videos of all the Atari 2600 games playing were shown on the walls. Even my favorite artwork was hanging (Dogs playing Poker). It was really a site to see.
We kicked back, drank a few beers, played some video games and were just chatting about the world in general. Then, of course, the conversation drifted to Microsoft and the things I was working on. He said, for all of your contributions to the development community I thought that I should honor you in some way that is way above the normal “MVP” or even “ACE Award” status. Below is a snippet of the conversation as I remember it:

Me: My instant reaction was “No way!!!, Are you going to autograph my copy of Vista?”.
Bill: Keep dreaming.
Me: “Are you going to offer me a that new “Chief Development Community Officer” position at Microsoft that I keep asking you about?” I even tried something worthy of such a role, I gave my rendition of “Developers, Developers, Developers, Developers.”
Bill: “Chief Development Community Officer”? ROFLOL(he actually pronounced ROFLOL, which made it funnier)
Me: Are you going to remove support for C# and force everybody to code in VB.NET?
Bill: Yes, but that is another conversation. Besides, you did win the race.
Me: Okay, I give up.
Bill: Good, you would never guess it anyway.
Bill: Name your favorite new technology? Any technology you like! (Now he had me really curious)
Me: Well, that is an easy one….WPF/E
Bill: Well, here it is…. We are going to name it after you!!!
Me: Microsoft David?!?!?
Bill: Even better, we are going to name it “Microsoft Silverlight”!!! Besides, remember what happened with Microsoft Bob.
Me: Wow (getting emotional). I don’t know what to say. When are you going to announce it?
Bill: May of this year.
Me: May!!!!! Can’t you do it any earlier? April, Maybe?
Bill: I will speak with the WPFE Team…or should I say the Silverlight Team and get them to move it up.
Me: Thanks, Bill

So that is the story of how Microsoft Silverlight came to be. I would like to thank everybody at the Academy, My Mom, Dad, all of the folks at Microsoft, and last but not least, Bill.

Wow, what a great honor..Go ahead David! You rocked…Fingers crossed..

CommunityCredit

 

 

Dear  .NET people,

                          You have one Apple and i have one apple,if we can exchange each have only one apple.But you have one idea and i have one idea,If we can exchange each have two ideas

so please start your communit activities; get cool prizes

What is CommunityCredit?
Community Credit is a way of showing your contribution to the technology community.  Community Credit helps show off the enthusiasm and passion you have for the success of our industry.

more here…

 

 

 

Microsoft Techmela

Calling all developers, IT pros, architects, designers and technology enthusiasts…
Here’s all you wanted to know about TechMela 2007

TechMela – the defintive confluence of the latest technologies, technical expertise, and structured learning experiences.
Creating many pathways to knowledge exchange, TechMela comprises of technology presentations, scenario showcases, experience zones and discussion forums and parallel hands-on learning sessions, besides lots of networking opportunity, and of course – tonnes of fun!
This is one event you simply can’t afford to miss – no matter which technology stream you work in, or which level you work at.

3 Days  |   7 Tracks  |  150 Sessions  |  300 Labs… One place, at one time!  

Track 1 : Infrastructure Platform

Track 2 : Collaboration Platform

Track 3 : Application Platform

Track 4 : UX Platform

Track 5 : Web Platform

Track 6 : Mobility Platform

 Track 7 : Embedded Devices Platform

Venue details >>

City : Mumbai

Date :14-16th june 2007

Venue : Renaissance  Hotel and Convention Center

Address : #2 & 3B,Near Chinmayanand Ashram,Powai,Mumbai 400087(India)

Participation Fee
INR 9,000/- per participant
For group discounts, write to techmela@endtoend.in

For further details or queries, email techmela@endtoend.in

MUMBAI: RSVP: 022 – 6502.1700 | FAX: 022 – 4005 9922
BANGALORE: RSVP: 080 – 4116 1411 | FAX: 080 – 2521 3656
CHENNAI: RSVP: 044 – 2472 8113 | FAX: 044 – 2473 4247
DELHI: RSVP: 011 – 4655 2158 | FAX: 011 – 2622 3485

www.techmela.com

© 2007 Microsoft Corporation (India) Pvt. Ltd. | All Rights Reserved | Terms of Use | Privacy Statement |
If you are not able to view this page properly, please click here

Microsoft Office Online File Converters and Viewers

With the help of converters and viewers from Office Online, you can share your Microsoft Office files with people who have different versions of Office programs, or even with people who don’t have Office installed.

Converters allow you to open files created by people using different versions of your Office programs.

Viewers provide a means for people who don’t have Office programs to see your work. You can provide them with the appropriate viewer along with your Office files.

Access viewers

• Access 2002 viewers
• Access 2000 viewers
• Access 97 viewers

Excel converters and viewers

• Excel 2003 viewers
• Excel 2000 converters
• Excel 2000 viewers
• Excel 97 converters

Outlook converters and viewers

• Outlook 2000 converters
• Outlook 97/98 converters

PowerPoint converters and viewers

• PowerPoint 2007 viewers
• PowerPoint 2003 viewers
• PowerPoint 2002 viewers
• PowerPoint 2000 converters
• PowerPoint 2000 viewers
• PowerPoint 97 converters
• PowerPoint 97 viewers

Microsoft Project converters

• Microsoft Project 2000 converters

Visio converters and viewers

• Visio 2007 viewers
• Visio 2003 viewers
• Visio 2002 viewers
• Visio 2000 converters

Word converters and viewers

• Word 2003 viewers
• Word 2002 converters
• Word 2000 viewers
• Word 2000 converters
• Word 97 viewers
• Word 97 converters

Converters and viewers for Macintosh users

• Mactopia downloads