SQL Server Data Modelling Tools

Problem


Rarely do we (Developers and DBAs) have sufficient time for our projects and every day tasks. So when it comes to building a new system, inevitability what happens is time is limited and individual tables end up being built instead of a comprehensive data model. As the system grows, we hope that the front end code is correct and keeps the data related, but when you have a data problem it may take hours to check and validate that the data is correct. So how can we break this cycle and migrate from building table by table to a comprehensive data model?

Solution 
Working with your team and letting them know the time needed is the first step. The second step is exploring data modeling tools to streamline the process to be able to work on projects and daily tasks. With SQL Server 2005, Management Studio includes the Visual Data Modeling tools. This tool set enables you to graphically build your data model and translates the model into the appropriate T-SQL commands.

What are the capabilities of SQL Server 2005's Visual Data Modeling tools? 
The chart below outlines the primary activities that can be performed in SQL Server 2005's Visual Data Modeling tools:

Sql Server optimization and Tips

csharpwithdj.blogspot.com

SQL Server Optimization

 

Summary: This article describes different ways of improving the performance of SQL Server queries, with a focus on index optimization.

Contents

Introduction Data and Index Organization Optimization Rules of Thumb

Introduction

The purpose of this document is to describe different ways of improving the performance of SQL Server queries. Most of this document will describe index optimization, with occasional references to particular code snippets. In other words, this document will describe how to achieve the best performance, given the tables and queries to run against.

Database design issues and entity-relationship modeling techniques are out of scope of this document, even though flawed design can severely impact the performance in many ways.

Data and Index Organization

Index and data organization will be discussed together for two reasons. First, there is rarely a table without an index. Even a small lookup table has a primary key that allows other table to refer to it, and a primary key is always based on the index. Second, data organization depends on the indexes that exist for the table; a table with a clustered index is organized differently than a table without one.

Non-Clustered Indexes/Heap-Based Tables

A heap table, by definition, is a table that doesn't have any clustered indexes. Different pages of the heap-based table occupy different non-contiguous areas on a disk, and they are not linked together in any way. Each non-clustered index defined on the table will have a corresponding entry in a sysindexes table with an indid between 2 and 254, pointing to the first IAM (Index Allocation Map) page. IAM pages represent the linked list of all pages used by the database object (table or index) and used by SQL Server for allocating and deallocating storage space. It is important to remember that IAM pages are not used to search through data or index pages, but only to allocate and deallocate them.

An observant reader might ask at this point: What if there is no index defined for a table at all? Well, the address of the first IAM page of the heap table itself is stored in the sysindexes table with indid = 0 (see Figure 2). In that respect, the abbreviation IAM is a little misleading; it would be better called SAM (Storage Allocation Map or Space Allocation Map).

All the indexes (clustered and non-clustered) are B-Trees, where B stands for "balanced" (not "binary," as is sometimes thought), which basically means that it will take about the same number of jumps to get to the leaf node for any value of the key. The number of jumps required to get to the leaf node has a direct impact on the total time required to read the data using that index, so let's explore this a little bit more.

All pages, including non-leaf index pages, have a size of 8192 bytes, including 96 bytes occupied by the header—this leaves 8096 bytes for the index data. Let's calculate how many jumps will be required for the index built on the single field of the integer type. Each pointer to the index page of the next level or to the data page in the heap takes 6 bytes, so the total size of one index entry is 10 bytes. This means that each index page can contain up to 800 index entries. Let's assume that the average number of entries on each index page is 400. This means that for a table holding up to 160,000 rows of data, it will take only three jumps to get to the data page. The first jump is to the root index page, the second jump is to the leaf-node-level index page, and the third jump is to the data page itself. For a table holding 160,000 to 64,000,000 rows of data, it will take four jumps.

This calculation demonstrates that the width of the key directly affects the number of required jumps. If we have the key defined on the char (200) column, it will take only three jumps for a table holding 400 rows or less; four jumps for a table holding up to 8,000 rows; and five jumps for a table holding up to 160,000 rows. This leads to the conclusion that indexes built on wider keys perform searches slower (in addition to taking more space).

Covering Indexes

The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It is discussed here because, as will be shown later, for most practical purposes, a clustering index can be thought of as a covering index.

