Tibor Karaszi : General

Analogy between SQL Server and operating systems

TiborKaraszi — Fri, 08 Aug 2008 08:25:00 GMT

With SQL Server 2008 released, I was thinking back of earlier versions of SQL Server. And I decided to compare them to the MS operating systems. Not a point-in-time comparsion, like "SQL Server version x was released year a, which was the same year that OS y was released.". I'm thinking more of the feel you have for the product. Why would anyone want to do that? I don't know - for fun, perhaps? While writing below I realized that the comparsions/analogies worked better the older the product is. Perhaps a product need to be obsolete for us to have the sentimental feeling required for this type of comparsion? Anyhow, here goes:

SQL Server 1.x <-> DOS
(I do know it ran on OS/2, but again this is more about how you feel for the product.)
I know, perhaps not all fair, but think about it. We are talking about command-line environments, or at the best some full-screen character based applications (like edit.exe or saf.exe). And installation was floppy based where the product did fit on a couple of floppies.

SQL Server 4.x <-> OS/2 or Windows pre-95
I can't decide here.
OS/2 had the merit that it wasn't a bad OS, but almost no apps were developed for it (think back to version 1.2 and 1.3 and what it was at the time - and what it could have been), and it wasn't a fun environment to work in. Windows pre-95 had the merit of being a GUI which, sort of, brought multitasking to the desktop - but what about robustness?
Same goes for SQL server 4.x. It was revolutionary in some sense, like: Imagine fitting a real RDBMS in a PC? Now smaller businesses can start using "real" RDBMSs. But OTOH, it was very unpolished. Remember the GUI tools? They were really Windows apps where some conversion tool converted them for OS/2.
So, I think it is a draw between OS/2 and Windows pre-95.

SQL Server 6.x <-> Windows NT 3.x or Windows 9x
Again, I can't decide.
In one way, SQL Server 6.x was MS first "own" release. But OTOH, the Sybase code base was still there. MS mainly did tool stuff, along with some engine stuff (like ANSI SQL compliance). But it wasn't a re-write of the engine.
This can compare to Windows 9x - the DOS heritage was still there, in some sense.
If you compare SQL Server 6.x to Windows NT 3.x you can also see similarities. NT 3.x was the first versions of the new revolutionary OS from MS. But it still looked like ... old Windows - something you might compare with SQL Server 6.x enterprise Manager.

SQL Server 7.0 <-> NT 4
I was originally going to put Windows 2000 here, but after thinking a while, I decide for NT 4.
7.0 was the first version of the new architecture. A lot happened, where the engine was all re-written. New stuff was introduced (Profiler, DTS, Olap server). So, at the engine level, we basically got a more modern look-and-feel.
To some extent NT 4 was similar. You got a new GUI (adopted from Windows 9x). The revolution was that you now had an *stable* OS which you also could run as your desktop OS. I bet that many of you (computer nerds)/readers preferred NT 4 instead of Windows 9x at that time. I did. There were some architectural news in the OS as well, like the device driver model (some stuff were moved to kernel mode - if my memory serves me).

SQL Server 2000 <-> Windows 2000
Seems too easy, but think about it.
SQL Server 2000 was when the new architecture matured. IMO, a great release at that time. OK, some would argue that it didn't happened that much between 7.0 and 2000, but maturing and polish of the new architecture is a major thing to me.
Windows 2000 can also be seen as becoming mature - ready to be used in masses. OK, there were some revolutionary new stuff like AD, but you can't expect the analogy to fit 100%. ;-)

SQL Server 2005 <-> Vista
Hmm, is my analogy breaking down here?
I was originally going to put Windows 2003 here. But that was a bit too much going chronologically hand-in-hand.
And I think that XP is a bit unfair (perhaps XP would be a better fit for SQL Server 2000?).
But 2005 did have lots and lots of changes and new features. And so did Vista. Vista has a rather slow adaption rate, and I have the same feeling for SQL Server 2005. Many people seems to wait for Vista+, a perhaps more cleaned-up OS? And some seem to be waiting for SQL Server 2008, even though perhaps not for the same reasons.

SQL Server 2008 <-> Vista +
This was unavoidable, considering how we got here. I won't dwell into this, since it is too early to say how we feel about these releases in 10 years from now...

Now why on earth did I write this post? Well, I have been doing some 6 full installations and some 12 database engine installations of SQL Server 2008 the last two days - so I've had a lot of time on my hands. :-)

Endpoints, Netlibs, IPC and stuff...

TiborKaraszi — Wed, 28 May 2008 15:37:00 GMT

Its been a while since my last post. No special reason, just a combination of lot of work and I didn't feel I had something pressing to say...

This topic is basically on how the client app communicates with SQL Server. Not the API level (like ADO or ODBC), or the packet level (TDS), but in between. Basicaly we're talking IPC, Inter Process Communication - in a SQL Server context:

