I had an interesting debate with a customer during a demo where I said that his assertion, “I don’t need to specify ORDER BY, I have a clustered index on that table” was problematic. You see, defining a clustered index does tell SQL Server to store data in the order stipulated by the clustering key, and many times running SELECTs against that data will return the data ordered as expected. But there are some gotchas you should keep in mind:

• When SQL Server runs a query in parallel, each stream will complete it’s set of work and return its result which could cause your clustered-index-ordered data to appear out of order.
• When another query is already scanning the data when your SELECT statement is run, the data will be read out of the order you’re expecting as your query will piggy-back on the other scan, then come back to the beginning to gather all the data requested.
• If statistics are out-of-date on your clustered index, or SQL Server otherwise chooses to use a non-clustered index to scan the data, the order of the clustered index will not be reflected in the result set.

But don’t take my word for it, check out what Conor Cunningham has to say on the topic, including some sample code to prove the point.

If you’ve been around a while, you tend to do things the way you first learned how to do them.  This can turn you into an “old timer” whose oblivious to new and better ways to do things that have appeared in the newer releases of the technology.

Take measuring log space, for example.  If I wanted to find out how much log space has been utilized, I would dash off a DBCC SQLPERF(LOGSPACE) statement.  But SQLPERF(LOGSPACE) only shows used/free space and not much more.  I might use DBCC LOGINFO to see how the active log “moved across the ldf file”, then examine the values columns such as “active” for more understanding.  Or I could go even more old-school, and look at the PerfMon counters or perhaps at the PerfMon counters exposed through the system table sysperfinfo.

However, I want to thank Tibor Karaszi and Gert Drapers for this great tip.  Simply use this query:

SELECT SUM([Log Record Length])

FROM ::fn_dblog(null, null)

WHERE…

The ::fn_dblog pseudotable provides a wonderful amount of information about the transaction log. Read more about this undocumented function at http://www.novicksoftware.com/udfOfWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm, or google ‘::fn_dblog’ for lots of other sources of information about it.

Thanks,

-Kevin

Technorati Tags: transaction log

The following question was posed to me during a call today and its one that I’ve heard quite a bit so I figured it warranted a blog post.

The answer is, absolutely!  SELECT statements acquire a shared lock on the tables being accessed.  This shared lock will not affect other SELECT statements hitting the same table, but if someone tries to modify data in the table (via an UPDATE statement for example), the UPDATE statement will be blocked.  When analyzing locks using sp_lock the (S) symbol indicates a shared lock is on an object.

This default behavior can be modified by using one of many query hints.  Using the NOLOCK hint on a SELECT statement will force SQL Server to read data from the table without creating a shared lock on it.  When using this hint, you run the risk of reading uncommited data from the database but in cases where reading data with 100% accuracy is not required, NOLOCK can dramatically reduce blocking and improve the performance of your SELECT queries.  Another option is to use the READ UNCOMMITED isolation level when running your transactions, which conceptually does the same thing.

One additional caveat about both methods.  If your SELECT statement is running, and it expects to read a page that has been deleted by a transaction that is currently executing, SQL Server may deadlock this transaction.  There is an interresting post on this topic that can be found here.

Technorati Tags: SQLServer 2000; SQL Server 2005; Blocking; Locking

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

Technorati Tags: sp_configure, SQL Server 2000, SQL Server 2005

Scott Moore, a friend of mine from my days at Deloitte, is now a noted expert in application development testing, especially on HP and Mercury Interactive products.  Scott has a new article up about “The Performance Authority” of a testing center of excellence.  If you work at all with QA and software testing, then this is worth a read.

This was mainly in response to the many inquiries he got about how to explain the highest level of maturity when it comes to addressing application performance issues in the software development lifecycle. Many of us have been seeing the slides around the maturity model for years, and we always see that the highest level – the Performance Authority – is always listed with a statement that it represents less than 1% of clients for companies like HP (especially Mercury Interactive).

This statistic hasn’t changed in at least four years that anyone has seen or written about. Either companies are just not getting it, or HP and their partners are not telling clients how to get there, and more importantly what it would look like if they had such a thing as a “Performance Authority”. This is Scott’s take from years of studying various real clients of his and doing Center of Excellence assessments – the typical holes that he sees.  Many companies are looking to achieve this level of maturity, and this article might give some clarity to what the “end game’ looks like. You can find the article online now at:

http://www.loadtester.com/performanceauthority.php

Enjoy,

-Kevin

Technorati Tags: QA

Q:  We use DB2 Connect to access DB2 from MS Access. Do we need this for SQL Server also or does SQL Server have a built-in interface or do we need something else?