Let's consider a simple but typical example. Suppose that we need to join two tables—Master, defined as (MasterId int primary key, Data int), and Detail, defined as (DetailId int primary key, Data int, MasterId int)—and select columns (MasterID, Detail.Data) where Master.Data satisfies a certain condition. The condition can be anything that is built directly on the value of Master.Data—for example, Data = @data, Data between @start and @end, or Data in (1, 2, 3). Let's also assume that, besides primary key indexes, the Master table has an index idxMaster built on (Data, MasterID), and the Detail table has an index idxDetail built on (MasterID). The query plan will look as shown in Figure 3.

What does that mean, exactly? Let's analyze it step by step:

1. The query engine searches idxMaster, to find the entries that satisfy our condition for Master.Data, and it builds a list of corresponding Master.MasterID values. This is fast, because:
• Index pages are sorted by Master.Data, so the engine can jump directly to leaf index pages that satisfy our condition.
• Those leaf index pages already contain values of Master.MasterId, so there is no need to jump somewhere else to read them.
2. The engine selects entries from idxDetail having values of Detail.MasterID that match the list of Master.MasterID built during Step 1. This can be done by simply looping through the list of Master.MasterID and looking up each corresponding entry in idxDetail, or by using more complicated hash or merge algorithms. The query engine determines which algorithm to use in each particular case, based on the relative cost of each algorithm. This, in turn, depends on the following criteria:
• Selectivity of the index
• Estimated number of lookups
• Estimated number of rows to find.

For the low relative values of the second and third criteria, the loop algorithm is preferred, whereas for higher values, the hash/merge algorithms become more efficient. The output of this step is a list of (MasterID, Detail:FID:PN:RN) values (see Figure 1).

3. A list of heap pointers (FID:PN:RN), also known as bookmarks, is used to read randomly located heap data pages of the Detail table, and to retrieve values of the Data field in order to compile the requested result set (MasterID, Detail.Data). This step is called bookmark lookup, and, in our case, it is the most expensive one. The cost of bookmark lookup can be anywhere from 90 to 99 percent of the total query cost. This means that eliminating this step can improve the performance ten-fold or even one-hundred-fold. The cost of bookmark lookup depends on the fragmentation of data pages, the number of data pages to read, that availability of read-ahead optimization, the IO-to-CPU cost ratio, and other factors.

Please note that, in this example, bookmark lookup has only been performed on the Detail table, not on the Master table. This is because the required output field MasterID was a part of the index. This leads us to the definition of covering index: A covering index is the index that contains all output fields required by the operation performed on that index. Please note that if the Master table contained more fields, and at least one of these fields was present in the SELECT clause, the query engine would have to perform bookmark lookup on the Master table, and therefore idxMaster could not serve as a covering index anymore. That is why it is not possible to tell whether the index is covering without taking into consideration the query it is used in, and even the particular execution plan.

It is very easy now to devise a way to improve the performance of our query. Adding Detail.Data as a second field to idxDetail eliminates the need for bookmark lookup and considerably improves the performance.

Good candidates for covering indexes are junction tables (tables that exist to resolve many-to-many relationships). In the simplest case of a many-to-many relationship between T1 and T2, it is enough to have two indexes, (T1_Id, T2_Id) and (T2_Id, T1_Id), in order to save the query engine from ever doing bookmark lookups on this table.

It is rarely feasible to use covering indexes for the fact tables, since there are usually many fields requested from these. Putting all required fields into the index, in order to make it covering, would in a way make it a very ineffective clustered index, which brings us to the next topic.

Clustered Indexes/Index-Based Tables

As you can see, this diagram is very similar to the diagram of a non-clustered index (Figure 1), except that leaf nodes contain data, instead of pointers to data. Therefore, the easiest way to understand a clustered index is to treat it as a covering index consisting of all the fields in the table, with three additional advantages:

• Data stored in leaf nodes is not duplicated. On the other hand, in the case of a covering index on a heap table, data will be stored both in the index and in the heap.
• Table scan (called clustered index scan in the case of an index-based table) is faster than table scan of a heap table, because leaf nodes of the clustered index are stored together. The internal implementation of modern hard drives allows storing all the data read from one logical cylinder of the hard drive in its internal cache; in the case of an index-based table, it is more likely that this data will contain more data pages of the table being scanned.
• If hash or merge algorithms are selected by the query engine for the join, data retrieved from the table should be sorted by the join key. If a clustered index is built on the join key, retrieved data is already sorted.

