都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心としたマイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の記事も投稿するようにしています。

Summary of SQL Server tuning - Part 3 - (How much performance does it improve?)

Also today, I am translating the old Japanese post to English while watching "HAKONE EKIDEN". Which does get first prize, Soka-Univ. or Komazawa-Univ? I am looking forward to know the result. In this time, I try to translate following post.
ryuchan.hatenablog.com

I've been slacking a bit on writing my blog lately. I'd like to resume it again. This is a supplement to post on “
Summary of SQL Server Tuning - Part 1 - (Examine indexes) - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo
"

Let's start with a quick way to see how many indexes are missing for a query that executing the against database. Let's issue the following query in SQL Server Management Studio (just a simple count...).

SELECT DB_NAME(database_id) as [Database name],
       count(*) as [Number of expected indexes that do not exist]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140525/20140525090601.png

Depending on the number of outputs, a sad reality awaits. Because the indexes have not been created, it is possible to grasp the current situation where the DBMS is under a tremendous load. It is difficult to know what to do with just this, so I add “avg_user_impact” to query that used on “Summary of SQL Server Tuning - Part 1 - (Examine indexes) - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo". This is the percentage improvement in performance when an index is created.

USE AdventureWorks2012
SELECT gs.avg_user_impact AS [Predicted query performance improvement rate],
       gs.last_user_seek AS [Time of last seek],
       id.statement AS [Table name] ,
       id.equality_columns AS [Column that can be used for equality predicates],
       id.inequality_columns AS [Column that can be used for inequality predicates] ,
       id.included_columns AS [Column required as included columns],
       gs.unique_compiles AS [Number of compilations and recompilations],
       gs.user_seeks AS [The number of seeks generated by the query]
 FROM  sys.dm_db_missing_index_group_stats AS gs
       INNER JOIN sys.dm_db_missing_index_groups AS ig
			ON gs.group_handle = ig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS id
			ON ig.index_handle = id.index_handle
 WHERE id.[database_id] =DB_ID() Order By gs.last_user_seek ASC

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140525/20140525092400.png

