The difference between a fast running script and a slow one

When logging errors is not a good idea

I like to log errors and debug messages when running scripts as they help diagnose problems not immediately apparent when the script is started. There is nothing worse than starting a script running and then coming into work on a Monday morning to find that it hasn't done what it was supposed to and not know why. A helpful log file with details of the operation that failed and the input parameter values can be worth its weight in gold.

However logging is an overhead and can literally be the difference between a script that takes days to run and minutes. I recently had a script to run on a number of webservers that had to segment out hundreds of thousands of files (documents in the < 500KB range) into a new folder structure.

My ideal solution was to collate a list of physical files in the folder I wanted to segment and pass that into my SELECT statement so that I could return a recordset containing only the files that actually existed. However my ideal solution was thwarted due to an error message I have never come across before which claimed the DB server didn't have the resources available to compile the necessary query plan. Apparently this was due to the complexity of my query and it recommended to reduce the amount of joins. As I only had one join this was not a solution and after a few more attempts with some covering indexes that also failed I tried another solution.

The solution was just to try to move the file and catch any error that was raised and then log it to a file. The script was set running and a day later it was still running along with a large file containing lots of "File not found errors".

The script was eventually killed for another unrelated reason which pissed me off as I had to get the segmented folder structure up running sharpish. I modified the script so that before each move it checked whether the File Existed before the move. I initially had thought that this in itself may have been an overhead as the actual move method would be doing the same thing only at a lower level therefore I was duplicating two searches for a file in a large folder. I reckoned that just trying to do the move and then catching any error would speed things up.

However because I was logging the error to a text file this was causing a bottleneck in I/O and slowing down the procedure immensely. Adding the FileExists check round each move ensured that only files that definitely existed were attempted to be moved and no errors were raised which meant no logging and no I/O overhead.

I had forgone the nicety of knowing which files were not on the server any more but I had also reduced the scripts running time down to a mere 25 minutes. Therefore the lesson to be learned is that although logging is useful it can also be a major overhead and if you can do without it you may just speed up your scripts.

SQL Performance Tuning Queries

SQL 2005 Performance Tuning Queries

Here are some of my favourite queries that help with SQL performance tuning.
I have gathered most of these from the web and 3rd party sources over the years with the odd tweak here and there if needed and they form the bulk of my performance tuning toolkit. You should have a file containing a number of similar queries always close at hand as they are invaluable in hunting down problematic queries or even SQL injections or DOS attacks.

Update: 07-Dec-08
In response to the comment about filtering by db_id() I have removed that filter from a few of the examples as he is correct in stating that filtering by db_id() will not return all the possible data and will miss out queries run from the client or from within stored procs that use dynamic sql. Also if you are in a hurry and want to skip to a very useful procedure that will output nearly 20 different performance related reports for one or more databases then go straight to this download of mine: Download SQL 2005 Performance Reports. 

Top 50 worst queries for I/O

There are 2 different kinds of I/Os that are tracked within SQL Server: Logical and Physical IOs. Logical I/O account for data that is processed from the buffer pool which resides in memory, hence the phrase Logical I/O. Physical I/Os are I/Os that are associated with accessing data directly from the physical disks that SQL Server uses to store databases. Physical I/O’s are more expensive I/O’s, meaning they take longer to process. I/O is general the single most expensive operation that impacts the overall performance of a TSQL statement. So when you are tuning your queries you want to minimize the number of logical and physical I/O’s operation performed to produce a result set.

If you query shows physical I/O on the first run but none on the second its due to SQL holding the data in memory so you need to clear your buffer cache using DBCC DROPCLEANBUFFER

SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
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)
as query_text,
qt.dbid,
qt.objectid
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) as qt
ORDER BY [Avg IO] DESC

Top 50 worst queries by average running time

The following query will list the worst performing queries based on their average elapsed running time. It will give you the times, logical & physical reads and the TSQL of the statement in question.

SELECT TOP 50
creation_time,last_execution_time,total_physical_reads
  ,total_logical_reads,total_logical_writes,  execution_count
  ,total_worker_time, total_elapsed_time, total_elapsed_time  / execution_count
  avg_elapsed_time, SUBSTRING(st.text,  (qs.statement_start_offset/2) + 1,
  ((CASE statement_end_offset WHEN -1 THEN  DATALENGTH(st.text) ELSE      
  qs.statement_end_offset END -  qs.statement_start_offset)/2) + 1) AS statement_text
FROM   sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)  st
ORDER BY total_elapsed_time / execution_count DESC;

