SQL Server Indexed Views: The Basics. SQL Server views are helpful in many ways, for example in encapsulating complex multi- table query logic, allowing us to simplify client code. Views make queries faster to write, but they don’t improve the underlying query performance. However, we can add a unique, clustered index to a view, creating an indexed view, and realize potential and sometimes significant performance benefits, especially when performing complex aggregations and other calculations. In short, if an indexed view can satisfy a query, then under certain circumstances, this can drastically reduce the amount of work that SQL Server needs to do to return the required data, and so improve query performance. Indexed views can be a powerful tool, but they are not a ‘free lunch’ and we need to use them with care. Once we create an indexed view, every time we modify data in the underlying tables then not only must SQL Server maintain the index entries on those tables, but also the index entries on the view. This can affect write performance. In addition, they also have the potential to cause other issues. For example, if one or more of the base tables is subject to frequent updates, then, depending on the aggregations we perform in the indexed view, it is possible that we will increase lock contention on the view’s index. This article will start from the basics of creating indexed views, and the underlying requirements in order to do so, and then discuss their advantages and the situations in which they can offer a significant boost to query performance. We’ll also consider the potential pitfalls of which you need to be aware before deciding to implement an indexed view. ![]() Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so. Part of check-in [564c7340] Less dramatic changes to the source-id following an edit. Modify the way that the amalgamation is constructed to give it the opportunity. ![]() Nobody sets out to write overly complex queries. Unfortunately, however, applications grow more complex as the users demand new features, and so the accompanying queries grow more complex also. We don’t always have time to rewrite a query completely, or may not even know a better way to write it. Standard SQL Server views can help. When we encapsulate complex multi- table query logic in a view, any application that needs that data is then able to issue a much simpler query against the view, rather than a complex multi- JOIN query against the underlying tables. Views bring other advantages too. Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression. They aren't there for reasons. We can grant users SELECT permissions on the view, rather than the underlying tables, and use the view to restrict the columns and rows that are accessible to the user. We can use views to aggregate data in a meaningful way. Let’s say we need to run various queries against the Adventure. Works. 20. 12 database to return information regarding items that customers have purchased. The query in Listing 1 joins five tables to get information such as the client name, the order number and date, the products and quantities ordered. SELECT CUST. Customer. ![]() ID, PER. First. Name, PER. Last. Name, SOH. Sales. Order. ID, SOH. Order. Date, SOH.[Status], SOD. Product. ID, PROD. Name, SOD. Order. Qty. FROM Sales. Sales. Order. Header. SOH INNERJOINSales. Sales. Order. Detail. SOD ONSOH. Sales. Order. ID=SOD. Sales. Order. ID INNERJOINProduction. Product. PROD ONPROD.Product. ID=SOD. Product.ID INNERJOINSales.Customer. CUST ONSOH. Calendario Liga Zon Sagres Download Adobe there. Customer. ID=CUST.Customer. ID INNERJOINPerson. Person. PER ONPER. Business. Entity. ID=CUST. Person. ID; Listing 1. Notice that we use two- part naming for all tables. Not only is this a good practice, it’s also a requirement when creating an indexed view (we’ll discuss further requirements as we progress). Let’s assume that many applications need to run queries like this, joining the same tables, and referencing the same columns in various combinations. To make it easier for our application to consume this data, we can create a view. Create a View. Listing 2 creates a view based on our query definition, as shown in Listing 2. CREATEVIEWSales. v. Customer. Orders. WITHSCHEMABINDINGAS < Select. Statmenetfrom. Listing. Listing 2. Note that the WITH SCHEMABINDING option is included here and is a requirement for creating an index on the view, which we’ll want to do shortly. This option stipulates that we cannot delete any of the base tables for the view, or ALTER any of the columns in those tables. In order to make one of these changes, we would have to drop the view, change the table, and then recreate the view (and any indexes on the view). Now, each application simply has to run a much simpler query referencing the view, as shown in Listing 3. SELECT Customer. ID, First.Name, Last.Name, Sales. Billing Software For Stationery Shop In Singapore . Order. ID, Order.Date, Status, Product.ID, Name, Order.Qty. FROM Sales. Customer. Orders. CO; Listing 3. However, when looking at the execution plan (Figure 1) we can see that SQL Server still performs index scans against each of the five underlying tables. Figure 1. Likewise, the STATISTICS IO output (Figure 2) shows that SQL Server performed 2,1. Figure 2. The execution plan reports the query cost as 6. Figure 3. Figure 3. We see the exact same execution plan, STATISTICS IO output, and query cost if we run the query in Listing 1 again. Although the use of the view made writing the query easier, it had no impact on query performance. A simple view is just a virtual table, generated from a saved query. It does not have its own physical page structure to use, so it reads the pages of its underlying tables. In other words, when we query a simple view, the optimizer still has to access all of the underlying tables and perform the necessary JOINs and aggregations. It derives cardinality estimations, and hence the query plan, from statistics associated with those tables. Let’s see what happens, however, if we turn our standard view into an indexed view. Create a Unique, Clustered Index. Before we start, I should mention that there are a host of requirements attached to the creation of indexed views, in any SQL Server Edition. We’ll discuss these in more detail in the Indexed View Requirements section, but if you have trouble creating an index on a view, it’s likely you’re breaking one of the rules. In order to turn our normal Sales. Customer. Orders view into an indexed view, we need to add a unique clustered index, as shown in Listing 4. CREATEUNIQUECLUSTEREDINDEXCIX_v. Customer. Orders. ONSales. v. Customer. Orders(Customer. ID,Sales. Order. ID,Product. ID); Listing 4. When we add a unique clustered index to a view, we ‘materialize’ it. In other words, the ‘virtual table’ persists to disk, with its own page structure, and we can treat it just like a normal table. Any aggregations defined by the indexed view are now pre- computed, and any joins pre- joined, so the engine no longer has to do this work at execution time. SQL Server creates statistics for the indexed view, different from those of the underlying tables, to optimize cardinality estimations. A well- crafted indexed view can write fewer pages to disk than the underlying tables, meaning fewer pages queries need to read fewer pages to return results. This means faster, more efficient queries. Use the techniques and tips in this article to ensure your views are optimal! Let’s see the impact of our indexed view on query performance. These examples assume you’re running SQL Server Enterprise Edition, which will automatically consider indexes on a view when creating a query execution plan, whereas SQL Server Standard Edition won’t; you’ll need to use the WITH (NOEXPAND) table hint directly in the FROM clause of any query you wish to use the view (more on this shortly). When we re- run the query from Listing 3, we get the same result set, but the execution plan, shown in Figure 4, looks very different. Rather than several index scans with joins, the optimizer now determines that the optimal way to satisfy the query is to scan the clustered index of our view. Figure 4. The optimizer now reads all the pages required from one index, rather than five, and STATISTICS IO output reveals that this results in a 2. Figure 5. The overall query cost falls to 1. Figure 6. Figure 6. It’s not only queries that reference the view directly that will benefit in this way. Any query that the Optimizer determines the view could satisfy can use the indexed view rather than underlying tables, a process termed view matching. Try re- running Listing 1, which references the base tables rather than our indexed view. Slow in the Application, Fast in SSMS? An SQL text by Erland Sommarskog, SQL Server MVP. Last revision: 2. Copyright applies to this text. See here for font conventions used in this article. This article is also available in Russian, translated by Dima Piliugin. Introduction. When I read various forums about SQL Server, I frequently see questions from. They have identified a slow query or slow stored procedure. They take the SQL batch from the application and run it in SQL Server. Management Studio (SSMS) to analyse it, only to find that the. At this point. they are inclined to think that SQL Server is all about. A similar mystery is when a developer has extracted a query in his stored. No, SQL Server is not about magic. But if you don't have a good understanding. SQL Server compiles queries and maintains its plan cache, it may seem so. Furthermore, there are some unfortunate combinations of different defaults in. In this article, I will try to straighten out why you get this seemingly inconsistent behaviour. I explain how SQL Server compiles a stored procedure, what parameter sniffing. I explain how SQL Server uses the cache, and why there may be. Once you have come this far, you. SSMS. To understand how to address that performance problem in your application, you. I first make a short break from the theme of parameter sniffing. This is followed by two chapters how to deal with. The first is about. In the second chapter I discuss some scenarios. Next comes a chapter where I discuss how dynamic SQL. SSMS and the application with. SQL. In the last chapter I look at how you can use the Query Store that was introduced in SQL 2. At the end there is a. Microsoft white papers and similar documents in this area. Presumptions. The essence of this article applies to all versions of SQL Server, but the focus is on SQL 2. The article includes several queries to inspect the plan cache; these queries run only on SQL 2. SQL 2. 00. 0 and earlier versions had far less instrumentation in this regard. Beware that to run these queries you need to have the server- level permission. VIEW SERVER STATE. For the examples in this article, I use the Northwind sample database. This. database shipped with SQL 2. For later versions of SQL Server you can. Microsoft's web site. This is not a beginner- level article, but I assume that the reader has a. SQL programming. You don't need to have any prior experience of. I will. not explain the basics in depth, as my focus is a little beyond that point. This article will not. Caveat: In some places, I give links to the online version of Books Online. Beware that the URL may lead to Books Online for a. SQL. Server than you are using. On the topic pages, there is a link Other versions, so that you easily can go to. SQL Server. you are using. Or at least that was how Books Online on MSDN was organised when I wrote this article.)How SQL Server Compiles a Stored Procedure. In this chapter we will look at how SQL Server compiles a stored procedure. If your application does not use stored procedures, but. SQL statements directly, most of what I say this chapter is still applicable. But there are further. SQL, and since the facts about stored procedures are confusing enough I have deferred the discussion on dynamic. SQL to a separate chapter. What is a Stored Procedure? That may seem like a silly question, but the question I am getting at is What objects. SQL. Server builds query plans for these types of objects: Stored procedures. Scalar user- defined functions. Multi- step table- valued functions. Triggers. With a more general and stringent terminology I should talk about modules. I prefer to. talk about stored procedures to keep it simple. For other types of objects than the four listed above, SQL Server does not build query plans. Specifically, SQL Server does not create query plans for views and inline- table. Queries like: SELECT abc, def FROM myview. SELECT a, b, c FROM mytablefunc(9)are no different from ad- hoc queries that access the tables directly. When. compiling the query, SQL Server expands the view/function into the query, and. There is one more thing we need to understand about what constitutes a stored. Say that you have two procedures, where the. CREATE PROCECURE Outer_sp AS. I would guess most people think of Inner_sp as being independent from Outer_sp. The execution plan for Outer_sp does not include the. Inner_sp, only the invocation of it. However, there is a very similar situation where. I've noticed that posters on SQL forums often have a. SQL: CREATE PROCEDURE Some_sp AS. DECLARE @sql nvarchar(MAX). MAX). SELECT @sql = 'SELECT ..'. EXEC sp_executesql @sql, @params, @par. It is important to understand that this is no different from nested stored procedures. The generated. SQL string is not. Some_sp, nor does it appear anywhere in the query plan for Some_sp, but it has a. This applies, no matter if the dynamic. SQL is executed through EXEC() or sp_executesql. How SQL Server Generates the Query Plan. Overview. When you enter a stored procedure with CREATE PROCEDURE (or CREATE FUNCTION. CREATE TRIGGER for a trigger), SQL Server verifies that the. But if you refer to non- existing tables, it lets get you. However. at this point SQL Server does not build any query plan, but merely stores. It is not until a user executes the procedure, that SQL Server creates the. For each query, SQL Server looks at the distribution statistics it. From. this, it makes an estimate of what may be best way to execute the query. This. phase is known as optimisation. While the procedure is compiled in one go. This has a very important ramification: the optimizer has no idea. However, it does know what values. Parameters and Variables. Consider the Orders table in the Northwind database, and these three procedures: CREATE PROCEDURE List_orders_1 AS. SELECT * FROM Orders WHERE Order. Date > '2. 00. CREATE PROCEDURE List_orders_2 @fromdate datetime AS. SELECT * FROM Orders WHERE Order. Date > @fromdate. CREATE PROCEDURE List_orders_3 @fromdate datetime AS. DECLARE @fromdate_copy datetime. SELECT @fromdate_copy = @fromdate. SELECT * FROM Orders WHERE Order. Date > @fromdate_copy. Note: Using SELECT * in production code is bad practice. I use it in this article to keep the examples concise. Then we execute the procedures in this way: EXEC List_orders_1. EXEC List_orders_2 '2. EXEC List_orders_3 '2. Before you run the procedures, enable Include Actual Execution Plan under. Query menu. (There is also a toolbar button and Ctrl- M is the normal keyboard shortcut.). If you look at the query plans for the procedures, you will see the. That is, SQL Server seeks the index on Order. Date, and uses a key lookup to. The plan for the third execution is different. In this case, SQL Server scans the table. Keep in mind that in a. Why this difference? To understand why the optimizer makes certain decisions, it is always a good idea to look at what. If you hover with the mouse over the two Seek operators and the Scan operator, you will see the pop- ups similar to those below. Note: the exact appearance depends on which version of SSMS you are using. The samples above were captured with SSMS 2. SQL 2. 00. 8. If you use a later version of SSMS, you may see the items in a somewhat different order, and there are also more items listed. None of those extra items are relevant for the example, which is why I have preferred to keep my original screen shots. The interesting element is Estimated Number of Rows. For the first two procedures, SQL Server estimates that one row will be returned, but for. List_orders_3, the estimate is 2. This difference in estimates explains the different choice of plans. Index Seek + Key Lookup is a good strategy to return a. But if more rows match the seek. SQL. Server will need to access the same data page more than once. In the extreme case where all rows. With a. SQL Server has to read every data page exactly once, whereas with. The Orders table in Northwind has 8. SQL Server estimates that as many. Where Do These Estimates Come From? Now we know why the optimizer arrives at different execution plans: because the estimates are different. But that only leads to the next question: why. That is the key topic of this article. In the first procedure, the date is a constant, which means that the SQL Server only needs to consider exactly this case. It interrogates the statistics for the. Orders table, which indicates that there are no rows with an Order. Date in the. third millennium. All orders in the Northwind database are from 1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |