SQL running serial generating tips

SQL running serial generating tips:

Traditionally developers and Database administrators used temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example:

select ROW_NUMBER() OVER(ORDER BY CLM1),* from Table1

Remark:

The ORDER BY clause determines the sequence in which the rows are assigned their unique
ROW_NUMBER within a specified partition.

Enable remote connection SQL Server

Error Description ::

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
  2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
  3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.Note Click OK when you receive the following message:
    Changes to Connection Settings will not take effect until you restart the Database Engine service.
  4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
  2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
  3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
  4. Click Start, and then click OK.

Create exceptions in Windows Firewall:

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
  3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

  1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
  2. In the Add a Program window, click Browse.
  3. Click the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe executable program, click Open, and then click OK.Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
  4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

  1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
  2. In the Add a Program window, click Browse.
  3. Click the C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe executable program, click Open, and then click OK.

Case sensitive comparisons in SQL

Case sensitive comparisons in SQL Server

Some time we need to compare some data in binary see the examples:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE convert(varbinary(255), PASS) = convert(varbinary(255), 'RAGHU')

Normal comparison:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE convert(varbinary(255), PASS) = convert(varbinary(255), 'Raghu') AND PASS= 'TEST'

Uuse the BINARY_CHECKSUM function:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE BINARY_CHECKSUM(answer) = BINARY_CHECKSUM('TEST')

Use SQL Server case insensitive collation:

SELECT *  FROM TABLE1 WITH (NOLOCK) WHERE PASSWORD COLLATE SQL_Latin1_General_CP1_CS_AS = 'RAGHU' COLLATE SQL_Latin1_General_CP1_CS_AS

Sub Query Update

Sub Query Update:
if you want to run update query in join or by comparing other table then here is the example.

UPDATE Table1
SET Price = Price * 2
WHERE ProductID IN
    (SELECT ID
     FROM Table2
     WHERE ID= 51);
GO

Here is an equivalent UPDATE statement using a join:

USE AdventureWorks;
GO
UPDATE Table1
SET Price = Price * 2
FROM Table1 AS t1
INNER JOIN Table2 AS t2
    ON t1.ProductID = t2.ProductID AND t2.ID= 51;
GO

BEGIN TRANSACTION (Transact-SQL)

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

Syntax:

BEGIN { TRAN | TRANSACTION }
    [ { transaction_name | @tran_name_variable }
      [ WITH MARK [ 'description' ] ]
    ]
[ ; ]

Example:

You can catch your error in T-SQL as per my experience

-- Exec spa_begintrnExample 'a'

Create Proc spa_begintrnExample
@flag char(1)

AS
if @flag='a'
begin

BEGIN TRANSACTION

Update table set clm1='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

Update table2 set clm12='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

Delete from table3 where clm1='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

Delete from table4 set clm1='test'
IF (@@ERROR <> 0) GOTO QuitWithRollback 

COMMIT TRANSACTION

GOTO  EndSave

QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: 

end

Remarks:

The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

  • An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.
  • A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

SQL RAND Function

SQL RAND Function

The SQL RAND() function is used to generate some random numbers at run time. Here is the syntax:

Syntax

RAND ( [ seed ] )

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND()

Examples:

The following example produces four different random numbers that are generated by the RAND function.

DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND() Random_Number
SET @counter = @counter + 1
END
GO

The following example returns random float numbers based on three different seed values.

CREATE TABLE Random (Seed1 float, Seed5 float, Seed10 float)
INSERT INTO Random Values (RAND(1), RAND(5), RAND(10))
SELECT * FROM Random

The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function. If no seed is provided, the system generates its own variable seed numbers. If you call RAND with a seed value, you must use variable seed values to generate random numbers. If you call RAND multiple times with the same seed value, it returns the same generated value. The following script returns the same value for the calls to RAND because they all use the same seed value:

SELECT RAND(159784)
SELECT RAND(159784)
SELECT RAND(159784)

A common way to generate random numbers from RAND is to include something relatively variable as the seed value, such as adding several parts of a GETDATE:

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

When you use an algorithm based on GETDATE to generate seed values, RAND can still generate duplicate values if the calls to RAND are made within the interval of the smallest datepart used in the algorithm. This is especially likely when the calls to RAND are included in a single batch. Multiple calls to RAND in a single batch can be executed within the same millisecond. This is the smallest increment of DATEPART. In this case, incorporate a value based on something other than time to generate the seed values.

How to use Bulk Insert

Introduction Bulk Insert:

The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view. For example, suppose your company stores its million-row product list on a mainframe system, but the company’s e-commerce system uses SQL Server 2005 to populate Web pages. You must update the SQL Server product table nightly with the master product list from the mainframe. To update the table, you save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly into the SQL Server table.

To ensure high-speed data copying, transformations cannot be performed on the data while it is moving from the source file to the table or view.

You can configure the Bulk Insert task in the following ways:

  • Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted.
  • Specify the File or Flat File connection manager to access the source file and provide information about the source data file, such as the code page and file type.
  • Define the format used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file.
  • Specify actions to perform on the destination table or view when inserting the data. The options include whether to check constraints, enable identity inserts, keep nulls, fire triggers, or lock the table.
  • Provide information about the batch of data to insert, such as the batch size, the first and last row from the file to insert, the number of insert errors that can occur before the task stops inserting rows, and the names of the columns that will be sorted.

If the Bulk Insert task uses a Flat File connection manager to access the source file, the task does not use the format specified in the Flat File connection manager. Instead, the Bulk Insert task uses either the format specified in a format file, or the values of the RowDelimiter and ColumnDelimiter properties of the task.

BULK INSERT (Transact-SQL)

BULK INSERT
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE  =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FIRSTROW  =first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
   [ [ , ] ERRORFILE = 'file_name' ]
    )]