A: No you do not need to use DB2 Connect in able to access DB2 data within SQL Server.  SQL Server supports linked servers which allow you to register a remote data source in SQL Server and execute queries against it.  Linked servers can be remote SQL Server instances or instances of Oracle, DB2, Sybase, MySQL, etc…  As long as there is a supported OLEDB driver for the data source you can add it as a linked server.  There is a blog post you can read here:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

that walks you through the process of setting up a linked server to DB2 and you can also look up Linked Servers in books online for more information.

Basically, you want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint. From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

Here’s a good start-to-finish walkthrough entitled Configuring a Linked Microsoft Access Server on SQL 2005 Server that should help in your particular case.

Technorati Tags: distributed query, linked server

You want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint.

From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

From the Oracle side you might want to have a look at the Oracle Migration Workbench.

I would backup the database and restore it using the WITH MOVE option. For the sake of illustration however, you could use SQLCMD to run a script as follows (assuming you are working in an environment that allows xp_cmdshell to be run, AND assuming that the SQL Server service is running under an account with the rights to move the files on the operating system (big IFs in most places))…

Script file contents:

declare @cmd varchar(2000);
select 'drop connected users'
while (select count(*) from [master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’))>0
begin
select @cmd=’kill ‘+CAST(spid as varchar(5)) from [[master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’);
exec [dbo].[sp_executesql] @cmd;
end
select ’setting database to SINGLE_USER mode’
alter database $(database) set SINGLE_USER;
select ‘updating database settings to move file’
alter database $(database) modify file (name=’$(logfilename)’, FILENAME=’$(newlogpath)’);
select ‘detaching database’
exec [master].[dbo].[sp_detach_db] @dbname = N’$(database)’, @keepfulltextindexfile=N’true’;
select ‘moving file’
exec [dbo].[xp_cmdshell] ‘move “$(originallogpath)” “$(newlogpath)”‘;
select ‘re-attaching database’
create database [$(database)] on (filename = N’$(datafilepath)’), (filename = N’$(newlogpath)’) for attach;
go

Execute syntax:

sqlcmd
-S <instance name>
-d master
-E
-i <script name where the code above was saved>
-v database =”<database>” logfilename=”<current log file>” newlogpath =”<new log file path>” originallogpath =”<original log file path>” datafilepath =”<data file path>”
-o <output file>

There are a number of articles (like this one) explaining how to do this for SQL Server 2000, but it’s so much easier in SQL Server 2005 that I’ll post the 2005-compatible code here. You should read this Microsoft Research article to get the Microsoft best practices justification for storing various file sizes in the database versus on the filesystem. Note, the following code sample is extremely simplistic and various iterations of similar logic are available from Microsoft and others.

if exists(select * from sysobjects where type=N'U' and name=N'picTable')
drop table dbo.picTable;
go
create table dbo.picTable(FileName sysname, Document image)
go
if exists(select * from sysobjects where type=N'P' and name=N'spLoadImagesFromLocalFolder')
drop procedure dbo.spLoadImagesFromLocalFolder;
go
create procedure [dbo].[spLoadImagesFromLocalFolder](@directory sysname,@fileextention nvarchar(10))
as
begin
declare @cmd varchar(8000),@fname nvarchar(200),@sqlstmt nvarchar(500),@ParmDefinition nvarchar(500),@cnt int

if object_id(’tempdb..#filestoload’) is not null
begin
drop table #filestoload
end
create table #filestoload ( RowNum int identity, fname varchar(8000) null)

SET @cmd = ‘dir ‘ + QUOTENAME(@directory,’”‘) + ‘ /T:W /A:-D /b’
insert into #filestoload ([fname])
exec [master].[dbo].[xp_cmdshell] @cmd
/* print @cmd */
delete from #filestoload where fname is null or lower(fname) not like N’%.’+lower(@fileextention)
select @fname = ”

select @cnt=count(*) from #filestoload
set @ParmDefinition = N’@fname nvarchar(200)’;
while @cnt>0
begin
select @fname = fname from #filestoload where RowNum > @cnt
set @sqlstmt=N’INSERT INTO picTable(FileName, Document) SELECT ”’+@fname+N”’ AS FileName,* FROM OPENROWSET(BULK N”’+@directory+@fname+N”’, SINGLE_BLOB) AS Document’
/* print @sqlstmt */
exec sp_executesql @sqlstmt,@ParmDefinition,@fname=@fname
select @cnt=@cnt-1;
end
drop table #filestoload;
end
go
exec [dbo].[spLoadImagesFromLocalFolder] N’C:\’,N’JPG’

Hope that helps!