Several special considerations have to be kept in mind about index-based tables. The most important one is that all other (non-clustered) indexes of the index table contain values of the clustering key, instead of heap pointers. In the available sources, it is never mentioned what exactly was the reason for this design decision, so we can only guess that the additional cost of searching through the clustering index is outweighed by the cost saved during the reallocation of the clustered index pages. Indeed, nothing would prevent non-clustered indexes of the index-based table from pointing directly to the row in the leaf node that contains the data, but this pointer should be updated each time this row is moved to another location. Also, obviously, only one clustered index can exist for the table. So out of all possible indexes that could be clustered, it is necessary to select one that will give maximum performance gain.

Good candidates for clustered indexes are:

• Primary keys of the lookup/reference/dimension/master tables
• Foreign keys of the fact/detail tables
• Datetime fields of the tables queried by the date range

Optimization Rules of Thumb

• Always look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the execution plan and start optimizing from there. However, even before that, make sure that the statistics on all tables in your query are up to date, by running the update statistics <TableName> command on all tables in your query.
• If you see table scan, optimize. Table scan is the slowest possible way of execution. Table scan means not only that no index is used, but that there is no clustered index for this table at all. Even if you can only replace table scan with clustered index scan, it is still worth it.
• If you see clustered index scan, find out whether it can be replaced with index seek. For that, find what conditions are applied to this table. Usually, conditions exist for two or three fields of the table. Find out the most selective condition (that is, the condition that would produce the smallest number of records if applied alone), and see whether an index on this field exists. Any index that lists this field first will qualify. If there is no such index, create it and see whether the query engine picks it up.
• If the query engine is not picking up the existing index (that is, if it is still doing a clustered index scan), check the output list. It is possible that seek on your index is faster than clustered index scan, but involves bookmark lookup that makes the combined cost greater than use of a clustered index. Clustered index operations (scan or seek) never need bookmark lookup, since a clustered index already contains all the data. If the output list is not big, add those fields to the index, and see whether the query engine picks it up. Please remember that the combined size is more important than the number of fields. Adding three integer fields to the index is less expensive than adding one varchar field with an average data length of 20.

Summarizing this rule, try to make your index covering, and see whether it works better than clustered index scan. Please note that it is not always possible to make the query engine pick up your index automatically. A small table or a low-selectivity index will produce clustered index scan, even if your index is covering.

• If you see bookmark lookup, it means that your index is not covering. Try to make it covering if it makes sense (see the preceding guidelines).
• The execution plan selected by the query engine may be not the best one. The query engine makes certain assumptions about disk subsystem and CPU cost versus IO cost. These assumptions sometimes can be incorrect. If you don't believe that the query engine's selection is the best one, run a query in the loop for 10 to 15 minutes with automatic selection, change the query to use your index (you will have to use index hint to force it), and then run it for 10 to 15 minutes again. Compare the results to see which one works better.
• Avoid any operations on the fields, where possible. Some operations will prevent the use of the index on this field even if it exists—for example, the infamous ltrim(rtrim(FieldName)); other operations will degrade the performance. For example, instead of using the condition cast(DateField as varchar(20)) = @dateString, try to convert @dateString to an expression of datetime type first, and then compare it to DateField.
• Please note that the query engine cost estimate does not include the cost of embedded procedure or function calls. If you compare between plain join and select from table-value functions, the latter would seem to have smaller cost, but it usually does not. In such a situation, use your own metrics to find out which query performs better.
• When it is not possible to avoid operation on the field, use an index built on that expression. This can be done in two ways:
• Create a calculated field based on your expression.
• Create a view, and build an index on it.

Note    SQL Server requires certain conditions to be met in order to allow the use of calculated fields and indexed views (set quoted_identifier on, set arithabort on, and so on).

• Indexed views are a good way to further speed up the query if you are not satisfied with the results. Indexed view is a clustered index built over the view's select list. You can also define additional indexes for the indexed view, just as you can for any regular table. Indexed views take disk space and involve some maintenance overhead (every time underlying tables change, the indexed view also has to change), but they usually provide a good boost in performance, even after all other optimization techniques are exhausted.

Twelve Tips For Optimizing Sql Server 2005 Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

What is a query plan and what is the value from a performance tuning perspective?
A query plan is the physical break down of the code being passed to the SQL Server optimizer.
The value from a performance tuning perspective is that each component of the query can be understood and the percentage of resource utilization can be determined at a micro level.  As query tuning is being conducted, the detailed metrics can be reviewed to compare the individual coding techniques to determine the best alternative.

How to: Schedule a Job (SQL Server Management Studio)

csharpwithdj.blogspot.com

