How useful is your backup?

A backup is worth nothing, if you can’t utilize it for restore.

You probably agree with this well known word of wisdom, don’t you?

This week I had to learn another aspect - the hard way: There are many cases that may require a restore. Only one of those cases is recovering a database from a state of failure. Another situation may require access to legacy data of a meanwhile deactivated database, a database that doesn’t exist anymore. In this case only a successful restore from an older backup, although necessary, may not be sufficient. Here’s the story, why.

I was called by a customer who requested me to pull out two documents from a legacy (SQL Server 2000) database. We found an 8 year old backup we could rely on and restore worked well. Great! But then we discovered the documents had been stored in an IMAGE column and nobody had an idea how it had been encoded at the time it was stored. We soon recognized that we need the original application to get access to the documents, only to recognize that nobody had an idea where to find the installation package. Eventually I could find an 8 year old backup which included the old VB6 code. I could have used this code to rebuild the application and also an installation package only with considerable difficulties, because:

• The application uses some ActiveX components that I may not be able to find anymore.
• The original application run on Windows NT, so I may have to install this OS first, including the required service pack. Even if I find the installation CDs; I doubt, I will be able to find the appropriate device drivers but maybe, I can set up a virtual machine.
• I had to install Visual Studio 6, including the latest service pack and I had no idea where to find the installation CDs.
• The application may rely on deprecated SQL Server 2000 features. So I have to have a SQL Server 2000 installation on which the existing backup has to be restored. If it gets worse, I might even have to install the appropriate service pack to make the application working. I have no idea which service pack this would be, so there’s a chance I have to experiment.

I recognized that all those steps require a big effort and will take some days to accomplish. If only we had built a virtual machine of at least one legacy client system before replacing all client PCs by newer machines…

So, I’d modify the introductory statement like this:

A backup is worth nothing, if you can’t utilize the data that is contained inside this backup.

Multiple statistics sharing the same leading column

SQL Server will not prevent you from creating identical indexes or statistics. You may think that everything is under your control, but have you ever added a missing index that has been reported by the query optimizer, or the DTA? If so, you will certainly have created duplicate statistics. Have a look at the following example:

-- create test table with 3 columns -- filler is only nedded to widen the row create table T1  (   c1 int not null identity(1,1) primary key  ,c2 int not null  ,filler nchar(200) not null default '#'  ) go -- insert 500000 rows with random -- values between 0 and 49999 for c2 insert T1(c2)   select top(500000) abs(checksum(newid())) % 50000     from sys.fulltext_system_stopwords as a         ,sys.fulltext_system_stopwords as b

Before invoking the following SELECT command, allow the actual execution plan being displayed (Strg-M or selecting Query/Include Actual Execution Plan from the menu).

-- get some data select * from T1 where c2 = 100

If you look at the execution plan, you see that the optimizer complains about a missing index on column c2. The prognosticated improvement is about 99%, so adding the index it’s certainly a good idea. Let’s do it:

-- add missing index create nonclustered index ix_1 on t1(c2)

Perfect! The query runs much faster now and needs a lot fewer resources. But have a look at the table’s statistics:

You see three statistics, one for the primary key, a second one for our created index ix_1, and a third one that was automatically created during execution plan generation for the first SELECT statement. This is the statistics, named _WA_Sys…. If the AUTO CREATE STATISTICS option is set to ON, the optimizer will add missing statistics automatically. In our little experiment, the optimizer had to generate this column statistics on column c2 in order to make some assumptions about the number of rows that had to be processed.

And here’s the problem: When creating the index on column c2, a statistics on this column is also created, since every index has a corresponding linked statistics. That’s just the way it works. At the time the index was added, the column statistics on c2 (that _WA_Sys… statistics) already existed. If you don’t remove it manually, this statistics will remain there forever, although it is needless now. All it’s good for is to increase maintenance efforts during statistics updates. You can safely remove this statistics by executing:

drop statistics t1._WA_Sys_...