Examples

A. Using pipes to import data from a file:

This example imports order detail information into the AdventureWorks.Sales.SalesOrderDetail table from the specified data file by using a pipe (|) as the field terminator and |n as the row terminator.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail     
FROM 'f:orderslineitem.txt'     
WITH         
(           
FIELDTERMINATOR =' |',           
ROWTERMINATOR =' |n'        
)

B. Using the FIRE_TRIGGERS argument

This example specifies the FIRE_TRIGGERS argument.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail     
FROM 'f:orderslineitem.txt'     
WITH       
(          
FIELDTERMINATOR =' |',          
ROWTERMINATOR = ':n',          
FIRE_TRIGGERS        )

C. Using line feed as a row terminator

This example loads a file that uses the line feed as a row terminator such as a UNIX output:

DECLARE @bulk_cmd varchar(1000)  
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail  
FROM ''<drive>:<path><filename>''   
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'  
EXEC(@bulk_cmd)

What is BCP Utility in SQL server?

Introduction BCP Utility:

The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

Syntax:

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

Examples:

Copying Table Rows into a Data File (with a Trusted Connection)

The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At a command prompt, enter the following command:

bcp AdventureWorks.Sales.Currency out Currency.dat -T -c


Copying Table Rows into a Data File (with Mixed-Mode Authentication)

The following example illustrates the out option on the AdventureWorks.Sales.Currency table. This example creates a data file named Currency.dat and copies the table data into it using character format.

he example assumes that you are using mixed-mode authentication, you must use the -U switch to specify your login ID. Also, unless you are connecting to the default instance of SQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.

bcp AdventureWorks.Sales.Currency out 
Currency.dat -c -U<login_id> -S<server_nameinstance_name>

The system will prompt you for your password.

Copying Data from a File to a Table

The following example illustrates the in option by using the file created in the preceding example (Currency.dat). First, however, this example creates an empty copy of the AdventureWorks Sales.Currency table, Sales.Currency2, into which the data is copied. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

To create the empty table, in Query Editor, enter the following command:

USE AdventureWorks;
GO
SELECT * INTO AdventureWorks.Sales.Currency2
FROM AdventureWorks.Sales.Currency WHERE 1=2

To bulk copy the character data into the new table–that is, to import the data–enter the following command at a command prompt:

bcp AdventureWorks.Sales.Currency2 
in Currency.dat -T -c

To verify that the command succeeded, display the contents of the table in Query Editor, and enter

USE AdventureWorks;
GO
SELECT * FROM Sales.Currency2


Copying a Specific Column into a Data File

To copy a specific column, you can use the queryout option. The following example copies only the Name column of the Sales.Currency table into a data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" 
queryout Currency.Name.dat -T -c


Copying Data From a Query to a Data File

To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the names from the AdventureWorks.Person.Contact table, ordered by last name then first name, into the Contacts.txt data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp "SELECT FirstName, LastName FROM 
AdventureWorks.Person.Contact 
ORDER BY LastName, Firstname" 
queryout Contacts.txt -c -T


Creating a Non-XML Format File

a non-XML format file, Currency.fmt, for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency 
format nul -T -c  -f Currency.fmt

Creating an XML Format File

The following example creates an XML format file named Currency.xml for the Sales.Currency table in the AdventureWorks database. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency 
format nul -T -c -x -f Currency.xml

Using a Format File to Bulk Import with bcp

To use a previously created format file when importing data into an instance of SQL Server, use the -f switch with the in option. For example, the following command bulk copies the contents of a data file, Currency.dat, into a copy of the Sales.Currency table (Sales.Currency2) by using the previously created format file (Currency.xml). The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.

At the Windows command prompt, enter:

bcp AdventureWorks.Sales.Currency2 
in Currency.dat -T -f Currency.xml

Tips And Tricks For Advanced MS SQL Server Developers

Tips And Tricks For Advanced MS SQL Server Developers:

  1. Use “TRUNCATE TABLE” statement instead of “DELETE” clause if you want to delete all rows from a table. It is much faster then “DELETE” statement without any conditions. “TRUNCATE TABLE” frees all the space occupied by that table’s data and indexes, without logging the individual row deletes.
  2. Always use owner prefix in T-SQL  queries:

    SELECT mycolumn FROM dbo.mytable

    In this case query optimizer does not have to decide whether to retrieve from dbo.mytable or other owner’s table and avoids recompilation.  Recompilation results in no performance advantages of stored procedures usage.

  3. Don’t use “sp_“ as your prefix for stored procedures – it is a reserved prefix in MS SQL server! MS SQL server searches for a stored procedure with “sp_” prefix in the system procedures first, and only after that looks for them in client procedures.
  4. If you are unable to install MSDE at home because of unknown error – check that you did not stop “Server” system service on you PC…
  5. There are thousands of examples, when developers use “SELECT COUNT(*)” statement. But there is another, much faster way to accomplish the task:
    SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Table_Name') AND indid < 2
  6. Include “SET NOCOUNT ON” statement in your stored procedures to greatly reduce network traffic.
  7. Use the “BETWEEN” clause instead of “IN” for greater performance:
    SELECT productId FROM customer
    WHERE productId BETWEEN 1 AND 9

    Instead of:

    SELECT productId
    FROM customer
    WHERE productId IN (1, 2, 3, 4,5,6,7,8,9)
  8. Use Table variables – new feature of MS SQL 2000 instead of temp tables. Table variables are created in memory, not written to the tempdb database, and therefore they are much faster. However, be careful to use them only with not very huge amount of data that you want to allocate in temp tables, otherwise you can easily get the server down.

Database Settings Optimization Tips

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

SQL Server Settings Optimization Tips

SQL Server Settings Optimization Tips:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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’