How to: Schedule a Job (SQL Server Management Studio)
This topic describes how to schedule a SQL Server Agent job. Before running the procedure in this topic, review Creating and Attaching Schedules to Jobs.

To create and attach a schedule to a job

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.

3. Select the Schedules page, and then click New.

4. In the Name box, type a name for the new schedule.

5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.

6. For Schedule Type, select one of the following:

• Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.

• Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.

• Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.

• Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

To attach a schedule to a job

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.

3. Select the Schedules page, and then click Pick.

4. Select the schedule that you want to attach, and then click OK.

5. In the Job Properties dialog box, double-click the attached schedule.

6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.

7. In the Job Properties dialog box, click OK.

Difference in Sql Server

Basic difference between Temp Tables and Table Variables

There are two types of temporary tables

A.) Local declared with one # Sign (#)

B.) Global declared with two # Sign (##)

Both of these tables created by simple CREATE TABLE (BOL) syntax except that # is used for Local Temporary Tables declaration and ## is used for Global Temporary Tables.

The Major difference between these two are Local Temporary tables (#) are visible in current connection and Global Temporary Table (##) are visible to all sessions or to the entire instance. You should always check for existence of global temporary table before creating it.

Let see with an example..

Open SSMS connect it and now create a Local Temporary table like this.

-- Creating Local Temporary Table

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

This will create a temporary table named LOCALTEST now open other SSMS connect it and again retry to create another table with same name but using the new connection and check if it created or not so here it is ….

-- Create Temporary Table with same name

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

-- Try to find out table with 'LOCALTEST' name in TempDb

USE TEMPDB

GO

SELECT Table_Catalog, Table_Name FROM information_schema.tables

WHERE table_name like '%LOCALTEST%'

GO

So here is the result set for the above query

So its very clear from the result set that there are two Local Temporary Tables with the same name.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number, you can see that in this image.

But when you try the same thing with Global Temporary Tables then it will result into an error.

So here is some points about Temporary tables

1. Can be create by simple Create Table Syntax use # for Local Temporary Table and ## for Global Temporary table.

2. Local temporary tables will be dropped at the end of current session but if it is created inside a store procedure, it will be dropped when store procedure is finished.

3. Its name is limited to 116 characters.

4. Foreign Key constraints can’t be applied to primary tables.

Now we know a little about Table Variables :

Table Variables (@) introduced first in SQL Server 2000 and onwards as name suggest its a variable of type table. Its definition’s include column definition, names, data type and constraint(Foreign Key Constraint are not allowed). They are more flexible and and always stay in memory well this is not true that table Variables stay always in memory this is a MYTH as table variables may be stored as in same way in TempDb as temporary tables we will look at some example for this. They are created with the Declare @variable syntax. There are some properties for Table Variables

1. They have well defined limited scope and are not part of persistent database.

2. Transactional rollbacks doesn’t affect on them.

3. They have very limited scope like current batch of statements so they can be deleted automatically after the completion of statements.

4. You don’t use them in nested procedures.

5. You not able to truncate a table variable.

So these are the some of basics for Table Variables and Temporary Tables but our Aim is to be find out which one is best in terms of performance, Advantages & Disadvantages for each and some considerations so we try to understand things why one is better then another and why not so

A.) Table Variables have a limited scope and are not part of persistent database, transaction rollback do not affect them.

Let’s understand what does it mean by transaction rollback do not affect them just take a simple example for this..

-- Create Tempoarary Table

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

-- Declare Table Variable

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(50))

-- Insert Values into both

Insert into #LOCALTEST SELECT 1 ,'Ashish'

Insert into @LOCALTEST SELECT 1 ,'Ashish'

-- Begin Transaction (Update both Temp table & Table Var)

BEGIN TRAN

UPDATE #LOCALTEST SET C2='ASHISH GILHOTRA'

UPDATE @LOCALTEST SET C2='ASHISH GILHOTRA'

-- Rollback Tran

ROLLBACK TRAN

-- Check data

SELECT * FROM #LOCALTEST

SELECT * FROM @LOCALTEST

and here is the result set

From this it’s very clear that Table Variables doesn’t take part in rollback transactions.So it may be or may not be useful for developers depends on the developers.

B.) There is less locking and logging on table variables then temporary tables so what does it means so we take an another example to understand this ..

USE tempdb

GO

-- Drop table #LOCALTEST

-- Drop table DUMMYDATA

-- Create a dummy table

CREATE TABLE DUMMYDATA (A int, B varchar(100))

