mysql

SQL Server covering index and key lookup performance

April 15, 2012 by Andy Hayes 7 Comments

In this post, I wanted to write about the covering index and key lookup and how adding covering indexes can help increase performance by reducing lookups to the table data.

It’s helpful to know what each of these terms mean and then we will look at an example scenario further down the post.

What is a covering index?

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

To be considered as a covering index, all columns referenced by the query must be contained in the index. So this means all columns in the SELECT, JOIN, WHERE, GROUP BY, HAVING etc etc.

What is a key lookup?

A key lookup is an extra read which the query has to make in the absence of a suitable covering index.

In the query, the number of columns involved in the select statement may exceed the number of columns in the non-clustered index and when this happens, for each row in the result set, a key lookup is performed. So 500 rows in the result set equates to 500 extra key lookups.

Key lookup medicine – AKA the covering index!

Lets look at a simple query to help explain what I was talking about above. Imagine that dbo.MyTable consists of just two columns “Col1” and “Col2”.

SELECT Col1 FROM dbo.MyTable WHERE Col2 = 1;

For this query to be considered covered by an index, columns “Col1” and “Col2” need to be part of the same index and you could create different variants of indexes which would act as a covering index.

For example, you could do:

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col1, Col2);

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col2, Col1);

You could also use included columns:

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col1) INCLUDE (Col2);

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col2) INCLUDE (Col1);

So the columns need only be found in the index and the column order or whether they are an “included column” or not does not matter. It is important to remember that the execution plan and performance may vary considerably depending on which index is applied however, so choosing the correct covering index is important.

Lets look at how the covering index removes the need for a key lookup.

I am going to use this simple query against a copy of the AdventureWorks2012 database using SQL Server 2012 Express.

I’ll enable the execution plan output and enable STATISTICS IO output.

SET STATISTICS IO ON;

SELECT CustomerID, OrderDate
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
WHERE CustomerID = 11000;

There is an non-clustered index on the CustomerID column. The clustered index on this table is on a column not referenced by the query called SalesOrderID.

The output looks like this:

STATISICS IO output….

Table 'SalesOrderHeader'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now lets look at the execution plan

You can see that 68% of the total cost of this query has been allocated to the key lookup against the clustered index.

Let’s see what happens to it after we modify the index to cover the columns in the query.

DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID;

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_OrderDate
ON Sales.SalesOrderHeader(CustomerID, OrderDate);

The key lookup is gone (well you were expecting that right 🙂 ) and now the query is retrieving the data entirely from the covering index.

Lets look at STATISTICS IO again

Table 'SalesOrderHeader'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A big reduction can be seen in the number of logical reads compared to the first query. Logical reads are the pages read from the data cache regardless of whether the pages had to be read from disk or not. In both cases, they did not need to be read from disk (physical reads = 0), both queries read their pages direct from the data cache. As the second one read less pages from the data cache, that is still a good indicator that the covering index has improved performance.

If I ran DBCC DROPCLEANBUFFERS (do not do this on a production server as you could see massive performance reduction until the cache is re-populated) before each query and index change in order to force the optimizer to read from disk, then STATISTICS IO would look like:

--BEFORE covering index
Table 'SalesOrderHeader'. Scan count 1, logical reads 11, physical reads 7, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--AFTER covering index
Table 'SalesOrderHeader'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can see the differences in the number of pages read from disk and this helps to show the benefit of having the covering index in place. Once the data pages are read from disk, they enter the data cache. All queries get their results by reading the data cache.

The second query reads less data pages into the data cache and subsequently, there is more room in the data cache. More room means more pages can be read into cache and the frequency for disk reads becomes less with performance increasing.

Should you apply a covering index in all cases?

Indexing is a complex subject and there is no definitive “one size fits all” solution to indexing a database. You have to know what is possible and then assess your needs. You should always test your index changes properly before putting them into production.

An analysis of server workload will help you figure this out. A few things to consider:

Is existing query performance acceptable?
How often is the query running?
How selective is the query?
Is the table you are adding the index to write intensive?

Remember, a query may perform well and return results in good time. What if it is running many hundreds or thousands of times a minute? Cumulatively without a covering index, it could be generating many more extra IO’s than necessary.

You need to assess whether the frequency and selectivity of the query warrants adding a covering index. Covering indexes have the greatest benefit to non-selective queries.

In addition, if your system is write intensive, adding an index could have the opposite affect of what you are trying to do by slowing down overall database performance. This is because for each update to the data, the indexes have to be updated as well.

For more information on SQL Server index creation, you can visit this link which talks about the covering index and included columns.

10 simple tips on improving query and server performance

February 18, 2012 by Andy Hayes 3 Comments

The following tips do not promise that your database server will magically become blisteringly fast and go like a Ferrari but they are important things to bear in mind and relatively simple to implement either when building your database or if you have an existing performance problem and are looking for ways in which to make your database run faster.

