Archive for the ‘SQL Server’ Category

h1

SQL Server Indexed Views Barney Style

June 26, 2012

During my first few weeks at a new client I was asked to do some DBA tasks that I don’t normally do.  They were in the process of developing a new application and as a result a legacy DB was being carried over inside a new DB as a series of views instead of physical tables.  While the views rendered the data correctly they query performance went from a matter of less than 10 seconds to more than 10 minutes, and this was just on development data, not a full production cycle.  So with the performance blown out of the water they asked me to do a some research and see what was causing the slow down.

The first thing I did was looked at the tables in the legacy DB to see if there were indexes on the corresponding tables, and as I’m sure you’ve guessed, there were.  As I’m sure you have also deduced the indexes weren’t created in the new DB on the views.

At this point the only thing I really knew about creating indexes on views was that you could.  So I did a little digging and found out what was required to create and indexed view.  Of course I didn’t discover all of this at once, but instead after every hurdle I cleared I was presented another one.  So for your benefit and mine, I’m going to share with you what I’ve learned about indexed views.  As you can tell by the title, this is not an all inclusive dissertation on SQL Server indexed views.  This will be very much an introduction so I hope you get what you need from it.

So the first thing that I tried that didn’t work was scripting out the index from the old table and try adding it to the view.  This brought up the first requirement I found.  I got an error saying the view didn’t have Schema Binding on it.  I had no idea what this meant but I found the MSDN article that listed what Schema Binding was:

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

So I did what was suggested and dropped and created the view with schema binding.  Great the view was created so I was ready to create the index again.  That was of course when the next error popped up.  The underlying tables where in different schemas which isn’t a big deal except, on top of that they had different owners for each schema, and that my friends is not allowed.

At this point my brain actually started to work properly, and in a stroke of, well not genius but somewhere above a drunken stupor and genius, I decided to search out how to create an indexed view instead of figuring out each error as it popped up.  That lead me to this MSDN article.

A view must meet the following requirements before you can create a clustered index on it:

  • The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

  • The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

  • The view must not reference any other views, only base tables.

  • All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

  • The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

  • User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

  • Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

  • All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic….

The list goes on from there, but you get the point.  There is a lot of prep work to do before creating an indexed view.  You might find it hard to go back and create an index on a view if you hadn’t planned it out well in advance.  with that being said here is the basic syntax for creating an indexed view, assuming all other conditions are met.


SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

IF OBJECT_ID ('dbo.vTest', 'view') IS NOT NULL
DROP VIEW dbo.vTest ;
GO
CREATE VIEW dbo.vTest
WITH SCHEMABINDING
AS
SELECT SUM(ColA*ColB) AS Revenue,
ColC, ColD
FROM dbo.TableA AS a, dbo.TableB AS b
WHERE a.ColE = b.ColE
GROUP BY ColC, ColD;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON dbo.vTest (ColC, ColD);

I hoped this helped get you on your way to creating indexed view.  For more information please check out these MDSN Articles 1, 2  and 3

h1

Annoying DB Restore Error

May 30, 2012

The other day I was at a client site, and I was trying to set up my development environment to begin working on some SSIS packages.  I was on the next to last restore of 12 separate DB (all told, over 500GB), when the restore failed.  Up to this point I hadn’t had any issues at all.  Then this sucker popped up:

 

TITLE: Microsoft SQL Server Management Studio
——————————
Restore failed for Server.  (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText]&EvtID=Restore+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SomeNewDB.mdf’ is claimed by ‘SomeNewDB_UserData'(3) and ‘SomeNewDB_Primary'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
——————————
BUTTONS:
OK

The link provided in the first part of the message led absolutely nowhere, and the second one wasn’t much help either.  Strangely enough I read the error message a few times, and for once I was able to determine what the problem was.  Here was the key to the error message:  File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ SomeNewDB.mdf’ is claimed by ‘SomeNewDB_UserData'(3) and ‘SomeNewDB_Primary'(1).

So what I did was script out the restore and discovered that I was trying to use the .mdf file twice.

RESTORE DATABASE [SomeNewDB]

FROM DISK = N’C:\SQLBackup\SomeNewDB.bak’

WITH FILE = 1,

MOVE N’SomeNewDB_Primary’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_UserData’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_Log’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB_1.ldf’,

NOUNLOAD, REPLACE, STATS = 10

GO

 

So I changed the second file to .ndf and ran the script and it seemed to work.

RESTORE DATABASE [SomeNewDB]

FROM DISK = N’C:\SQLBackup\SomeNewDB.bak’

WITH FILE = 1,

MOVE N’SomeNewDB_Primary’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_UserData’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.ndf’,

MOVE N’SomeNewDB_Log’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB_1.ldf’,

NOUNLOAD, REPLACE, STATS = 10

GO

So if you ever run into that problem make sure your not trying to reference the .mdf file twice inside your restore statement.

h1

Indexing Strategies for OLTP Databases.

March 24, 2012

Let me start by saying that I am not a DBA, I am a BI Developer.  I work with some really bright DBA’s and they can tell you much more about this topic then I can, and probably in great depth, with many terms like PERFMON, DMV and querying a bunch of those Sys tables.  To this point in my career I’ve haven’t had the exposure or need to think about many of the DBA tasks.  I mostly worry about building packages, cubes and repots.  This just isn’t going to cut it.  As I’ve progressed in my career as a consultant, I realized that clients are going to be expecting more and more from me, and rightfully so.  As a junior developer I could lean on the more senior guys to help carry me when things outside of my knowledge base came up, but now I need to become one of those senior guys, and that means <gulp> moving outside of my comfort zone and diving into topics that are in areas other than strictly Business Intelligence.  So for my first foray into the land of the DBA I decided to look into Indexes, and more specifically indexing strategies in OLTP systems, since my only real exposure to them is reading from them when loading data into a data warehouse, and not writing to them. 

Now some of this might seem pretty basic to you but this is how I think about them.  Online Transactional Processing (OLTP) systems are primarily used for data entry. Since the primary function of them is to input data, and usually high volumes of data over an extended period of time, the primary performance concern for an OLTP system is how fast can it input the data. Now that is over simplifying it a bit, because its not like every new insert is coming from a new customer ordering a new item.  Most likely it’s a returning customer ordering an item that has been ordered by other customers before.  So obviously we aren’t going to put duplicate data inside our OLTP system (3NF and all that goodness) so there will have to be some reads performed on the database. This is where the indexes come in.  You want to create indexes that will speed up the reads you are doing on your OLTP system to enable you to do faster inserts updates and deletes to your system.  The problem is that indexes can actually slow down the inserts updates and deletes.  The trick then is to find the “sweet spot” where the indexes speed up the  overall process, not just the reads on your queries. As you can imagine there is no single way to do it, no one “right” answer.  I did however find some tips out there to help fledgling DBAs and other wannabes like me, keep from making a fool of ourselves when the cool kids (DBAs) come behind and look at the indexes we’ve implemented.  Here are some of the Highlights and I’ve included links to them.

Well I hope this has helped someone other than me. One thing I have learned since I started blogging, is that if I can explain it in writing then I have a pretty good grasp of the concept, and I can always fall back on the blog that I wrote as a refresher (convenient!).