Problem
I'm trying to design indexes on a new Customer table and I have three separate query searches to satisfy. One query search is by lastname. The 2nd query search is by last name and first name. The last query search is by lastname and an active flag. How should I create my indexes?
Solution
I've come across more instances than I'd like of data models where the original database developer would solve this problem by creating three separate indexes as follows:
create index ix_customer_lastname on dbo.Customer(lastname)
GO
create index ix_customer_lastname_firstname on dbo.Customer(lastname, firstname)
GO
create index ix_customer_lastname_activesw on dbo.Customer(lastname, activesw)
GO
The reality is, in this case you only need one!
The following queries
select * from dbo.Customer where lastname = 'Washington'
GO
select * from dbo.Customer where lastname = 'Washington' and activesw = 1
GO
select * from dbo.Customer where firstname = 'George' and lastname = 'Washington'
GO
select * from dbo.Customer
where firstname = 'George' and lastname = 'Washington' and activesw = 1
GO
can all be satisfied using the following single index
create index ix_customer_lastname on dbo.Customer(lastname, firstname, activesw)
GO
The lead column of each index is lastname and this is the most important one. SQL Server keeps frequency distribution statistics in the form of a histogram on the lead column of every index. It will also keep secondary selectivity information for the additional index column permutations.
Having redundant indexes in your database wastes SQL Server system resources because the database engine has to maintain more data structures than it needs to. Consider a new Customer being added to the database. The new last name inserted would have to be accounted for in the logical ordering of each index resulting in unnecessary overhead to maintain each of these indexes. Furthermore, redundant indexes waste disk space.
Let's create a Customer table and some indexes to illustrate these concepts. This script will create the table and 10,000 customer rows.
set nocount on
go
create table dbo.Customer (customerid int identity primary key,
firstname char(40), lastname char(40),
address1 char(500), city char(40), state char(20), activesw bit)
declare @counter int, @id varchar(4)
select @counter = 1
while (@counter <= 10000)
begin
select @id = cast(@counter as varchar)
insert into customer(firstname, lastname, address1, city, activesw)
select 'f'+@id, 'l'+@id, 'a'+@id, 'c'+@id, 1
select @counter = @counter + 1
end
create index ix_customer_lastname on dbo.Customer(lastname)
create index ix_customer_lastname_firstname on dbo.Customer(lastname, firstname)
create index ix_customer_lastname_activesw on dbo.Customer(lastname, activesw)
go
Now let's examine the space used by the table's indexes
We see that the indexes take up 2kb of disk space
Now let's run the following queries and examine the optimizer's index selections
set showplan_text on
go
select customerid from dbo.Customer where lastname = 'l22'
select customerid from dbo.Customer where lastname = 'l22' and activesw = 1
select customerid from dbo.Customer where firstname = 'f22' and lastname = 'l22'
select customerid from dbo.Customer where firstname = 'f22' and lastname = 'l22' and activesw = 1
Now these are interesting. The first and second queries used index ix_customer_lastname_activesw. The third query used index ix_customer_lastname_firstname. For the last query, the optimizer decided to use ix_customer_lastname_firstname as well and decided to do a lookup to get the activesw value. If you haven't noticed yet, index ix_customer_lastname was not used to satisfy any of these queries. It's just sitting around, literally taking up space.
Now let's drop these indexes and add a single index to satisfy the same four queries
drop index dbo.Customer.ix_customer_lastname
drop index dbo.Customer.ix_customer_lastname_firstname
drop index dbo.Customer.ix_customer_lastname_activesw
create index ix_customer_lastname on dbo.Customer(lastname, firstname, activesw)
If we now re-run sp_spaceused, we'll see that the index space used by the table has been reduced by over 100%
Now let's re-run the queries we ran earlier and examine the new execution plans
As we now see, the single index satisfies all four queries. In addition, the last query that performed a lookup for the activesw now reads it directly from the index and does not resort to searching the clustered table for the value.
By carefully defining your indexes you can save the database engine some work, save some space on your disks, and still have high performing queries.
Thursday, September 11, 2008
Understanding SQL Server Indexing (clustered and nonclustered)
Problem
With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non clustered indexes. Based on the number of questions that we have received, this tip will discuss the differences of indexes and some general guidelines around indexing.
Solution
From a simple standpoint SQL Server offers two types of indexes clustered and non-clustered. In its simplest definition a clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes. If a table does not have a clustered index it is referred to as a Heap. So what does this actually mean?
To further clarify this lets take a look at what indexes do and why they are important. The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. If you have ever looked at a query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.
Here are some examples of queries that were run. These were run against table dbo.contact that has about 20,000 rows of data. Each of these queries was run with no index as well as with a clustered and non-clustered indexes. To show the impact a graphical query plan has been provided. This can be created by highlighting the query and pressing Control-L (Ctrl-L) in the query window.
1 - Table with no indexes
When the query runs, since there are no indexes, SQL Server does a Table Scan against the table to look through every row to determine if any of the records have a lastname of "Adams". This query has an Estimated Subtree Cost of 0.437103. This is the cost to SQL Server to execute the query. The lower the number the less resource intensive for SQL Server.
2- Table with non-clustered index on lastname column
When this query runs, SQL Server uses the index to do an Index Seek and then it needs to do a RID Lookup to get the actual data. You can see from the Estimated Subtree Cost of 0.263888 that this is faster then the above query.
3- Table with clustered index on lastname column
When this query runs, SQL Server does an Index Seek and since the index points to the actual data pages, the Estimated Subtree Cost is only 0.0044572. This is by far the fastest access method for this type of query.
4- Table with non-clustered index on lastname column
In this query we are only requesting column lastname. Since this query can be handled by just the non-clustered index (covering query), SQL Server does not need to access the actual data pages. Based on this query the Estimated Subtree Cost is only 0.0033766. As you can see this even better then example #3.
To take this a step further, the below output is based on having a clustered index on lastname and no non-clustered index. You can see that the subtree cost is still the same as returning all of the columns even though we are only selecting one column. So the non-clustered index performs better.
5- Table with clustered index on contactId and non-clustered on lastname column
For this query we now have two indexes. A clustered and non-clustered. The query that is run in the same as example 2. From this output you can see that the RID Lookup has been replaced with a Clustered Index Seek. Overall it is the same type of operations, except using the Clustered Index. The subtree cost is 0.264017. This is a little better then example 2.
So based on these examples you can see the benefits of using indexes. This example table only had 20,000 rows of data, so this is quite small compared to most database tables. You can see the impact this would have on very large tables. The first idea that would come to mind is to use all clustered indexes, but because this is where the actual data is stored a table can only have one clustered index. The second thought may be to index every column. Although this maybe helpful when querying the data, there is also the overhead of maintaining all of these indexes every time you do an INSERT, UPDATE or DELETE.
With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non clustered indexes. Based on the number of questions that we have received, this tip will discuss the differences of indexes and some general guidelines around indexing.
Solution
From a simple standpoint SQL Server offers two types of indexes clustered and non-clustered. In its simplest definition a clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes. If a table does not have a clustered index it is referred to as a Heap. So what does this actually mean?
To further clarify this lets take a look at what indexes do and why they are important. The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. If you have ever looked at a query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.
Here are some examples of queries that were run. These were run against table dbo.contact that has about 20,000 rows of data. Each of these queries was run with no index as well as with a clustered and non-clustered indexes. To show the impact a graphical query plan has been provided. This can be created by highlighting the query and pressing Control-L (Ctrl-L) in the query window.
1 - Table with no indexes
When the query runs, since there are no indexes, SQL Server does a Table Scan against the table to look through every row to determine if any of the records have a lastname of "Adams". This query has an Estimated Subtree Cost of 0.437103. This is the cost to SQL Server to execute the query. The lower the number the less resource intensive for SQL Server.
2- Table with non-clustered index on lastname column
When this query runs, SQL Server uses the index to do an Index Seek and then it needs to do a RID Lookup to get the actual data. You can see from the Estimated Subtree Cost of 0.263888 that this is faster then the above query.
3- Table with clustered index on lastname column
When this query runs, SQL Server does an Index Seek and since the index points to the actual data pages, the Estimated Subtree Cost is only 0.0044572. This is by far the fastest access method for this type of query.
4- Table with non-clustered index on lastname column
In this query we are only requesting column lastname. Since this query can be handled by just the non-clustered index (covering query), SQL Server does not need to access the actual data pages. Based on this query the Estimated Subtree Cost is only 0.0033766. As you can see this even better then example #3.
To take this a step further, the below output is based on having a clustered index on lastname and no non-clustered index. You can see that the subtree cost is still the same as returning all of the columns even though we are only selecting one column. So the non-clustered index performs better.
5- Table with clustered index on contactId and non-clustered on lastname column
For this query we now have two indexes. A clustered and non-clustered. The query that is run in the same as example 2. From this output you can see that the RID Lookup has been replaced with a Clustered Index Seek. Overall it is the same type of operations, except using the Clustered Index. The subtree cost is 0.264017. This is a little better then example 2.
So based on these examples you can see the benefits of using indexes. This example table only had 20,000 rows of data, so this is quite small compared to most database tables. You can see the impact this would have on very large tables. The first idea that would come to mind is to use all clustered indexes, but because this is where the actual data is stored a table can only have one clustered index. The second thought may be to index every column. Although this maybe helpful when querying the data, there is also the overhead of maintaining all of these indexes every time you do an INSERT, UPDATE or DELETE.
Wednesday, September 10, 2008
Advanced SQL Injection In SQL Server Applications
This document discusses in detail the common 'SQL injection' technique, as it applies to the popular Microsoft Internet Information Server/Active Server Pages/SQL Server platform. It discusses the various ways in which SQL can be 'injected' into the application and addresses some of the data validation and database lockdown issues that are related to this class of attack.
The paper is intended to be read by both developers of web applications which communicate with databases and by security professionals whose role includes auditing these web applications.
[Introduction]
Structured Query Language ('SQL') is a textual language used to interact with relational databases. There are many varieties of SQL; most dialects that are in common use at the moment are loosely based around SQL-92, the most recent ANSI standard. The typical unit of execution of SQL is the 'query', which is a collection of statements that typically return a single 'result set'. SQL statements can modify the structure of databases (using Data Definition Language statements, or 'DDL') and manipulate the contents of databases (using Data Manipulation Language statements, or 'DML'). In this paper, we will be specifically discussing Transact-SQL, the dialect of SQL used by Microsoft SQL Server.
SQL Injection occurs when an attacker is able to insert a series of SQL statements into a 'query' by manipulating data input into an application.
A typical SQL statement looks like this:
select id, forename, surname from authors
This statement will retrieve the 'id', 'forename' and 'surname' columns from the 'authors' table, returning all rows in the table. The 'result set' could be restricted to a specific 'author' like this:
select id, forename, surname from authors where forename = 'john' and surname = 'smith'
An important point to note here is that the string literals 'john' and 'smith' are delimited with single quotes. Presuming that the 'forename' and 'surname' fields are being gathered from user-supplied input, an attacker might be able to 'inject' some SQL into this query, by inputting values into the application like this:
Forename: jo'hn
Surname: smith
The 'query string' becomes this:
select id, forename, surname from authors where forename = 'jo'hn' and
Page 3
surname = 'smith'
When the database attempts to run this query, it is likely to return an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'hn'.
The reason for this is that the insertion of the 'single quote' character 'breaks out' of the single-quote delimited data. The database then tried to execute 'hn' and failed. If the attacker specified input like this:
Forename: jo'; drop table authors--
Surname:
…the authors table would be deleted, for reasons that we will go into later.
[Stored Procedures]
Traditional wisdom holds that if an ASP application uses stored procedures in the database, that SQL injection is not possible. This is a half-truth, and it depends on the manner in which the stored procedure is called from the ASP script.
Essentially, if a parameterised query is run, and the user-supplied parameters are passed safely to the query, then SQL injection is typically impossible. However, if the attacker can exert any influence over the non - data parts of the query string that is run, it is likely that they will be able to control the database.
Good general rules are:
• If the ASP script creates a SQL query string that is submitted to the server, it is vulnerable to SQL injection, *even if* it uses stored procedures
• If the ASP script uses a procedure object that wraps the assignment of parameters to a stored procedure (such as the ADO command object, used with the Parameters collection) then it is generally safe, though this depends on the object's implementation.
Obviously, best practice is still to validate all user supplied input, since new attack techniques are being discovered all the time.
To illustrate the stored procedure query injection point, execute the following SQL string:
sp_who '1' select * from sysobjects
or
sp_who '1'; select * from sysobjects
The paper is intended to be read by both developers of web applications which communicate with databases and by security professionals whose role includes auditing these web applications.
[Introduction]
Structured Query Language ('SQL') is a textual language used to interact with relational databases. There are many varieties of SQL; most dialects that are in common use at the moment are loosely based around SQL-92, the most recent ANSI standard. The typical unit of execution of SQL is the 'query', which is a collection of statements that typically return a single 'result set'. SQL statements can modify the structure of databases (using Data Definition Language statements, or 'DDL') and manipulate the contents of databases (using Data Manipulation Language statements, or 'DML'). In this paper, we will be specifically discussing Transact-SQL, the dialect of SQL used by Microsoft SQL Server.
SQL Injection occurs when an attacker is able to insert a series of SQL statements into a 'query' by manipulating data input into an application.
A typical SQL statement looks like this:
select id, forename, surname from authors
This statement will retrieve the 'id', 'forename' and 'surname' columns from the 'authors' table, returning all rows in the table. The 'result set' could be restricted to a specific 'author' like this:
select id, forename, surname from authors where forename = 'john' and surname = 'smith'
An important point to note here is that the string literals 'john' and 'smith' are delimited with single quotes. Presuming that the 'forename' and 'surname' fields are being gathered from user-supplied input, an attacker might be able to 'inject' some SQL into this query, by inputting values into the application like this:
Forename: jo'hn
Surname: smith
The 'query string' becomes this:
select id, forename, surname from authors where forename = 'jo'hn' and
Page 3
surname = 'smith'
When the database attempts to run this query, it is likely to return an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'hn'.
The reason for this is that the insertion of the 'single quote' character 'breaks out' of the single-quote delimited data. The database then tried to execute 'hn' and failed. If the attacker specified input like this:
Forename: jo'; drop table authors--
Surname:
…the authors table would be deleted, for reasons that we will go into later.
[Stored Procedures]
Traditional wisdom holds that if an ASP application uses stored procedures in the database, that SQL injection is not possible. This is a half-truth, and it depends on the manner in which the stored procedure is called from the ASP script.
Essentially, if a parameterised query is run, and the user-supplied parameters are passed safely to the query, then SQL injection is typically impossible. However, if the attacker can exert any influence over the non - data parts of the query string that is run, it is likely that they will be able to control the database.
Good general rules are:
• If the ASP script creates a SQL query string that is submitted to the server, it is vulnerable to SQL injection, *even if* it uses stored procedures
• If the ASP script uses a procedure object that wraps the assignment of parameters to a stored procedure (such as the ADO command object, used with the Parameters collection) then it is generally safe, though this depends on the object's implementation.
Obviously, best practice is still to validate all user supplied input, since new attack techniques are being discovered all the time.
To illustrate the stored procedure query injection point, execute the following SQL string:
sp_who '1' select * from sysobjects
or
sp_who '1'; select * from sysobjects
Tuesday, September 9, 2008
Importing text files into tables, and Creating Text Files using BCP
[Importing text files into tables]
Using the 'bulk insert' statement, it is possible to insert a text file into a temporary table. Simply create the table like this:
create table foo( line varchar(8000) )
…and then run an bulk insert to insert the data from the file, like this:
bulk insert foo from 'c:\inetpub\wwwroot\process_login.asp'
…the data can then be retrieved using any of the above error message techniques, or by a 'union' select, combining the data in the text file with the data that is normally returned by the application. This is useful for obtaining the source code of scripts stored on the database server, or possibly the source of ASP scripts.
[Creating Text Files using BCP]
It is fairly easy to create arbitrary text files using the 'opposite' technique to the 'bulk insert'. Unfortunately this requires a command line tool, 'bcp', the 'bulk copy program'
Since bcp accesses the database from outside the SQL Server process, it requires a login. This is typically not difficult to obtain, since the attacker can probably create one anyway, or take advantage of 'integrated' security mode, if the server is configured to use it.
The command line format is as follows:
bcp "SELECT * FROM test..foo" queryout c:\inetpub\wwwroot\runcommand.asp -c -Slocalhost -Usa -Pfoobar
The 'S' parameter is the server on which to run the query, the 'U' is the username and the 'P' is the password, in this case 'foobar'.
Using the 'bulk insert' statement, it is possible to insert a text file into a temporary table. Simply create the table like this:
create table foo( line varchar(8000) )
…and then run an bulk insert to insert the data from the file, like this:
bulk insert foo from 'c:\inetpub\wwwroot\process_login.asp'
…the data can then be retrieved using any of the above error message techniques, or by a 'union' select, combining the data in the text file with the data that is normally returned by the application. This is useful for obtaining the source code of scripts stored on the database server, or possibly the source of ASP scripts.
[Creating Text Files using BCP]
It is fairly easy to create arbitrary text files using the 'opposite' technique to the 'bulk insert'. Unfortunately this requires a command line tool, 'bcp', the 'bulk copy program'
Since bcp accesses the database from outside the SQL Server process, it requires a login. This is typically not difficult to obtain, since the attacker can probably create one anyway, or take advantage of 'integrated' security mode, if the server is configured to use it.
The command line format is as follows:
bcp "SELECT * FROM test..foo" queryout c:\inetpub\wwwroot\runcommand.asp -c -Slocalhost -Usa -Pfoobar
The 'S' parameter is the server on which to run the query, the 'U' is the username and the 'P' is the password, in this case 'foobar'.
Thursday, September 4, 2008
Identifying Queries Running Slower Than Normal with SQL Server 2005
Introduction
As part of your normal monitoring it makes sense to ensure the runtime duration your queries does not deviate significantly from the expected norm. Identifying these queries will allow you to target your efforts and correct any performance problems in a timely manner.
A query can start to run slower for many reasons, including: an increased number of users (resulting in more waiting on resources), increased volume of data to process, too much context switching for parallel queries, and slower hardware.
When a query is run, its query plan is cached to improve subsequent run of the query. Many interesting details relating to the query are recorded, these details can be accessed via SQL Server’s Dynamic Management Views (DMVs). In particular the DMV sys.dm_exec_query_stats contains performance statistics for cached query plans. This DMV has everything we need to determine if a query is running slower than normal.
Details of the sys.dm_exec_query_stats columns relevant to us, are described in figure 1 (edited and taken from SQL Server 2005 Books online).
Column name Data type Description
statement_start_offset int Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offset int Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. A value of -1 indicates the end of the batch.
plan_handle varbinary(64) A token that refers to the compiled plan that the query is part of. This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.
execution_count bigint Number of times that the plan has been executed since it was last compiled.
total_worker_time bigint Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time bigint CPU time, in microseconds, that was consumed the last time the plan was executed.
total_physical_reads bigint Total number of physical reads performed by executions of this plan since it was compiled.
last_physical_reads bigint Number of physical reads performed the last time the plan was executed.
total_logical_writes bigint Total number of logical writes performed by executions of this plan since it was compiled.
last_logical_writes bigint Number of logical writes performed the last time the plan was executed.
total_logical_reads bigint Total number of logical reads performed by executions of this plan since it was compiled.
last_logical_reads bigint Number of logical reads performed the last time the plan was executed.
total_elapsed_time bigint Total elapsed time, in microseconds, for completed executions of this plan.
last_elapsed_time bigint Elapsed time, in microseconds, for the most recently completed execution of this plan.
Figure 1 shows the sys.dm_exec_query_stats fields used by the Query Time Delta utility
The _elapsed_time columns record how long the query took, taking into account any waiting on resources.
The _worker_time columns record how long the query took, ignoring any waiting on resources, instead it is concerned only with the time spent using the CPU.
In this article, we will look only at queries that are slower based on the _worker_time columns. This will allow us to ignore many of the variable factors that affect query duration, allowing us instead to create and discuss a simplified utility to quickly determine if a query is running slower than normal, based on the amount of CPU it has used.
Query Time Delta Utility
To determine if a query is running slower than normal, we need to calculate the average duration of the query and compare it to its last run value, adjusted for the amount of data it has processed.
The number of runs (execution_count) and the total worker time (total_worker_time) both include data relating to the last run, thus in order to create a more representative average, the number of runs is reduced by 1, and the total worker time has the last run time subtracted from it.
The average duration of the query is calculated by subtracting the last_worker_time from the total_worker_time and dividing the result by execution_count – 1.
The algorithm used to determine if a query is running slower than average is:
%Slower = (LAST – AVG) x 100 / AVG where LAST represents the duration of last query, and AVG represents the average duration of the query
For example, if the average query duration (AVG) is 40ms, and the last query duration (LAST) is 160ms, then %Slower = (160 – 40) x 100 / 40 = 300%
The duration of a query is affected by the amount of data it processes, this volume of data is reflected in the various IO columns of sys.dm_exec_query_stats. This utility calculates the slowness of a query (Impedance) by combining the duration of the query with the amount of IO performed. Again the ‘total’ IO values include the last run values, and thus need to be adjusted.
To see the SQL text of the individual query statement together with its parent query we call the Dynamic Management Function (DMF) sys.dm_exec_sql_textpassing it the query’s plan_handle as the function parameter. We can extract the individual query using the statement start and end offset values that are part of the DMV sys.dm_exec_query_stats.The DMF sys.dm_exec_sql_textcontains the id of the database the query related to, this is also shown. Note the WHERE clause only selects queries that have been executed at least twice.
For maintainability, the calculation of “% IO deviation” is separated from the main query. The data is sorted to show the TOP 100 slowest queries, by Impedance, this is a reflection of time deviation taking into account IO deviation.
Running the utility on my SQL Server gives the results given in Figure 2.
Figure 2 Output from the Query Time Delta utility.
The results show the individual statements, within a stored procedure or batch, that are running slower than normal. Adhoc or prepared queries are not associated with a database, hence the database name column is set to null.
Discussion
The utility described in this article allows you to quickly identify which queries are running slower than expected, after taking into account the volume of data processed.
Since this utility concerns itself with _worker_time rather than _elapsed_time, we can ignore many of the reasons why the query might be running slower (e.g. concurrency and blocking), and concentrate on why the query is using more CPU (i.e. worker_time) to fulfill its needs.
Perhaps the most common reason why a query starts to run slower (often suddenly) relates to “parameters sniffing”. Here the query plan is optimised based on the value of the parameters when the query is first used. You can imagine a parameter that retrieves only a few rows would produce a different plan than a parameter that would retrieve many rows.
Various solutions exist to create cached plans that are more optimal for the passed parameters, including:
1. Execute the stored procedure with the recompile option, this does not replace the query’s current cached plan.
e.g. EXEC sprocName WITH RECOMPILE
2. Add WITH RECOMPILE to the body of the stored procedure signature, this causes the stored procedure to be recompiled on every usage.
e.g. CREATE PROC sprocName WITH RECOMPILE
3. Use the optimizer hint RECOMPILE on an individual query within a stored procedure.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (RECOMPILE)
4. Use the optimizer hint OPTIMIZE FOR. This allows you to create a plan based on more typical parameter values.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (OPTIMIZE FOR (@param1=71))
5. Use Plan Guides, these provide a degree of stability by allowing you to use an existing optimised query plan.
Each of these proposed solutions has advantages and disadvantages, and is left as an exercise for the reader to investigate them further.
Further work
If you’re interesting in the effect of changing concurrency patterns, blocking, waits etc you might want to look at elapsed time rather than the worker time. You can see the effects of these by replacing the _worker_time variables with _elapsed_time variables in the Utility script.
Various changes (e.g. adding indexes, updating statistics) can cause a query to be recompiled, resulting in a new cache plan, thus making comparisons of a query over time difficult. In this case, you should keep a history for comparison purposes. Keeping a history is also useful because DMV data is transient, often being cleared due to server restarts or memory pressures.
Conclusion
The utility described in this article will help identify easily and quickly those queries that have taken longer to run than normal, and should prove valuable in the everyday work of the SQL Server DBA/developer.
Credits
Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. He can be contacted at Ian_Stirk@yahoo.com.
Code:
CREATE PROC [dbo].[dba_QueryTimeDelta]
AS
/*----------------------------------------------------------------------
Purpose: Identify queries that are running slower than normal
, when taking into account IO volumes.
------------------------------------------------------------------------
Parameters: None.
Revision History:
13/01/2008 Ian_Stirk@yahoo.com Initial version
Example Usage:
1. exec YourServerName.master.dbo.dba_QueryTimeDelta
----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Identify queries running slower than normal.
SELECT TOP 100
[Runs] = qs.execution_count
--, [Total time] = qs.total_worker_time - qs.last_worker_time
, [Avg time] = (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)
, [Last time] = qs.last_worker_time
, [Time Deviation] = (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [% Time Deviation] =
CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [Last IO] = last_logical_reads + last_logical_writes + last_physical_reads
, [Avg IO] = ((total_logical_reads + total_logical_writes + total_physical_reads)
- (last_logical_reads + last_logical_writes + last_physical_reads))
/ (qs.execution_count - 1)
, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
, [Parent Query] = qt.text
, [DatabaseName] = DB_NAME(qt.dbid)
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
ORDER BY [% Time Deviation] DESC
-- Calculate the [IO Deviation] and [% IO Deviation].
-- Negative values means we did less I/O than average.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation] = [Last IO] - [Avg IO]
, [% IO Deviation] =
CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC
-- Extract items where [% Time deviation] less [% IO deviation] is 'large'
-- These queries are slow running, even when we take into account IO deviation.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC
-- Tidy up.
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO
END
As part of your normal monitoring it makes sense to ensure the runtime duration your queries does not deviate significantly from the expected norm. Identifying these queries will allow you to target your efforts and correct any performance problems in a timely manner.
A query can start to run slower for many reasons, including: an increased number of users (resulting in more waiting on resources), increased volume of data to process, too much context switching for parallel queries, and slower hardware.
When a query is run, its query plan is cached to improve subsequent run of the query. Many interesting details relating to the query are recorded, these details can be accessed via SQL Server’s Dynamic Management Views (DMVs). In particular the DMV sys.dm_exec_query_stats contains performance statistics for cached query plans. This DMV has everything we need to determine if a query is running slower than normal.
Details of the sys.dm_exec_query_stats columns relevant to us, are described in figure 1 (edited and taken from SQL Server 2005 Books online).
Column name Data type Description
statement_start_offset int Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offset int Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. A value of -1 indicates the end of the batch.
plan_handle varbinary(64) A token that refers to the compiled plan that the query is part of. This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.
execution_count bigint Number of times that the plan has been executed since it was last compiled.
total_worker_time bigint Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time bigint CPU time, in microseconds, that was consumed the last time the plan was executed.
total_physical_reads bigint Total number of physical reads performed by executions of this plan since it was compiled.
last_physical_reads bigint Number of physical reads performed the last time the plan was executed.
total_logical_writes bigint Total number of logical writes performed by executions of this plan since it was compiled.
last_logical_writes bigint Number of logical writes performed the last time the plan was executed.
total_logical_reads bigint Total number of logical reads performed by executions of this plan since it was compiled.
last_logical_reads bigint Number of logical reads performed the last time the plan was executed.
total_elapsed_time bigint Total elapsed time, in microseconds, for completed executions of this plan.
last_elapsed_time bigint Elapsed time, in microseconds, for the most recently completed execution of this plan.
Figure 1 shows the sys.dm_exec_query_stats fields used by the Query Time Delta utility
The _elapsed_time columns record how long the query took, taking into account any waiting on resources.
The _worker_time columns record how long the query took, ignoring any waiting on resources, instead it is concerned only with the time spent using the CPU.
In this article, we will look only at queries that are slower based on the _worker_time columns. This will allow us to ignore many of the variable factors that affect query duration, allowing us instead to create and discuss a simplified utility to quickly determine if a query is running slower than normal, based on the amount of CPU it has used.
Query Time Delta Utility
To determine if a query is running slower than normal, we need to calculate the average duration of the query and compare it to its last run value, adjusted for the amount of data it has processed.
The number of runs (execution_count) and the total worker time (total_worker_time) both include data relating to the last run, thus in order to create a more representative average, the number of runs is reduced by 1, and the total worker time has the last run time subtracted from it.
The average duration of the query is calculated by subtracting the last_worker_time from the total_worker_time and dividing the result by execution_count – 1.
The algorithm used to determine if a query is running slower than average is:
%Slower = (LAST – AVG) x 100 / AVG where LAST represents the duration of last query, and AVG represents the average duration of the query
For example, if the average query duration (AVG) is 40ms, and the last query duration (LAST) is 160ms, then %Slower = (160 – 40) x 100 / 40 = 300%
The duration of a query is affected by the amount of data it processes, this volume of data is reflected in the various IO columns of sys.dm_exec_query_stats. This utility calculates the slowness of a query (Impedance) by combining the duration of the query with the amount of IO performed. Again the ‘total’ IO values include the last run values, and thus need to be adjusted.
To see the SQL text of the individual query statement together with its parent query we call the Dynamic Management Function (DMF) sys.dm_exec_sql_textpassing it the query’s plan_handle as the function parameter. We can extract the individual query using the statement start and end offset values that are part of the DMV sys.dm_exec_query_stats.The DMF sys.dm_exec_sql_textcontains the id of the database the query related to, this is also shown. Note the WHERE clause only selects queries that have been executed at least twice.
For maintainability, the calculation of “% IO deviation” is separated from the main query. The data is sorted to show the TOP 100 slowest queries, by Impedance, this is a reflection of time deviation taking into account IO deviation.
Running the utility on my SQL Server gives the results given in Figure 2.
Figure 2 Output from the Query Time Delta utility.
The results show the individual statements, within a stored procedure or batch, that are running slower than normal. Adhoc or prepared queries are not associated with a database, hence the database name column is set to null.
Discussion
The utility described in this article allows you to quickly identify which queries are running slower than expected, after taking into account the volume of data processed.
Since this utility concerns itself with _worker_time rather than _elapsed_time, we can ignore many of the reasons why the query might be running slower (e.g. concurrency and blocking), and concentrate on why the query is using more CPU (i.e. worker_time) to fulfill its needs.
Perhaps the most common reason why a query starts to run slower (often suddenly) relates to “parameters sniffing”. Here the query plan is optimised based on the value of the parameters when the query is first used. You can imagine a parameter that retrieves only a few rows would produce a different plan than a parameter that would retrieve many rows.
Various solutions exist to create cached plans that are more optimal for the passed parameters, including:
1. Execute the stored procedure with the recompile option, this does not replace the query’s current cached plan.
e.g. EXEC sprocName WITH RECOMPILE
2. Add WITH RECOMPILE to the body of the stored procedure signature, this causes the stored procedure to be recompiled on every usage.
e.g. CREATE PROC sprocName WITH RECOMPILE
3. Use the optimizer hint RECOMPILE on an individual query within a stored procedure.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (RECOMPILE)
4. Use the optimizer hint OPTIMIZE FOR. This allows you to create a plan based on more typical parameter values.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (OPTIMIZE FOR (@param1=71))
5. Use Plan Guides, these provide a degree of stability by allowing you to use an existing optimised query plan.
Each of these proposed solutions has advantages and disadvantages, and is left as an exercise for the reader to investigate them further.
Further work
If you’re interesting in the effect of changing concurrency patterns, blocking, waits etc you might want to look at elapsed time rather than the worker time. You can see the effects of these by replacing the _worker_time variables with _elapsed_time variables in the Utility script.
Various changes (e.g. adding indexes, updating statistics) can cause a query to be recompiled, resulting in a new cache plan, thus making comparisons of a query over time difficult. In this case, you should keep a history for comparison purposes. Keeping a history is also useful because DMV data is transient, often being cleared due to server restarts or memory pressures.
Conclusion
The utility described in this article will help identify easily and quickly those queries that have taken longer to run than normal, and should prove valuable in the everyday work of the SQL Server DBA/developer.
Credits
Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. He can be contacted at Ian_Stirk@yahoo.com.
Code:
CREATE PROC [dbo].[dba_QueryTimeDelta]
AS
/*----------------------------------------------------------------------
Purpose: Identify queries that are running slower than normal
, when taking into account IO volumes.
------------------------------------------------------------------------
Parameters: None.
Revision History:
13/01/2008 Ian_Stirk@yahoo.com Initial version
Example Usage:
1. exec YourServerName.master.dbo.dba_QueryTimeDelta
----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Identify queries running slower than normal.
SELECT TOP 100
[Runs] = qs.execution_count
--, [Total time] = qs.total_worker_time - qs.last_worker_time
, [Avg time] = (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)
, [Last time] = qs.last_worker_time
, [Time Deviation] = (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [% Time Deviation] =
CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [Last IO] = last_logical_reads + last_logical_writes + last_physical_reads
, [Avg IO] = ((total_logical_reads + total_logical_writes + total_physical_reads)
- (last_logical_reads + last_logical_writes + last_physical_reads))
/ (qs.execution_count - 1)
, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
, [Parent Query] = qt.text
, [DatabaseName] = DB_NAME(qt.dbid)
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
ORDER BY [% Time Deviation] DESC
-- Calculate the [IO Deviation] and [% IO Deviation].
-- Negative values means we did less I/O than average.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation] = [Last IO] - [Avg IO]
, [% IO Deviation] =
CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC
-- Extract items where [% Time deviation] less [% IO deviation] is 'large'
-- These queries are slow running, even when we take into account IO deviation.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC
-- Tidy up.
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO
END
Identifying Queries Running Slower Than Normal with SQL Server 2005
Introduction
As part of your normal monitoring it makes sense to ensure the runtime duration your queries does not deviate significantly from the expected norm. Identifying these queries will allow you to target your efforts and correct any performance problems in a timely manner.
A query can start to run slower for many reasons, including: an increased number of users (resulting in more waiting on resources), increased volume of data to process, too much context switching for parallel queries, and slower hardware.
When a query is run, its query plan is cached to improve subsequent run of the query. Many interesting details relating to the query are recorded, these details can be accessed via SQL Server’s Dynamic Management Views (DMVs). In particular the DMV sys.dm_exec_query_stats contains performance statistics for cached query plans. This DMV has everything we need to determine if a query is running slower than normal.
Details of the sys.dm_exec_query_stats columns relevant to us, are described in figure 1 (edited and taken from SQL Server 2005 Books online).
Column name Data type Description
statement_start_offset int Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offset int Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. A value of -1 indicates the end of the batch.
plan_handle varbinary(64) A token that refers to the compiled plan that the query is part of. This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.
execution_count bigint Number of times that the plan has been executed since it was last compiled.
total_worker_time bigint Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time bigint CPU time, in microseconds, that was consumed the last time the plan was executed.
total_physical_reads bigint Total number of physical reads performed by executions of this plan since it was compiled.
last_physical_reads bigint Number of physical reads performed the last time the plan was executed.
total_logical_writes bigint Total number of logical writes performed by executions of this plan since it was compiled.
last_logical_writes bigint Number of logical writes performed the last time the plan was executed.
total_logical_reads bigint Total number of logical reads performed by executions of this plan since it was compiled.
last_logical_reads bigint Number of logical reads performed the last time the plan was executed.
total_elapsed_time bigint Total elapsed time, in microseconds, for completed executions of this plan.
last_elapsed_time bigint Elapsed time, in microseconds, for the most recently completed execution of this plan.
Figure 1 shows the sys.dm_exec_query_stats fields used by the Query Time Delta utility
The _elapsed_time columns record how long the query took, taking into account any waiting on resources.
The _worker_time columns record how long the query took, ignoring any waiting on resources, instead it is concerned only with the time spent using the CPU.
In this article, we will look only at queries that are slower based on the _worker_time columns. This will allow us to ignore many of the variable factors that affect query duration, allowing us instead to create and discuss a simplified utility to quickly determine if a query is running slower than normal, based on the amount of CPU it has used.
Query Time Delta Utility
To determine if a query is running slower than normal, we need to calculate the average duration of the query and compare it to its last run value, adjusted for the amount of data it has processed.
The number of runs (execution_count) and the total worker time (total_worker_time) both include data relating to the last run, thus in order to create a more representative average, the number of runs is reduced by 1, and the total worker time has the last run time subtracted from it.
The average duration of the query is calculated by subtracting the last_worker_time from the total_worker_time and dividing the result by execution_count – 1.
The algorithm used to determine if a query is running slower than average is:
%Slower = (LAST – AVG) x 100 / AVG where LAST represents the duration of last query, and AVG represents the average duration of the query
For example, if the average query duration (AVG) is 40ms, and the last query duration (LAST) is 160ms, then %Slower = (160 – 40) x 100 / 40 = 300%
The duration of a query is affected by the amount of data it processes, this volume of data is reflected in the various IO columns of sys.dm_exec_query_stats. This utility calculates the slowness of a query (Impedance) by combining the duration of the query with the amount of IO performed. Again the ‘total’ IO values include the last run values, and thus need to be adjusted.
To see the SQL text of the individual query statement together with its parent query we call the Dynamic Management Function (DMF) sys.dm_exec_sql_textpassing it the query’s plan_handle as the function parameter. We can extract the individual query using the statement start and end offset values that are part of the DMV sys.dm_exec_query_stats.The DMF sys.dm_exec_sql_textcontains the id of the database the query related to, this is also shown. Note the WHERE clause only selects queries that have been executed at least twice.
For maintainability, the calculation of “% IO deviation” is separated from the main query. The data is sorted to show the TOP 100 slowest queries, by Impedance, this is a reflection of time deviation taking into account IO deviation.
Running the utility on my SQL Server gives the results given in Figure 2.
Figure 2 Output from the Query Time Delta utility.
The results show the individual statements, within a stored procedure or batch, that are running slower than normal. Adhoc or prepared queries are not associated with a database, hence the database name column is set to null.
Discussion
The utility described in this article allows you to quickly identify which queries are running slower than expected, after taking into account the volume of data processed.
Since this utility concerns itself with _worker_time rather than _elapsed_time, we can ignore many of the reasons why the query might be running slower (e.g. concurrency and blocking), and concentrate on why the query is using more CPU (i.e. worker_time) to fulfill its needs.
Perhaps the most common reason why a query starts to run slower (often suddenly) relates to “parameters sniffing”. Here the query plan is optimised based on the value of the parameters when the query is first used. You can imagine a parameter that retrieves only a few rows would produce a different plan than a parameter that would retrieve many rows.
Various solutions exist to create cached plans that are more optimal for the passed parameters, including:
1. Execute the stored procedure with the recompile option, this does not replace the query’s current cached plan.
e.g. EXEC sprocName WITH RECOMPILE
2. Add WITH RECOMPILE to the body of the stored procedure signature, this causes the stored procedure to be recompiled on every usage.
e.g. CREATE PROC sprocName WITH RECOMPILE
3. Use the optimizer hint RECOMPILE on an individual query within a stored procedure.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (RECOMPILE)
4. Use the optimizer hint OPTIMIZE FOR. This allows you to create a plan based on more typical parameter values.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (OPTIMIZE FOR (@param1=71))
5. Use Plan Guides, these provide a degree of stability by allowing you to use an existing optimised query plan.
Each of these proposed solutions has advantages and disadvantages, and is left as an exercise for the reader to investigate them further.
Further work
If you’re interesting in the effect of changing concurrency patterns, blocking, waits etc you might want to look at elapsed time rather than the worker time. You can see the effects of these by replacing the _worker_time variables with _elapsed_time variables in the Utility script.
Various changes (e.g. adding indexes, updating statistics) can cause a query to be recompiled, resulting in a new cache plan, thus making comparisons of a query over time difficult. In this case, you should keep a history for comparison purposes. Keeping a history is also useful because DMV data is transient, often being cleared due to server restarts or memory pressures.
Conclusion
The utility described in this article will help identify easily and quickly those queries that have taken longer to run than normal, and should prove valuable in the everyday work of the SQL Server DBA/developer.
Credits
Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. He can be contacted at Ian_Stirk@yahoo.com.
Code:
CREATE PROC [dbo].[dba_QueryTimeDelta]
AS
/*----------------------------------------------------------------------
Purpose: Identify queries that are running slower than normal
, when taking into account IO volumes.
------------------------------------------------------------------------
Parameters: None.
Revision History:
13/01/2008 Ian_Stirk@yahoo.com Initial version
Example Usage:
1. exec YourServerName.master.dbo.dba_QueryTimeDelta
----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Identify queries running slower than normal.
SELECT TOP 100
[Runs] = qs.execution_count
--, [Total time] = qs.total_worker_time - qs.last_worker_time
, [Avg time] = (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)
, [Last time] = qs.last_worker_time
, [Time Deviation] = (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [% Time Deviation] =
CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [Last IO] = last_logical_reads + last_logical_writes + last_physical_reads
, [Avg IO] = ((total_logical_reads + total_logical_writes + total_physical_reads)
- (last_logical_reads + last_logical_writes + last_physical_reads))
/ (qs.execution_count - 1)
, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
, [Parent Query] = qt.text
, [DatabaseName] = DB_NAME(qt.dbid)
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
ORDER BY [% Time Deviation] DESC
-- Calculate the [IO Deviation] and [% IO Deviation].
-- Negative values means we did less I/O than average.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation] = [Last IO] - [Avg IO]
, [% IO Deviation] =
CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC
-- Extract items where [% Time deviation] less [% IO deviation] is 'large'
-- These queries are slow running, even when we take into account IO deviation.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC
-- Tidy up.
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO
END
As part of your normal monitoring it makes sense to ensure the runtime duration your queries does not deviate significantly from the expected norm. Identifying these queries will allow you to target your efforts and correct any performance problems in a timely manner.
A query can start to run slower for many reasons, including: an increased number of users (resulting in more waiting on resources), increased volume of data to process, too much context switching for parallel queries, and slower hardware.
When a query is run, its query plan is cached to improve subsequent run of the query. Many interesting details relating to the query are recorded, these details can be accessed via SQL Server’s Dynamic Management Views (DMVs). In particular the DMV sys.dm_exec_query_stats contains performance statistics for cached query plans. This DMV has everything we need to determine if a query is running slower than normal.
Details of the sys.dm_exec_query_stats columns relevant to us, are described in figure 1 (edited and taken from SQL Server 2005 Books online).
Column name Data type Description
statement_start_offset int Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offset int Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. A value of -1 indicates the end of the batch.
plan_handle varbinary(64) A token that refers to the compiled plan that the query is part of. This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.
execution_count bigint Number of times that the plan has been executed since it was last compiled.
total_worker_time bigint Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time bigint CPU time, in microseconds, that was consumed the last time the plan was executed.
total_physical_reads bigint Total number of physical reads performed by executions of this plan since it was compiled.
last_physical_reads bigint Number of physical reads performed the last time the plan was executed.
total_logical_writes bigint Total number of logical writes performed by executions of this plan since it was compiled.
last_logical_writes bigint Number of logical writes performed the last time the plan was executed.
total_logical_reads bigint Total number of logical reads performed by executions of this plan since it was compiled.
last_logical_reads bigint Number of logical reads performed the last time the plan was executed.
total_elapsed_time bigint Total elapsed time, in microseconds, for completed executions of this plan.
last_elapsed_time bigint Elapsed time, in microseconds, for the most recently completed execution of this plan.
Figure 1 shows the sys.dm_exec_query_stats fields used by the Query Time Delta utility
The _elapsed_time columns record how long the query took, taking into account any waiting on resources.
The _worker_time columns record how long the query took, ignoring any waiting on resources, instead it is concerned only with the time spent using the CPU.
In this article, we will look only at queries that are slower based on the _worker_time columns. This will allow us to ignore many of the variable factors that affect query duration, allowing us instead to create and discuss a simplified utility to quickly determine if a query is running slower than normal, based on the amount of CPU it has used.
Query Time Delta Utility
To determine if a query is running slower than normal, we need to calculate the average duration of the query and compare it to its last run value, adjusted for the amount of data it has processed.
The number of runs (execution_count) and the total worker time (total_worker_time) both include data relating to the last run, thus in order to create a more representative average, the number of runs is reduced by 1, and the total worker time has the last run time subtracted from it.
The average duration of the query is calculated by subtracting the last_worker_time from the total_worker_time and dividing the result by execution_count – 1.
The algorithm used to determine if a query is running slower than average is:
%Slower = (LAST – AVG) x 100 / AVG where LAST represents the duration of last query, and AVG represents the average duration of the query
For example, if the average query duration (AVG) is 40ms, and the last query duration (LAST) is 160ms, then %Slower = (160 – 40) x 100 / 40 = 300%
The duration of a query is affected by the amount of data it processes, this volume of data is reflected in the various IO columns of sys.dm_exec_query_stats. This utility calculates the slowness of a query (Impedance) by combining the duration of the query with the amount of IO performed. Again the ‘total’ IO values include the last run values, and thus need to be adjusted.
To see the SQL text of the individual query statement together with its parent query we call the Dynamic Management Function (DMF) sys.dm_exec_sql_textpassing it the query’s plan_handle as the function parameter. We can extract the individual query using the statement start and end offset values that are part of the DMV sys.dm_exec_query_stats.The DMF sys.dm_exec_sql_textcontains the id of the database the query related to, this is also shown. Note the WHERE clause only selects queries that have been executed at least twice.
For maintainability, the calculation of “% IO deviation” is separated from the main query. The data is sorted to show the TOP 100 slowest queries, by Impedance, this is a reflection of time deviation taking into account IO deviation.
Running the utility on my SQL Server gives the results given in Figure 2.
Figure 2 Output from the Query Time Delta utility.
The results show the individual statements, within a stored procedure or batch, that are running slower than normal. Adhoc or prepared queries are not associated with a database, hence the database name column is set to null.
Discussion
The utility described in this article allows you to quickly identify which queries are running slower than expected, after taking into account the volume of data processed.
Since this utility concerns itself with _worker_time rather than _elapsed_time, we can ignore many of the reasons why the query might be running slower (e.g. concurrency and blocking), and concentrate on why the query is using more CPU (i.e. worker_time) to fulfill its needs.
Perhaps the most common reason why a query starts to run slower (often suddenly) relates to “parameters sniffing”. Here the query plan is optimised based on the value of the parameters when the query is first used. You can imagine a parameter that retrieves only a few rows would produce a different plan than a parameter that would retrieve many rows.
Various solutions exist to create cached plans that are more optimal for the passed parameters, including:
1. Execute the stored procedure with the recompile option, this does not replace the query’s current cached plan.
e.g. EXEC sprocName WITH RECOMPILE
2. Add WITH RECOMPILE to the body of the stored procedure signature, this causes the stored procedure to be recompiled on every usage.
e.g. CREATE PROC sprocName WITH RECOMPILE
3. Use the optimizer hint RECOMPILE on an individual query within a stored procedure.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (RECOMPILE)
4. Use the optimizer hint OPTIMIZE FOR. This allows you to create a plan based on more typical parameter values.
e.g. SELECT col3 FROM table1 WHERE col3 = @param1
OPTION (OPTIMIZE FOR (@param1=71))
5. Use Plan Guides, these provide a degree of stability by allowing you to use an existing optimised query plan.
Each of these proposed solutions has advantages and disadvantages, and is left as an exercise for the reader to investigate them further.
Further work
If you’re interesting in the effect of changing concurrency patterns, blocking, waits etc you might want to look at elapsed time rather than the worker time. You can see the effects of these by replacing the _worker_time variables with _elapsed_time variables in the Utility script.
Various changes (e.g. adding indexes, updating statistics) can cause a query to be recompiled, resulting in a new cache plan, thus making comparisons of a query over time difficult. In this case, you should keep a history for comparison purposes. Keeping a history is also useful because DMV data is transient, often being cleared due to server restarts or memory pressures.
Conclusion
The utility described in this article will help identify easily and quickly those queries that have taken longer to run than normal, and should prove valuable in the everyday work of the SQL Server DBA/developer.
Credits
Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. He can be contacted at Ian_Stirk@yahoo.com.
Code:
CREATE PROC [dbo].[dba_QueryTimeDelta]
AS
/*----------------------------------------------------------------------
Purpose: Identify queries that are running slower than normal
, when taking into account IO volumes.
------------------------------------------------------------------------
Parameters: None.
Revision History:
13/01/2008 Ian_Stirk@yahoo.com Initial version
Example Usage:
1. exec YourServerName.master.dbo.dba_QueryTimeDelta
----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Identify queries running slower than normal.
SELECT TOP 100
[Runs] = qs.execution_count
--, [Total time] = qs.total_worker_time - qs.last_worker_time
, [Avg time] = (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)
, [Last time] = qs.last_worker_time
, [Time Deviation] = (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [% Time Deviation] =
CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
, [Last IO] = last_logical_reads + last_logical_writes + last_physical_reads
, [Avg IO] = ((total_logical_reads + total_logical_writes + total_physical_reads)
- (last_logical_reads + last_logical_writes + last_physical_reads))
/ (qs.execution_count - 1)
, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
, [Parent Query] = qt.text
, [DatabaseName] = DB_NAME(qt.dbid)
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
ORDER BY [% Time Deviation] DESC
-- Calculate the [IO Deviation] and [% IO Deviation].
-- Negative values means we did less I/O than average.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation] = [Last IO] - [Avg IO]
, [% IO Deviation] =
CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC
-- Extract items where [% Time deviation] less [% IO deviation] is 'large'
-- These queries are slow running, even when we take into account IO deviation.
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC
-- Tidy up.
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO
END
Monday, September 1, 2008
Cross Tab queries , Converting Rows to Columns
Hi all,
While browsing on the net I got a really good article by Jeff Moden on Cross Tab queries , Converting Rows to Columns . Hats off to jeff for this beautiful article. Some part of it that I feel was really very helpful for me I am reproducing it here for my future reference and I recommend you guys to read the complete series here
Cross Tab queries , Converting Rows to Columns
Sometimes it is necessary to rotate results so that [the data in] columns are presented horizontally and [the data in] rows are presented vertically. This is known as creating a cross-tab report, or rotating data.
In other words, you can use a Cross Tab or Pivot to convert or transpose information from rows to columns.
A simple introduction to Cross Tabs:
The Cross Tab Report example from Books Online is very simple and easy to understand. I've shamelessly borrowed from it to explain this first shot at a Cross Tab
The Test Data
Basically, the table and data looks like this...
--===== Sample data #1 (#SomeTable1)
--===== Create a test table and some data
CREATE TABLE #SomeTable1
(
Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1)
)
GO
INSERT INTO #SomeTable1
(Year, Quarter, Amount)
SELECT 2006, 1, 1.1 UNION ALL
SELECT 2006, 2, 1.2 UNION ALL
SELECT 2006, 3, 1.3 UNION ALL
SELECT 2006, 4, 1.4 UNION ALL
SELECT 2007, 1, 2.1 UNION ALL
SELECT 2007, 2, 2.2 UNION ALL
SELECT 2007, 3, 2.3 UNION ALL
SELECT 2007, 4, 2.4 UNION ALL
SELECT 2008, 1, 1.5 UNION ALL
SELECT 2008, 3, 2.3 UNION ALL
SELECT 2008, 4, 1.9
GO
Every row in the code above is unique in that each row contains ALL the information for a given quarter of a given year. Unique data is NOT a requirement for doing Cross Tabs... it just happens to be the condition that the data is in. Also, notice that the 2nd quarter for 2008 is missing.
The goal is to make the data look more like what you would find in a spreadsheet... 1 row for each year with the amounts laid out in columns for each quarter with a grand total for the year. Kind of like this...
... and, notice, we've plugged in a "0" for the missing 2nd quarter of 2008.
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 0.0 2.3 1.9 5.7
The KEY to Cross Tabs!
Let's start out with the most obvious... we want a Total for each year. This isn't required for Cross Tabs, but it will help demonstrate what the key to making a Cross Tab is.
To make the Total, we need to use the SUM aggregate and a GROUP BY
... like this...
--===== Simple sum/total for each year
SELECT Year,
SUM(Amount) AS Total
FROM #SomeTable1
GROUP BY Year
ORDER BY Year
And, that returns the following...
Year Total
------ ----------------------------------------
2006 5.0
2007 9.0
2008 5.7
Not so difficult and really nothing new there. So, how do we "pivot" the data for the Quarter?
Let's do this by the numbers...
1. How many quarters are there per year? Correct, 4.
2. How many columns do we need to show the 4 quarters per year? Correct, 4.
3. How many times do we need the Quarter column to appear in the SELECT list to make it show up 4 times per year? Correct, 4.
4. Now, look at the total column... it gives the GRAND total for each year. What would we have to do to get it to give us, say, the total just for the first quarter for each year? Correct... we need a CASE statement inside the SUM.
Number 4 above is the KEY to doing this Cross Tab... It should be a SUM and it MUST have a CASE to identify the quarter even though each quarter only has 1 value. Yes, if each quarter had more than 1 value, this would still work! If any given quarter is missing, a zero will be substituted.
To emphasize, each column for each quarter is just like the Total column, but it has a CASE statement to trap info only for the correct data for each quarter's column. Here's the code...
--===== Each quarter is just like the total except it has a CASE
-- statement to isolate the amount for each quarter.
SELECT Year,
SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
SUM(Amount) AS Total
FROM #SomeTable1
GROUP BY Year
... and that gives us the following result in the text mode (modified so it will fit here)...
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 .0 2.3 1.9 5.7
Also notice... because there is only one value for each quarter, we could have gotten away with using MAX instead of SUM.
For most applications, that's good enough. If it's supposed to represent the final output, we might want to make it a little prettier. The STR function inherently right justifies, so we can use that to make the output a little prettier. Please, no hate mail here! I'll be one of the first that formatting of this nature is supposed to be done in the GUI!
--===== We can use the STR function to right justify data and make it prettier.
-- Note that this should really be done by the GUI or Reporting Tool and
-- not in T-SQL
SELECT Year,
STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [1st Qtr],
STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [2nd Qtr],
STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [3rd Qtr],
STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],
STR(SUM(Amount),5,1) AS Total
FROM #SomeTable1
GROUP BY Year
The code above gives us the final result we were looking for...
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 0.0 2.3 1.9 5.7
While browsing on the net I got a really good article by Jeff Moden on Cross Tab queries , Converting Rows to Columns . Hats off to jeff for this beautiful article. Some part of it that I feel was really very helpful for me I am reproducing it here for my future reference and I recommend you guys to read the complete series here
Cross Tab queries , Converting Rows to Columns
Sometimes it is necessary to rotate results so that [the data in] columns are presented horizontally and [the data in] rows are presented vertically. This is known as creating a cross-tab report, or rotating data.
In other words, you can use a Cross Tab or Pivot to convert or transpose information from rows to columns.
A simple introduction to Cross Tabs:
The Cross Tab Report example from Books Online is very simple and easy to understand. I've shamelessly borrowed from it to explain this first shot at a Cross Tab
The Test Data
Basically, the table and data looks like this...
--===== Sample data #1 (#SomeTable1)
--===== Create a test table and some data
CREATE TABLE #SomeTable1
(
Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1)
)
GO
INSERT INTO #SomeTable1
(Year, Quarter, Amount)
SELECT 2006, 1, 1.1 UNION ALL
SELECT 2006, 2, 1.2 UNION ALL
SELECT 2006, 3, 1.3 UNION ALL
SELECT 2006, 4, 1.4 UNION ALL
SELECT 2007, 1, 2.1 UNION ALL
SELECT 2007, 2, 2.2 UNION ALL
SELECT 2007, 3, 2.3 UNION ALL
SELECT 2007, 4, 2.4 UNION ALL
SELECT 2008, 1, 1.5 UNION ALL
SELECT 2008, 3, 2.3 UNION ALL
SELECT 2008, 4, 1.9
GO
Every row in the code above is unique in that each row contains ALL the information for a given quarter of a given year. Unique data is NOT a requirement for doing Cross Tabs... it just happens to be the condition that the data is in. Also, notice that the 2nd quarter for 2008 is missing.
The goal is to make the data look more like what you would find in a spreadsheet... 1 row for each year with the amounts laid out in columns for each quarter with a grand total for the year. Kind of like this...
... and, notice, we've plugged in a "0" for the missing 2nd quarter of 2008.
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 0.0 2.3 1.9 5.7
The KEY to Cross Tabs!
Let's start out with the most obvious... we want a Total for each year. This isn't required for Cross Tabs, but it will help demonstrate what the key to making a Cross Tab is.
To make the Total, we need to use the SUM aggregate and a GROUP BY
... like this...
--===== Simple sum/total for each year
SELECT Year,
SUM(Amount) AS Total
FROM #SomeTable1
GROUP BY Year
ORDER BY Year
And, that returns the following...
Year Total
------ ----------------------------------------
2006 5.0
2007 9.0
2008 5.7
Not so difficult and really nothing new there. So, how do we "pivot" the data for the Quarter?
Let's do this by the numbers...
1. How many quarters are there per year? Correct, 4.
2. How many columns do we need to show the 4 quarters per year? Correct, 4.
3. How many times do we need the Quarter column to appear in the SELECT list to make it show up 4 times per year? Correct, 4.
4. Now, look at the total column... it gives the GRAND total for each year. What would we have to do to get it to give us, say, the total just for the first quarter for each year? Correct... we need a CASE statement inside the SUM.
Number 4 above is the KEY to doing this Cross Tab... It should be a SUM and it MUST have a CASE to identify the quarter even though each quarter only has 1 value. Yes, if each quarter had more than 1 value, this would still work! If any given quarter is missing, a zero will be substituted.
To emphasize, each column for each quarter is just like the Total column, but it has a CASE statement to trap info only for the correct data for each quarter's column. Here's the code...
--===== Each quarter is just like the total except it has a CASE
-- statement to isolate the amount for each quarter.
SELECT Year,
SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
SUM(Amount) AS Total
FROM #SomeTable1
GROUP BY Year
... and that gives us the following result in the text mode (modified so it will fit here)...
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 .0 2.3 1.9 5.7
Also notice... because there is only one value for each quarter, we could have gotten away with using MAX instead of SUM.
For most applications, that's good enough. If it's supposed to represent the final output, we might want to make it a little prettier. The STR function inherently right justifies, so we can use that to make the output a little prettier. Please, no hate mail here! I'll be one of the first that formatting of this nature is supposed to be done in the GUI!
--===== We can use the STR function to right justify data and make it prettier.
-- Note that this should really be done by the GUI or Reporting Tool and
-- not in T-SQL
SELECT Year,
STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [1st Qtr],
STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [2nd Qtr],
STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [3rd Qtr],
STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],
STR(SUM(Amount),5,1) AS Total
FROM #SomeTable1
GROUP BY Year
The code above gives us the final result we were looking for...
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 0.0 2.3 1.9 5.7
Subscribe to:
Posts (Atom)
Split the String values with a special character in MS Flow to convert this into Array
Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...
-
Sometimes you experienced when you have subgrid in your CRM Form, but when you click the ‘expand’ button to expand the view then it will re...
-
I have a web service that takes a serialize string and saves it. This works fine for "small" files, but once I hit a large siz...
-
In CRM 2011, I found a way to add color to entities grids using the ribbon, even if there are still two limitations: 1) This is still no...