-- Populate Data in it

Declare @i int

set @i=1

while @i<1001

BEGIN

INSERT into DUMMYDATA SELECT @i,REPLICATE('A',100)

SET @i=@i+1

END

--select * from DUMMYDATA

--Using #LOCALTEST

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))

GO

BEGIN TRAN

INSERT INTO #LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- Using @LOCALTEST

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))

BEGIN TRAN

INSERT INTO @LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- Now check for locks

select request_session_id, resource_type, db_name(resource_database_id),(case resource_type

WHEN 'OBJECT' then object_name(resource_associated_entity_id)

WHEN 'DATABASE' then ' '

ELSE (select object_name(object_id)

from sys.partitions

where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description,

request_mode,

request_status

from sys.dm_tran_locks

--Rollback tran

So if you going to run this query batch you will see lock acquire by “#LOCALTEST” not by @LOCALTEST so yes that true that Table Variables takes less locking then temporary tables.

for logging purpose just populate temp Table and Table Variable with same data as shown above and now use fn_dblog(Un Documented) to see what happen like this..

So here is a way to find logging

First of all see logging for Table Variables

-- Table Variable

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))

INSERT INTO @LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- update all the rows

update @LOCALTEST set C2 = replicate ('AB', 50)

-- Look at the top 10 log records.

select top 10 operation, AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%LOCALTEST%'

order by [Log Record Length] Desc

For this i don’t get any records of update log in this and try same for Temporary Tables you will get update logs in this..

-- Temp Table

--drop table #localtest

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))

GO

INSERT INTO #LOCALTEST(C1,C2)

SELECT A,B FROM DUMMYDATA

-- update all the rows

update #LOCALTEST set C2 = replicate ('ab', 50)

-- Look at the log records. Here you get log records for update

select operation,AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%LOCALTEST%'

order by [Log Record Length] Desc

(1)Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.

(2)Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.

(3)You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.

(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.

(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back using logs.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE Can be Rolled back using logs.

DELETE is DML Command.

DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

What is the difference between a local and a global variable?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

 What is the difference between a CHAR and a VARCHAR datatype?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

What is difference between OSQL and Query Analyzer

Both are same for functioning but there is a little difference OSQL is command line tool which execute query and display the result same a Query Analyzer do but Query Analyzer is graphical.OSQL have not ability like Query Analyzer to analyze queries and show statistics on speed of execution .And other useful thing about OSQL is that its helps in scheduling which is done in Query Analyzer with the help of JOB.

SQL Server Performance Tuning for Stored Procedures

http://csharpwithdj.blogspot.com/SQL Server Performance Tuning for Stored Procedures




Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including:

• Reduced network traffic and latency, boosting application performance.

• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.

• Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.

• Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.

• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don't remove any result sets columns). This saves developer time.

• Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access. This saves DBA's time.

Keep in mind that just because you use a stored procedure does not mean that it will run fast. The code you use within your stored procedure must be well designed for both speed and reuse.
*****

One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications.

Here are some examples how stored procedures reduce network traffic:

• When an application executes a stored procedure, only a simple, small RPC (remote procedure call) is made from the client to SQL Server. But if the application is not using stored procedures, but sending Transact-SQL code directly from the client to SQL Server, network traffic can often very high. For example, if the amount of Transact-SQL code is 500 lines (and this would not be all that unusual), then it would take hundreds of network packets to transmit the Transact-SQL code from the client to SQL Server. On the other hand, if the 500 lines of Transact-SQL code are in a stored procedure, this code never has to travel the network, as it is already located on the server.

• When an application needs to retrieve one or more rows from SQL Server and then takes some action on this data, such as INSERTing, UPDATing, or DELETing rows in the database based on the data retrieved, network traffic is significantly reduced if all this code is stored in a stored procedure. As before, it only takes a single RPC call to execute a stored procedure. But if all the code to perform these steps is not in a stored procedure, but located in the application, network traffic can be high. For example, first, the application has to send the Transact-SQL code to SQL Server (lots of potential network traffic). Then SQL Server has to return the result set back to the client, then the client has to use the data, and then send additional requests (INSERT, UPDATE, DELETE) to SQL Server, and then SQL Server has to respond back to the client, and so on, until the task is completed. As you can see, this can generate a lot of network traffic. But if all the work is being done from within a stored procedure, network traffic is greatly reduced.

• Along the same lines as above, putting the business logic of your application in stored procedures can help your application's performance. By locating virtually all of the processing on SQL Server, round-trip network traffic is greatly reduced, helping boost performance.

The goal should be to limit network traffic from the client to SQL Server to simple RPCs, and limit the traffic from SQL Server to the client as finished results.

*****

To help identify performance problems with stored procedures, use the SQL Server's Profiler Create Trace Wizard to run the "Profile the Performance of a Stored Procedure" trace to provide you with the data you need to identify poorly performing stored procedures.
*****

By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. Rarely is this information useful to the client. By turning off this default behavior, you can reduce network traffic between the server and the client, helping to boost overall performance of your server and applications.

There are two main ways to turn this feature off. You can also turn this feature off using a server trace setting, but it is unnecessary as there are easier ways, as described here.

To turn this feature off on at the stored procedure level, you can include the statement:

SET NOCOUNT ON

at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.

If you want this feature turned off for your entire server, you can do this by running these statements at your server:

SP_CONFIGURE 'user options', 512

RECONFIGURE

You may or may not want to do this for your entire server, as it affects every transaction on your server. For example, some application programs need the count information, otherwise they will not work. If this is the case, you don't want to turn this option for the entire server, but just for the stored procedures you write that don’t need the count information.

*****

Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.

Two ways to help reduce the length of a transaction are to: 1) break up the entire job into smaller steps (or multiple stored procedures) so each step can be committed as soon as possible; and 2) take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server.
When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server's buffer. If the same stored procedure is called again from the same connection, it will used the cached query plan instead of creating a new one, often saving time and boosting performance. This may or may not be what you want.

If the query in the stored procedure is exactly the same each time, and the query plan is the same each time, then this is a good thing. But if the query within the stored procedure is dynamic (for example, the WHERE clauses changes from one execution of the stored procedure to the next), then this may not be a good thing, as the query may not be optimized when it is run, and the performance of the query can suffer greatly. This can happen because changes in the query plan may occur, and if you run a cached query plan for what is essentially a new query, it may not be appropriate and it may cause performance to suffer greatly.

If you know that your query's query plan will vary each time it is run from a stored procedure, you will want to add the WITH RECOMPILE option when you create the stored procedure. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized with the correct query plan each time it is run. Yes, this will circumvent the reuse of cached query plans, hurting performance a little, but it is more desirable than reusing incorrect query plans.

*****

Many stored procedures have the option to accept multiple parameters. This in and of itself is not a bad thing. But what can often cause problems is if the parameters are optional, and the number of parameters varies each time the stored procedure runs. There are two ways to handle this problem, the slow performance way and fast performance way.

If you want to save your development time, but don't care about your application's performance, you can write your stored procedure generically so that it doesn't care how many parameters it gets. The problem with this method is that you may end up unnecessarily joining tables that don't need to be joined based on the parameters submitted for any single execution of the stored procedure.

Another, much better performing way, although it will take you more time to code, is to include IF...ELSE logic in your stored procedure, and create separate queries for each possible combination of parameters that are to be submitted to the stored procedure. This way, you can be sure you query is as efficient as possible each time it runs.
*****

Here's a way to handle the problem of not knowing what parameters your stored procedure might face. The problems are the query plans, the pre-compilation of stored procedures that SQL Server does for you. As you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them. The problem that is that SQL Server will only generate a query-plan for the path taken through your stored procedure when you first call it, not all possible paths.