This query is similar to the previous one in the DMVs (dynamic management views) it uses but it provides the most CPU intensive queries in a system. If you are seeing high CPU spikes or top end flatlining on your DB server then this query will help find out whether you are being attacked with SQL Denial of Service attacks or whether you have some queries that are using pattern matching techniques (LIKE, PATINDEX, CLR Regular Expressions) that have been given a complex string to search for that has caused the server to max out on CPU.

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
Total_Physical_Reads,
SUBSTRING(b.text,a.statement_start_offset/2,
(case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
else
a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 db_name(b.dbid)as DatabaseName,
b.objectid AS 'Object_ID'
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
ORDER BY [Avg_CPU_Time] DESC

Suggested index columns and usage

SELECT d.*
 ,s.avg_total_user_cost
 ,s.avg_user_impact
 ,s.last_user_seek
 ,s.unique_compiles
FROM sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
WHERE s.group_handle = g.index_group_handle
AND d.index_handle = g.index_handle
AND database_id = db_id()
ORDER BY s.avg_user_impact DESC

View index usage by table.

Ideally you want high figures in the user_seeks column.
If you have no seeks and lots of lookups then you should investigate. Also if you have indexes with no seeks, scans or lookups then they should be dropped as the index is not being used.

SELECT i.Name, i.Type_Desc, i.Is_unique, d.*
FROM    sys.dm_db_index_usage_stats as d
JOIN    sys.indexes as i
ON   i.object_id = d.Object_id and
  i.index_id = d.Index_id
WHERE database_id=db_id()
  AND d.object_Id=object_id('JOBS')

View the fragmentation levels of your In Row (non blob/text) indexes

SELECT object_name(d.object_id), d.object_id, d.index_id, i.name as IndexName,
  avg_fragmentation_in_percent, alloc_unit_type_desc
FROM    sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) as d
JOIN    sys.indexes as i
ON   i.object_id = d.object_id and
  d.index_id = i.index_id
WHERE   d.Index_Type_Desc <> 'HEAP' AND
  i.Name is not null AND
  avg_fragmentation_in_percent>= 30 --change this figure to reflect your own requirements
ORDER BY object_name(d.object_id)

Finds the degree of selectivity for a specific column in a row

The higher the percentage the more selective it is and an index should be considered. There is no point having indexes on columns that have low selectivity as it would not help differentiate between rows when 50% of rows are one value and 50% another. This is the reason why its not a good idea to place indexes on bit columns as there are only two possible values. The only exception to this would be when you have a very high percentage that have one value (99% true) and you want to find the 1% that have the opposite value (false) however even then I would advise against it.

Declare @total_unique float
Declare @total_rows float
Declare @selectivity_ratio float

SELECT @total_unique = 0
SELECT @total_rows = 0
SELECT @selectivity_ratio = 0

/*
Finds the Total Number of Unique Rows in a Table
Be sure to replace OrderID below with the name of your column
Be sure to replace [Order Details] below with your table name
*/
SELECT @total_unique = (SELECT COUNT(DISTINCT ClientFK) FROM JOBS)

/*
Calculates Total Number of Rows in Table
Be sure to replace [Order Details] below with your table name
*/
SELECT @total_rows = (SELECT COUNT(*) FROM JOBS)

--Calculates Selectivity Ratio for a Specific Column
SELECT @selectivity_ratio = ROUND((SELECT @total_unique/@total_rows),2,2)
SELECT @selectivity_ratio as 'Selectivity Ratio'

So those are a few of my favourite queries that I use very frequently to help hunt down and resolve back end performance bottlenecks. Since SQL 2005 came out with all these really cool DMVs a lot of people have spent time putting these sorts of queries together into one report.

If you want to get your hands on one of these all encompassing queries then check out the following stored procedure which uses a query I came across and extended to make it more customisable:

http://www.strictly-software.com/scripts/downloads/database_performance_script.txt

Its a beast of a report but gives you everything you need to know in one report. Read the comments before each section carefully so you understand what the data is showing you. Also if you run the report as it is on a server with lots of databases, tables, indexes etc it will take some considerable time to execute so I suggest using one of the modes that enables you to filter only on those databases you require statistics for.  So call it like so:

EXEC dbo.usp_sql_rpt_database_performance_stats 3, 'strictlysoftware%'

will only report on databases on that server that have a name (sys.databases) that start with strictlysoftware. You will most likely also want to exclude the system tables (master, msdb, model) although you may want to report on tempdb so call it like so:

EXEC dbo.usp_sql_rpt_database_performance_stats 1, NULL

Another script that contains some useful code related to blocking and wait times can be found at Microsoft's site. The code is near the bottom of the page.

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

So there you go a one stop shop of goodies that should help you on your way. Please let me know if you have other useful DMV related queries or anything specific to SQL 2008 that is coming our way soon.

SQL Performance Top Tips

Another SQL Performance Top Tips?

I know there are lots of Top Tips for improving SQL performance out there on the web but you can never have too much of a good thing so I have created my own list of issues to identify and resolve when trying to improve the performance of an SQL database.


1. Investigate and resolve dead locks or blocked processes.

All it takes is one blocked process on a commonly hit table for your whole site to hang and start reporting timeout issues.

On my jobboard sites I had locking issues with my main JOBS table that had to be joined to a number of other tables to get the data needed on results pages that were hit constantly by crawlers. These tables were also being updated constantly by clients and automated feeds from multi-posters which meant that locks were likely.

As I use one database for multiple sites it also meant that a lock caused by one site would cause timeout issues for the other sites.

To resolve this I created some de-normalised flat tables per site that contain all the fields needed for reporting and searching which meant:

• The JOB table is only updated at regular intervals and not instantly. SQL 2005 introduced the synonym feature which I demonstrate here. This is used to swap between tables being built and tables in use by the site so that a new table can be built behind the scenes with up to date data.
• Clustered indexes can be site specific depending on the columns the site search on the most.
• No joins needed when accessing the table and results are returned very fast.
• Any problems accessing this table do not affect other sites.

Another trick to handle instances where you are selecting from tables that maybe updated constantly such as job or banner hit tables is to look at the following:

1. If the table you are selecting from is never updated or deleted from during the times you are selecting from then you can use the WITH (NOLOCK) statement on your SQL SELECTS. You won't have to worry about dirty reads as you are not actually updating the data and you bypass all the overhead SQL has to go through to maintain the LOCKING.
2. Use the LOCK_TIMEOUT statement to reduce the time LOCKS are held for.
3. Use the TRY / CATCH statement to catch deadlocks and other blocking errors in combination with a retry loop. After X retries return from the proc. View my example of using LOCK_TIMEOUT to handle DEADLOCKS and BLOCKING.

2. Look into large batch processes that have long run times, take up I/O, have long wait times or cause blocking.

A good example is where you have to transfer lots of records from one table to another e.g from a daily table that only receives INSERTS (e.g job / banner hits) into a historical table for reporting.

If you are deleting large numbers of records from a table that is also being used then you don't want to cause blocks that will freeze your site. In conjunction with point 1 you should look into how you handle your DELETE / UPDATE statements so that they are done in small BATCHES using the SQL 2005+ TOP command.

Read this article for an example of updating or deleting in batches to prevent blocking.

To find out problematic queries after the fact you can utilise the SQL 2005+ Data Management Views (DMV's) which hold key information about the system.

Read this article on my top queries for performance tuning to help identify those queries that need tuning.


3. Re-Index and De-Frag tables

Make sure you regularly re-index and de-fragment your tables as over time fragmentation will build up and performance will be affected. I tend to set up a weekly MS Agent job that runs a Defrag / Re-organize or  on tables with fragmentation over a set percentage as well as rebuild index statistics.

I then also try to schedule a full re-index of all my main tables once a quarter at scheduled down times as during a full rebuild the tables can be taken off line depending on your SQL setup.


4. Identify Slow Queries

Investigate your slowest running queries to make sure they are covered adequately by indexes but also try not to over use indexes. Try to cover as many queries with as few indexes as possible.

Try running this SQL performance report which will identify a dozen or so areas which could be improved on your system including:

• Causes of the server waits
• Databases using the most IO
• Count of missing indexes, by database
• Most important missing indexes
• Unused Indexes
• Most costly indexes (high maintenance)
• Most used indexes
• Most fragmented indexes
• Most costly queries, by average IO
• Most costly queries, by average CPU
• Most costly CLR queries, by average CLR time
• Most executed queries
• Queries suffering most from blocking
• Queries with the lowest plan reuse

This is an invaluable tool for any SQL DBA or SQL performance tuning developer.



5. Split tables

If your tables are used for inserting/updating as well as selection then for each index you have on a table that's an extra update required when a record is saved. On some of my big tables that are used heavily for reporting I split the data into daily and historical data. The daily table will allow updates but the historical table will not.

At night a job will transfer the daily data into the historical table, dropping all current indexes, populating the new data and then rebuilding the indexes with a 100% fill factor. You need to balance out whether speed on data retrieval or on update is more important if the table is used for both. You should also look into points 1 and 2 about managing blocking and batch updates/deletes to handle instances where a table is being deleted whilst accessed at the same time.


6. Re-write complex queries and make WHERE clauses SARGABLE

Can you rewrite complex queries to be more efficient. Can you remove left joins to large tables
by populating temporary tables first. Are you putting functions in the WHERE, HAVING clause on the column and negating any index usage. If so can you rewrite the clause and make it SARGABLE so that you make use of the index. For example a WHERE clause like so

WHERE DateDiff(day,CreateDate,GetDate()) = 0

Which is filtering by todays date should be rewritten to:

WHERE CreateDate > '2008-sep-09 00:00:00' --where this is the previous midnight

Put the value from GetDate() into a variable before the SELECT and then use that in the filter
so that no function is used and any index on CreateDate will be available.

Read this article of mine which proves the benefits of SARGABLE clauses in SQL.


7. Query Plan Caching

Check that you are benefiting from query plan caching. If you are using stored procedures that contain branching through use of IF statements that run different SQL depending on the values passed in by parameters then the cached query plan for this procedure may not be the best for all parameter variations.

You can either rewrite the procedure so that each IF branch calls another stored procedure that contains the query required as it will be this plan that gets used.

Or you could rewrite the query to use dynamic SQL so that you build up a string containing the appropriate syntax and parameters and then execute it using sp_executesql which will take advantage of plan re-use. Do not use EXEC as this will not take advantage of plan re-use and its not as safe in terms of sql injection.

You should also look into whether your query plans are not getting used due to your SQL Server settings as the default mode is to use SIMPLE PARAMETERIZATION and not FORCED PARAMETERIZATION.

This features takes AdHoc queries containing literal values and removes values replacing them with parameters. This means that the query plan which gets cached can be re-used for similar queries that have different values which can aid performance as it will reduce compilation time.

When the system is set to SIMPLE mode only AdHoc query plans that contain the same literal values get cached and re-used which in many cases is not good enough as the values for most queries will change all the time for example in SIMPLE mode only the plan for the exact query below will be cached.

SELECT *
FROM PRODUCTS
WHERE ProductID = 10

Which means only products with the ProductID of 10 will benefit from a cached plan however with FORCED PARAMETERIZATION enabled you would have plan with parameters so that any ProductID can benefit from it e.g

SELECT *
FROM PRODUCTS
WHERE ProductID = @1 -- name of SQL parameter

For a more detailed look at the benefits of this method read my article on forced paramaterization.


8. Use the appropriate table for the situation

SQL has a variety of tables from fixed permanent tables to global and local temporary tables to table variables that are both stored in tempdb.

I have found a number of times now that the use of table variables start off being used in stored procedures as very useful memory efficient storage mechanisms but once the datasets stored within them rises above some threshold (I have not found the exact threshold amount yet) the performance drops incredibly.

Whilst useful for array like behaviour within procs and quick for small datasets they should not be used with record sets of many thousands or millions of records. The reason being that no indexes can be added to them and any joins or lookups will result in table scans which are fine with 10-100 records but with 20 million will cause huge problems.

Swapping table variables for either fixed tables or temporary tables with indexes should be considered when the size of data is too great for a table variable.

If your procedures are being called by website logins with execute permission you will need to impersonate a login with higher privileges to allow for the DDL statements CREATE and DROP as a basic website login should have nothing more than read privileges to prevent SQL injection attacks.

If you don't want to risk the rise in privileges then consider using a fixed table and the use of a stamp and unique key so that multiple calls don't clash with each other. Appropriate indexes should be used to ensure speedy data retrieval from these tables.

A real world example I have found is explained in this article on speeding up batch processes that use table variables.


9. Tuning for MySQL

MS SQL has a vast array of useful tools to help you performance tune it from it's Data Management Views and Activity Monitor to it's detailed Query Execution Plans. However if you are using MySQL and have to suffer using a tool like NAVICAT then you are not in such a good position to tune your queries.

The EXPLAIN option is nowhere near as useful as the MS SQL Query Execution Plan but it can be used to ensure the appropriate indexes are added if missing and the Slow Query Log is a useful place to identify problematic queries.

If you are using LINUX or Wordpress and running your system on MySQL then read this article of mine on performance tuning for MySQL.


These are just some tips to look at when trying to improve back end database performance and I will add to this list when I get some more time. Feel free to post your own tips for identifying problematic SQL and then fixing it.