{"id":71516,"date":"2017-06-30T12:40:03","date_gmt":"2017-06-30T12:40:03","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71516"},"modified":"2021-08-24T13:39:23","modified_gmt":"2021-08-24T13:39:23","slug":"sql-server-2017-new-queries-high-variation-graph","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-2017-new-queries-high-variation-graph\/","title":{"rendered":"SQL Server 2017: New ‘Queries with high variation’ Graph"},"content":{"rendered":"

A few weeks ago I published an interesting article about how to use query store to identify parameterization problems<\/a>.<\/p>\n

At that point I hadn’t played with the new SQL Server 2017. While later, when I did, I got surprised by the new ‘Queries with High Variation’<\/strong> graph included in query store. This new graph can help you identifying parameterization problems.<\/p>\n

\"Queries<\/p>\n

As I explained in the article, parameterization problems creates a high variation of the query execution. Sometimes the query runs well and very fast, sometimes the query will be very slow. Query Store<\/strong> keeps track of the standard deviation of the query execution and, due to the parameterization problem, the standard deviation will be high.<\/p>\n

The new graph, ‘Queries with High Variation’<\/strong>, allows us to easily identify the queries with most severe parameterization problems. We can use the graph to achieve a first view of these queries and only after that get more details using the queries I explained my previous article.<\/p>\n

In the “Based On”<\/strong> box, over the graph, we have two options: Variation or Standard Deviation. The ‘Standard Deviation’<\/strong> option gives a better result to illustrate parameterization problems. I suggest the use of ‘Standard Deviation’<\/strong> together ‘CPU Time’<\/strong> in the box ‘Metric’<\/strong> to achieve better result.<\/p>\n

\"standard<\/p>\n

Let’s do a small walkthrough to illustrate how this new graph will react to a parameterization problem.<\/p>\n

1) The following script will create a new database. Don’t forget to correct the path of the files.<\/p>\n

\n
use<\/span>\u00a0master<\/span>
\n go<\/span> <\/p>\n

—\u00a0Criando\u00a0todo\u00a0o\u00a0ambiente<\/i><\/span>
\n drop<\/span>\u00a0database<\/span>\u00a0if<\/span>\u00a0exists<\/span>\u00a0qstore_demo<\/span> <\/p>\n

CREATE<\/span>\u00a0DATABASE<\/span>\u00a0[qstore_demo]<\/span>
\n ON<\/span>\u00a0PRIMARY<\/span>
\n (<\/span>\u00a0NAME<\/span>\u00a0=<\/span>\u00a0N’qs_demo’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’C:\\dbfiles\\qs_demo.mdf’<\/span>\u00a0,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a0102400<\/span>KB<\/span>\u00a0,<\/span>
\n MAXSIZE<\/span>\u00a0=<\/span>\u00a01024000<\/span>KB<\/span>\u00a0,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a020480<\/span>KB<\/span>\u00a0)<\/span>
\n LOG<\/span>\u00a0ON<\/span>
\n (<\/span>\u00a0NAME<\/span>\u00a0=<\/span>\u00a0N’qs_demo_log’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’C:\\dbfiles\\qs_demo_log.ldf’<\/span>\u00a0,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a050480<\/span>KB<\/span>\u00a0,<\/span>
\n MAXSIZE<\/span>\u00a0=<\/span>\u00a01024000<\/span>KB<\/span>\u00a0,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a020480<\/span>KB<\/span>\u00a0)<\/span>
\n GO<\/span>
\n ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[qstore_demo]<\/span>\u00a0SET<\/span>\u00a0QUERY_STORE<\/span>\u00a0(<\/span>INTERVAL_LENGTH_MINUTES<\/span>\u00a0=<\/span>\u00a01<\/span>)<\/span>
\n go<\/span>
\n ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[qstore_demo]<\/span>\u00a0SET<\/span>\u00a0QUERY_STORE<\/span>\u00a0=<\/span>\u00a0OFF<\/span>
\n GO<\/span> <\/p>\n

USE<\/span>\u00a0qstore_demo<\/span>
\n GO<\/span> <\/span><\/div>\n<\/blockquote>\n

2) Let’s create the database objects, a table and a stored procedure<\/p>\n