**1/ SELECT * FROM**

The rest of my points are in no particular order but I must place this one at the top of the list as it really annoys me. I’ve seen this many times where developers take the lazy approach to writing their queries by including all columns for the table, or in the case of there being joins involved, then this means every column for every table involved in the query. It is far better to limit the query to only bring back the columns required reducing I/O, memory pressure and on bandwidth between the database and the application layers.

2/ Incorrect sizing of data types – less is more

Database design is sometimes a neglected aspect of building an application. The need to rapidly build a database to store the data and get coding results in tables containing data types which are incorrectly sized.

So lets look at a simple example of what I mean using MS SQL Server.

The system we are designing for allows advertisements to be placed in one county in England per advert. Whilst this does not sound like a terribly flexible system, it serves the purpose in trying to demonstrate the issue with badly sized data types 🙂

So here is the create table script. (I have omitted the Customer create table script which would theoretically exist here to ensure that the relationship on Advert.CustomerID could be created).

CREATE TABLE County
(
CountyID INT PRIMARY KEY IDENTITY(1,1),
County VARCHAR(20)
);

CREATE TABLE Advert
(
AdvertID INT PRIMARY KEY IDENTITY(1,1),
CustomerID INT NOT NULL REFERENCES Customer(CustomerID),
DatePlaced DATETIME DEFAULT GETDATE() NOT NULL,
CountyID INT NOT NULL REFERENCES County(CountyID),
AdvertText VARCHAR(MAX) NOT NULL,
);

So, two tables and where in this design are there issues?

How many counties are there in the England? Well certainly not this many: 2,147,483,647 which is the limit of the data type above 0. Yes you can store negatives also up to -2,147,483,648.

So what, they’re just numbers right? What’s the problem? The problem is the storage needed. The INT data type will require 4bytes of storage. The number of English counties is less than 255. Is it ever going to get larger than 255? I can’t see that happening any time soon so better to design the County table to be CountyID TINYINT and the related field in Advert to also be a TINYINT. This data type uses 1byte of storage. That’s a 75% reduction and if you had an extremely large Advert table it could all start to add up.

The other field in question is the DatePlaced. Always question whether you need the increased size of the types you are setting. In my example, I don’t think I am too fussy about the extra precision which the DATETIME data type gives me, so I should have designed that as a SMALLDATETIME.

But storage is cheap right? Yes but this is not the point. It’s all about I/O and memory pressure. The smaller the data types are, the less pages have to be read from disk into the memory buffers. If the memory buffers are not constantly being emptied to make way for other data, then your database server and application will be a lot happier as there will be less read requests to disk.

3/ Adding and Removing Indexes

Identify queries which are performing badly. Can you add indexes which would make them faster? SQL Server 2008 Management Studio will even recommend which indexes to add when you view the execution plan. Identify where you need to add clustered indexes and put them in.

Identify unused indexes and remove them. For each record inserted to a table, every index is updated. If you do not need an index, get rid of it. It will help speed up inserts.

4/ Use stored procedures instead of in-line SQL

There are many advantages to using stored procedures. From a performance perspective, having them in place means that each time the procedure is executed, the execution plan is pulled out of cache. As the server does not need to work out the execution plan, the process from calling the procedure to it completing its execution is typically faster.

5/ Set up maintenance plans to rebuild indexes and statistics

All indexes will suffer from fragmentation and statistics will get out of date. Set up maintenance plans in SQL Server or batch tasks in MySQL which will periodically rebuild statistics and indexes to help the optimizer choose the best execution plan.

6/ Replace use of functions in WHERE clauses with SARG’s

It’s all about the search arguments (SARG’s)

Here’s an example. Two queries, the first uses a LIKE, the second uses the SUBSTRING function. There is an index on the LastName column in my table and the first query reports using an index seek which should normally be faster compared with the second which reports using a clustered index scan.

SELECT FirstName, LastName
FROM Employee
WHERE LastName LIKE 'Hay%';

SELECT FirstName, LastName
FROM Employee
WHERE LastName SUBSTRING(LastName,1,3) = 'Hay';

Note that use of LIKE will not always reference an index for example this query would not use an index

WHERE LastName LIKE '%es'

Other examples of SARG’s:

LastName = 'Hayes'
Amount > 0
Amount BETWEEN 10 AND 20

I’ve also seen UDF’s in place in WHERE clauses and unsuprisingly, performance is poor.

Get the query using a SARG and there is a higher chance that the optimizer will use an index.

7/ Ensure that foreign keys in are in place

Please refer to my post on the importance of foreign keys and how they can help improve query performance.

8/ Increasing chances of using the query cache (MySQL)

Minor changes to queries to make them deterministic can increase query cache hit rate. For example if a query uses CURDATE(), then the results will not be cached.

9/ Deal in sets not cursors