If you didn’t think about this before, there’s a chance that you’ll find some of those superfluous statistics duplicates inside your database(s). Here’s a query that finds index-related and column-statistics that match on the first column. Looking for matches on the first column is sufficient here, since the optimizer only automatically adds missing single-column statistics.

with all_stats as  (   select s.object_id, s.name as stats_name, s.stats_id, s.has_filter         ,s.auto_created, sc.stats_column_id         ,sc.column_id, c.name as column_name     from sys.stats as s          inner join sys.stats_columns as sc                  on s.stats_id = sc.stats_id                 and s.object_id = sc.object_id                 and sc.stats_column_id = 1          inner join sys.columns as c                  on c.object_id = s.object_id                 and c.object_id = sc.object_id                 and c.column_id = sc.column_id    where objectproperty(s.object_id, 'IsUserTable') = 1 ) select row_number() over(partition by s1.object_id, s1.column_name order by s1.column_name) as stats_no       ,object_name(s1.object_id) as table_name       ,s1.stats_name as stats_name       ,s2.stats_name as identical_stats_name       ,s2.column_name   from all_stats as s1        inner join all_stats as s2                on s1.object_id = s2.object_id               and s1.stats_id != s2.stats_id               and s1.stats_column_id = s2.stats_column_id               and s1.column_id = s2.column_id               and s1.has_filter = s2.has_filter               and s1.auto_created != s2.auto_created

With that query at hand, you may easily find redundant statistics. Here’s a sample output:

If you’d like to find out more about SQL Server statistics, you may want to check out my series of two articles, published recently on the Simple-Talk platform.

Part 1: Queries, Damned Queries and Statistics
Part 2: SQL Server Statistics: Problems and Solutions

It’s free, so you might want to give it a try. Feel free to vote, if you like it!

No automatically created or updated statistics for read-only databases

Ok, that’s not really the latest news and also well documented. But keep in mind that database snapshots also fall into the category of read-only databases, so you won’t see automatically maintained statistics for these as well.

I’ve seen the advice of taking a snapshot from a mirrored database for reporting purposes many times. Even books online contains a chapter Database Mirroring and Database Snapshots, where the scenario is explained.

The intention is to decouple your resource intensive reporting queries from the OLTP system which is generally a good idea. But keep in mind that:

1. Reporting queries are highly unpredictable, and
2. Reporting queries differ from OLTP queries.

So there’s more than only a slightly a chance that query performance will suffer from missing statistics in your reporting snapshot, since the underlying OLTP database simply does not contain all statistics (or indexes) that your reporting applications could take advantage of. These missing statistics can’t be added in the snapshot, because it’s a read-only database. Additionally, you won’t experience any automatic updates of stale statistics in the snapshot. And moreover, any added or updated statistics in the source database are not transferred into the snapshot, of course. This can affect your query performance significantly!

Have a look at the following example:

use master go -- Create test database create database ssBase go use ssBase go -- create test table and insert 10000 rows create table t1  (   c1 int not null primary key  ,c2 int not null  ) go go insert t1(c1,c2)  select top 10000         row_number() over(order by current_timestamp)        ,checksum(newid())    from sys.fulltext_system_stopwords go   -- create snapshot of test database -- NOTE: the filename has to be adjusted -- for your own experimenting create database ssBase_SS on   (    name = ssBase   ,filename = 'e:\SqlTestData\ssBase_SS.ss'   ) as snapshot of ssBase; go

The script above creates a test database with one table and adds some rows to the table before creating a snapshot of this database. As we have never used column c2 in any query (besides the INSERT), there won’t be any statistics for column t1.c2.

Now let’s query the snapshot and have a look at the actual execution plan. Here’s the query:

-- Be sure to show the actual execution plan -- for the following query use ssBase_SS select * from t1  where c2 = 10

Here’s the actual execution plan:

Clearly evident that the optimizer detects a missing statistics, although the options AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS have both been set to ON. The plan reveals a noticeable difference between the actual and estimated number of rows and also a warning regarding the missing statistics.

So, keep that in mind when using snapshots for reporting applications.