\n
—\u00a0create\u00a0a\u00a0table<\/i><\/span>
\n CREATE<\/span>\u00a0TABLE<\/span>\u00a0dbo<\/span>.<\/span>db_store<\/span>\u00a0(<\/span>c1<\/span>\u00a0CHAR<\/i><\/span>(<\/span>3<\/span>)<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>,<\/span>\u00a0c2<\/span>\u00a0CHAR<\/i><\/span>(<\/span>3<\/span>)<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>,<\/span>
\n c3<\/span>\u00a0SMALLINT<\/i><\/span>\u00a0NULL<\/span>)<\/span>GO<\/span>CREATE<\/span>\u00a0NONCLUSTERED<\/span>\u00a0INDEX<\/span>\u00a0NCI_1<\/span>
\n ON<\/span>\u00a0dbo<\/span>.<\/span>db_store<\/span>\u00a0(<\/span>c3<\/span>)<\/span>GO<\/span> <\/p>\n

—\u00a0create\u00a0a\u00a0stored\u00a0procedure<\/i><\/span><\/span><\/div>\n

CREATE<\/span>\u00a0PROC<\/span>\u00a0dbo<\/span>.<\/span>proc_1<\/b><\/span>\u00a0@par1<\/span>\u00a0SMALLINT<\/i><\/span>
\n AS<\/span>
\n SET<\/span>\u00a0NOCOUNT<\/span>\u00a0ON<\/span>
\n SELECT<\/span>\u00a0c1<\/span>,<\/span>\u00a0c2<\/span>\u00a0FROM<\/span>\u00a0dbo<\/span>.<\/span>db_store<\/span>
\n WHERE<\/span>\u00a0c3<\/span>\u00a0=<\/span>\u00a0@par1<\/span><\/span><\/div>\n
GO<\/span> <\/span><\/div>\n<\/blockquote>\n

3) Insert a lot of records. This will take a while to execute. Pay heed to the uneven data distribution in field c3.<\/p>\n

\n
SET<\/span>\u00a0nocount<\/span>\u00a0ON<\/span>
\n go<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0[dbo]<\/span>.<\/span>db_store<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>c1<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c2<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c3<\/span>)<\/span>
\n SELECT<\/span>\u00a0’18’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘2f’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02<\/span> <\/p>\n

go<\/span> 20000<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0[dbo]<\/span>.<\/span>db_store<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>c1<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c2<\/span>)<\/span>
\n SELECT<\/span>\u00a0‘171’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘1ff’<\/span> <\/p>\n

go<\/span> 4000<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0[dbo]<\/span>.<\/span>db_store<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>c1<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c2<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c3<\/span>)<\/span>
\n SELECT<\/span>\u00a0‘172’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘1ff’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00<\/span> <\/p>\n

go<\/span> 10<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0[dbo]<\/span>.<\/span>db_store<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>c1<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c2<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0c3<\/span>)<\/span>
\n SELECT<\/span>\u00a0‘172’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘1ff’<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04<\/span> <\/p>\n

go<\/span> 15000<\/span>\u00a0 <\/span><\/div>\n<\/blockquote>\n

4) Execute the procedure. Using ‘0’ as the parameter the procedure will use the index.<\/p>\n

\n
EXEC<\/span>\u00a0dbo<\/span>.<\/span>Proc_1<\/span> 0<\/span>
\n go<\/span> 20<\/span>\u00a0 <\/span><\/div>\n<\/blockquote>\n

5) Execute the procedure again, with a different parameter, ‘2’. This time the procedure shouldn’t use the index, but it will because the plan is already in the cache.<\/p>\n

\n
EXEC<\/span>\u00a0dbo<\/span>.<\/span>Proc_1<\/span> 2<\/span>
\n go<\/span> 20<\/span>\u00a0 <\/span><\/div>\n<\/blockquote>\n

6) Check the ‘Queries with high variation’<\/strong> graph, selecting ‘CPU Time’<\/strong> and ‘Standard Deviation’<\/strong> in the boxes ‘Metric’<\/strong> and ‘Based On’<\/strong><\/p>\n

\"\"<\/p>\n

As you may notice, our query with parameterization problem has the highest standard deviation in the graph. This graph will call our attention to parameterization problems before we start the queries I explained in the previous article.<\/p>\n","protected":false},"excerpt":{"rendered":"

A few weeks ago I published an interesting article about how to use query store to identify parameterization problems. At that point I hadn’t played with the new SQL Server 2017. While later, when I did, I got surprised by the new ‘Queries with High Variation’ graph included in query store. This new graph can……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143527],"tags":[5966],"coauthors":[6810],"class_list":["post-71516","post","type-post","status-publish","format-standard","hentry","category-blogs","category-database-administration-sql-server","tag-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71516"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71516\/revisions"}],"predecessor-version":[{"id":83069,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71516\/revisions\/83069"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71516"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}