The result was an improvement of 83.09%. I think we need to create indexes, but it is necessary to think about it carefully because it can cause performance degradation and capacity increase when updating tables (Be especially careful with the included column....). By adding this column, I think it will be an indicator of which indexes should be prioritized. Good luck and improve your query performance (If I had properly added this in my previous post, I wouldn't have needed to add it....).

Ginger is a really useful website to check my grammar!!
www.getginger.jp

Summary of SQL Server Tuning - Part 2 - (Filtered Index)

Happy New Year to all! I am translating the old Japanese post while watching "Hakone Ekiden". This year, Tokai Univ. team is quite strong. Next, I try to translate following post.
ryuchan.hatenablog.com

Today, I suddenly realized that the "SQL Server Tuning" series ended with "Part 1” lol. So I thought it would be a good idea to end with Part 1, so I'd like to put together Part 2 (Too random ….). In this post, I explain about "filtered index". This feature added in SQL Server 2008.

A filtered index, for example, if the contents of one field of a table are mostly NULL, can create an index that contains only records where the value of that field is not NULL (The filter condition is like what you would specify in a Where clause.).

Let's try with the usual “AdventureWorks" database. The target table is “Sales.SalesOrderDetail".
http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415040851.png

Create an index on the “CarrierTrackingNumber" column.
http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415041111.png

The number of rows in the Leaf level is "121317", which matches the total number of rows.
http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415081730.png

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415082921.png

Similarly, create an index on the “CarrierTrackingNumber" column. However, in this time, set the filter condition to "CarrierTrackingNumber IS NOT NULL".
http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415042123.png

The number of rows in the Leaf level is "60919", which is the same as the number of rows excluding the null records.
http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415081911.png

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20140415/20140415082903.png

By using "filtered index", you can perform an efficient search that matches certain conditions. Also, since the number of physical rows stored in the index will be reduced, the I / O load will be reduced, and the speed will be improved.

However, it is quite difficult to design an index that takes the business perspective and it requires a lot of its experience.

Ginger is a really useful website to check my grammar!!
www.getginger.jp

Summary of SQL Server Tuning - Part 1 - (Examine indexes)

Happy New Year to all! I decided to translate old Japanese version posts to English for studying English in this year. First of all, I try to translate following post.
ryuchan.hatenablog.com

I would like to summarize the tuning of SQL Server. First, let's look at how to extract queries that don't use indexes, SQL Server tuning makes heavy use of dynamic management views that start with "dm_*". At this time, mainly use the following three tables to find queries that don't use indexes.

  • sys.dm_db_missing_index_group_stats
    Get information about the missing index group.
  • sys.dm_db_missing_index_groups
    Table required to JOIN sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_details.
  • sys.dm_db_missing_index_details
    Get detailed information about the missing indexes.

You can use the following query to find the missing index. (Using the usual AdventureWorksDB.)

USE AdventureWorks2012
SELECT gs.last_user_seek AS [Time of last seek],
       id.statement AS [Table name] ,
       id.equality_columns AS [Columns that can be used for equality predicates],
       id.inequality_columns AS [Columns that can be used for inequality predicates] ,
       id.included_columns AS [Columns required as included columns],
       gs.unique_compiles AS [Number of compilations and recompilations],
       gs.user_seeks AS [Number of seeks generated by the query.]
 FROM  sys.dm_db_missing_index_group_stats AS gs
       INNER JOIN sys.dm_db_missing_index_groups AS ig
			ON gs.group_handle = ig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS id
			ON ig.index_handle = id.index_handle
 WHERE id.[database_id] =DB_ID() Order By gs.last_user_seek ASC


After executing some queries without indexes, the above query will look like the below. I explain how to improve the area in the red frame.
http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20130923/20130923124748.jpg

  • Last seek time
    2013-09-23 03:42:41.773
  • Table name
    [AdventureWorks2012]. [Purchasing]. [PurchaseOrderHeader].
  • Columns that can be used for equality predicates
    [RevisionNumber].
  • Columns that can be used for inequality predicates
    NULL
  • Columns required as inclusive columns
    [PurchaseOrderID], [Status], [EmployeeID], [VendorID], [ShipMethodID], [OrderDate], [ShipDate], [SubTotal], [TaxAmt], [Freight], [TotalDue], [ModifiedDate].
  • Number of compilations and recompilations
    1
  • Number of seeks generated by the query
    2

From the above results, it can be determined that the RevisionNumber column, which has no index is specified in the WHERE clause. In addition, you can see that the following included columns are required.

[PurchaseOrderID], [Status], [EmployeeID], [VendorID], [ShipMethodID], [OrderDate], [ShipDate], [SubTotal], [TaxAmt], [Freight], [ TotalDue], [ModifiedDate]

Therefore, you can solve this problem by executing the following query.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderHeader_RevisionNumber
ON [Purchasing].[PurchaseOrderHeader] ([RevisionNumber])
INCLUDE ([PurchaseOrderID],[Status],[EmployeeID],[VendorID],[ShipMethodID],[OrderDate],[ShipDate],[SubTotal],[TaxAmt],[Freight],[TotalDue],[ModifiedDate])

After executing the above query, if you run the script again, you will get the following result. The problematic rows have disappeared. A proper index has been created.

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20130923/20130923130127.jpg

However, even without this kind of examination, it is possible to handle by checking the execution plan of SQL Server Management Studio during daily development. When the execution plan is displayed, if there are not enough indexes, they will be displayed as shown in the red frame below.

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20130923/20130923132105.jpg

Right-click on the red frame to display the menu as shown below. Click "Missing Index Details..." from the menu.

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20130923/20130923132224.jpg

As shown in the figure below, CREATE INDEX statement will be generated.

http://cdn-ak.f.st-hatena.com/images/fotolife/k/koogucc11/20130923/20130923132428.jpg

The generated query is shown below. It also shows what percentage improvement, etc. (In this example, it seems to improve by 83.0941%.)

/*
Missing Index Details from SQLQuery1.sql - SQLSERVERTEST.AdventureWorks2012 (SQLSERVERTEST\sqlservertest (53))
The Query Processor estimates that implementing the following index could improve the query cost by 83.0941%.
*/

/*
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Purchasing].[PurchaseOrderHeader] ([RevisionNumber])
INCLUDE ([PurchaseOrderID],[Status],[EmployeeID],[VendorID],[ShipMethodID],[OrderDate],[ShipDate],[SubTotal],[TaxAmt],[Freight],[TotalDue],[ModifiedDate])
GO
*/

Be sure to check your queries in SQL Server Management Studio on a regular basis.

Ginger is a really useful website to check my grammar!!
www.getginger.jp

年末に記事書いてみる / I'll write an article at the end of the year.

The year is almost over. There weren't many articles this year, either. Next year will be a year of new challenges, so I will try my best to write articles next year. Have a happy new year, everyone!

Following article is still being read. Tuning is an eternal issue.
ryuchan.hatenablog.com

 今年ももうすぐ終わりです。今年も記事数少なかったなぁ。来年はまた新たな挑戦の年になるので、来年こそ記事頑張って書いていきたいと思います。皆様、良いお年を!

 この記事が未だに読まれているなぁ。チューニングは永遠の課題ですね。
ryuchan.hatenablog.com

Useful SQL functions, syntax, and more - Part 19 (FAST?)

Recently I started working on SQL Server just a little bit on side job. Tuning is an area that I personally like very much because it's like putting the puzzle back together. While doing some research, I found a query hint that I'd never used called "FAST".

FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows. This result is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

Is there anything this convenient for the keyword "fast search"? But I was interested in it, so I'll check out how it works as soon as possible. I'll use the following query.

SELECT 
    SOH.SalesOrderID,
    SOH.CustomerID,
    SOH.SalesPersonID
FROM 
    Sales.SalesOrderHeader SOH
WHERE 
    SalesPersonID = 279;

Above execution plan as follows.
f:id:koogucc11:20200831201858p:plain

Next, let's add OPTION (FAST n). n = 100.

SELECT
    SOH.SalesOrderID,
    SOH.CustomerID,
    SOH.SalesPersonID
FROM 
    Sales.SalesOrderHeader SOH
WHERE 
    SalesPersonID = 279
OPTION (FAST 100);

 Above Execution plan as follows.
f:id:koogucc11:20200831201946p:plain

Let's compare the two plans. The number of rows is different and the cost is lower with OPTION (FAST 100) added.
f:id:koogucc11:20200831202923p:plain

The estimated number of rows is different. The one with OPTION (FAST 100) has 100. There is an attribute "EstimateRowsWithoutRowGoal = 429" that I've never seen before. (My knowledge of SQL Server stops at version 2012, but I don't remember this being around for a long time.)
f:id:koogucc11:20200831203652p:plain