If you’d like to find out more about SQL Server statistics, you may want to check out my series of two articles, published recently on the Simple-Talk platform.

Part 1: Queries, Damned Queries and Statistics
Part 2: SQL Server Statistics: Problems and Solutions

It’s free, so you might want to give it a try. Feel free to vote, if you like it!

Blog Mirror on InsideSQL.org

Two weeks ago Frank Kalis invited me to syndicate my blog on the InsideSql platform that he’s driving for some years now. I gratefully accepted, since I find myself in very good company there.

I will also keep this blog active and just mirror my posts to http://www.insidesql.org/blogs/holgerschmeling/. You may want to check out blog entries on http://www.insidesql.org/blogs/. Some interesting stuff going on there. The majority of people uses German for their blog posts, so you should be somewhat familiar with that language. I decided to stick to English. From now on you will find duplicates of my blog posts here and there.

Cheers,
Holger

How to treat your MDF and LDF files

Have you ever set a database to read only? If so, you probably did this by using SQL Server Management Studio or by by executing the regarding ALTER DATABASE command.

Here’s another method that I had to investigate recently: I colleague of mine with some limited knowledge of SQL Server didn’t discover the ALTER DATABASE statement so far. But he knew how to detach and attach a database, because this is what he does all the time in order to copy databases from one computer to another. One day, when he wanted to prevent modifications to one of his databases, he decided to protect the MDF- and LDF- files of these database. Very straightforward he detached the database, set the MDF- and LDF-file to read only mode (by using Windows Explorer) and attached the database after completing this. Voila: SQL Server does not complain at all (I was very surprised about this) and as he expected, the database was displayed as read only in the Object Explorer of SSMS.

As I said: I was very surprised, since I didn’t expect this method would work. Smart SQL Server! But then the trouble began…

Very shortly, after a system reboot, SQL Server started showing the database in question as “suspect”. What happened? I don’t know, but I was able to reproduce the behavior with SQL Server 2008 on Windows Server 2008 R2 every time I repeated the following steps:

• Create a database
• Detach the database
• Set the MDF- and LDF-file to read only
• Attach the database again
• Restart the computer

So, I think you should be careful with any modifications to MDF- and LDF-files outside SQL Server. This not only seems to be true for data itself but also for these files’ attributes. You should always treat MDF- and LDF files as kind of SQL Server’s exclusive property and never touch them!

Just another point to add here: If you try setting a database with read only MDF- or LDF files to read write again by executing ALTER DATABASE, you’ll get an error like this:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\SqlData\User\db2.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\SqlData\User\db2_log.LDF". Operating system error 5: "5(Access is denied.)".
Msg 945, Level 14, State 2, Line 1
Database 'db2' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

But that’s as expected, I’d say.

Renaming a stored procedure may lead to inconsistent system catalogs

I came across this behavior a while ago and decided to write about it, so it may prevent you from falling into the same trap.

It’s very straight to reproduce. First, let’s create a simple stored procedure:

use tempdb go create procedure SP1 as select 1 as x go

This procedure doesn’t do anything useful but that’s not the point here. Let’s rename the procedure by utilizing the system stored procedure sp_rename like this:

exec sp_rename 'SP1','SP2'

After invoking the statement above, our procedure SP1 will have been renamed to SP2. You may check this by calling the procedure.

Now, let’s see what sp_helptext returns for SP2:

exec sp_helptext 'SP2'

Here’s the result:

Oops. Wrong (old) name in the CREATE PROCEDURE statement.

So, how about querying sys.sql_modules then:

select object_name(object_id) as sp_name, definition   from sys.sql_modules where object_id=object_id('SP2')

This query will return the following result:

Also not the correct CREATE PROCEDURE statement.

I was right about adding an item on Microsoft’s connect platform for reporting this behavior as a bug, when I discovered this warning in books online regarding sp_rename:

So, the behavior seems to be as expected. Be careful with sp_rename – not only when renaming stored procedures but also triggers, functions and views!