Here's how I understand it (I probably gonna get some points wrong and you are all welcome to correct me):

There are network protocols, such as:

TCP/IP (has routing functionality of course)
NetBEUI (very limited, if any, routing functionality)
IPX (the original protocol for Novell networks)
SNA (mainly used in IBM mainframe and such environments)

A network protocol is of little use if we can't send data back and fort between application over that network protocol. So, there are APIs to facilitate IPC:

NETBIOS (originally developed for NetBEUI, but is also supported over IP (requires WINS or LMHOST for name resolution))
Sockets (not available for NetBEUI AFAIK, only TCP/IP)
Named Pipes (built on top of NETBIOS)
RPC (implemented and available over both NetBEUI and IP)
SPX (as I understand it, the API over IPX)
APPC (program-to-program protocol over SNA)

When MS released "their" SQL Server, they needed a way for the client app to communicate to the server. They decided to go for Named Pipes and developed what we call "netlib". I.e., the MS deveopers used the Named Pipes API (which is similar to reading and writing to a file from the programmers perspective) when developing the Named Pipes netlib.

Over time, new netlibs were developed, where in SQL Server 2000, this culminated in below list:

Shared Memory (only for local connections, obviously)
Named Pipes
Sockets
RPC
VIA
SPX

There was never a netlib deveoped directly on top of NETBIOS, but indirectly through Named Pipes. Named Pipes uses NETBIOS, which available over IP, and hence is routable. In 2005, the list has shrunk to:

Shared Memory
Named Pipes
Sockets
VIA

And I have a feeling that in the end Named Pipes will go away. I don't have any experience with VIA, but I believe that it is closer to the metal than Sockets so it might stick around for dedicated AppServer-to-SqlServer networks.

So, what does above have to do with Endpoints? Well, MS are categorizing netlibs as endpoint nowadays. This makes sense since the netlibs are a "way in" to SQL Server, as are HTTP, Service Broker and Database Mirroring endpoints.

The SQL Server script repository

TiborKaraszi — Mon, 03 Mar 2008 20:18:00 GMT

How come I've missed this? I don't know, but I'm writing this blog post so to bookmark this site.

You'll find a handful of scripts, mainly using Dynamic Management Views and Functions, to monitor various aspects of SQL Server.

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

Is statistics over non-indexed columns updated by index rebuild?

TiborKaraszi — Thu, 09 Aug 2007 15:18:00 GMT

Short answer: no.

This question came up today in the MCT discussion group. My gut instinct said no, but I wanted to test it to be certain. But first a brief background:

You can rebuild an index using DBCC DBREINDEX or in 2005 (and now preferred) ALTER INDEX ... REBUILD. Rebuilding an index internally creates a new index and when that has been done, drops the old index.

So it is pretty obvious that we also get new statistics for that index (based on all data, not sampled, just as when we do CREATE INDEX). As an aside, reorganizing does *not* update the statistics...

But what about statistics over non-indexed columns? SQL Server can create this by itself, assuming you didn't turn off this database option. These are named something like _WA_sys. And you can also create these explicitly usinf CREATE STATISTICS.

A few words about below script: I wanted to use the STATS_DATE function to retrieve datetime for when the statistics was built/updated. But STATS_DATE doesn't seem to work on statistics only; it expect an id for an index... So, this is why I use DBCC SHOW_STATISTICS instead. And, unfortunately, DBCC SHOW_STATISTICS only display the statistics build time with minute precision. This is why I have a WAITFOR with > 1 minute in between the operations.

I got the same resuld whether or not I rebuild a clustered or non-clustered index on the table or even when specifying ALL indexes. Script:

USE tempdb
SET NOCOUNT ON
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(c1 int identity, c2 char(5))
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM syscolumns a, syscolumns b

CREATE CLUSTERED INDEX x1 ON t(c2)
CREATE STATISTICS s1 ON t(c1)

SELECT ' ' AS "Before mass modification"
DBCC SHOW_STATISTICS('t', 'x1') WITH STAT_HEADER, NO_INFOMSGS
DBCC SHOW_STATISTICS('t', 's1') WITH STAT_HEADER, NO_INFOMSGS

WAITFOR DELAY '00:01:02'

INSERT INTO t (c2)
SELECT TOP 10000 'Hi' FROM syscolumns a, syscolumns b
SELECT ' ' AS "Before index rebuild"
DBCC SHOW_STATISTICS('t', 'x1') WITH STAT_HEADER, NO_INFOMSGS
DBCC SHOW_STATISTICS('t', 's1') WITH STAT_HEADER, NO_INFOMSGS

WAITFOR DELAY '00:01:02'

