SQL Server

Before launching into this, I must give due deference to Mogens Nørgaard’s landmark article, You Probably Don’t Need RAC (YPDNR), available here, but originally published Q3 2003 in IOUG Select Journal.  Mogens showed that you can be a friend of Oracle without always agreeing with everything they do.

SimpleTalk have just published another of my SQL Server articles – which looks at the way that SQL Server stores data in “clustered indexes”, and the variation that appears depending on the pattern of data.

This afternoon I’ve been cleaning up some data in an SQL Server database. I decided to use SQL*Developer to connect to SQL Server by following this post.

• Using Oracle SQLDeveloper to access SQLServer (The “Check for Updates” functionality doesn’t seem to work properly on my SQL*Developer running on CentOS 5 (64-bit), so I had to install the JDBC driver manually.)

I made liberal use of the following tip when dealing with TEXT and NTEXT types.

• How to use REPLACE() within NTEXT columns in SQL Server

The joys of dealing with multiple engines…

Cheers

Tim…

In Microsoft SQL Server, session IDs of 50 or less are dedicated background threads. These are analogous to Oracle’s background processes.  Suppose you needed to obtain the Windows thread ID of a SQL server background thread.  Just try finding out how to do that in the Microsoft documentation or by Googling.  I tried, and came up empty-handed.

Why might you need to know this kind of thing?  Well suppose one of those threads was misbehaving or hanging. You would need to debug it and obtain a stack trace.  For that you would need the OS thread ID.

A little sleuthing shows that you need to query three dynamic system views: sys.dm_os_threads, sys.dm_os_workers and sys.dm_exec_requests.  The following SQL should do the trick in MSSS 2005 and 2008:

SimpleTalk have just published another of my SQL Server articles – which talks about storage methods and block dumping in SQL Server, and ends with the suggestion that the reason that SQL Server DBAs seem to be very keen on “clustered indexes” may be related to the fact that SQL Server doesn’t seem to handle [...]

Continuing my little list of things in SQL Server that I’d like to see in Oracle: 1 Index statistics: when SQL Server collects index statistics, it also collects stats not only on the full index definition, but also on every “prefix” of the index, i.e. if you have a 4-column index, you will get stats [...]

I pointed out some time ago a few of the things in SQL Server that I would like to see in Oracle. Here’s a couple more: Executions Plans Insert, update, and delete execution plans show the indexed access paths used to check the side effects of referential integrity constraints. (I haven’t  checked to see what [...]

I haven’t had much spare time for writing this week – although I’ve got about 45 drafts of notes to publish – but Simpletalk have just published the “learning SQL Server” paper I sent them a couple of weeks ago, and it’s worth a link. Filed under: SQL Server

My trip back from Miracle Open World was extremely uneventful compared to the travail (and travel) of most of the foreign speakers who were there – 13 hours across five trains to get from Copenhagen to Amsterdam, and the worst delay was a train arriving 30 second late in Osnabruck.  Then the airports reopened before [...]