Backup and Restore of the SQL Server buffer pool

Have you ever missed this feature? Whenever you install the latest security update, it is very likely that your server requires a restart, leading also to a restart of SQL Server. If this ever happens, SQL Server needs to start from scratch, having nothing in the plan cache and also no data pages in the pool.I have many customers that complain about applications performing poorly after a server restart – for whatever reason that may have been necessary.

So finally, in the current project, we ended up developing a method for warming up the cache on our own after SQL Server has been started. Obviously not an easy task, but we had a smart guy who implemented this. The final solution is by no means perfect but almost sufficient according to our requirements.

When discussing about how we should implement this feature and also the pros and cons, we had the idea that we did nothing more than SQL Server itself should be capable of. Wouldn’t it be nice, if we could just perform a backup of the buffer pool and later restore it? Or even better, how about just sending SQL Server to hibernate mode (like Windows) and wake it up later? Clearly this won’t work under every circumstance, since some updates or patches may require a "cold" restart. But in those cases where it’s technically possible, I’d like to have this opportunity.

I like the idea so much that I’ve added a regarding change request on Microsoft’s connect platform (item# 561951). If you agree, you can vote for it here.

Do you rebuild your indexes periodically?

Most of you will – hopefully – have some index maintenance strategy. That is, you need some criteria, when to rebuild or reorganize an index. I will not repeat every advice that you may find elsewhere on the internet, e.g. here, or here.

In almost all given advices the dynamic management view sys.dm_db_index_physical_stats plays a central role, since it can easily be used to detect the degree of fragmentation of an index and the course of action to perform. Generally spoken, if index fragmentation exceeds a distinct value (let’s say 40%), you should perform a complete rebuild of the index. By doing so, the whole index tree is built from scratch and also the index related statistics is rebuilt. If the fragmentation is noticeable, but not too high (somewhere between 10% and 40%), an index reorganize may be sufficient. By performing a reorganize only the index pages in the leaf level are rearranged which is less cost (I/O) intensive than a complete index rebuild.

Unfortunately, sys.dm_db_index_physical_stats can create huge I/O stress, as you can check inside this blog post of Paul Randal (yes, the guru). There is, of course, an opportunity to affect the I/O load created, by specifying the last parameter of the sys.dm_db_index_physical_stats DMF. IF you use LIMITED here, the I/O load is as minimal as possible. DETAILED, on the other hand, may create very noticeable physical I/O, especially for larger indexes, although it should reveal very detailed and realistic.

But be aware that querying the fragmentation by the use of sys.dm_db_index_physical_stats, as also suggested in Books Online (see here), may not be sufficient to detect candidates for necessary rebuilds.

Look at the following example.

We start by creating a simple table and inserting 400,000 rows:

create table t1 (   c1 uniqueidentifier not null default newsequentialid() primary key  ,c2 nchar(513) not null default '#' ) go -- Insert 400,000 rows insert t1(c2)   select top 400000 '?' from sys.trace_event_bindings c1,sys.trace_event_bindings as c2 go

Now, let’s see the fragmentation of the primary key (which is also the clustered key in our case). Oh, btw.: I know very well that a clustered index on a GUID column is less than optimal, but I see this practice all the time. It’s just tat developers love the idea of creating objects (table rows) that are unique throughout the whole universe. But that’s not the point here, so let’s see how the fragmentation as well as the space used values look like. First, we use the LIMITED mode:

select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'limited')

Here’s the result:

Looks pretty good, he? 0.7% fragmentation, so no need for any actions, right? (avg_space_used_in_percent is not shown in LIMITED mode.) Well, let’s see what sys.dm_db_index_physical_stats returns when running in DETAILED mode:

select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'detailed')

Here’s the result:

Also nothing to worry about, right? Almost no fragmentation. But wait! Why has the index tree a depth of 4? That seems too deep! So let’s see, if an index rebuild will shrink the depth:

alter index all on t1 rebuild go select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent, ''   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'detailed')

Now the index looks like that:

