Tibor Karaszi : SQL Server 2008

Heaps, forwarding pointers, ALTER TABLE REBUILD and non-clustered indexes

TiborKaraszi — Fri, 28 Aug 2009 10:15:00 GMT

Let's start with some background on forwarding pointers: Forwarding pointers in heaps can be a mess to get rid of. A forwarding pointer happens when you modify a row so that the row doesn't fit on the same page anymore. SQL Server moves the row to a new...(read more)

Spooky: What do you connect to?

TiborKaraszi — Fri, 26 Jun 2009 14:12:00 GMT

I only recently discovered that SSMS will connect to different things. For instance, press the "New query" button. What were you connected to? The answer is the same server as your "current" server. But what is the current server? It is the server where...(read more)

sp_altermessage is back in business!

TiborKaraszi — Thu, 14 May 2009 20:36:00 GMT

Just a quick note that we again can modify whether system messages are to go to eventlog/errorlog again. I.e., we can change the is_event_logged column in sys.messages. This is very valuable in general and specifically is you want to define Agent alerts (for which Agent polls the Eventlog). For instance:

SELECT * FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033

Notice the value for the is_event_logged column. Now, run below:

EXEC sp_altermessage
@message_id = 1205
,@parameter = 'WITH_LOG'
,@parameter_value = 'true'

Now, re-run the select statement and see that you modified the behavior for the system message. Now, re-run the sp_altermessage with 'false' to reset to default.

The ability to modify this behavior for system messages was available prior to SQL Server 2005, but some re-architecturing in 2005 removed the functionality. kozloski informed me in this blog post that 2005 sp3 re-introduced the functionality and obviously as of sp1 the functionlity is back in 2008 as well.

German translation of my updated datetime article

TiborKaraszi — Wed, 26 Nov 2008 11:02:00 GMT

