Call us Today! 201-728-8809 | | Contact | Download | About |

Database Troubleshooting and Tuning Series

Digging Deeper into SQL

Are your developers hindering database performance? Part III

April 2012. Volume 1 - Issue 3
By Tom Romeo

The goal of this article is to help developers design applications with consideration for the importance of proper database interaction for maximum performance.

Digging Deeper

In our previous article we covered the basics of what you should do first when blindsided by a critical situation. If most of the initial statistics seem normal, that is
  1. batch requests per second are reasonable
  2. SQL Errors are not excessive
  3. Very few deadlocks occur if at all
  4. Logins / Logouts per second are reasonable
  5. SQL Connections are not excessive
  6. Compilations are not excessive
  7. Plan cache hit ratio is reasonable
Despite all normal statistics, either CPU or Disk sustains undesirably high values causing bottlenecks that ripple throughout the system. If CPU is high and SQL Server is the cause, then a SQL statement or statements could be the cause.

We must now look to the SQL procedure cache for our next clues. While the method for doing so is the same on SQL Server and Oracle, we will focus on SQL Server.

Dissecting the query from our previous article, we will target SQL query tuning opportunities.

Select ob.SQL, case isnull(, '') when '' then convert(varchar(16), ob.dbid) else end DBName, pl.execution_count, pl.min_elapsed_time, pl.max_elapsed_time, pl.last_elapsed_time, pl.total_elapsed_time, pl.total_worker_time, pl.last_worker_time, pl.min_worker_time, pl.max_worker_time, pl.total_physical_reads, pl.last_physical_reads, pl.min_physical_reads,pl.max_physical_reads, pl.total_logical_writes, pl.last_logical_writes, pl.min_logical_writes, pl.max_logical_writes, pl.total_logical_reads, pl.last_logical_reads, pl.min_logical_reads, pl.max_logical_reads, pl.creation_time, pl.last_execution_time, pl.sql_handle, pl.plan_handle
from sys.dm_exec_query_stats pl
join sys.dm_exec_cached_plans ph on pl.plan_handle = ph.plan_handle
join sys.syscacheobjects ob on ph.bucketid = ob.bucketid
left outer join sys.databases db on ob.dbid = db.database_id
order by pl.max_elapsed_time desc

The fields, min_elapsed_time, max_elapsed_time, and last_elapsed_time are expressed in microseconds but are accurate to the millisecond.
A quick review:
  • 1 Millisecond = one thousandth of a second or 10-3
  • 1 Microsecond = one millionth of a second or 10-6
  • 1 Nanosecond = one billionth of a second or 10-9
We can start out limiting our search for queries happening in our target database.

As we are looking for repeat offenders, even if max_elapsed_time >= 30,000,000 (30 seconds), but min_elapsed_time <= 100,000 (one tenth of a second or 100 milliseconds) and or last_elapsed_time <= 100,000 then we can probably bypass that particular query because it potentially runs very quickly all the time and only hiccupped a few times due to other events.

We want to target queries that have a high value for max_elapsed_time as well as min_elapsed_time, and last_elapsed_time, though they don't necessarily need to be equal.

We may also keep a watchful eye on queries that have a high execution count with respect to the minimum elapsed time. That it so say a query with an average execution time of 200 milliseconds may be overlooked unless that query has an execution count that exceeds 1000 per minute.

Finally we may also target queries that have a high number of physical reads.

I find it useful to run the above query and paste the contents into an Excel Workbook for three reasons.
  • It is easy to re-sort or group the statistics without re-running the query
  • It is easy to format the numeric data with commas - or you can use the function below
  • It is easy to record your progress by keeping a copy of the procedure cache for each iteration of tuning attempts.

After we attempt to tune each query that we target, it may be useful to clear the procedure cache to verify that the query is no longer on the list of offenders.
These SQL statements clear out the procedure cache and other system buffers allowing you to collect new performance data.
  • DBCC FREEPROCCACHE { Removes all elements from the plan cache }
  • DBCC FREESESSIONCACHE { Flushes the distributed query connection cache }
  • DBCC FREESYSTEMCACHE { Releases all unused cache entries from all caches }
  • DBCC DROPCLEANBUFFERS { Removes all clean buffers from the buffer pool }
For illustration purposes I've downloaded the Microsoft Adventure works databases. Before demonstrating the procedure cache is cleared.

The Adventure works database contains an index named "IX_TransactionHistory_ProductID" which indexes the field [Production].[TransactionHistory].ProductID. For demonstration purposes, it has been dropped.

The following queries are run against the AdventueeWorks database with the "Include actual execution plan turned on:

use AdventureWorks
select * from HumanResources.Department
select * from HumanResources.Employee
select * from HumanResources.EmployeeAddress
select * from HumanResources.EmployeePayHistory
exec sp_spaceused N'Production.TransactionHistory'
exec sp_spaceused N'Production.Product'
select * from Production.Product
select top 10 * from Production.TransactionHistory

select top 10 *
from Production.TransactionHistory th
join Production.Product p on th.ProductID = p.ProductID
join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID
join Production.ProductInventory pii on p.ProductID = pii.ProductID

select *
from Production.TransactionHistory th
join Production.Product p on th.ProductID = p.ProductID
join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID
join Production.ProductInventory pii on p.ProductID = pii.ProductID

select *
from Production.Product p
join Production.TransactionHistory th on p.ProductID = th.ProductID
join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID
join Production.ProductInventory pii on p.ProductID = pii.ProductID

Running the query above to view the procedure cache renders the following result:

SQL Performance Tuning

Pasting the second query into a query window SQL management studio renders the query plan below.

SQL Query Tuning

There is no where clause on the query. Every step performs a clustered index scan, but if the index "IX_TransactionHistory_ProductID" had not been dropped the plan would read differently, or would it?

Highlight and right click the query and select "Analyze query in database Engine Tuning Advisor".

The SQL Tuning Advisor recommends that IX_TransactionHistory_ProductID is constructed differently than as it ships with the examples.

The index is newly constructed as follows:
CREATE NONCLUSTERED INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory]
[ProductID] ASC
INCLUDE ( [TransactionID],

The new execution plan reads differently using an index seek. Keep in mind that this is a bad query in that the select statement returns all 161,659 rows as there is no where clause and it returns' every column of each table. This is why the query analyzer included every column of the TransactionHistory table in the new index, so it would not need to perform a secondary fetch operation to retrieve the data.

Recall from a previous article; always select what you need and no more. A properly constructed where clause would limit the rows returned. Similarly, selecting only the columns necessary would not only affect the time required to render the result set, but drive the selection of the proper execution plan. This is paramount to optimizing performance.

SQL Server Query Analysis

Again, the queries used for this example where purposefully created to perform poorly enabling us to illustrate the techniques for tuning. Also, it illustrates how one could carefully construct an index that should help performance and then create a query that causes SQL Server's execution plan to ignore the index due to inefficiencies in the query.

In the real world, a query's flaw may not be so obvious as in this case, but SQL server provides powerful tools to help you analyze queries. Keep in mind that over time as data grows and is mined, new tuning opportunities may present themselves.

The function below accepts an integer and returns a string that is broken by thousands with a comma and no decimal places.

create function mySf_AddCommas (@intIn int)
returns varchar(64)
declare @strOut varchar(64)
set @strOut = SUBSTRING( CONVERT(varchar, CAST(@intIn AS money), 1) , 0, LEN( CONVERT(varchar, CAST(@intIn AS money), 1) ) -2)
return @strOut

If you created the function in the master database, then you can call it this way.

select master.dbo.mySf_AddCommas(987654321)
Returns the result : 987,654,321
You can modify the query above it to format master.dbo.mySf_AddCommas(pl.max_elapsed_time) for example.

Click here to see other articles in the series.

Corporate Headquarters

Practical Compliance Solutions
One Orient Way - Suite F156 Rutherford, NJ 07070
Phone: 201-728-8809

40.827731 N 74.101029 W
Connect With Us


© 2002 - 2024 - Copyright Practical Compliance Solutions - All rights reserved