The estimated number of rows has been reduced here as well, and "EstimateRowsWithoutRowGoal = 31465" has been added.
f:id:koogucc11:20200831204006p:plain

Hmmm, I haven't written an article in a while, but I'm halfway through. But I guess this is convenient because it's a good story to write an article about above, lol.

便利な SQL の関数とか、構文とか、その他色々まとめてみる - その19 (FASTって?)

 最近、副業でほんの少しだけ SQL Server のお仕事を開始しました。チューニングは、パズルを組みなおすような作業なので個人的に非常に好きな分野です。色々調べものをしていたら、FAST という使ったことのないクエリヒントを見つけました。

FAST number_rows
最初の number_rows を高速検索するためにクエリの最適化を行うことを指定します。 この結果は負以外の整数です。 最初の number_rows を返した後、クエリは実行を続け、完全な結果セットを作成します。

 高速検索というキーワードにこんな都合がいいものあるんかいな?と思いながらも興味を持ったので、早速動作をチェックしてみます。下記のクエリを使用します。

SELECT 
    SOH.SalesOrderID,
    SOH.CustomerID,
    SOH.SalesPersonID
FROM 
    Sales.SalesOrderHeader SOH
WHERE 
    SalesPersonID = 279;

 実行プランは下図の通りです。
f:id:koogucc11:20200831201858p:plain

 次に、OPTION (FAST n) を付加してみましょう。n = 100 にしておきます。

SELECT
    SOH.SalesOrderID,
    SOH.CustomerID,
    SOH.SalesPersonID
FROM 
    Sales.SalesOrderHeader SOH
WHERE 
    SalesPersonID = 279
OPTION (FAST 100);

 実行プランは下図の通りです。
f:id:koogucc11:20200831201946p:plain

 二つのプランを比較してみましょう。件数が違っていたり、Cost が OPTION (FAST 100) を付加したほうが低くなってますね。
f:id:koogucc11:20200831202923p:plain

 推定行数が異なっていますね。OPTION (FAST 100) を付加したほうは 100件になっています。しかも、付加した方には EstimateRowsWithoutRowGoal = 429 とか見たことない属性があります。(知識が 2012 で止まっているのですが、こんなの昔からあったっけ?)
f:id:koogucc11:20200831203652p:plain

 こちらも推定行数が減っており、EstimateRowsWithoutRowGoal = 31465 が付加されています。
f:id:koogucc11:20200831204006p:plain

 うーん、久しぶりに記事を書いてみたけど、中途半端に終わってしまった。しかし、これは記事を書くネタになるから都合がいいかな笑