Indexed Views

To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to the underlying tables and retrieves the data at the time it is called. An indexed view on the other hand, is a normal view that takes a copy of the underlying data it points to, and stores it locally. This way, the joins and aggregations that are processed at run-time in a normal view, are already materialized, so when queried, it’s as fast as querying a normal table. Therefore, another name for indexed view is “materialized view”. This is what it’s called in Oracle. Creating To make a normal view an indexed view, you need to do two things. First you need to enable SCHEMABINDING for the view. Schemabinding essentially locks the underlying DDL schemas for the tables that the view references. This prevents any DDL changes from being made to the referenced tables. If you want to make a change to the tables, you need to drop the view first. Let’s create this new view using the AdventureWorks Database: [cc lang=”sql”] CREATE VIEW Sales.OrderTotals WITH SCHEMABINDING AS SELECT SalesOrderID = soh.SalesOrderID, OrderTotal = SUM(sod.UnitPrice), OrderCount = COUNT_BIG(*) FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID GROUP BY soh.SalesOrderID [/cc] Okay, we’re half […] Continue reading ...

Auto Update Statistics & Auto Create Statistics

The ability to create and update statistics is a crucial liberty for the query optimizer. The optimizer needs to know how it is going to query underlying tables, and when the statistics do not match the data, it is very probable that it will choose a non-efficient method for querying. I’ve seen and heard a lot of debate from DBA’s who think they should turn this off. Of all those DBA’s I think there is one who was correct, and he ended up convincing me of his scenario so I’m not as closed minded as I was before. His server was so bottle-necked during peak time, that he updated the stats off hours. I’m very weary of those who blindly say “it’s better to turn it off” without any factual statistics to back them up. Most of the DBA’s that think they should turn it off are stuck in the SQL Server 7.0 days. At that time, turning this feature off was acceptable because they interfered with the currently running queries. Example Case in point, we had a DBA who did turn this feature off in an environment that mirrored production. I overheard the developers complaining how their queries took twice as long on this box. Looking at perfmon, I saw no physical bottlenecks. Since this was the case, I turned to statistics and found both auto update and auto create turned off. After turning it back on, the box was just as fast as production. Long story short, these […]

Using DBCC UPDATEUSAGE

When DBCC UpdateUsage is run, page and row counts are updated. When this information is incorrect, it can give incorrect reports for how much space is used by tables and indexes. The most likely cause for incorrect usage stats is when a database is upgraded to a new compatibility level of SQL Server (i.e. 2000 to 2005). Therefore it’s important to run this command after the statistics have been updated. It may not be a bad idea to run on a weekly or monthly schedule if your database undergoes frequent DDL modifications. To execute you can run one of the following commands: [cc lang=”sql”] Use MyDB; GO DBCC UPDATEUSAGE(0); — Execute for the current database GO DBCC UPDATEUSAGE(MyDB); — Execute using the database name GO DBCC UPDATEUSAGE(MyDB); — Execute using the database ID [/cc] This may take some time, so make sure to run it in a non production environment or during off hours as it may cause blocking. You can also run for a specific table or index. If you run for a table, then the usage stats are updated for all indexes on that table. [cc lang=”sql”] — Update for a table (and it’s indexes) DBCC UPDATEUSAGE(AdventureWorks, ‘Sales.SalesOrderDetail’); — Update usage for a single index DBCC UPDATEUSAGE(AdventureWorks, ‘Sales.SalesOrderDetail’, ‘IX_SalesOrderDetail_ProductID’); [/cc] That’s basically it. Now you may be running these commands and are not seeing any output. Well, that’s because the usage stats are already correct. But if you want to spoof the stats in order to see the […] Continue reading ...

Using IDENTITY_INSERT

The only way to insert values into a field that is defined as an “IDENTITY” (or autonumber) field, is to set the IDENTITY_INSERT option to “ON” prior to inserting data into the table. To illustrate, let’s create a table that has an identity column defined. [cc lang=”sql”] — Create table with identity column CREATE TABLE MyOrders ( OrderID int IDENTITY(1,1), ProductName varchar(20) ); — Now try to insert into the table INSERT INTO dbo.MyOrders ( OrderID, ProductName ) VALUES ( 1, ‘socks’ ); [/cc] Executing the above code, we see that we get the following error: Cannot insert explicit value for identity column in table ‘MyOrders’ when IDENTITY_INSERT is set to OFF. To fix this, we simply need to set the IDENTITY_INSERT to On. Keep in mind, the minimum permissions needed to perform this action is database owner (dbo). [cc lang=”sql”] SET IDENTITY_INSERT dbo.MyOrders ON; INSERT INTO dbo.MyOrders ( OrderID, ProductName ) VALUES ( 1, ‘socks’ ); SET IDENTITY_INSERT dbo.MyOrders OFF; [/cc] You want to make sure to turn this off after it’s used. Otherwise, you will not be able to turn this feature on for any other table from within the same session until it is off for this table.

Monitor Queries against a Table

I recently had a need to monitor inserts against a particular table in order to determine what kind of locks they were acquiring. Being that we could not run traces on the system, I had to resort to a roll-your-own approach to monitoring the table. The goal was to determine what kind of locking was occurring, and it would also be nice to be able to associate it to the executing query. The following code uses DMVs in order to trap a sample of the queries running against it. Please note that this will not trap all queries, however it will work in getting a good number of samples. This script will run in an endless loop so be sure to hit cancel at some point. Also, for some reason the SQL Statement will not always be trapped. It was not that important for me, as I mostly needed the locks, however if someone figures it out, please post. [cc lang=”sql”] — Capture query activity against a table using DMVs DECLARE @TableName varchar(255); — Specify the table you want to monitor SET @TableName = ‘Sales.SalesOrderDetail’; DECLARE @ObjectID int; SET @ObjectID = (SELECT OBJECT_ID(@TableName)); IF OBJECT_ID(‘tempdb..##Activity’) IS NOT NULL BEGIN DROP TABLE ##Activity; END; — Create table SELECT TOP 0 * INTO ##Activity FROM sys.dm_tran_locks WITH (NOLOCK); — Add additional columns ALTER TABLE ##Activity ADD SQLStatement VARCHAR(MAX), SQLText VARCHAR(MAX), LoginName VARCHAR(200), HostName VARCHAR(50), Transaction_Isolation VARCHAR(100), DateTimeAdded DATETIME; DECLARE @Rowcount INT = 0; WHILE 1 = 1 BEGIN INSERT INTO ##Activity SELECT […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!
css.php