--ALTER INDEX x1 ON t REBUILD
ALTER INDEX ALL ON t REBUILD
SELECT ' ' AS "After index rebuild"
DBCC SHOW_STATISTICS('t', 'x1') WITH STAT_HEADER, NO_INFOMSGS
DBCC SHOW_STATISTICS('t', 's1') WITH STAT_HEADER, NO_INFOMSGS

No Notification Services in SQL Server 2008

TiborKaraszi — Fri, 03 Aug 2007 08:08:00 GMT

If you have been wondering where Notification Services (NS) is in the prior CTPs of SQL Server 2008, you now have the answer. NS will not be carried forward to SQL Server 2008. Here's a quote from July CTP readme:

"SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases. "

So, now we know.

I've been delivering training of SQL Server 2005 for a while now, and one of the courses has a module on NS. Over time, I gradually adapted to the fact that almost no-one was interested in NS and nowadays I just give a brief overwiew of what NS is, and the basics of creating an NS solution. Funny thing is that when you "get" NS, you realize that it is a neat piece of infrastructural software, taking writing some code off your hands. I guess that there hasn't been enough interest in NS to carry it forward to next release, but that it pure speculation from my side...

Will SQL Server 2008 ship February 27?

TiborKaraszi — Tue, 17 Jul 2007 15:05:00 GMT

No.

Let me elaborate a bit. You might have seen and read about the feb 27 launch of SQL Server 2008. Now, in MS lingua, "launch" is not the same as "ship" or "release to manufacturing" (RTM). So, the Feb 27 event is a marketing and awareness event. RTM will be later.

Want to get rid of "Compact Edition" from Books Online?

TiborKaraszi — Fri, 13 Jul 2007 17:17:00 GMT

I'm sure that some of you have been annoyed when searching or using the index and found a hit, thinking it looks strange, only to realize that you are looking at a "Compact Edition" topic. This has happened to me more than once.

We just had a discussion about this in the MVP group and Umachandar Jayachandran (aka UC) have use this great tip.

Apparently we can remove collections from Books Online. If you use the index in Books Online and type "Help Coll", you will find and entry named "Help Collection [SQL Server]". Here you can remove "SQL Server 2005 Compact Edition", and later add it back in if you wish.

Are execution plans for functions cached?

TiborKaraszi — Thu, 14 Jun 2007 17:08:00 GMT

Obviously, were talking about multi-statement functions, since in-line functions are just views in the end.

My gut feeling for this is "yes", but I wanted to be absolutely certain. So here goes:

Create a function in Adventureworks
Use that function in a SELECT statement
Check if a plan exists in the plan cache for above

USE Adventureworks
GO

IF OBJECT_ID('fn') IS NOT NULL DROP FUNCTION fn
GO

CREATE FUNCTION fn(@rg uniqueidentifier)
RETURNS @tbl TABLE(SalesOrderDetailID int NOT NULL PRIMARY KEY, OrderQty smallint NOT NULL)
AS
BEGIN
INSERT INTO @tbl(SalesOrderDetailID, OrderQty)
SELECT SalesOrderDetailID, OrderQty FROM sod WHERE rowguid = @rg
RETURN
END
GO

SELECT * FROM fn('80667840-F962-4EE3-96E0-AECA108E0D4F')
GO

SELECT cp.cacheobjtype, cp.plan_handle, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.objectid = OBJECT_ID('fn')

IF OBJECT_ID('fn') IS NOT NULL DROP FUNCTION fn

Note the execution plan in XML format picked up from sys.dm_exec_query_plan. If you executed the query in grid format, you can cklick on it, and then save the XML as a file. Rename the file extension to .sqlplan and open that file in SSMS. You can now see the plan for this function graphically.

Whats in the default trace?

TiborKaraszi — Mon, 12 Mar 2007 09:23:00 GMT

As you probably know, there's a trace running by default in SQL Server 2005. The directory for the trace file is the SQL Server log directory, and you can turn off and on this trace with sp_configure.

But how do we find out what events and columns are traced to this? We use a trace function and some trace catalog views:

The function fn_trace_geteventinfo returns what columns and events are captured by a configured trace. But we don't want to see the column id and event id, we want the names. So we join this to the following functions:
sys.trace_events
sys.trace_categories
sys.trace_columns

Here's the end result:

SELECT cat.name AS CategoryName, e.name AS EventName, c.name AS ColumnName
FROM fn_trace_geteventinfo(1) AS rt
INNER JOIN sys.trace_events AS e
   ON rt.eventid = e.trace_event_id
INNER JOIN sys.trace_columns AS c
   ON rt.columnid = c.trace_column_id
INNER JOIN sys.trace_categories AS cat
   ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName, ColumnName

And here's one with only category and event:

SELECT DISTINCT cat.name AS CategoryName, e.name AS EventName
FROM fn_trace_geteventinfo(1) AS rt
INNER JOIN sys.trace_events AS e
ON rt.eventid = e.trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName