In this article you will learn, everything about using Function Execution in SQL Server 2005String FunctionsDate and Time FunctionsMathematical Functions
String Functions
String Functions are used for manipulating string expression.
Note: string expression should be passed within single quote.
Len('') - Returns length of string.
Example: select Len("Shri Ganesh") will return 11
Lower('') - Convert all characters to lowercase characters.
Example: select Lower('Shri Ganesh') will return shri ganesh
Upper('') - Convert all characters to uppercase characters.
Example: select Upper('Shri Ganesh') will return SHRI GANESH
LTrim('') - Removes spaces from given character strings on left.
Example: select LTrim(' Shri Ganesh') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.
RTrim('') - Removes space from given character strings on right.
Example: select LTrim('Shri Ganesh ') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.
Trim('') - Removes spaces from given character strings from both left and right.
Example: select LTrim(' Shri Ganesh ') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.
SubString('') - Returns a part of string from original string.
SubString(character_expression, position, length)position - specifies where the substring begins.length - specifies the length of the substring as number of characters.
Example: select SubString('Shri Ganesh',6,7)
where in6 - Starting position of sub string from given string.6 - It is no. of characters to be extract from given string, starting from 6.That is it will return "Ganesh" As ganesh start from 6th character upto 6 characters.
Replace('') - Replace the desired string within the original string.
Replace(character_expression, searchstring, replacementstring)SearchString - string which you want to replace.ReplaceString - new string which you want to replace with
Example: select replace('Think High To Achieve High','High','Low')here, function search for every occurrence of High and replace it with Low.
Original - Think High To Achieve High
Result - Think Low To Achieve Low
Right('') - extract particular characters from right part of given string expression.
Example: select right('Think High To Achieve High',15) will return "To Achieve High"This function will be helpful when you want particular characters from right part.
Example: Let say i have social security nos. and i want to extract last 4 digit of it
.select right('111-11-1111',4) will return 1111
select right('222-22-2222',4) will return 2222
select right('333-33-3333',4) will return 3333
select right('444-44-4444',4) will return 4444
Date and Time Function
Date and Time Functions are used for manipulating Date and Time expression.
GetDate() - Returns current date and time of a system.
Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
GetUTCDate() - Returns current date and time information as per UTC (Universal Time Coordinate or Greenwich Mean Time)
Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
DatePart and Abbrevation, which we will be using with DatePart, DateADD, DateDIFF function.
Datepart Abbreviations
Year yy, yyyy
Quarter qq, q
Month mm, m
Dayofyear dy, y
Day dd, d
Week wk, ww
Weekday dw, w
Hour Hh
Minute mi, n
Second ss, s
Millisecond Ms
DatePart() - Returns an integer representing a datepart of a date.
Note: Example are based on considering "2007-10-10 15:34:37.287" as GetDate()
Example:
select DatePart("day",GetDate()) will return 10.
select DatePart("hour",GetDate()) will return 16.
select DatePart("dayofyear",GetDate()) will return 283. And so on...
DateADD() - Returns adds a date or time interval to a specified date.
Syntax: DateADD(Abbrevation, number to be added, date)
Example:
select DateAdd("day",7,GetDate()) will return 2007-10-17 16:09:18.280
Select DateAdd("month",20,GetDate()) will return 2009-06-10 16:10:02.643
And so on...
DateDIFF() - Returns difference between two specified dates.
Syntax: DateDIFF(Abbrevation, startdate, enddate)
Note: If the end date is earlier than the start date, the function returns a negative number. If the start and end dates are equal or fall within the same interval, the function returns zero
Example:
select DATEDIFF("mm", Getdate()-500,GETDATE()) will return 17
You must pass valid start and end date otherwise you will receive error.
Mathematical Functions
Mathematical Functions are used for manipulating Mathematical expression.
ABS() - Returns positive value of numeric expression.
Example: In following example both statement will return 3.14select
ABS(3.14)
select ABS(-3.14)
Ceiling() - Returns the smallest integer that is greater than or equal to a numeric expression.
Example:
select Ceiling(3.14) will return 4
select Ceiling(-3.14) will return 3.
Floor() -Returns the largest integer that is less than or equal to a numeric expression.
Example:
select Floor(3.14) will return 3
select Floor(-3.14) will return 4
Round() - Returns a numeric expression that is rounded to the specified length or precision.
Example:
select Round(3.14, 1) will return 3.10
select Round(-3.17, 1) will return -3.20
select Round(3.12345, 4) will return 3.12350
select Round(3.12345, 3) will return 3.12300
Power() - POWER(numeric_expression, power)
Example:
select power(2,3) will return 8
Tuesday, March 11, 2008
Monday, March 10, 2008
Introduction to SQL
SQL is a standard computer language for accessing and manipulating databases.
What is SQL?
SQL stands for Structured Query Language
SQL allows you to access a database
SQL is an ANSI standard computer language
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert new records in a database
SQL can delete records from a database
SQL can update records in a database
SQL is easy to learn
SQL is a Standard - BUT....
SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
SQL Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
SQL Queries
With SQL, we can query a database and have a result set returned.
A query like this:
SELECT LastName FROM Persons
Gives a result set like this:
LastName
Hansen
Svendson
Pettersen
SQL Data Manipulation Language (DML)
SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.
These query and update commands together form the Data Manipulation Language (DML) part of SQL:
SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table
SQL Data Definition Language (DDL)
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
What is SQL?
SQL stands for Structured Query Language
SQL allows you to access a database
SQL is an ANSI standard computer language
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert new records in a database
SQL can delete records from a database
SQL can update records in a database
SQL is easy to learn
SQL is a Standard - BUT....
SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
SQL Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
SQL Queries
With SQL, we can query a database and have a result set returned.
A query like this:
SELECT LastName FROM Persons
Gives a result set like this:
LastName
Hansen
Svendson
Pettersen
SQL Data Manipulation Language (DML)
SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.
These query and update commands together form the Data Manipulation Language (DML) part of SQL:
SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table
SQL Data Definition Language (DDL)
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
SQL Server Management Studio Tutorial
Welcome to the Microsoft SQL Server Management Studio tutorial. Management Studio is a new tool for SQL Server database administrators and developers. Hosted inside Microsoft Visual Studio, it brings graphical tools for database management together with a rich development environment. Management Studio includes the functions of SQL Server 2000 Enterprise Manager, Analysis Manager, and SQL Query Analyzer in one tool, along with the ability to write MDX, XMLA, and XML statements.
This tutorial will help you understand the presentation of information in Management Studio and how to take advantage of the features that were not available in Enterprise Manager.
To begin this tutorial, let's take a look at SQL Server Management Studio.
Opening SQL Server Management Studio
To open SQL Server Management Studio
On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the Connect to Server dialog box, verify the default settings, and then click Connect. To connect, the Server name box must contain the name of the computer where SQL Server is installed. If the Database Engine is a named instance, the Server name box should also contain the instance name in the format\.
Management Studio Components
Management Studio presents information in windows dedicated to specific types of information. Database information is shown in Object Explorer and document windows.
Object Explorer is a tree view of all the database objects in a server. This can include the databases of the SQL Server Database Engine, Analysis Services, Reporting Services, Integration Services, and SQL Server Compact Edition. Object Explorer includes information for all servers to which it is connected. When you open Management Studio, you are prompted to connect Object Explorer to the settings that were last used. You can double-click any server in the Registered Servers component to connect to it, but you do not have to register a server to connect.
The document window is the largest portion of Management Studio. The document windows can contain query editors and browser windows. By default, the Object Explorer Details page is displayed, connected to the instance of Database Engine on the current computer.
Showing Additional Windows
Users familiar with the SQL Server Enterprise Manager of SQL Server 2000 may wish to show the Registered Servers window.
To show the Registered Servers window
On the View menu, click Registered Servers.
The Registered Servers window appears above Object Explorer. Registered Servers lists servers which you manage frequently. You can add and remove servers from this list. If SQL Server 2000 Enterprise Manager was previously installed on this computer, you will be prompted to import the list of registered servers. Otherwise, the only servers listed are the SQL Server instances on the computer where you are running Management Studio.
If your server does not appear, in Registered Servers, right-click Database Engine, and then click Update Local Server Registration.
This tutorial will help you understand the presentation of information in Management Studio and how to take advantage of the features that were not available in Enterprise Manager.
To begin this tutorial, let's take a look at SQL Server Management Studio.
Opening SQL Server Management Studio
To open SQL Server Management Studio
On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the Connect to Server dialog box, verify the default settings, and then click Connect. To connect, the Server name box must contain the name of the computer where SQL Server is installed. If the Database Engine is a named instance, the Server name box should also contain the instance name in the format
Management Studio Components
Management Studio presents information in windows dedicated to specific types of information. Database information is shown in Object Explorer and document windows.
Object Explorer is a tree view of all the database objects in a server. This can include the databases of the SQL Server Database Engine, Analysis Services, Reporting Services, Integration Services, and SQL Server Compact Edition. Object Explorer includes information for all servers to which it is connected. When you open Management Studio, you are prompted to connect Object Explorer to the settings that were last used. You can double-click any server in the Registered Servers component to connect to it, but you do not have to register a server to connect.
The document window is the largest portion of Management Studio. The document windows can contain query editors and browser windows. By default, the Object Explorer Details page is displayed, connected to the instance of Database Engine on the current computer.
Showing Additional Windows
Users familiar with the SQL Server Enterprise Manager of SQL Server 2000 may wish to show the Registered Servers window.
To show the Registered Servers window
On the View menu, click Registered Servers.
The Registered Servers window appears above Object Explorer. Registered Servers lists servers which you manage frequently. You can add and remove servers from this list. If SQL Server 2000 Enterprise Manager was previously installed on this computer, you will be prompted to import the list of registered servers. Otherwise, the only servers listed are the SQL Server instances on the computer where you are running Management Studio.
If your server does not appear, in Registered Servers, right-click Database Engine, and then click Update Local Server Registration.
SQL Server Query Execution Plan Analysis
When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information if very valuable when it comes time to find out why a specific query is running slow.
There are several different ways to view a query's execution plan. They include:
From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.
From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.
Of these options, I prefer using the "Show Execution Plan", which produces a graphical output and considers current server operations.
*****
If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.
Index or table scans: May indicate a need for better or additional indexes.
Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes.
Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be.
*****
If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer or Management Studio to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.
On the other hand, if you use a table variable instead of a temp table, you can use the "Display Estimated Execution Plan" option.
*****
If you have a very complex query you are analyzing in Query Analyzer or Management Studio as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately.
*****
The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:
In very complex query plans, the plan is divided into many parts, with each part listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer has to perform in order to get to the final results.
Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.
Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.
Eventually, all of the parts come together at the top left side of the screen.
If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.
If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.
The arrows that connect one icon to another in a graphical query plan have different thicknesses. The thickness of the arrow indicates the relative cost in the number of rows and row size of the data moving between each icon. The thicker the arrow, the more the relative cost is.
You can use this indicator as a quick gauge as to what is happening within the query plan of your query. You will want to pay extra attention to thick arrows in order to see how it affects the performance of your query. For example, thick lines should be at the right of the graphical execution plan, not the left. If you see them on the left, this could indicate that too many rows are being returned, and that the query execution plan is less than optimal.
*****
In an execution plan, each part of it is assigned a percentage cost. This represents how much this part costs in resource use, relative to the rest of the execution plan. When you analyze an execution plan, you should focus your efforts on those parts that have the largest percentage cost. This way, you focus your limited time on those areas that have the greatest potential for a return on your time investment.
*****
In an execution plan, you may have noticed that some parts of the plan are executed more than once. As part of your analysis of an execution plan, you should focus some of your time on any part that takes more than one execution, and see if there is any way to reduce the number of executions performed. The fewer executions that are performed, the faster the query will be executed.
*****
In an execution plan you will see references to I/O and CPU cost. These don't have a "real" meaning, such as representing the use of a specific amount of resources. These figures are used by the Query Optimizer to help it make the best decision. But there is one meaning you can associate with them, and that is that a smaller I/O or CPU cost uses less server resources than a higher I/O or CPU cost.
*****
When you examine a graphical SQL Server query execution plan, one of the more useful things to look for are how indexes were used (if at all) by the query optimizer to retrieve data from tables from a given query. By finding out if an index was used, and how it was used, you can help determine if the current indexes are allowing the query to run as well as it possibly can.
When you place the cursor over a table name (and its icon) in a graphical execution plan and display the pop-up window, you will see one of several messages. These messages tell you if and how an index was used to retrieve data from a table. They include:
Table Scan: If you see this message, it means there was no clustered index on the table and that no index was used to look up the results. Literally, each row in the table had to be examined. If a table is relatively small, table scans can be very fast, sometimes faster than using an index. So the first thing you want to do, when you see that a table scan has been performed, is to see how many rows there are in the table. If there are not many, then a table scan may offer the best overall performance. But if this table is large, then a table scan will most likely take a long time to complete, and performance will suffer. In this case, you need to look into adding an appropriate index(s) to the table that the query can use. Let's say that you have identified a query that uses a table scan, but you also discover that there is an appropriate nonclustered index, but it is not being used. What does that mean, and why isn't the index being used? If the amount of data to be retrieved is large, relative to the size of the table, or if the data is not selective (which means that there are many rows with the same values in the same column), a table scan is often performed instead of an index seek because it is faster. For example, if a table has 10,000 rows, and the query returns 1,000 of them, then a table scan of a table with no clustered index will be faster than trying to use a non-clustered index. Or, if the table had 10,000 rows, and 1,000 of the rows have the same value in the same column (the column being used in the WHERE clause), a table scan is also faster than using a non-clustered index. When you view the pop-up window when you move the cursor over a table in a graphical query plan, notice the "Estimated Row Count" number. This number is the query optimizer's best guess on how many rows will be retrieved. If a table scan was done, and this number is very high, this tells you that the table scan was done because a high number of records were returned, and that the query optimizer believed that it was faster to perform a table scan than use the available non-clustered index.
Index Seek: When you see this, it means that the query optimizer used a non-clustered index on the table to look up the results. Performance is generally very quick, especially when few rows are returned.
Clustered Index Seek: If you see this, this means that the query optimizer was able to use a clustered index on the table to look up the results, and performance is very quick. In fact, this is the fastest type of index lookup SQL Server can do.
Clustered Index Scan: A clustered index scan is like a table scan, except that it is done on a table that has a clustered index. Like a regular table scan, a clustered index scan may indicate a performance problem. Generally, they occur for two different reasons. First, there may be too many rows to retrieve, relative to the total number of rows in the table. See the "Estimated Row Count" to verify this. Second, it may be due to the column queried in the WHERE clause may not be selective enough. In any event, a clustered index scan is generally faster than a standard table scan, as not all records in the table always have to be searched when a clustered index scan is run, unlike a standard table scan. Generally, the only thing you can do to change a clustered index scan to a clustered index seek is to rewrite the query so that it is more restrictive and fewer rows are returned.
In most cases, the query optimizer will analyze joins and JOIN the tables using the most efficient join type, and in the most efficient order. But not always. In the graphical query plan you will see icons that represent the different types of JOINs used in the query. In addition, each of the JOIN icons will have two arrows pointing to it. The upper arrow pointing to the JOIN icon represents the outer table in the join, and the lower arrow pointing to the JOIN icon represent the inner table in the join. Follow the arrows back to see the name of the tables being joined.
Sometimes, in queries with multiple JOINs, tracing the arrow back won't reveal a table, but another JOIN. If you place the cursor over the arrows pointing to the upper and lower JOINs, you will see a popup window that tells you how many rows are being sent to the JOIN for processing. The upper arrow should always have fewer rows than the lower arrow. If not, then the JOIN order selected by the query optimizer might be incorrect (see more on this below).
First of all, let's look at JOIN types. SQL Server can JOIN a table using three different techniques: nested loop, hash, and merge. Generally, the fastest type of join in a nested loop, but if that is not feasible, then a hash JOIN or merge JOIN is used (as appropriate), both of which tend to be slower than the nested JOIN.
When very large tables are JOINed, a merge join, not a nested loop join, may be the best option. The only way to know is to try both and see which one is the most efficient.
If a particular query is slow, and you suspect it may be because the JOIN type is not the optimum one for your data, you can override the query optimizer's choice by using a JOIN hint. Before you use a JOIN hint, you will want to take some time to learn about each of the JOIN types and how each one works. This is a complicated subject, beyond the scope of this tip.
JOIN order is also selected by the query optimizer, which it trying to select the most efficient order to JOIN tables. For example, for a nested loop join, the upper table should be the smaller of the two tables. For hash joins, the same is true; the upper table should be the smaller of the two tables. If you feel that the query optimizer is selecting the wrong order, you can override it using JOIN hints.
In many cases, the only way to know for sure if using a JOIN hint to change JOIN type or JOIN order will boost or hinder performance is to give them a try and see what happens.
*****
If your SQL Server has multiple CPUs, and you have not changed the default setting in SQL Server to limit SQL Server's ability to use all of the CPUs in the server, then the query optimizer will consider using parallelism to execute some queries. Parallelism refers to the ability to execute a query on more than one CPU at the same time. In many cases, a query that runs on multiple processors is faster than a query that only runs on a single processor, but not always.
The Query Optimizer will not always use parallelism, even though it potentially can. This is because the Query Optimizer takes a variety of different things into consideration before it decides to use parallelism. For example, how many active concurrent connections are there, how busy is the CPU, is there enough available memory to run parallel queries, how many rows are being processed, and what is the type of query being run? Once the Query Optimizer collects all the facts, then it decides if parallelism is best for this particular run of the query. You may find that one time a query runs without parallelism, but later, the same query runs again, but this time, parallelism is used.
In some cases, the overhead of using multiple processors is greater than the resource savings of using them. While the query processor does try to weigh the pros and cons of using a parallel query, it doesn't always guess correctly.
If you suspect that parallelism might be hurting the performance of a particular query, you can turn off parallelism for this particular query by using the OPTION (MAXDOP 1) hint.
The only way to know for sure is to test the query both ways, and see what happens.
*****
When you review a graphical execution plan, you may notice that the text of a icon is displayed in red, not black, which is the normal color. This means that the related table is missing some statistics that the Query Optimizer would like to have in order to come up with a better execution plan.
To create the missing statistics, you need to right-click on the icon and select "Create Missing Statistics." This will display the "Create Missing Statistics" dialog box, where you can then easily add the missing statistics.
If you are given the option to update missing statistics, you should always take the opportunity to do so as it will most likely benefit the performance of the query that is being analyzed.
*****
Sometimes, when viewing a graphical query execution plan, you see an icon labeled "Assert." All this means is that the query optimizer is verifying a referential integrity or check constraint to see if the query will violate it or not. If not, there is no problem. But if it does, then the Query Optimizer will be unable to create an execution plan for the query and an error will be generated.
*****
Often, when viewing a graphical query execution plan, you see an icon labeled "Bookmark Lookup." Bookmark lookups are quite common to see. Essentially, they are telling you that the Query Processor had to look up the row columns it needs from a heap or a clustered index, instead of being able to read it directly from a non-clustered index.
For example, if all of the columns in the SELECT, JOIN, and WHERE clauses of a query don't all exist in the non-clustered index used to locate the rows that meet the query's criteria, then the Query Optimizer has to do extra work and look at the heap or clustered index to find all the columns it needs to satisfy the query.
Another cause of a bookmark lookup is using SELECT *, which should never be used, as in most cases it will return more data that you really need.
Bookmark lookups are not ideal from a performance perspective because extra I/O is required to look up all the columns for the rows to be returned.
If you think that a bookmark lookup is hurting a query's performance, you have four potential options to avoid it. First, you can create a clustered index that will be used by the WHERE clause, you can take advantage of index intersection, you can create a covering non-clustered index, or you can (if you have SQL Server 2000/2005 Enterprise Edition, create an indexed view. If none of these are possible, or if using one of these will use more resources than using the bookmark lookup, then the bookmark lookup is the optimal choice.
Sometimes, the Query Optimizer will need to create a temporary worktable in the tempdb database. If this is the case, it will be indicated in the graphical query execution plan with an icon labeled like this: Index Spool, Row Count Spool, or Table Spool.
Anytime a worktable is used, performance is generally hurt because of the extra I/O required to maintain the worktable. Ideally, there should be no worktables. Unfortunately, they cannot always be avoided. And sometimes their use can actually boost performance because using a worktable is more efficient that alternatives.
In any event, the use of a worktable in a graphical query execution plan should raise an alert with you. Take a careful look at such a query and see if there is anyway it can be rewritten to avoid the worktable. There may not be. But if there is, you are one step closer to boosting the performance of the query.
*****
In a graphical query execution plan, often you see the Stream Aggregate icon. This just means that some sort of aggregation into a single input is being performed. This is most commonly seen when a DISTINCT clause is used, or any aggregation operator, such as AVG, COUNT, MAX, MIN, or SUM.
*****
Query Analyzer and Management Studio are not the only tools that can generate and display query execution plans for queries. The SQL Server Profiler can also display them, albeit in text format only. One of the advantages of using Profiler instead of Query Analyzer or Management Studio to display execution plans is that it can do so for a great many queries from your actual production work, instead of running one at a time.
To capture and display query execution plans using Profiler, you must create a trace using the following configuration:
Events to Capture
Performance: Execution Plan
Performance: Show Plan All
Performance: Show Plan Statistics
Performance: Show Plan Text
Data Columns to Display
StartTime
Duration
TextData
CPU
Reads
Writes
Filters
Duration. You will want to specify a maximum duration, such as 5 seconds, so that you don't get flooded with too much data.
Of course, you can capture more information than is listed above in your trace; the above is only a guideline. But keep in mind that you don't want to capture too much data, as this could have a negative affect on your server's performance as the trace is being run.
*****
If you use the OPTION FAST hint in a query, be aware that the Execution Plan results may not be what you expect. The Execution Plan that you get is based on the results of using the FAST hint, not the actual Execution Plan for the full query.
The FAST hint is used to tell the Query Optimizer to return the specified number of rows as fast as possible, even if this hurts the overall performance of the query. The purpose of this hint is to return a specified number of records quickly in order to produce an illusion of speed for the user. Once the specified number of rows is returned, the remaining rows are retuned as they would be normally.
So if you are using the FAST hint, the execution plan will be for only those rows that are returned FAST, not for all of the rows. If you want to see the execution plan for all the rows, then you must perform an Execution Plan of the query with the hint removed.
There are several different ways to view a query's execution plan. They include:
From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.
From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.
Of these options, I prefer using the "Show Execution Plan", which produces a graphical output and considers current server operations.
*****
If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.
Index or table scans: May indicate a need for better or additional indexes.
Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes.
Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be.
*****
If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer or Management Studio to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.
On the other hand, if you use a table variable instead of a temp table, you can use the "Display Estimated Execution Plan" option.
*****
If you have a very complex query you are analyzing in Query Analyzer or Management Studio as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately.
*****
The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:
In very complex query plans, the plan is divided into many parts, with each part listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer has to perform in order to get to the final results.
Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.
Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.
Eventually, all of the parts come together at the top left side of the screen.
If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.
If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.
The arrows that connect one icon to another in a graphical query plan have different thicknesses. The thickness of the arrow indicates the relative cost in the number of rows and row size of the data moving between each icon. The thicker the arrow, the more the relative cost is.
You can use this indicator as a quick gauge as to what is happening within the query plan of your query. You will want to pay extra attention to thick arrows in order to see how it affects the performance of your query. For example, thick lines should be at the right of the graphical execution plan, not the left. If you see them on the left, this could indicate that too many rows are being returned, and that the query execution plan is less than optimal.
*****
In an execution plan, each part of it is assigned a percentage cost. This represents how much this part costs in resource use, relative to the rest of the execution plan. When you analyze an execution plan, you should focus your efforts on those parts that have the largest percentage cost. This way, you focus your limited time on those areas that have the greatest potential for a return on your time investment.
*****
In an execution plan, you may have noticed that some parts of the plan are executed more than once. As part of your analysis of an execution plan, you should focus some of your time on any part that takes more than one execution, and see if there is any way to reduce the number of executions performed. The fewer executions that are performed, the faster the query will be executed.
*****
In an execution plan you will see references to I/O and CPU cost. These don't have a "real" meaning, such as representing the use of a specific amount of resources. These figures are used by the Query Optimizer to help it make the best decision. But there is one meaning you can associate with them, and that is that a smaller I/O or CPU cost uses less server resources than a higher I/O or CPU cost.
*****
When you examine a graphical SQL Server query execution plan, one of the more useful things to look for are how indexes were used (if at all) by the query optimizer to retrieve data from tables from a given query. By finding out if an index was used, and how it was used, you can help determine if the current indexes are allowing the query to run as well as it possibly can.
When you place the cursor over a table name (and its icon) in a graphical execution plan and display the pop-up window, you will see one of several messages. These messages tell you if and how an index was used to retrieve data from a table. They include:
Table Scan: If you see this message, it means there was no clustered index on the table and that no index was used to look up the results. Literally, each row in the table had to be examined. If a table is relatively small, table scans can be very fast, sometimes faster than using an index. So the first thing you want to do, when you see that a table scan has been performed, is to see how many rows there are in the table. If there are not many, then a table scan may offer the best overall performance. But if this table is large, then a table scan will most likely take a long time to complete, and performance will suffer. In this case, you need to look into adding an appropriate index(s) to the table that the query can use. Let's say that you have identified a query that uses a table scan, but you also discover that there is an appropriate nonclustered index, but it is not being used. What does that mean, and why isn't the index being used? If the amount of data to be retrieved is large, relative to the size of the table, or if the data is not selective (which means that there are many rows with the same values in the same column), a table scan is often performed instead of an index seek because it is faster. For example, if a table has 10,000 rows, and the query returns 1,000 of them, then a table scan of a table with no clustered index will be faster than trying to use a non-clustered index. Or, if the table had 10,000 rows, and 1,000 of the rows have the same value in the same column (the column being used in the WHERE clause), a table scan is also faster than using a non-clustered index. When you view the pop-up window when you move the cursor over a table in a graphical query plan, notice the "Estimated Row Count" number. This number is the query optimizer's best guess on how many rows will be retrieved. If a table scan was done, and this number is very high, this tells you that the table scan was done because a high number of records were returned, and that the query optimizer believed that it was faster to perform a table scan than use the available non-clustered index.
Index Seek: When you see this, it means that the query optimizer used a non-clustered index on the table to look up the results. Performance is generally very quick, especially when few rows are returned.
Clustered Index Seek: If you see this, this means that the query optimizer was able to use a clustered index on the table to look up the results, and performance is very quick. In fact, this is the fastest type of index lookup SQL Server can do.
Clustered Index Scan: A clustered index scan is like a table scan, except that it is done on a table that has a clustered index. Like a regular table scan, a clustered index scan may indicate a performance problem. Generally, they occur for two different reasons. First, there may be too many rows to retrieve, relative to the total number of rows in the table. See the "Estimated Row Count" to verify this. Second, it may be due to the column queried in the WHERE clause may not be selective enough. In any event, a clustered index scan is generally faster than a standard table scan, as not all records in the table always have to be searched when a clustered index scan is run, unlike a standard table scan. Generally, the only thing you can do to change a clustered index scan to a clustered index seek is to rewrite the query so that it is more restrictive and fewer rows are returned.
In most cases, the query optimizer will analyze joins and JOIN the tables using the most efficient join type, and in the most efficient order. But not always. In the graphical query plan you will see icons that represent the different types of JOINs used in the query. In addition, each of the JOIN icons will have two arrows pointing to it. The upper arrow pointing to the JOIN icon represents the outer table in the join, and the lower arrow pointing to the JOIN icon represent the inner table in the join. Follow the arrows back to see the name of the tables being joined.
Sometimes, in queries with multiple JOINs, tracing the arrow back won't reveal a table, but another JOIN. If you place the cursor over the arrows pointing to the upper and lower JOINs, you will see a popup window that tells you how many rows are being sent to the JOIN for processing. The upper arrow should always have fewer rows than the lower arrow. If not, then the JOIN order selected by the query optimizer might be incorrect (see more on this below).
First of all, let's look at JOIN types. SQL Server can JOIN a table using three different techniques: nested loop, hash, and merge. Generally, the fastest type of join in a nested loop, but if that is not feasible, then a hash JOIN or merge JOIN is used (as appropriate), both of which tend to be slower than the nested JOIN.
When very large tables are JOINed, a merge join, not a nested loop join, may be the best option. The only way to know is to try both and see which one is the most efficient.
If a particular query is slow, and you suspect it may be because the JOIN type is not the optimum one for your data, you can override the query optimizer's choice by using a JOIN hint. Before you use a JOIN hint, you will want to take some time to learn about each of the JOIN types and how each one works. This is a complicated subject, beyond the scope of this tip.
JOIN order is also selected by the query optimizer, which it trying to select the most efficient order to JOIN tables. For example, for a nested loop join, the upper table should be the smaller of the two tables. For hash joins, the same is true; the upper table should be the smaller of the two tables. If you feel that the query optimizer is selecting the wrong order, you can override it using JOIN hints.
In many cases, the only way to know for sure if using a JOIN hint to change JOIN type or JOIN order will boost or hinder performance is to give them a try and see what happens.
*****
If your SQL Server has multiple CPUs, and you have not changed the default setting in SQL Server to limit SQL Server's ability to use all of the CPUs in the server, then the query optimizer will consider using parallelism to execute some queries. Parallelism refers to the ability to execute a query on more than one CPU at the same time. In many cases, a query that runs on multiple processors is faster than a query that only runs on a single processor, but not always.
The Query Optimizer will not always use parallelism, even though it potentially can. This is because the Query Optimizer takes a variety of different things into consideration before it decides to use parallelism. For example, how many active concurrent connections are there, how busy is the CPU, is there enough available memory to run parallel queries, how many rows are being processed, and what is the type of query being run? Once the Query Optimizer collects all the facts, then it decides if parallelism is best for this particular run of the query. You may find that one time a query runs without parallelism, but later, the same query runs again, but this time, parallelism is used.
In some cases, the overhead of using multiple processors is greater than the resource savings of using them. While the query processor does try to weigh the pros and cons of using a parallel query, it doesn't always guess correctly.
If you suspect that parallelism might be hurting the performance of a particular query, you can turn off parallelism for this particular query by using the OPTION (MAXDOP 1) hint.
The only way to know for sure is to test the query both ways, and see what happens.
*****
When you review a graphical execution plan, you may notice that the text of a icon is displayed in red, not black, which is the normal color. This means that the related table is missing some statistics that the Query Optimizer would like to have in order to come up with a better execution plan.
To create the missing statistics, you need to right-click on the icon and select "Create Missing Statistics." This will display the "Create Missing Statistics" dialog box, where you can then easily add the missing statistics.
If you are given the option to update missing statistics, you should always take the opportunity to do so as it will most likely benefit the performance of the query that is being analyzed.
*****
Sometimes, when viewing a graphical query execution plan, you see an icon labeled "Assert." All this means is that the query optimizer is verifying a referential integrity or check constraint to see if the query will violate it or not. If not, there is no problem. But if it does, then the Query Optimizer will be unable to create an execution plan for the query and an error will be generated.
*****
Often, when viewing a graphical query execution plan, you see an icon labeled "Bookmark Lookup." Bookmark lookups are quite common to see. Essentially, they are telling you that the Query Processor had to look up the row columns it needs from a heap or a clustered index, instead of being able to read it directly from a non-clustered index.
For example, if all of the columns in the SELECT, JOIN, and WHERE clauses of a query don't all exist in the non-clustered index used to locate the rows that meet the query's criteria, then the Query Optimizer has to do extra work and look at the heap or clustered index to find all the columns it needs to satisfy the query.
Another cause of a bookmark lookup is using SELECT *, which should never be used, as in most cases it will return more data that you really need.
Bookmark lookups are not ideal from a performance perspective because extra I/O is required to look up all the columns for the rows to be returned.
If you think that a bookmark lookup is hurting a query's performance, you have four potential options to avoid it. First, you can create a clustered index that will be used by the WHERE clause, you can take advantage of index intersection, you can create a covering non-clustered index, or you can (if you have SQL Server 2000/2005 Enterprise Edition, create an indexed view. If none of these are possible, or if using one of these will use more resources than using the bookmark lookup, then the bookmark lookup is the optimal choice.
Sometimes, the Query Optimizer will need to create a temporary worktable in the tempdb database. If this is the case, it will be indicated in the graphical query execution plan with an icon labeled like this: Index Spool, Row Count Spool, or Table Spool.
Anytime a worktable is used, performance is generally hurt because of the extra I/O required to maintain the worktable. Ideally, there should be no worktables. Unfortunately, they cannot always be avoided. And sometimes their use can actually boost performance because using a worktable is more efficient that alternatives.
In any event, the use of a worktable in a graphical query execution plan should raise an alert with you. Take a careful look at such a query and see if there is anyway it can be rewritten to avoid the worktable. There may not be. But if there is, you are one step closer to boosting the performance of the query.
*****
In a graphical query execution plan, often you see the Stream Aggregate icon. This just means that some sort of aggregation into a single input is being performed. This is most commonly seen when a DISTINCT clause is used, or any aggregation operator, such as AVG, COUNT, MAX, MIN, or SUM.
*****
Query Analyzer and Management Studio are not the only tools that can generate and display query execution plans for queries. The SQL Server Profiler can also display them, albeit in text format only. One of the advantages of using Profiler instead of Query Analyzer or Management Studio to display execution plans is that it can do so for a great many queries from your actual production work, instead of running one at a time.
To capture and display query execution plans using Profiler, you must create a trace using the following configuration:
Events to Capture
Performance: Execution Plan
Performance: Show Plan All
Performance: Show Plan Statistics
Performance: Show Plan Text
Data Columns to Display
StartTime
Duration
TextData
CPU
Reads
Writes
Filters
Duration. You will want to specify a maximum duration, such as 5 seconds, so that you don't get flooded with too much data.
Of course, you can capture more information than is listed above in your trace; the above is only a guideline. But keep in mind that you don't want to capture too much data, as this could have a negative affect on your server's performance as the trace is being run.
*****
If you use the OPTION FAST hint in a query, be aware that the Execution Plan results may not be what you expect. The Execution Plan that you get is based on the results of using the FAST hint, not the actual Execution Plan for the full query.
The FAST hint is used to tell the Query Optimizer to return the specified number of rows as fast as possible, even if this hurts the overall performance of the query. The purpose of this hint is to return a specified number of records quickly in order to produce an illusion of speed for the user. Once the specified number of rows is returned, the remaining rows are retuned as they would be normally.
So if you are using the FAST hint, the execution plan will be for only those rows that are returned FAST, not for all of the rows. If you want to see the execution plan for all the rows, then you must perform an Execution Plan of the query with the hint removed.
Subscribe to:
Posts (Atom)