NULLIF (Transact-SQL)

How to use NULLIF (Transact-SQL)

NULLIF will Return a null value if the two specified expressions are equal. If you want to compare to fields and then this will work for you like :

Run these examples and you can see how to use it on your way.
Select NULLIF('aa','aa')
Select isnull( NULLIF('aa','aa'),'right')

MSDN is comparing NULLIF and CASE

To show the similarity between NULLIF and CASE, the following queries evaluate whether the values in the MakeFlag and FinishedGoodsFlag columns are the same. The first query uses NULLIF. The second query uses the CASE expression.


USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

So NULLIF and ISNULL functions can help you on different way.

Difference with (nolock) vs (nolock) as a SQL Table Hint

Difference with (nolock) vs (nolock) as a SQL Table Hint:

select sum(amount) from reward_expired (nolock)

difference was between using the table hint (nolock) and the table hint with (nolock).
Look at MSDN under SQL 2005,

“In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.”

Micorosft plan to remove the ability to use just (nolock), and to future proof all SQL, users should write all table hints using the with keyword.

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Table Hints (Transact-SQL)

Table Hints (Transact-SQL)

Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options.

WITH ( <table_hint> [ [ , ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW
  | FORCESEEK
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY
  | KEEPDEFAULTS
  | FASTFIRSTROW
  | HOLDLOCK
  | IGNORE_CONSTRAINTS
  | IGNORE_TRIGGERS
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

The following table hints are allowed with and without the WITH
keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD,
SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK,
ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints
are specified without the WITH keyword, the hints should be specified
alone.

For example:

Select * FROM table1 (TABLOCK)

Select * FROM table1 WITH (TABLOCK, INDEX(myindex))

Reducing SQL Server Deadlocks

Deadlocking and SQL best practices:

Deadlocking refers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first. This is different from being blocked, or having to wait for a resource. Using the locks above, if a transaction had a shared lock, then you issued a delete on those same records held by the first lock, you would not be deadlocked. Instead, you would be blocked. When the shared lock was released, your delete statement would complete. Blocking implies some performance hit, but the transaction will complete. It simply has to wait for something else to finish first. A deadlock on the other hand, means there is no way to finish. Your transaction is stuck in a loop with some other transaction. At this point, the database system will usually pick one transaction to be killed so the other can complete.

Here are some tips on how to avoid deadlocking on your SQL Server:

  • Esure the database design is properly normalized.
  • Have the application access server objects in the same order each time.
  • During transactions, don’t allow any user input. Collect it before the transaction begins.
  • Avoid cursors.
  • Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
  • Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
  • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
  • If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
  • Consider using bound connections
  • Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
  • Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
  • Use Column name in ORDER BY clause instead of numbers.
  • Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
  • Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. This extraction can be done in any of the following ways:

  • At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab.
  • Using the Extract SQL Server Events option on the File menu.
  • Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.

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.

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’

Basic Definitions

Definition of a Database

A database is a collection of related information, accessed and managed by its DBMS. After experimenting with hierarchical and networked DBMSs during the 1970’s, the IT industry became dominated by relational DBMSs (Or Object-Relational Database Management System) such as Informix database, Oracle, Sybase, and, later on, Microsoft SQL Server and the like.

In a strictly technical sense, for any database to be defined as a “Truly Relational Model Database Management System,” it should, ideally, adhere to the twelve rules defined by Edgar F. Codd, pioneer in the field of relational databases. To date, while many come close, it is admitted that nothing on the market adheres 100% to those rules, any more than they are 100% ANSI-SQL compliant.

While IBM and Oracle technically were the earliest on the RDBMS scene, many others have followed, and while it is unlikely that miniSQL still exist in their original form, Monty’s MySQL is still extant and thriving, along with the Ingres-descended PostgreSQL. Microsoft Access – the 1995+ versions, not the prior versions – were, despite various limitations, technically the closest thing to being ‘Truly Relational’ DBMS’s for the desktop PC, with Visual FoxPro, and many other desktop products marketed at that time far less compliant with Codd’s Rules.

A relational DBMS manages information about types of real-world things (entities) in the form of tables that represent the entities. A table is like a spreadsheet; each row represents a particular entity (instance—), and each column represents a type of information about the entity (domain). Sometimes entities are made up of smaller related entities, such as orders and order lines; and so one of the challenges of a multi-user DBMS is provide data about related entities from the standpoint of an instant of logical consistency.

Properly managed relational databases minimize the need for application programs to contain information about the physical storage of the data they access. To maximize the isolation of programs from data structures, relational DBMSs restrict data access to the messaging protocol SQL, a nonprocedural language that limits the programmer to specifying desired results. This message-based interface was a building block for the decentralization of computer hardware, because a program and data structure with such a minimal point of contact become feasible to reside on separate computers.

Recoverability

Recoverability means that, if a data entry error, program bug or hardware failure (Vista) occurs, the DBA can bring the database backward in time to its state at an instant of logical consistency before the damage was done. Recoverability activities include making database backups and storing them in ways that minimize the risk that they will be damaged or lost, such as placing multiple copies on removable media and storing them outside the affected area of an anticipated disaster. Recoverability is the DBA’s most important concern.

The backup of the database consists of data with timestamps combined with database logs to change the data to be consistent to a particular moment in time. It is possible to make a backup of the database containing only data without timestamps or logs, but the DBA must take the database offline to do such a backup.

The recovery tests of the database consist of restoring the data, then applying logs against that data to bring the database backup to consistency at a particular point in time up to the last transaction in the logs. Alternatively, an offline database backup can be restored simply by placing the data in-place on another copy of the database.

If a DBA (or any administrator) attempts to implement a recoverability plan without the recovery tests, there is no guarantee that the backups are at all valid. In practice, in all but the most mature RDBMS packages, backups rarely are valid without extensive testing to be sure that no bugs or human error have corrupted the backups.

Security
Security means that users’ ability to access and change data conforms to the policies of the business and the delegation decisions of its managers. Like other metadata, a relational DBMS manages security information in the form of tables. These tables are the “keys to the kingdom” and so it is important to protect them from intruders.

Performance

Performance means that the database does not cause unreasonable online response times, and it does not cause unattended programs to run for an unworkable period of time. In complex client/server and three-tier systems, the database is just one of many elements that determine the performance that online users and unattended programs experience. Performance is a major motivation for the DBA to become a generalist and coordinate with specialists in other parts of the system outside of traditional bureaucratic reporting lines.

Techniques for database performance tuning have changed as DBA’s have become more sophisticated in their understanding of what causes performance problems and their ability to diagnose the problem.

In the 1990s, DBAs often focused on the database as a whole, and looked at database-wide statistics for clues that might help them find out why the system was slow. Also, the actions DBAs took in their attempts to solve performance problems were often at the global, database level, such as changing the amount of computer memory available to the database, or changing the amount of memory available to any database program that needed to sort data.

DBA’s now understand that performance problems initially must be diagnosed, and this is best done by examining individual SQL statements, table process, and system architecture, not the database as a whole. Various tools, some included with the database and some available from third parties, provide a behind the scenes look at how the database is handling the SQL statements, shedding light on what’s taking so long.Having identified the problem, the individual SQL statement can be clarify

Development/Testing Support

Development and testing support is typically what the database administrator regards as his or her least important duty, while results-oriented managers consider it the DBA’s most important duty. Support activities include collecting sample production data for testing new and changed programs and loading it into test databases; consulting with programmers about performance tuning; and making table design changes to provide new kinds of storage for new program functions.

Indexing Service

What is Indexing Service?

Indexing Service is a base service for Microsoft® Windows® 2000 or later that extracts content from files and constructs an indexed catalog to facilitate efficient and rapid searching.

Indexing Service can extract both text and property information from files on the local host and on remote, networked hosts. The files can be simply members of a selected file system or part of a virtual Web hosted by, for example, Internet Information Services (IIS).

Indexing Service extracts the content by filtering—using filter components that understand a file’s format. The format could include multi-language features such as international languages and locales. A filter component implements the IFilter interface, which supplies methods to read a file to extract text and properties. Windows 2000 and Microsoft Windows XP supply filters for Microsoft Office files, Hypertext Markup Language (HTML) files, Multipurpose Internet Mail Extension (MIME) messages, and plain-text files.

Indexing Service then merges the extracted information into catalogs of indexes for efficient searches. Indexing is the overall process of filtering, creating index entries, and merging them into catalogs.

The final step in the indexing process is creation of a catalog that contains a master index (and any temporary word lists and shadow indexes) storing words and their locations within a set of indexed documents. Subsequently, searching, or querying, the catalogs for particular word combinations uses the master index as well as word lists and shadow indexes to execute queries quickly and efficiently.

Windows 2000 and Windows XP include basic facilities for querying the Indexing Service catalog and for managing the state and properties of Indexing Service itself. These facilities include:

  • When Indexing Service is running, Start/Search/For Files or Folders uses the Indexing Service catalog.
  • The Indexing Service snap-in for the Microsoft Management Console (MMC) provides the means to start, stop, and pause Indexing Service, and to administer many of its properties, such as those defining its catalogs.
  • The Platform Software Development Kit (SDK) provides additional versatile and flexible facilities for programmatically interacting with Indexing Service. These facilities include:
  • Admin and Query Helper objects and ActiveX® Data Object (ADO) methods for use with Microsoft Visual Basic®, Microsoft Visual Basic Scripting Edition (VBScript), Microsoft Visual J++® and Microsoft JScript® development software.
  • ISAPI Extensions for use in .idq, .ida, and .htx files.
  • OLE DB Helper functions for use with Microsoft Visual C++® development system.
  • OLE DB Provider for Indexing Service interfaces for use with Visual C++.
  • IFilter interface for use with Visual C++

Source: MSDN

SQL Performance Tuning using Indexes

SQL Performance Tuning using Indexes

This article looks at general guidelines to creating effective indexes using short keys, distinct keys, covering indexes and clustered indexes.

Effective indexes are one of the best ways to improve performance in a database application. Without an index, the SQL Server engine is like a reader trying to find a word in a book by examining each page. By using the index in the back of a book, a reader can complete the task in a much shorter time. In database terms, a table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

One of the most important jobs for the database is finding the best index to use when generating an execution plan. Most major databases ship with tools to show you execution plans for a query and help in optimizing and tuning indexes. This article outlines several good rules of thumb to apply when creating and modifying indexes for your database. First, let’s cover the scenarios where indexes help performance, and when indexes can hurt performance.

Useful Index Queries

Just like the reader searching for a word in a book, an index helps when you are looking for a specific record or set of records with a WHERE clause. This includes queries looking for a range of values, queries designed to match a specific value, and queries performing a join on two tables. For example, both of the queries against the Northwind database below will benefit from an index on the UnitPrice column.

DELETE FROM Products WHERE UnitPrice = 1

SELECT * FROM PRODUCTS
WHERE UnitPrice BETWEEN 14 AND 16

Since index entries are stored in sorted order, indexes also help when processing ORDER BY clauses. Without an index the database has to load the records and sort them during execution. An index on UnitPrice will allow the database to process the following query by simply scanning the index and fetching rows as they are referenced. To order the records in descending order, the database can simply scan the index in reverse.

SELECT * FROM Products ORDER BY UnitPrice ASC

Grouping records with a GROUP BY clause will often require sorting, so a UnitPrice index will also help the following query to count the number of products at each price.

SELECT Count(*), UnitPrice FROM Products
GROUP BY UnitPrice

By retrieving the records in sorted order through the UnitPrice index, the database sees matching prices appear in consecutive index entries, and can easily keep a count of products at each price. Indexes are also useful for maintaining unique values in a column, since the database can easily search the index to see if an incoming value already exists. Primary keys are always indexed for this reason.

Index Drawbacks

Indexes are a performance drag when the time comes to modify records. Any time a query modifies the data in a table the indexes on the data must change also. Achieving the right number of indexes will require testing and monitoring of your database to see where the best balance lies. Static systems, where databases are used heavily for reporting, can afford more indexes to support the read only queries. A database with a heavy number of transactions to modify data will need fewer indexes to allow for higher throughput. Indexes also use disk space. The exact size will depends on the number of records in the table as well as the number and size of the columns in the index. Generally this is not a major concern as disk space is easy to trade for better performance.

Building The Best Index

There are a number of guidelines to building the most effective indexes for your application. From the columns you select to the data values inside them, consider the following points when selecting the indexes for your tables.

Short Keys

Having short index is beneficial for two reasons. First, database work is inherently disk intensive. Larger index keys will cause the database to perform more disk reads, which limits throughput. Secondly, since index entries are often involved in comparisons, smaller entries are easier to compare. A single integer column makes the absolute best index key because an integer is small and easy for the database to compare. Character strings, on the other hand, require a character by character comparison and attention to collation settings.

Distinct Keys

The most effective indexes are the indexes with a small percentage of duplicated values. As an analogy, think of a phone book for a town where almost everyone has the last name of Smith. A phone book in this town is not very useful if sorted in order of last name, because you can only discount a small number of records when you are looking for a Smith.

An index with a high percentage of unique values is a selective index. Obviously, a unique index is highly selective since there are no duplicate entries. Many databases will track statistics about each index so they know how selective each index is. The database uses these statistics when generating an execution plan for a query.


Covering Queries

Indexes generally contain only the data values for the columns they index and a pointer back to the row with the rest of the data. This is similar to the index in a book: the index contains only the key word and then a page reference you can turn to for the rest of the information. Generally the database will have to follow pointers from an index back to a row to gather all the information required for a query. However, if the index contains all of he columns needed for a query, the database can save a disk read by not returning to the table for more information.

Take the index on UnitPrice we discussed earlier. The database could use just the index entries to satisfy the following query.

SELECT Count(*), UnitPrice FROM Products
GROUP BY UnitPrice

We call these types of queries covered queries, because all of the columns requested in the output are covered by a single index. For your most crucial queries, you might consider creating a covering index to give the query the best performance possible. Such an index would probably be a composite index (using more than one column), which appears to go against our first guideline of keeping index entries as short as possible. Obviously this is another tradeoff you can only evaluate with performance testing and monitoring.

Clustered Indexes

Many databases have one special index per table where all of the data from a row exists in the index. SQL Server calls this index a clustered index. Instead of an index at the back of a book, a clustered index is closer in similarity to a phone book because each index entry contains all the information you need, there are no references to follow to pick up additional data values.

As a general rule of thumb, every non-trivial table should have a clustered index. If you only create one index for a table, make the index a clustered index. In SQL Server, creating a primary key will automatically create a clustered index (if none exists) using the primary key column as the index key. Clustered indexes are the most effective indexes (when used, they always cover a query), and in many databases systems will help the database efficiently manage the space required to store the table.

When choosing the column or columns for a clustered index, be careful to choose a column with static data. If you modify a record and change the value of a column in a clustered index, the database might need to move the index entry (to keep the entries in sorted order). Remember, index entries for a clustered index contain all of the column values, so moving an entry is comparable to executing a DELETE statement followed by an INSERT, which can obviously cause performance problems if done often. For this reason, clustered indexes are often found on primary or foreign key columns. Key values will rarely, if ever, change.

Conclusion

Determining the correct indexes to use in a database requires careful analysis, benchmarking, and testing. The rules of thumb presented in this article are general guidelines. After applying these principals you need to retest your specific application in your specific environment of hardware, memory, and concurrent activity. See my previous article: SQL Server Indexes, for a more thorough introduction.

CREATE PARTITION FUNCTION

CREATE PARTITION FUNCTION (Transact-SQL)

Creates a function in the current database that maps the rows of a table or index into partitions based on the values of a specified column. Using CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index.

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
[ ; ]

partition_function_name
Is the name of the partition function. Partition function names must be unique within the database and comply with the rules for identifiers.
input_parameter_type
Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.The actual column, known as a partitioning column, is specified in the CREATE TABLE or CREATE INDEX statement.
boundary_value
Specifies the boundary values for each partition of a partitioned table or index that uses partition_function_name. If boundary_value is empty, the partition function maps the whole table or index using partition_function_name into a single partition. Only one partitioning column, specified in a CREATE TABLE or CREATE INDEX statement, can be used.boundary_value is a constant expression that can reference variables. This includes user-defined type variables, or functions and user-defined functions. It cannot reference Transact-SQL expressions. boundary_value must either match or be implicitly convertible to the data type supplied in input_parameter_type, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type.

If boundary_value consists of datetime or smalldatetime literals, these literals are evaluated assuming that us_english is the session language. This behavior is deprecated. To make sure the partition function definition behaves as expected for all session languages, we recommend that you use constants that are interpreted the same way for all language settings, such as the yyyymmdd format; or explicitly convert literals to a specific style. For more information, see Writing International Transact-SQL Statements. To determine the language session of your server, run SELECT @@LANGUAGE.

…n
Specifies the number of values supplied by boundary_value, not to exceed 999. The number of partitions created is equal to n + 1. The values do not have to be listed in order. If the values are not in order, the Database Engine sorts them, creates the function, and returns a warning that the values are not provided in order. The Database Engine returns an error if n includes any duplicate values.
LEFT | RIGHT
Specifies to which side of each boundary value interval, left or right, the boundary_value [ ,…n ] belongs, when interval values are sorted by the Database Engine in ascending order from left to right. If not specified, LEFT is the default. For more information, see Examples.
The scope of a partition function is limited to the database that it is created in. Within the database, partition functions reside in a separate namespace from the other functions.Any rows whose partitioning column has null values are placed in the left-most partition, unless NULL is specified as a boundary value and RIGHT is indicated. In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition.
Any one of the following permissions can be used to execute CREATE PARTITION FUNCTION:

  • ALTER ANY DATASPACE permission. This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.
  • CONTROL or ALTER permission on the database in which the partition function is being created.
  • CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition function is being created.

A. Creating a RANGE LEFT partition function on an int column

The following partition function will partition a table or index into four partitions.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

Partition 1 2 3 4
Values col1 <= 1 col1 > 1 AND col1 <= 100 col1 > 100 AND col1 <= 1000 col1 > 1000

B. Creating a RANGE RIGHT partition function on an int column

The following partition function uses the same values for boundary_value [ ,…n ] as the previous example, except it specifies RANGE RIGHT.

CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE RIGHT FOR VALUES (1, 100, 1000);

The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

Partition 1 2 3 4
Values col1 < 1 col1 >= 1 AND col1 < 100 col1 >= 100 AND col1 < 1000 col1 >= 1000

C. Creating a RANGE RIGHT partition function on a datetime column

The following partition function partitions a table or index into 12 partitions, one for each month of a year’s worth of values in a datetime column.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401',
               '20030501', '20030601', '20030701', '20030801',
               '20030901', '20031001', '20031101', '20031201');

The following table shows how a table or index that uses this partition function on partitioning column datecol would be partitioned.

Partition 1 2 11 12
Values datecol < February 1, 2003 datecol >= February 1, 2003 AND datecol < March 1, 2003 datecol >= November 1, 2003 AND col1 < December 1, 2003 col1 >= December 1, 2003

D. Creating a partition function on a char column

The following partition function partitions a table or index into four partitions.

CREATE PARTITION FUNCTION myRangePF3 (char(20))
AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

Partition 1 2 3 4
Values col1 < EX col1 >= EX AND col1 < RXE col1 >= RXE AND col1 < XR col1 >= XR

Partitioning the Data in a Table

Is it ever good database design practice (for speed sake, etc.) to essentially make copies of tables to hold a certain group of data?

For example, I have come across a database table that stores information for a housing subdivision; ie. lot number, lot size, lot price, etc. And the database to which this table belongs stores this data for many subdivisions. However, instead of having one table that stores the subdivision information for ALL subdivisions (and having some ID that represents the specific subdivision), this database has one table for each subdivision. For example, ‘Clair Ridge Estates Subdivision Info’ and another table ‘Possum Bend Subdivision Info’, etc, with each table having the exact same fields. And, if they needed another subdivision, they would make yet another copy and give it a unique name.”

Yes, there are times this is a good idea. I like this question because it reminded me of one of my favorite features of SQL Server – partitioned views.

What you’re referring to is an optimization method called horizontal partitioning. That is, a table is split up into multiple smaller tables containing the same number of columns, but fewer rows. Compare this to vertical partitioning, in which the table is split into multiple smaller tables with the same number of rows, but fewer columns.

And yes, this design decision is often made to improve performance. Horizontally partitioning a table gives us some advantages:

  • Each partition table will have fewer rows; if you have to (heaven forbid) table-scan the data, it will take less time.
  • Indexes on each partition table will be smaller (=faster seeks) than a corresponding index on the unpartitioned table.
  • If you need to, you can put each partition table on a different filegroup and partition the data among multiple disks/RAID volumes/drive controllers.
  • If you’re trying to whomp Oracle’s TPC-C benchmark, then you may want to consider partitioning the data among multiple federated servers in SQL Server 2000. (Although for storing information about housing subdivisions, this may be a bit over the top.)
  • If you create a partitioned view on the partitioned tables, you can treat the view like it is the whole table, and the QP (query processor) will only touch the tables it needs to fulfill the query. You get the benefits of horizontal partitioning without the query headache.

So, as you can see, horizontal partitioning is all about splitting up the workload – spreading out data access among tables, indexes, disks, and servers.

Why would you want to do this? Well, maybe you have a big table – hundreds of millions of rows, for instance. Or maybe not so many rows, but large rows. Or maybe you have a table in a data warehouse that contains frequently and infrequently accessed rows. All of these situations are candidates for partitioning.

Now, there are two big downsides to all of this:

  • Unless you use a partitioned view to access the data, you’ll have to build logic into your application to access the correct table, and that has a high suck factor. Please, please, use the partitioned view instead.
  • You actually have to partition the data. And maintain it. And create the partitioned view. And balance the amount of data in each partitioned table, if needed. In other words, the dreaded “administrative overhead”.

Okay, since I’m touting the partitioned view, let me quickly explain how to create one. I’ll use the information from this question as an example.First, the tables:


CREATE TABLE Subdiv_ClaireRidgeEstates (SubdivID int, LotID int /*, etc.*/)
CREATE TABLE Subdiv_TibetianYakFarms (SubdivID int, LotID int /*, etc.*/)

You may notice that I included the Subdivision ID in each table. This is important; for the partitioned view to work most effectively, the QP must be able to know that each partition table will only contain a certain type of data. To do this, you need to build CHECK constraints on each table on the ID that you’re partitioning on. Since you’re partitioning the data by subdivision, you will build CHECK constraints on SubdivID:


ALTER TABLE Subdiv_ClaireRidgeEstates ADD CONSTRAINT CK_CRE_SubdivID CHECK (SubdivID = 42)
ALTER TABLE Subdiv_TibetianYakFarms ADD CONSTRAINT CK_TYF_SubdivID CHECK (SubdivID = 9538)

You could just as easily partition by using a surrogate key field and assigning a range of key values to each partition table. Or by partitioning on a date and using a range of dates for each partition value. Regardless, you still need those CHECK constraints in place on each table.

After actually creating the partition tables, distributing the data, and building the CHECK constraints, building the view is pretty easy. You just SELECT * from each partition table and use UNION ALL to combine the results of the query:


CREATE VIEW Subdivision
AS
SELECT * FROM Subdiv_ClaireRidgeEstates
UNION ALL
SELECT * FROM Subdiv_TibetianYakFarms

Now, if you’ve been following along with the example, try inserting some sample rows into each table:


INSERT Subdiv_ClaireRidgeEstates VALUES (42,9999)
INSERT Subdiv_TibetianYakFarms VALUES (9538,1234)

Now, turn on the “Show Execution Plan” option in query analyzer, and run the following queries:

SELECT * FROM Subdivision WHERE SubdivID = 42
SELECT * FROM Subdivision WHERE SubdivID = 9538
SELECT * FROM Subdivision


You’ll notice that for the first two queries, SQL Server only pulls information from the required partition table. Only in the last query, where we don’t filter by SubdivID, does the QP pull data from each partition table.

In SQL Server 7.0, you unfortunately cannot update data in a partitioned view. However, this IS possible in SQL Server 2000. Check out SQL Server Books Online (especially if you’re going to use distributed partitioned views) for the do’s and don’ts of partitioning data.

Backup/Restore Optimization Tips

Backup/Restore Optimization Tips:

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

  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. Try to separate your database to different files and filegroups 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.
  9. 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; PhysicalDisk: % 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.
  10. 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.
  11. 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.
  12. 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.

Analyze and Fix Index Fragmentation in SQL Server 2008

Analyze and Fix Index Fragmentation in SQL Server 2008

It is very common that over time SQL Server tables and indexes tend to become fragmented. The fragmentation generally happens when data within the underlying tables on which an index exists is modified. The data modification basically can be an insert, update or a delete operation. The indexes over time become ineffective because they get fragmented. In this article you will see an example of how an index gets fragmented and the steps which database administrator needs to take to fix index fragmentations.

Example to Analyze and Fix Index Fragmentation in SQL Server 2008

Follow the below mentioned steps to see how an index fragmentation occurs on a table which has indexes defined on it. And finally you will see the steps which you need to take to fix index fragmentation issues.

Create AnalyzeFragmentation Database

First let us create a new database named AnalyzeFragmentation for this example. Database can be created by executing the below mentioned TSQL Query.

Use master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AnalyzeFragmentation')
DROP DATABASE [AnalyzeFragmentation]
GO
CREATE DATABASE AnalyzeFragmentation
GO

Create FindAndFixFragmentation Table in AnalyzeFragmentation Database

The next step will be to create a new table named FindAndFixFragmentation within the AnalyzeFragmentation database.


USE AnalyzeFragmentation
GO
IF OBJECT_ID (N'dbo.FindAndFixFragmentation', N'U') IS NOT NULL
DROP TABLE dbo.FindAndFixFragmentation;
GO

— Create FindAndFixFragmentation Table–
CREATE TABLE [dbo].[FindAndFixFragmentation]
(

[AddressID] [int] NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[RowGUID] [UNIQUEIDENTIFIER] NOT NULL

)
ON [PRIMARY]

GO

Populate the FindAndFixFragmentation Table using the below TSQL code

The next step will be to populate the FindAndFixFragmentation table which you have created earlier by executing the below mentioned TSQL code. For this example we will be using the data which is available in Person.Address table available in AdventureWorks database.


USE AnalyzeFragmentation
GO

— Populate FindAndFixFragmentation table with data from AdventureWorks.Person.Address —

INSERT INTO FindAndFixFragmentation
SELECT
AddressID,
AddressLine1,
City,
PostalCode,
ModifiedDate,
RowGUID
FROM AdventureWorks.Person.Address
GO

Create a Clustered Index on FindAndFixFragmentation Table using the below TSQL code

The next step will be to create a clustered index named CL_FindAndFixFragmentation_Index on FindAndFixFragmentation table using the below mentioned TSQL code.

-- Drop the index if it is already existing--
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FindAndFixFragmentation]') AND name = N'CL_FindAndFixFragmentation_Index')

DROP INDEX [CL_FindAndFixFragmentation_Index] ON [dbo].[FindAndFixFragmentation]
GO
— Create Clustered Index on FindAndFixFragmentation(RowGUID) —
CREATE CLUSTERED INDEX [CL_FindAndFixFragmentation_Index] ON [dbo].[FindAndFixFragmentation]
(
[RowGUID] ASC
)
WITH (FILLFACTOR = 90) ON [PRIMARY]
GO

You can see that we are creating a clustered index on FindAndFixFragmentation table with a Fill Factor 90. The fill factor option is basically provided for fine tuning index data storage and to improve performance. Whenever an index is created or it is rebuilt, the fill factor value basically determines the percentage of space on each leaf level page that needs to be filled with data. Based on the fill factor value a percentage of free space is allocated on every single page. By default the fill factor value is 0 or 100 which means there will be no free space allocated on each leaf level page. The value for fill factor is defined in percentages and this can be any value in between 1 to 100. In this example the fill factor value provide is 90 which mean on every single page there will be a 10 percentage of free space left to accommodate future growth.

Query to Find Existing Fragmentation on FindAndFixFragmentation Table

Next step will be to execute the below mentioned TSQL query to know the existing fragmentation on FindAndFixFragmentation table. The important values which need to be noted by the database administrators are AvgPageFragmentation and PageCounts. The value for AvgPageFragmentation is 0.341296928327645, which means there is a very little fragmentation existing on the table at this point of time. However the value for PageCounts is 293, which mean the data is stored in that many data pages on SQL Server. This query will be executing many a times in this article.


-- Find index fragmentation --
SELECT
DB_NAME(DATABASE_ID) AS [DatabaseName],
OBJECT_NAME(OBJECT_ID) AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
GO

Perform Update Operation on FindAndFixFragmentation Table

Next step will be to perform updates on FindAndFixFragmentation table by executing the below mentioned TSQL code. This query will modify all the data for RowGUID column on which we have created clustered index with fill factor as 90.

— Update all the rows within to FindAndFixFragmentation table create index fragmentation —

USE AnalyzeFragmentation
GO
UPDATE FindAndFixFragmentation
SET RowGUID =NEWID()
GO

Execute the query to find existing fragmentation on FindAndFixFragmentation table as shown in the below snippet.

Now you can see that the value for AvgPageFragmentation has changed from 0.341296928327645 to 99.0049751243781, which means index is completely fragmentation. At the same time the value for PageCounts has changed from 293 to 603, which mean more number of data pages are required to store the content. Now the question which comes to your mind is how this can be fixed.

There are two methods to fix index fragmentation issues in SQL Server 2005 and higher versions. The two methods are Reorganize or Rebuild Index. The Reorganize Index is an online operation, however Rebuild Index is not an online operation until you have specified the option ONLINE=ON while performing the Rebuild. Next step will be to perform first REORGANIZE Index option and then finally perform we will perform the REBUILD and see which options is the best.

Perform Reorgainize Index Operation on Clustered Index of FindAndFixFragmentation Table

First let us perform REORGANIZE Index operation on the clustered index, and then execute the query as shown in the snippet to find the fragmentation on FindAndFixFragmentation table.


-- Reorganize [CL_FindAndFixFragmentation_Index] index on FindAndFixFragmentation --

ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation

REORGANIZE;

GO

Once we have performed the REORGANIZE Index operation you can see that the value for AvgPageFragmentation has changed from 99.0049751243781 to 5.70469798657718, which means index fragmentation is much better that how it was earlier. And at the same time the value for PageCounts has also come down from 603 to 298, this is considerable improvement.

Perform Rebuild Index Operation on Clustered Index of FindAndFixFragmentation Table

Now let us perform REBUILD Index operation on the clustered index, when you are using the Rebuild index operation it basically drops and recreates the index. The important thing what we need to see is does this results in reducing the index fragmentation further down from 5.70469798657718. Once you have performed the Rebuild operation execute the query as shown in the snippet to check the fragmentation on FindAndFixFragmentation table.


-- Rebuild [CL_FindAndFixFragmentation_Index] index on FindAndFixFragmentation --

ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation
REBUILD WITH (FILLFACTOR = 90, ONLINE=ON)
GO

You can see that the value for AvgPageFragmentation is back to 0.341296928327645, which means the fragmentation is same as it was when we began this exercise. And at the same time the value for PageCounts is back to 293. This proves that using REBUILD Index operation is better than REORGANIZE Index operation.

Reorganize Index

Reorganize Index uses minimal system resources and it is performed online. The biggest advantage is it does not require locks for long time therefore it does not block updates or other user queries. If the index fragmentation ranges in between 5% to 30% then it is better to perform Reorganize Index.

Rebuild Index

Rebuild Index basically drops and recreates the index; this is by far the best approach. If the index fragmentation is greater than 30% then the best strategy will be to use Rebuild Index instead of Reorganize Index.

Conclusion

Database Administrators should always make sure that fragmentation of indexes is handled on time. If the indexes are fragmented then the query response will not only be very slow; the data storage will also require more disk space. In this article you have seen an example where the clustered index gets fragmented over time and the steps which you need to perform to resolve index fragmentation issues.

How to Cluster SQL Server 2005

How to Cluster SQL Server 2005

Believe it or not, the procedure to install a SQL Server 2005 instance onto a cluster is one of the easiest parts of getting your SQL Server 2005 cluster up and running. The SQL Server 2005 setup program is used for the install and does the hard work for you. All you have to do is make a few (but critically important) decisions, and then sit back and watch the installation take place. In fact, the setup program even goes to the trouble to verify that your nodes are all properly configured, and if not, will suggest how to fix most problems before the installation begins.

When the installation process does begin, the setup program recognizes all the nodes, and once you give it the go ahead to install on each one, it does, all automatically. SQL Server 2005 binaries are installed on the local drive of each node, and the system databases are stored on the shared array you designate.

In the next section are the step-by-steps instructions for installing a SQL Server 2005 instance in a cluster. The assumption for this example is that you will be installing this instance in a 2-node active/passive cluster. Even if you will be installing a 2-node active/active or a multi-node cluster, the steps in this section are virtually the same. The only real difference is that you will have to run SQL Server 2005 setup for every instance you want to install on the cluster, and you will have to specify a different logical drive on the shared array.

Clustering SQL Server

To begin installing your SQL Server 2005 cluster, you will need the installation CD or DVD. You can either install it directly from the media, or copy the install files from the media to the current active node of the cluster, and run the setup program from there.

To begin the installation, run Setup.exe. After an introductory screen, you will get the first install dialog box as shown in the figure below.

The Installing Prerequisites dialog box lists the prerequisites that need to be installed before installation of SQL Server 2005 can begin. The number of components may vary from the above figure, depending on what you have already installed on your nodes. What is interesting to note here is that these prerequisite components will only be installed immediately on the active node. They will be installed on the passive node later during the installation process. This is done automatically and you don’t have to worry about it.

Click Install to install these components. When completed, you will get a dialog box telling you that they were installed successfully, and then you can the click Next to proceed. On occasion, I have seen these components fail to install correctly. If this happens, you will have to troubleshoot the installation. Generally speaking, try rebooting both nodes of the cluster and try installing them again. This often fixes whatever caused the first setup try to fail.

Once the prerequisite components have been successfully installed, the SQL Server Installation Wizard launches

SQL Tuning or SQL Optimization

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

Instead of:

SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb