February 2010 - Posts

Readonly Databases
27 February 10 11:23 AM | MartinBell | 2 comment(s)

I was thinking the other day of a useful enhancement to the CREATE/ALTER DATABASE statement, regarding readonly databases; which was to have an additional clause that would either remove the need for a log file or shrink the log file to a minimum size. I logged this as two connect issues here and here.

There are various reasons why you may want to make a database read only, such as if you want to keep a database for historical reasons but want to preserve the data as it was as at given point in time or if the database only contains reference data.

I worked on a system which had two distinct sets of reference data, which would be periodically updated by third parties. When I first encountered the system, this reference data was imported into the main database which meant that the reference data was only updated when the system had a new release (users were not given this facility). This meant that the reference data was always several version behind what the third party had released. Having a readonly database for these database freed them up to be released separately. It also speeds up the releases, as there is no loading of data, just a detach/attach and possibly some updates to the existing data (although that would be necessary regardless of the method used!).

Having a separate readonly database does have at least one draw back though. You can not define foreign key relationships between columns in two different databases; although you can simulate a foreign key by writing a check constraint that uses a function or by using triggers, but these options may not always perform as well. The nature of reference data (in that it is not usually entered as free text) may make it acceptable to defer your referential checks to a maintenance task, although this would be a business decision to use this method.

So why the need for the extra options when ALTERING/CREATING a database? When I re-visited the system before the go-live with the changes for the reference databases, they had a huge log file for the reference database created by the build process. If this had been shipped it would have made the process of creating/installing the reference database much longer than necessary and as the log file would never be used it would also be a waste of disc space.

Ideally I would like the option not to have log files with readonly databases, but if this is not an option then creating one of minimum size would help. Of course this could be abused and therefore restricting it to readonly database would hopefully reduce that risk.

Filed under:
Breaking News - Scottish Area SQL Server User Group Meeting, Edinburgh 25th February
23 February 10 01:04 PM | MartinBell | with no comments

Great news regarding the User Group Meeting on Thursday

Quest have very kindly donated lots of swag to give away at the meeting. We are also going to run a prize draw for a £50 Amazon voucher.

So you can look forward to having fine food, a couple of great talks, a prize draw and a swagalicous evening

To sign up go to
http://www.sqlserverfaq.com/events/212/EndtoEnd-SQL-Server-Performance-Troubleshooting-with-Iain-Kick.aspx

Renaming a computer and maintenance plans
21 February 10 01:41 PM | MartinBell | 7 comment(s)

If you rename the computer where an instance of SQL Server resides then you have to make some changes to the each instance by dropping the old server and adding the new one. This is described in http://msdn.microsoft.com/en-us/library/ms143799.aspx the following article. To drop and re-add the server I wrote the following which should work if executed on any instance, without having to be edited.

DECLARE @INSTANCENAME sysname
SET @INSTANCENAME = NULLIF(RIGHT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0 THEN LEN(@@SERVERNAME) - CHARINDEX('\',@@SERVERNAME ) ELSE 0 END ),'') 

IF LEFT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0 THEN CHARINDEX('\',@@SERVERNAME ) -1 ELSE LEN(@@SERVERNAME) END ) <> HOST_NAME()
BEGIN 
                        DECLARE @cmd nvarchar(4000) 
                        SET @cmd = N'EXEC sp_dropserver ''' + @@SERVERNAME + N'''; 
                                                EXEC sp_addserver ''' + RTRIM(HOST_NAME()) + ISNULL(N'\' + @INSTANCENAME,'') + ''', local' 
                        EXEC ( @cmd )
END

As this relies on the HOST_NAME() function it has to be run on the server itself.

But if you look at the end of the above article one of the Community Content entries takes about how to fix maintenance plans when you have changed the hosts name. There are comments about loosing line breaks when copying and pasting the script. If you paste the script into MS Word and then copy/paste it from there you will not have this problem. For SQL 2005 systems look at dbo.sysdtspackages90 and not dbo.sysssispackages. The script has been added my MSFT on the SQL 2005 version of the page with this change. There is a disclaimer also posted “The script below was added by a customer to the SQL Server 2008 version of this topic ( http://msdn.microsoft.com/en-us/library/ms143799.aspx ). As it also applies to SQL Server 2005, it is being added here as well. However, please be advised that this method is not supported by Microsoft; use at your own risk.

Looking at the script the first thing I noticed was casting of the PackageData column.

CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))

Although SSIS packages are in XML format, the sysssispackages and sysdtspackages table store the XML in a image column. You can’t directly convert from image to varchar or XML, so first you have to convert to varbinary. I’ll look at trying to using XML to change connection string in another post.

The second issue is the use of the @xml and @packagedata variables which seem superflous as the replacement can be done on the update statement, although using them may make it clearer what is happening. Therefore you can eliminate these variables and just have the following update statement.

UPDATE    sysdtspackages90
SET packagedata = replace(cast(cast(packagedata as varbinary(max)) as varchar(max)),'server=''' + @oldservername + '''','server=''' + @newservername +'''')
WHERE (id= @planid)  -- update the plan

The next thing I would question is - Why is a cursor being used? The only real reason is to output the message about what plan has been updated. This can be done in using and OUTPUT clause:

UPDATE    sysdtspackages90
SET packagedata = REPLACE(CAST(CAST(packagedata AS varbinary(max)) AS varchar(max)),'server=''' + @oldservername + '''','server=''' + @newservername +'''')
OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @oldservername + ' to ' + @newservername 
WHERE CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%'

The result of this rationalisation is that the second cursor loop in the script can also be reduced to a single update statement:

UPDATE    sysdtspackages90
SET packagedata = REPLACE(CAST(CAST(packagedata AS varbinary(max)) AS varchar(max)),'Data Source=''' + @oldservername + '''','Data Source=''' + @newservername +'''')
OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @oldservername + ' to ' + @newservername
WHERE CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=''' + @oldservername + '%'

It is also possible to change all the necessary packages in a single statement, but with all the CAST statements I think it is getting a bit messy. So to tidy things up you can use a CTE:


WITH
PackageCTE(Id, PackageDataString)
AS
(
                        SELECT id, CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))
                        FROM sysdtspackages90
)
UPDATE P
SET PackageData = REPLACE(REPLACE(C.PackageDataString,'Data Source=''' + @oldservername + '''','Data Source=''' + @newservername +''''),'server=''' + @oldservername + '''','server=''' + @newservername +'''')
OUTPUT 'Changed ' + INSERTED.name + ' server from ' + @oldservername + ' to ' + @newservername
FROM sysdtspackages90  P
JOIN PackageCTE C ON C.id = P.id
WHERE C.PackageDataString LIKE '%server=''' + @oldservername +'''%'
OR C.PackageDataString LIKE '%Data Source''' + @oldservername +'''%'
GO

It seems logical to combine my original script and updating the packages which you can fine here. Of course the caveat posted with the original script still applies. Once you have run the script you will need to restart the SQL Server instance.

Filed under: ,
Scottish Area SQL Server User Group Meeting, Edinburgh 25th February
20 February 10 10:46 PM | MartinBell | with no comments

I’m looking forward to this weeks Scottish Area SQL Server User Group meeting. Iain Kick is our guest speaker talking about Performance Troubleshooting. I’ve seen this talk a couple of times and it’s always great to hear how a vendor approaches these problems. If everyone looked at things the same way, then there would be no progress or invention in the world, and each day would be one endless Ground Hog Day!

This sort links with the second presentation by Alistair Board and myself on how you can learn SQL Server. Everyone is different and knowing what is available could help open doors to progressing your knowledge and possible enhance your career.

Here’s the full itinerary (and don’t forget we’ll be having pizza!):



End-to-End SQL Server Performance Troubleshooting – Iain Kick

When attempting to diagnose a problem in a SQL Server application, there's no right or wrong way to go about troubleshooting. However, certain techniques yield much better results when used for end-to-end troubleshooting.

This presentation will teach you the best practices for finding the root cause of a problem in a SQL Server application, correlating that root cause to a specific set of activities being performed on the server, and tuning the problem activity for maximum performance-all using the native SQL Server tools. We will then compare and contrast those techniques to Quest Software's performance management and query tuning tools. All examples and syntax are verified for Microsoft SQL Server 2005.

Ways to learn SQL Server – Alistair Board and Martin Bell

One of the reasons that I like to work with SQL Server is that you never stop learning new things. This may sound daunting to new people coming to this as a career change or taking their first steps in a new position, but it’s one of the areas why we still work with SQL Server. Everyone has their own preferred methods of learning, but finding the resources that suit you best is not always easy. This talk will draw on the many years of experience that Alistair and Martin have with SQL Server, but if you have something you wish to contribute feel free to bring it along.

Agenda

18:30 - 19:00 - Introduction, Networking and Food

19:00 - 19:50 – End-to-End SQL Server Performance Troubleshooting – Iain Kick

19:50 - 20:00 - Break

20:00 - 20:50 – Ways to learn SQL Server - Alistair Board and Martin Bell

20:50 - 21:00 - Close

Location:

Microsoft Office at Waverley Gate http://www.microsoft.com/uk/about/map-edinburgh.mspx

Registration will be necessary as I have to submit registrations to Microsoft on Thursday morning.

SQL Server 2008 R2 Update for Developers Training Kit Released
06 February 10 09:37 PM | MartinBell | 2 comment(s)

I noticed that the SQL Server 2008 Developers Training Kit has been re-released last week with a R2 Update. It looks like a great resource for SQL 2008 information and the additional features in SQL 2008 R2. It includes links to 8 videos (mainly R2 features), 8 presentations (mainly RTM features) and 13 demos (mainly RTM features) and 8 Hands-on-labs (both releases).

Anyone looking for learning resources on SQL 2008 and SQL 2008 R2 should check it out, it could save you some searching. It can be downloaded from here.

ROWCOUNT when setting variables
03 February 10 12:02 PM | MartinBell | 16 comment(s)

At work last week the question was raised about what @@ROWCOUNT value was returned when you set variables in a SELECT statement. One of the most common problems you come across reviewing code as a DBA is the setting (or not) of a variable to something that the developer wasn’t expecting!

Using the following table:

USE tempdb
GO

CREATE TABLE nums ( num int, val char(10) ) ;
INSERT INTO nums ( num, val ) VALUES ( 1 , 'one') ;
GO

The two simplest ways you can set a single variable from data in a table are:

First Method 

DECLARE @num int
SET @num = ( SELECT num FROM nums ) ;
SELECT @num , @@ROWCOUNT -- 1, 1
GO

Second Method 

DECLARE @num int
SELECT @num = num
FROM nums ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO

The first method may be considered by some to be safer because using that syntax then if the table contains more than one row you will get an error e.g.

INSERT INTO nums ( num, val ) VALUES ( 2 , 'two') ;
GO

DECLARE @num int
SET @num = ( SELECT num FROM nums ) ;
SELECT @@ROWCOUNT
GO

Will give you the error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Therefore if your table contains more than one row, you will need to use a restriction so that only one row is returned e.g.

DECLARE @num int
SET @num = ( SELECT num FROM nums WHERE val = 'one') ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO

or you can specify TOP 1 to limit what is returned, and if you want a specific row you will also need an ORDER BY clause.

DECLARE @num int
SET @num = ( SELECT TOP 1 num FROM nums ORDER BY num ) ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO

DECLARE @num int
SET @num = ( SELECT TOP 1 num FROM nums ORDER BY num DESC ) ;
SELECT @num, @@ROWCOUNT -- 2, 1
GO

or if you are wanting to be really safe a restriction a TOP clause and an ORDER BY:

DECLARE @num int
SET @num = ( SELECT TOP 1 num FROM nums WHERE val = 'one' ORDER BY num DESC ) ;
SELECT @num, @@ROWCOUNT -- 1, 1
GO

Using the second method to set the variable will not give you an error, and this is often the cause of many of the problems e.g.


DECLARE @num int
SELECT @num = num
FROM nums ;
SELECT @num, @@ROWCOUNT -- 2, 2
GO

@@ROWCOUNT in this case returns 2 and not one as you may expect. The value returned is not always guaranteed for instance if I add a clustered descending index:

 

CREATE CLUSTERED INDEX CLX on nums (NUM DESC);
GO

The same query will return a different value;

 

DECLARE @num int
SELECT @num = num
FROM nums ;
SELECT @num, @@ROWCOUNT -- 1, 2
GO

If you are relying on this being a given value within your application, then it may suddenly start to behave differently even though the code has not changed. Like the first method you can use an order by or restriction to make sure that you are getting the row you expect:


DECLARE @num int
SELECT @num = num
FROM nums
ORDER BY num DESC;
SELECT @num, @@ROWCOUNT -- 1, 2
GO

or better:

DECLARE @num int
SELECT @num = num
FROM nums
WHERE val = 'one'
ORDER BY num DESC;
SELECT @num, @@ROWCOUNT -- 1, 1
GO

or even better:

 

DECLARE @num int
SELECT TOP 1 @num = num
FROM nums
WHERE val = 'one'
ORDER BY num DESC;
SELECT @num, @@ROWCOUNT -- 1, 1
GO

If you are setting more than one variable, then you can't use a single set statement and you would have to write:

 

DECLARE @num int, @val char(10) ;
SET @num = ( SELECT TOP 1 num FROM nums ORDER BY num DESC ) ;
SET @val = ( SELECT TOP 1 val FROM nums ORDER BY num DESC ) ;
SELECT @num, @val  -- 2, two
GO

 

but this method is starting to get a bit messy and inefficient. You can use multiple sub-queries with a SELECT statement:

DECLARE @num int, @val char(10) ;
SELECT @num = ( SELECT TOP 1 num FROM nums ORDER BY num DESC ),
            @val = ( SELECT TOP 1 val FROM nums ORDER BY num DESC ) ;
SELECT @num, @val, @@ROWCOUNT -- 2, two, 1
GO

Looking at the query plan for this:


and comparing it to when you don't use sub-queries, such as the following statement:

DECLARE @num int, @val char(10) ;
SELECT TOP 1 @num = num,
            @val = val
FROM nums
ORDER BY num DESC ;
SELECT @num, @val, @@ROWCOUNT -- 2, two, 1
GO

and look at the query plan:



If there is a where clause that utilises the clustered index, you would get an index seek:

DECLARE
@num int, @val char(10) ;
SELECT TOP 1 @num = num,
            @val = val
FROM nums
WHERE num = 2
ORDER BY num DESC ;
SELECT @num, @val, @@ROWCOUNT -- 2, two, 1
GO



Therefore I can conclude that checking @@ROWCOUNT may have some use when setting variables, although on it's own it will not guarantee that you are returning the value you are expecting.

Filed under:
SQLBits goes West the video(s)
01 February 10 07:19 AM | MartinBell | with no comments

We have loaded up the videos from the SQLBits V conference at Celtic Manor.

They can be viewed/downloaded from the agenda items on
http://www.sqlbits.com/information/newagenda.aspx.

All the slide decks that have been made available to us have also been uploaded.

Filed under:

This Blog

SQL Blogs

Syndication