So, the index three has one level less than before! Therefore, every index seek has to perform one read less – that’s 25% less!

If you look at the avg_space_used_in_percent column, you see why.The non-leaf levels of the index are more packed with data and don’t contain as much space after the rebuild as before.

So be aware, when following the best practices guides in BOL or the internet. Those guidelines are definitely very good and helpful, but may not be sufficient under all circumstances.

Non Clustered Indexes may prevent dead locks

If a table is very tiny, so that all table’s data fit into one single data page e.g., will any index be helpful when accessing/modifying the table’s data? You may think no, since for reading the data, the one and only data page has to be touched anyway, so a table (or clustered index) scan will get by with a single page read, and that’s the most efficient method for reading the data. So, there’s no need for an index, right?

Well, as the guru always says: It depends.

Have a look at the following table:

create table t1  (    c1 int not null primary key clustered   ,c2 int not null  )

Now, lets say we modify some data simultaneously from two different connections. Try the following scripts from two separate connections:

-- Connection 1 begin tran   insert t1(c1, c2) values (1, 1111)     -- pretend, we do something here   waitfor delay '00:00:10'   delete t1 where c2 = 1111 commit

 

-- Connection 2 begin tran   insert t1(c1, c2) values (2, 2222)     -- pretend, we do something here   waitfor delay '00:00:10'   delete t1 where c2 = 2222 commit

After a few seconds you will see an abortion one of the two transactions with a deadlock error like this:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, let’s create a nonclustered index on column c2.

create nonclustered index ix1 on t1(c2)

You may think, this index isn’t any useful, since our table only contains two rows and the optimizer will never decide for an index seek therefore. But if you repeat the above experiment, no deadlock shows up this time. So the index is good, helpful and may be even necessary.

Ok, let’s see what happened here. We start with the first case, without the nonclustered index. Our focus should be on the DELETE statement, since this is the cause for the problem. The execution plan (without the nonclustered index) looks like this:

In order to find the row that has to be deleted, a clustered index scan must be performed.

A call of sp_lock during the execution of the two statements reveals a situation like this:

spid	IndId	Type	Resource	Mode	Status
52	1	KEY	(8194443284a0)	U	WAIT
52	1	PAG	1:115	IX	GRANT
52	0	TAB		IX	GRANT
52	1	KEY	(61a06abd401c)	X	GRANT
54	1	KEY	(8194443284a0)	X	GRANT
54	0	TAB		IX	GRANT
54	1	PAG	1:115	IX	GRANT
54	1	KEY	(61a06abd401c)	U	WAIT

We see two connections (SPIDs) holding or waiting for locks. I’ve highlighted the rows of interest, where I used a green background for granted locks and a red background for waits. The resource column of the highlighted rows is related to a table (clustered index) row. You may investigate this on your own by using the undocumented %%lockres%% column. (See this blog post by James Rowland-Jones)  It is very obvious that the two connections are waiting on each other to release a lock on a row which is locked by the other connection, while – at the same time - holding a lock on the row the other connection is waiting for. Classical deadlock situation.

Now, let’s see how this changes with the nonclustered index in place. Here’s the execution plan for the delete:

Ok, the index is used for looking up the row that has to be deleted. Very good. The optimizer is aware of the fact that an index seek will be more appropriate for determining the row, since this will lead to fewer resource locks.

Here’s what sp_lock reveals if the two scripts are executed with an existing nonclustered index:

spid	IndId	Type	Resource	Mode	Status
52	3	PAG	1:126	IX	GRANT
52	1	PAG	1:120	IX	GRANT
52	3	KEY	(0fcd4e06ed44)	X	GRANT
52	0	TAB		IX	GRANT
52	1	KEY	(61a06abd401c)	X	GRANT
54	1	KEY	(8194443284a0)	X	GRANT
54	1	PAG	1:120	IX	GRANT
54	3	PAG	1:126	IX	GRANT
54	0	TAB		IX	GRANT
54	3	KEY	(917735e52a83)	X	GRANT

Very good! Only granted locks this time, no waits.