Try and build SQL queries which deal with sets of data. Leave any row by row processing to the application layer.

10/ Allocate more memory to buffers

You need a solution quickly and you have identified your server is under memory pressure. You have spare RAM available and so it is an easy task to allocate more memory to MS SQL or MySQL database memory buffers. Personally, I will always try and carry out a more exhaustive analysis of the server but if time does not permit that then allocate the memory to improve performance and analyse a typical workload on a staging server to determine where other performance improvements can be made.

It is important to ensure that these issues are not brushed under the carpet after increasing hardware resource. Think about it for a moment. Your application has been slowing down and user complaints have increased. What’s an obvious cause of that? – Growth.

So in order to effectively deal with growth, from a DBA perspective, the database server needs to be monitored for performance problems with issues reported to the relevant technical staff at the earliest opportunity. Adding hardware will always help improve performance but the application will never scale very well unless the issues “underneath the bonnet” in the database layer are effectively dealt with.

Database views explained

December 30, 2011 by Andy Hayes 2 Comments

Database views are virtual tables which are built up using a SELECT query. The query may contain one or more tables.

To help explain database views, here is a quick script created using SQL Server to create some tables and data. (If you want to create the tables using MySQL, then substitute the part “IDENTITY(1,1)” with “AUTO_INCREMENT”)

CREATE TABLE Department
(
ID TINYINT PRIMARY KEY IDENTITY(1,1),
Department VARCHAR(50) NOT NULL
)
GO

CREATE TABLE Staff
(
ID INT PRIMARY KEY auto_increment,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Salary INT NOT NULL,
DepartmentID TINYINT NOT NULL REFERENCES Department(ID)
)
GO

INSERT INTO Department(Department)
VALUES('Sales'),('Finance'),('Technology');

INSERT INTO Staff(FirstName, LastName, Salary, DepartmentID)
VALUES
('John','Smith',50000,1)
,('Raymond','Jones',50000,2)
,('Tracey','Carter',50000,3);

So lets create a view of the data joining the two tables together and presenting certain fields.

CREATE VIEW vwStaff
AS
SELECT
S.ID AS StaffID
, S.FirstName
, S.LastName
, D.Department
FROM Staff S
JOIN Department D ON S.DepartmentID = D.ID
GO

GRANT SELECT ON vwStaff TO application_developer;

Instead of selecting from the tables, the application developer would select from the view “vwStaff” to retrieve the data they needed.

SELECT * FROM vwStaff

StaffID     FirstName            LastName             Department
----------- -------------------- -------------------- --------------------
1           John                 Smith                Sales
2           Raymond              Jones                Finance
3           Tracey               Carter               Technology

(3 row(s) affected)

There is no performance difference between running the SQL or running database views. If you look at both SQL Server and MySQL, the execution plans are identical between the SQL and database views.

MySQL….

mysql>
explain SELECT S.ID AS StaffID
, S.FirstName
, S.LastName
, D.Department
FROM Staff S
JOIN Department D ON S.DepartmentID = D.IDG;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: S
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: D
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 1
          ref: dbadiaries.S.DepartmentID
         rows: 1
        Extra:
2 rows in set (0.00 sec)

mysql> explain SELECT * FROM vwStaffG;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: S
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: D
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 1
          ref: dbadiaries.S.DepartmentID
         rows: 1
        Extra:
2 rows in set (0.00 sec)

SQL Server….

Advantages of Database Views

Data protection – create views to protect sensitive data. For example the salary column was hidden in my example above.
Code re-use – simplifies application development. If some business logic changes for presenting data, change it once in the view and not many times in different places.
Simplifies access to data for untrained users. Lets say you have a department of data analysts who are experts with Excel but can’t write SQL. Create them a view of the data and have them load that into Excel. They can’t write SQL but they can choose a view from a list of available datasets – everyone is happy.
Performance – lets say your data analysts can write SQL but you don’t want them writing run-away queries which bring your database server down. Create them a view instead of allowing them direct access to the data.
If you are using BCP to export data, you can format the data through views as BCP data formatting is rather limited.
Working around non-deterministic function limitations.

Pitfalls of using Database Views

Ease of use – you’re probably thinking, what’s he talking about? Well it is very easy to think “I’ll join to that view I created in the database to satisfy this new query I am writing”. You have to remember that database views are SELECT queries. Every time you run one or join to one, the query is run on the fly to produce the results of the view. As an author of a query and using the view above, if you only needed access to the data in the “Staff” table to form part of your query, why join to a view which pulls in data from the “Department” table? That is extra IO overhead which when dealing with large volumes of data or frequent batch requests can have negative performance implications. It is better to have access to either the table (if you are allowed to) or another view of the “Staff” table which allows you to view the fields which you require and use that.
Nested database views – don’t create views based on other views as this has a negative performance impact. Better to create views from the base tables.