Rapid Tuning

I use this approach in general situations , where the user has complained of general performance degradation without giving you specific feedback .

Stage I
    DBCC UPDATEUSAGE
    UPDATE STATISTICS (with FULL scan) for all tables
    exec sp_recompile for all objects

    exec sp_refreshview for all views
Stage II
    DBCC DBREINDEX
    exec sp_recompile for all objects
    exec sp_refreshview for all views
Stage III
    Profiler
    Query Hints

Hint: Advise them of the need to write scalable code to start with !

SQL Tuning Jobs

Wait Stats scripts

Wait Stats script

SQL Server Full Text Search Optimization

I read and used an excellent tip on : http://www.sql-server-performance.com/tb_search_optimization.asp  .

The basic problem revolved around full text searches using WHERE predicates.For example:

SELECT *
FROM ads p
INNER JOIN containstable(properties, description, 'car') t
ON p.PropertyID = t.[key]
WHERE p.type = 'red'

This returns , 1,200,000 records back to the optimizer, which is then filtered down to a further 150,000 'cars' that are 'red'.

The solution proposed in the article, uses embedded text in the text column,and then some substring manipulation to take out the embedded text.Inelegant, but very effective

SQL 2005 : OPTIMIZE FOR

Recently, I had the following problem to consider. Two similar SELECT statements , the one difference being the WHERE clause. If you notice the statement 1 uses a variable whereas statement 2 uses date literals.

The problem related to the difference in performance. Statement 2 consistently performed better , by a margin of 10 seconds on average. How could this be?I ran the same queries on a 2 different machines , cleared caches but still the difference remained consistent.

(statement 1)
Declare @Date as datetime
set @Date = '01-04-06'
SELECT prod as code
FROM orders
WHERE (ship_date between @Date and dateadd(d,6,@Date))
GROUP BY prod

(statement 2)
SELECT prod as code
FROM orders
WHERE (ship_date between '01-04-06' and dateadd(d,6,'01-04-06'))
GROUP BY prod

The first thing I did to diagnose was check the execution plan. I noticed that INDEX SCAN was being used rather that INDEX SEEK. This pointed to low selectivity in the data , meaning that the data posessed a relatively low level of uniqueness. Essentially the index in place had a low level of helpfulness. Usually a SEEK is more effective than a SCAN , but there are exceptions. For example , if the table is small and memory cached.In this case the table had over 1 million records