Let me illustrate this with an example. Consider the following procedure (pre-compilation doesn't really have a huge effect on the queries used here, but these are just for illustration purposes):

CREATE PROCEDURE dbo.spTest (@query bit) AS

IF @query = 0

SELECT * FROM authors

ELSE

SELECT * FROM publishers

GO

Suppose I make my first call to this procedure with the @query parameter set to 0. The query-plan that SQL Server will generate will be optimized for the first query ("SELECT * FROM authors"), because the path followed on the first call will result in that query being executed.

Now, if I next call the stored procedure with @query set to 1, the query plan that SQL Server has in memory will not be of any use in executing the second query, since the query-plan is optimized for the authors table, not the publishers table. Result: SQL Server will have to compile a new query plan, the one needed for the second query. If I next call the procedure with @query set to 0 again, the whole path will have to be followed from the start again, since only one query-plan will be kept in memory for each stored procedure. This will result in sub-optimal performance.

As it happens I have a solution, one that I've used a lot with success. It involves the creation of what I like to call a 'delegator'. Consider again spTest. I propose to rewrite it like this:

CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS

IF @query = 0

EXEC spTestFromAuthors

ELSE

EXEC spTestFromPublishers

GO



CREATE PROCEDURE dbo.spTestFromAuthors AS

SELECT * FROM authors

GO



CREATE PROCEDURE dbo.spTestFromPublishers AS

SELECT * FROM publishers

GO

The result of this restructuring will be that there will always be an optimized query-plan for spTestFromAuthors and spTestFromPublishers, since they only hold one query. The only one getting re-compiled over and over again is the delegator, but since this stored procedure doesn't actually hold any queries, that won't have a noticeable effect on execution time. Of course re-compiling a plan for a simple 'SELECT *' from a single table will not give you a noticeable delay either (in fact, the overhead of an extra stored procedure call may be bigger then the re-compilation of "SELECT * FROM AnyTable"), but as soon as the queries get bigger, this method certainly pays off.

The only downside to this method is that now you have to manage three stored procedures instead of one. This is not that much of a problem though as the different stored procedures can be considered one single 'system', so it would be logical to keep all of them together in the same script, which would be just as easy to edit as a single stored procedure would be. As far as security is concerned, this method shouldn't give you any extra headaches either, as the delegator is the only stored procedure directly called by the client, this is the only one you need to manage permissions on. The rest will only be called by the delegator, which will always work as long as those stored procedures are owned by the same user as the delegator.

I've had large successes using this technique. Recently I developed a (partial full-text) search engine for our reports database, which resulted in a stored procedure that originally ran about 20 seconds. After employing the above technique, the stored procedure only took about 2 seconds to run, resulting in a ten-fold increase in performance!

While temporary stored procedures can provide a small performance boost in some circumstances, using a lot of temporary stored procedures in your application can actually create contention in the system tables and hurt performance.

Instead of using temporary stored procedures, you may want to consider using the SP_EXECUTESQL stored procedure instead. It provides the same benefits as temporary stored procedures, but it does not store data in the system tables, avoiding contention problems.

*****

If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix "sp_" in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.

The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database. Since it is not there, time is wasted looking for the stored procedure.

If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_".
*****

Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.
*****

For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.

If the object owner's or schemas are not specified for objects, then SQL Server must perform name resolution on the objects, which causes a small performance hit.

And if objects referred to in the stored procedure have different owners or schemas, SQL Server must check object permissions before it can access any object in the database, which adds unnecessary overhead. Ideally, the owner or schema of the stored procedure should own all of the objects referred to in the stored procedure.

In addition, SQL Server cannot reuse a stored procedure "in-memory plan" over if the object owner or schema is not used consistently. If a stored procedure is sometime referred to with its object owner's or schema name, and sometimes it is not, then SQL Server must re-execute the stored procedure, which also hinders performance.
*****

When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql offers two major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.

Sp_executesql executes a string of Transact-SQL in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.

Learn more about how to use sp_executesql in the SQL Server Books Online.
*****

SQL Server will automatically recompile a stored procedure if any of the following happens:

• If you include a WITH RECOMPILE clause in a CREATE PROCEDURE or EXECUTE statement.

• If you run sp_recompile for any table referenced by the stored procedure.

• If any schema changes occur to any of the objects referenced in the stored procedure. This includes adding or dropping rules, defaults, and constraints.

• New distribution statistics are generated.

• If you restore a database that includes the stored procedure or any of the objects it references.

• If the stored procedure is aged out of SQL Server's cache.

• An index used by the execution plan of the stored procedure is dropped.

• A major number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure.

• The stored procedure includes both DDL (Data Definition Language) and DML (Data Manipulation Language) statements, and they are interleaved with each other.

• If the stored procedure performs certain actions on temporary tables.



*****

One hidden performance problem of using stored procedures is when a stored procedure recompiles too often. Normally, you want a stored procedure to compile once and for it to be stored in SQL Server's cache so that it can be re-used without it having to recompile each time it is used. This is one of the major benefits of using stored procedures.

But in some cases, a stored procedure is recompiled much more often than it needs to be recompiled, hurting your server's performance. In fact, it is possible for a stored procedure to have to be recompiled while it is executing!

Here are three potential problems you want to look out for when writing stored procedures.

Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update

If your database has the "Auto Update Statistics" database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions. One side effect of this is that this also causes any stored procedures that reference these tables to be recompiled. Again, this is normal, as you don't want a stored procedure to be running an outdated query plan. But again, sometimes stored procedures recompile more than they have to. Here are some suggestions on how to reduce some of the unnecessary recompilations:

• Use sp_executesql instead of EXECUTE to run Transact-SQL strings in your stored procedures.

• Instead of writing one very large stored procedure, instead break down the stored procedure into two or more sub-procedures, and call then from a controlling stored procedure.

• If your stored procedure is using temporary tables, use the KEEP PLAN query hint, which is used to stop stored procedure recompilations caused by more than six changes in a temporary table, which is the normal behavior. This hint should only be used for stored procedures than access temporary tables a lot, but don't make many changes to them. If many changes are made, then don't use this hint.

Unnecessary Stored Procedure Recompilations Due to Mixing DDL and DML Statements in the Same Stored Procedure

If you have a DDL (Data Definition Language) statement in your stored procedure, the stored procedure will automatically recompile when it runs across a DML (Data Manipulation Language) statement for the first time. And if you intermix both DDL and DML many times in your stored procedure, this will force a recompilation every time it happens, hurting performance.

To prevent unnecessary stored procedure recompilations, you should include all of your DDL statements at the first of the stored procedure so they are not intermingled with DML statements.

Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations

Improper use of temporary tables in a stored procedure can force them to be recompiled every time the stored procedure is run. Here's how to prevent this from happening:

• Any references to temporary tables in your stored procedure should only refer to tables created by that stored procedure, not to temporary tables created outside your stored procedure, or in a string executed using either the sp_executesql or the EXECUTE statement.

• All of the statements in your stored procedure that include the name of a temporary table should appear syntactically after the temporary table.

• The stored procedure should not declare any cursors that refer to a temporary table.

• Any statements in a stored procedure that refer to a temporary table should precede any DROP TABLE statement found in the stored procedure.

• The stored procedure should not create temporary tables inside a control-of-flow statement.

• To find out if your SQL Server is experiencing excessive recompilations of stored procedures, a common cause of poor performance, create a trace using Profiler and track the SP:Recompile event. A large number of recompilations should be an indicator if you potentially have a problem. Identify which stored procedures are causing the problem, and then take correction action (if possible) to reduce or eliminate these excessive recompilations.

• *****

• Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS) instead of being called directly from your application. A stored procedure can be reused from the procedure cache only if the connection settings calling the stored procedure are the same. If different connections call a stored procedure, SQL Server must load a separate copy of the stored procedure for each connection, which somewhat defeats the purpose of stored procedures.