Frank Kalis has been kind enough to translate my datetime article (http://www.karaszi.com/SQLServer/info_datetime.asp) to German. I updated my article a while ago to reflect the new date and time related types in SQL Server 2008, and I just learned that Frank now has finished the translation of my updated article to to German. Check it out at:

http://www.insidesql.org/beitraege/datetime/der-ultimative-guide-fuer-die-datetime-datentypen

New (web) update for SQL Server 2008 Books Online

TiborKaraszi — Wed, 05 Nov 2008 08:19:00 GMT

Fire up your SQL Server 2008 Books Online (BOL) and check the date. You should find this in any article (except for the very start page).

RTM of SQL Server 2008 shipped with a BOL which has ... hmm,... doesn't have a date (just checked in a virtual machine). OK, so if you don't find a date (year month) you have the BOL which shipped with RTM.

The first BOL update was released 2008-09-30. You will see this as (August 2008). This was made available on the web as well as a download (http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97&DisplayLang=en). This BOL did not list what was changed from prior release since this was considered RTM of BOL and the BOL that shipped with RTM was considered RC0 (which explains why this BOL lists Aug 2008 when it was released late Sept 2008).

Today I learned that MS has released yet another release of Books Online. This is only available on the web, and you see this as (October 2008). Now we do have listing of what section are new and what were expended on. I admit that I prefer locally installed BOL, but I guess that MS has resource limitations the same was as all of us do. Check it out at http://msdn.microsoft.com/en-us/library/dd239407.aspx.

Does the Resource Governor govern?

TiborKaraszi — Wed, 22 Oct 2008 16:14:00 GMT

Two weeks ago, we did the "SQL 2008 Summit" roadshow here in Sweden. We did 4 cities around Sweden in 4 days (http://www.expertzone.se/sql2k8/). It was a bit exhaustive, but even more fun - being able to travel and spend some time with persons wish I could meet more often (Kalen), others I meet regularly but only at workplace (Roger, Patrik, Anna) and yet other persons I just met (George, Per).

One of my presentations was on Resource Governor (RG), and I has this super-simple demo meaning to show CPU throttling. I classified connections to one of two Workload Groups based on login name. One group used a Resource Pool with max CPU at 10% and the other a Resource Pool with max CPU at 90%. Since I have two CPU cores, I started two execution loops for each login. An execution loop uses SQLCMD to login using the appropriate loginID and execute a proc which loops and outputs a counter using RAISERROR and NOWAIT (so we see something happening in the console).

For two of my presentations it worked just as expected. For two presentations it didn't: the CPU usage looked very very strange - nowhere near what we expected. So, during the final day, I managed to spend some time with Mikael Colliander from MS Sweden. First we couldn't reproduce the strange behavior, but after several restart, re-boot etc. we could. We now finally got to look at what scheduler each connection was using and there was the answer. One connection (ending up in the pool with max 10% CPU) was alone on one scheduler meaning alone on one CPU! The other three connections (one one on 10% CPU and two on max 90% CPU) was using the other CPU. So for the CPU where we had only one connection (belonging to the pool to be capped at 10% CPU) we just had no contention. So this connection could use everything on that CPU since nobody else was assigned to the CPU.

Now when I understand why this happened, it doesn't look that strange. But I think we need to be very careful when we monitor resource usage for our connections and are using resource governor. The more CPUs we have the less chance we will see the (perhaps expected) distribution of load.

My datetime article updated for 2008

TiborKaraszi — Tue, 23 Sep 2008 14:10:00 GMT

Actually, the article has been out for about a month now, but I first solicited feedback on errors and typo's and then I forgot to mention the update here...

Anyhow, the article now also covers 2008 and the new date, time, datetime2 and datetimeoffset types. There are some interesting aspects of the new types which sets them apart from the old types. As always, I'll be happy if you let me know about errors or typos.

http://www.karaszi.com/SQLServer/info_datetime.asp

Rebuilding msdb on SQL Server 2008

TiborKaraszi — Wed, 27 Aug 2008 10:10:00 GMT

Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about instmsdb.sql, but it was a long time since I actually used it. I asked about rebuilding in the MVP group and Dan Jones (MS) pointed me to a Blog post from Paul Randal on how to do this on SQL Server 2005. Here's Paul's blog post:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

Since above is for SQL Server 2005 I realized that it might not work smoothly on 2008. And It didn't. Below are some of the things I discovered (also posted as a comment on Paul's blog). Read below in light of Paul's blog. I should also say that nowhere does Paul states that his instructions work on 2008. It was me taking a chance. :-)

You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers...

I initially started the instance from the Windows services applet by adding -T3608. That didn't allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb.

I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:

* Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.

* Bunch of errors when creating various Data Collector objects. This wasn't good, because cleaning up DC was the reason to rebuild msdb in the frist place.

* 3 errors about sp_configure and -1 wasn't allowed value (two for Agent Xps and one for xp_cmdshell).

Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp's when connecting. I tried to explicitly enabling Agent XP's using sp_configure but same error. When connected there's no node in Objects Explorer for Agent.

I took this as an indication that Agent isn't healthy. Whether it was me doing something fishy or it isn't as easy as just running insmsdb.sql for SQL Server 2008 - I don't know. But I'm in for a rebuild of system databases. This isn't that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul's initial advice: always backup msdb (also on test machines).

Missing F8 or ctrl-N in SSMS 2008?

TiborKaraszi — Tue, 26 Aug 2008 07:22:00 GMT

Short story: Turn on 2000 keyboard layout and then back to Standard layout.

Long story:

This topic has been discussed in both the MCT (MS Certified Trainer) as well as MVP groups. Also, see http://sqlblog.com/blogs/andy_leonard/archive/2008/08/08/sql-server-2008-management-studio-function-keys.aspx, including the comments.

The mystery seems to be that in some cases you do have F8 and Ctrl-N in Standard layout, where in other cases you don't. For instance I did a check on 4 installations where one had the desired layout (with F8) and the others didn't:

VPC. XP. Clean install. No prior SQL Server stuff. No F8 or ctrl-N.
My laptop, XP. I have 2000, 2005 and 2008 tools as well as 2000, 2005 and 2008 instances installed. No F8 or ctrl-N.
My desktop machine, Vista. I have 2005 and also 2008 instances. I have had 2005 SSMS which was uninstalled before I installed 2008 SSMS. Here both ctrl-N and F8 work.
VPC. XP. Had 2005 both tools and instance which were upgraded to 2008. No F8 or ctrl-N.

I was doing training on 2008 last week and I really needed to find my shortcut keys (I couldn't keep stumbling after menus all the time - too slow). So I switched to what I'm familiar with: the 2000 keyboard layout. I recall thinking for myself that perhaps if I now switch back I will have the desired Standard layout (F8 and Ctrl-N). I forgot all about it until today reading a post in the MVP group from Craig Beere suggesting exactly this. To confirm, I tried this in both a virtual machine (1 above) as well as my laptop (2 above) and it worked indeed.

One thing to watch out for: There doesn't seem to be a way to go back to Standard layout *without* F8 and Ctrl-N. For instance when you get F8 etc, you also get a different shortcut for comment code (or was it uncomment?). So you might want to think a little bit before setting to 2000 layout and back. I'm sure in the end that somebody finds a setting somewhere to control the behavior - and then we know how to switch between the two Standard alternatives...

Make sure you play with data collector on a virtual machine

TiborKaraszi — Mon, 25 Aug 2008 16:42:00 GMT

I'm in a situation where I have configured the new data collector functionality for three instances. And there's no way to undo the config performed by the wizard! It cannot be undone by the wizard, and BOL doesn't have information on how to do this. In fact, I suspect that you in the end need to use some of the undocumented data collector procedures to get rid of this configuration (like sp_syscollector_delete_jobs).

I'm not knocking data collector per se - it seems like a great way to get a baseline going etc. But my tip is that while you are playing with it in order to understand it - do it virtually.

Lara has reported this on connect, btw: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=334180

Backup compression in SQL Server 2008

TiborKaraszi — Wed, 12 Dec 2007 15:52:00 GMT

Having a few moments to spare, I decided to give this a spin.

Specifying for the backup to be compressed is really simple. Just specify COMPRESSION in the WITH clause of the BACKUP command. For example:

BACKUP DATABASE Adventureworks TO DISK = 'C:\Advc.bak' WITH INIT, COMPRESSION

For fun, I compared backup file size and backup time between compressing and not compressing. I ran below after priming the cache (not that it should matter since backup read pages from disk, see http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/03/does-backup-utilize-pages-in-cache.aspx). I also deleted the backup files, if they exist, before execution.

DECLARE @dt datetime SET @dt = GETDATE() BACKUP DATABASE Adventureworks TO DISK = 'C:\Adv.bak' WITH INIT SELECT DATEDIFF(ms, @dt, GETDATE()) SET @dt = GETDATE() BACKUP DATABASE Adventureworks TO DISK = 'C:\Advc.bak' WITH INIT, COMPRESSION SELECT DATEDIFF(ms, @dt, GETDATE())

Size of backup file is 129 MB vs. 35 MB. Of course, compression ratio varies depending on what type of data there is in the database (string data tend to compress better than other data,. for instance). Time to execute commands were 7.5 seconds vs. 3.8 seconds. Now, this is a virtual machine on VPC and Adventureworks is a tiny database. But at least we can see clear potential for savings here.

So how do we know if a database backup was compressed? If you've used RESTORE HEADERONLY, you probably noticed this annoying column called "Compressed". Well, this is the first time we see a value of "1" in that column.

It seems we cannot mix compressed and non-compressed backups on the same file, though. (Not that I often have multiple backups on the same file.) If I append a backup using COMPRESSION on a file were there already are non-compressed backup, I get an error. Or I do a backup without COMPRESSION on a file where there are compressed backups, the new backup will be compressed (even when not saying COMPRESSION). Something to look out for if you have several backups on the backup files.

So what about the RESTORE command? Well, there's nothing to say, really. You don't have to specify in the RESTORE command that the backup was compressed. Time for RESTORE was 10.4 vs 6.3 seconds (with the destination database already existing). I expect the difference to be bigger on real installation and realistic db size.