(As an aside if all else fails when it comes to hitting indexes , you can state the actual index to hit directly withing the SQL statement , such as :

SELECT prod as code
FROM orders with (index (ix_my_index index))
WHERE (ship_date between '01-04-06' and dateadd(d,6,'01-04-06'))
GROUP BY prod

The second thing I did (a SQL 2005 feature) was to utilise the OPTIMIZE FOR .The statement then became:

SELECT prod as code
FROM orders
WHERE (ship_date between '01-04-06' and dateadd(d,6,'01-04-06'))
GROUP BY prod
OPTION (OPTIMIZE FOR (@Date = '20060405'))

The literal used in the hint just needs to be one that suggests to the optimizer what should be the desired query plan. Essentially one that generates a similar rowcount estimate to values you are likely to use for the @Date value. Also, a colleague pointed out to me that using the date format 'dd-mm-yy' has different interpretation whereas 'YYYYMMDD' only has 1 interpretation

Eventually, I didn't get the queries equal but only on avearge a couple of seconds difference.

Optimize query for LIKE

I received a question from a client yesterday : We've implemented a query with LIKE in the WHERE clause. But if I want to search for a suffix (LIKE '%suffix') an Index Scan is used and takes very long. Is there any way that SQL Server does an Index Seek in case of LIKE '%suffix'? How can I optimize?

By it's nature a - '%suffix' will scan rather than seek. But , something like 'a%suffix' will seek. Note the 'a' before the '%'. The optimizer can make a guess on the basis of 'a'. If the '%' is in front , the optimizer cannot predict the outcome. As an example, inspect this index tree:

+----AAA----+
| |
+-ABB-+ +-CCC-+
| | | |
ADD AEE CFF CGG

An index is a binary tree. The query engine determines which branch of the tree to take by deciding if the value it's looking for is greater than or less than the value it's currently sitting on:

Seeking a value 'ABB': Two jumps gets us to the desired value, because we can navigate the tree.

Seeking a value of '%D', we start with the first node, 'AAA'. Which way do we branch to continue our search? We don't know, therefore we have to look at EVERY node to find those that end in 'G', therefore an index (or table) scan.

An interesting approach someone mentioned is to store another column with the REVERSE() of the existing column, then querying with the REVERSE() of the search string, 'xiffus%'. This could also be achieved with an indexed view, saving the space in the base table. This would require some manipulation of the query string.

SQL Server : Order of Operators

Something which may seem basic, but in many SQL statements I analyse for performance optimisation, the same mistakes are recurring. When a WHERE clause is used the actual operator used will influence the query speed. That is not to say , every statement has the option of using the optimal operator.
It's worth keeping in mind the order of the operator , the order starts with the fastest performer.
=
<,<=,>,>=
LIKE
<>

SQL Server Tuning Targets

There are many approaches to SQL tuning - Ratio-based analysis,Bottleneck analysis & Workload analysis to name a few. This section describes an efficient approach to SQL tuning based on current system load. Once the "best" overall optimization is achieved, we drill-down into the specific cases of sub-optimal SQL, and change their execution plans with SQL indexes.

Despite the complexity of tuning SQL, there are general guidelines that every SQL Server DBA follows in order to improve the overall performance of their SQL Server system. The goals of SQL tuning are simple:
- Tune subqueries to eliminate redundancy
- Analyse optimal JOIN techniques
- Replace unnecessary large-table full-table scans with useful indexes.
- Analyse optimal index usage
These goals may seem deceptively simple, but these tasks comprise 80 percent of SQL tuning. They do not require an indepth understanding of SQL Server internals.

If required, the SQL can be tuned regularly, but if the optimizer is not fed with the correct statistics, the optimizer may not make the correct decisions. Before tuning, it is important to ensure that statistics are available and that they are current.Even though you may have the Auto Update Statistics Property set at true , it will only kick in when changes are being made. A simple "sp_updatestats" will do the job.

SQL Server 2000- IO bottleneck problems

I was working on a system the other morning that was experiencing severe performance problems. Further compounded by the launch that day of a new product! Ideally , I would do a whole bunch of wait analysis , to try and identify the problem. The set up was 2 hard disks with no RAID and extremely high volume.

The conclusion was: I/O issue.Sustaining that level of volume with 2 hard disks will lead to problems. You need a caching Raid controller and disk arrays.

In their particular case , they were seeing checkpoints. During a Checkpoint all the dirty pages in the buffer cache are written to disk at one time. Without a good Raid array and a fair amount of disk cache you will never get good performance.

The other feature causing a problem was not separating the log files into their own RAID 1 disk array- insuring their is nothing else on that drive other than transaction log files. Log writes are exclusively sequential writes and can reside on RAID 1 (sequential writes are 20 times faster than random writes for a single drive.) If possible, try to have only transaction log per mirrored pair otherwise the disks start to perform random IO.

If there are an excessive amount of writes, it's best to have a RAID 10 for data.Also, a battery backed writeback conntroller with all the memory set to write cache. It's extremely difficult to get excellent performance unless a proper disk subsystem is put into place.

What is SQL Server Query Tuning?

I was chatting to a client last week and I suggested some Query Tuning may be required to solve some problems they were experiencing. He wanted some breakdown of what I would define as Query Tuning. I drafted an email , with an attempt at defining an approach .Find some notes from the email
What is Query tuning?
When it comes down to it, there are predeployment queries and post deployment queries. Both require optimisation. The first type need to be optimised, the second type need to be optimised ONCE they are identified as causing the problem.

DBA Strategy: The 90/10 rule
The 90/10 rule ,is it worth trying to spend 90% of your time to solve 10% of waits. A methodology is required to lead to systematically lead us to the main bottlenecks.
Ultimately the end user doesn't care about performance measures such as CPU counts, they are interested in getting a result. Typically , a wait for longer than 3 seconds is considered a performance issue.

What is the What is the Dynamic Management View called sys.dm_os_wait_stats?

The guts of sys.dm_os_wait_stats i.e waits?
Low level trace methods

Diagnosing a SQL Server Problem Demo
A set based methodology . Repeatable and provable. 1.Analyse Waits at instance level
2.Match waits with queues
3.Conclusiion & plan
4.Drill down to the db level
5.Drill down to the process level
6.Tune indexes/queries

Other considerations
1.Analyse waits at the instance level.
SQL 2005 use sys.dm_os_wait_stats ; SQL 2000 use DBCC SQLPERF(WAITSTATS) Focus on I/O, although there are other issues
2.Match waits with queries
use System monitor - e.g if most waits were I/O based use the different I/O queues
3.Determine course of action
What is the cause of the problem?
SQL Server Optimiser
SQL Server Instance/Database Configuration & Optimization
Design your db for performance