• But if the same connection calls a stored procedure, it can be used over and over from the procedure cache. The advantage of Transaction Server is that it reuses connections, which means that stored procedures can be reused more often. If you write an application where every user opens their own connection, then stored procedures cannot be reused as often, reducing performance.
• *****

• Avoid nesting stored procedures, although it is perfectly legal to do so. Nesting not only makes debugging more difficult, it makes it much more difficult to identify and resolve performance-related problems.

• *****

• If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation problem and the client application needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance.

• *****

• When calling a stored procedure from your application, it is important that you call it using its qualified name. Such as:

• exec dbo.myProcedure

• instead of:

• exec myProcedure

• Why? There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to eliminate any potential confusion about which stored procedure you want to run, helping to prevent bugs and other potential problems.

• But more importantly, doing so allows SQL Server to access the stored procedures execution plan more directly, and in turn, speeding up the performance of the stored procedure. Yes, the performance boost is very small, but if your server is running tens of thousands or more stored procedures every hour, these little time savings can add up.

• If a stored procedure needs to return only a single value, and not a recordset, consider returning the single value as an output statement. While output statements are generally used for error-checking, they can actually be used for any reason you like. Returning a single value as at output statement is faster than returning a single value as part of a recordset.

• *****

• One way to help ensure that stored procedures are reused from execution to execution of the same stored procedure is to ensure that any SET options, database options, or SQL Server configuration options don't change from execution to execution of the same stored procedure. If they do, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.

• Some examples of this include when you change the language used by the stored procedure (using SET) and if you change the Dateformat (using SET).

• *****

• If you find that a particular stored procedure recompiles every time it executes, and you have determined that there is nothing you can do about the recompiles, and if that stored procedure is very large, consider the following option. Try to determine what part or parts of the stored procedure is causing the recompiles. Once you have done this, break out this troublesome code into its own stored procedure, and then call this stored procedure from the main stored procedure. The advantage of this is that is it takes much less time to recompile a smaller stored procedure than a larger stored procedure.