July 2009 - Posts

SQLBits goes West - session submissions
30 July 09 09:20 PM | MartinBell | with no comments

On Monday we opened session for the next SQLBits conference at Celtic Manor near Newport. Chris Webb’s blog post has all the details about how to do this so I won’t repeat it!

Already there are some very interesting submissions http://www.sqlbits.com/information/PublicSessions.aspx from some excellent speakers and everything is shaping up to be a great conference.

All the sessions so far have been submitted by people who have presented before, but one of the reasons behind these conferences is to offer a speaking opportunity to people who are not the “usual suspects”. To help new speakers we will offer help and support to any speaker leading up to the conference, so you can give a presentation to be proud of. If you want to know more don’t delay and contact us.

Filed under: SQLBits

Executing all .SQL files in a directory with Powershell
30 July 09 07:30 PM | MartinBell | 3 comment(s)

Some time ago I published a blog post on how to execute all files in a directory against a given database. This took a single line on the command prompt, today I saw an implementation of this in T-SQL which had 86 lines of code.

As I’ve recently been looking at improving my Powershell skills, I thought it would be a good idea to try an produce a solution using that, and here is what I came up with:

foreach ($f in Get-ChildItem -path "C:\temp\SQLScripts\" -Filter *.sql | sort-object -desc )
{
$out = "C:\temp\SQLScripts\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;
invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
}
 

This may not be the most elegant of solutions, but here is what it does:

Get-Childitem will return all the files in the "C:\temp\SQLScripts\" directory, using the *.sql filter it will only get files with that extension (just in case there are other files which we don’t want to execute!).

Piping this through a sort will mean I do not have to execute the files in alphabetical order if the sort order can be identified through one of the file’s attributes e.g the order in which the files were created or as in the example, descending alphabetical file name order.

Then for each file I construct the name output the file where I want the output to go, the split function in conjunction with the 0 index takes the left part of the sql script’s filename up to the first dot (.).

I then use the cmdlet invoke-sqlcmd to execute the script. The above code assumes that you are in the context of the database where you wish the script the be run e.g

SQLSERVER:\SQL\ComputerName\InstanceName\Databases\DatabaseName

but you may want to specify the server, database, username and password parameters if this is not the case. It would be very easy to run all the scripts against every database on a given instance or a specific database on all instances of SQL Server on the given computer. This for me, is one of the attractions of Powershell, as trying to find a solution in T-SQL or at the command prompt will be much harder.

Filed under: SQLCMD, POWERSHELL

SQL Server 2008 Cumulative Updates
24 July 09 09:52 PM | MartinBell | with no comments

Monday saw a couple of cumulative updates released for SQL Server 2008 which seems to have gone unnoticed by most, including SQL Server Central and wasn't included in Steve's build list on Wednesday!

Cumulative update 3 for SQL Server 2008 SP1

Cumulative update 6 for SQL Server 2008 RTM

 

Filed under: SQL Server 2008

Determining Nullability of a procedure parameter
24 July 09 06:50 PM | MartinBell | 1 comment(s)

In my posting about NOCOUNT and linked servers I provided a script to create the necessary shell procedures that enabled NOCOUNT to be set ON and avoiding the problems of ROWCOUNTs being returned by linked servers.

One of the problems I encountered when creating the script was that there is no metadata held on whether the procedure parameter was optional i.e. it had a default. To get around this I gave all parameters default values which is not the ideal solution, but in the circumstances was the best solution I could think of.

Following a recent post in the newsgroup by MVP Aaron Bertrand I have become aware that this problem does have a connect item already raised for it at http://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=234143. Aaron has attached a work around to the connect item. which parses the T-SQL code and could be incorporated into my script. Anyone wishing to see expanded meta data for stored procedure parameters may want to add their vote.

Filed under: NOCOUNT, Linked Server

Speeding up SQL Server Management Studio Startup
23 July 09 08:30 PM | MartinBell | 2 comment(s)

One of the tips from the SQL Nuggets competition I ran at the Leeds Area user group this week was to use the –NOSPLASH option to speed up the start up of Management Studio. Adding this to your shortcuts or menu options will reduce the time before you are able to use the application.

This is not the only option you can specify for SSMS.exe. Books Online gives additional options such as specifying the filename for a project, script or solution to open as well as the options for specifying a database, server, username and password or windows authentication to be used when SSMS is started. Specifying a server and windows authentication allows me to open a query window when SSMS starts up. As this is quite often the first thing I do, it looks like being a very useful thing to know as there is no login dialog to slow the start up down.

This got me looking at what options can be specified for start up on the Environment-General tab of Tools/Options dialog. These are:

Open Object Explorer
Open New Query Window
Open Object Explorer and new query
Open Object Explorer and Activity Monitor
Open Empty Environment

Of these the fastest start up option is the empty environment as no connections are made. One advantage of changing the option here is that it will have the same effect if you start Management Studio from the menu, a shortcut or at a command prompt.

Filed under: User Group, SQL Nuggets, SSMS

Leeds Area User Group Slide Decks
23 July 09 04:07 PM | MartinBell | with no comments

Thanks to everyone who attended Monday’s meeting.

I really enjoyed Martin’s talk, and I'm sure you also found it useful.

I’d like to thank Martin for taking the time and effort to come to Leeds and present to us.

http://sqlblogcasts.com/blogs/martinbell/Misc/Size%20does%20matter!.zip

Filed under: User Group

Partitioning Existing Tables
22 July 09 07:00 PM | MartinBell | 4 comment(s)

Table partitions have been available since SQL Server 2005 and there are plenty of examples of how to create them available on the internet such as Craig Freedman's blog and Nigel Rivett’s article as well as Books Online topics  So why the reason for this post? The title may have given you a clue!! I have not found any examples of changing an existing table to be partitioned.

If you have ever moved a table between filegroups, then you will already be familiar with the procedure required to partition a table, as it is effectively the same, although there are specific issues you may have to address.

So how do you do it?

For these examples I will use the following partition function and scheme:

DECLARE @InitialPartitionDate date = '20090201';
CREATE PARTITION FUNCTION PartitionFunction (datetime)
AS RANGE LEFT FOR VALUES (@InitialPartitionDate);

CREATE PARTITION SCHEME PartitionScheme
AS PARTITION PartitionFunction
ALL TO ( [PRIMARY] );
GO

If your table is a heap then you will need to create a clustered index that uses the partition scheme and then drop it. e.g. for the table:

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null
 ) ;
GO

Create the clustered index which uses the partition scheme:

CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id )
ON [PartitionScheme]([PARTITION_COLUMN]);
GO

The following SQL will show the table partitions:

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO

This will return the results similar to:

 

After dropping the index you will see that the table is still partitioned:

DROP INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ;
GO

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO

 

You will notice that the index id which was previously the clustered index (1) is now the heap (0).

I received and email about doing this from Dave Levy who pointed out when you create the clustered indexes any non-clustered index will be re-built and when the index is dropped they will be re-built again, therefore you should consider if you need the table to remain as a heap.

If you already have a clustered index, then you will need to re-create the index, as the ALTER INDEX statement can’t be used to move an index to another filegroup or change the partition scheme you need to use CREATE INDEX and the DROP_EXISTING option.

e.g with the following table and index

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PARTITION_TABLE]') AND type in (N'U'))
      DROP TABLE [dbo].[PARTITION_TABLE] ;
GO

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null
 ) ;
GO

CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id ) ;
GO

Check out the index columns with the following SQL:

SELECT t.name, c.name, i.*
FROM sys.index_columns i
JOIN sys.tables t ON t.object_id = i.object_id AND name = 'PARTITION_TABLE'
JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = i.column_id ;
GO

You will see something similar to:

 

CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id )
WITH ( DROP_EXISTING = ON )
ON [PartitionScheme]([PARTITION_COLUMN]) ;
GO

Using the DROP_EXISTING clause will not re-built non-clustered indexes and so they will not be alligned with the partitions which I will look at in another blog post.

SELECT t.name, c.name, i.*
FROM sys.index_columns i
JOIN sys.tables t ON t.object_id = i.object_id AND name = 'PARTITION_TABLE'
JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = i.column_id ;
GO

If you then run the above query to view the index columns it will look like:

 

As you can see the partition column will also be added.

The partition column also has to be part of a unique index or clustered primary key, but in that situation it needs to be explicitly added to the index/primary key e.g. The following statement to create the tables cause errors:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PARTITION_TABLE]') AND type in (N'U'))
      DROP TABLE [dbo].[PARTITION_TABLE] ;
GO

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null,
 CONSTRAINT [pk_PARTITION_TABLE] PRIMARY KEY CLUSTERED ( id )
 )
 ON [PartitionScheme]([PARTITION_COLUMN]) ;
GO

/*
Msg 1908, Level 16, State 1, Line 1
Column 'PARTITION_COLUMN' is partitioning column of the index 'pk_PARTITION_TABLE'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity unique,
 PARTITION_COLUMN datetime not null
 )
ON [PartitionScheme]([PARTITION_COLUMN]) ;
GO
 
/*
Msg 1908, Level 16, State 1, Line 1
Column 'PARTITION_COLUMN' is partitioning column of the index 'UQ__PARTITION_TABLE2__72C60C4A'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
*/

Adding the partitioning column will solve this issue. So if you have a clustered primary key on your existing table, you will need to drop the constraint and re-create it with the extra column.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PARTITION_TABLE]') AND type in (N'U'))
      DROP TABLE [dbo].[PARTITION_TABLE] ;
GO

CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
 PARTITION_COLUMN datetime not null,
 CONSTRAINT [pk_PARTITION_TABLE] PRIMARY KEY CLUSTERED ( id )
 );
GO

BEGIN TRANSACTION ;
ALTER TABLE [dbo].[PARTITION_TABLE] DROP CONSTRAINT [pk_PARTITION_TABLE] ;-- Primary key index has to be on partitioned column
ALTER TABLE [dbo].[PARTITION_TABLE] ADD  CONSTRAINT [pk_PARTITION_TABLE] PRIMARY KEY CLUSTERED
(
      [id],[PARTITION_COLUMN]
)
ON [PartitionScheme]([PARTITION_COLUMN]) ;
COMMIT TRANSACTION ;
GO 

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO

Filed under: Partitioned Tables

Guids and compression
14 July 09 09:00 AM | MartinBell | 2 comment(s)

In a previous post I compared how GUIDS can have an adverse effect on fragmentation. One of the new SQL Server 2008 features available in the Enterprise and Developer editions is data compression. Apart from GUIDs taking more bytes than an integer, I expected that because of the nature of GUIDs they would not compress very well, and tried to set out and prove it.

I created the following script which uses the TransactionHistory table in the Adventureworks2008 database as the main source of data. To do so I have three tables containing five copies of the data from the TransactionHistory table. In two of the tables the four columns that contain identifiers (TransactionID, ProductID, ReferenceOrderID and ReferenceOrderLineID) are changed to be uniqueidentifiers with defaults using the NEWID or NEWSEQUENTIALID functions depending on the table. I then used the function sp_estimate_data_compression_savings to see what the estimated savings would be if I added row or page compression. These are the results I obtained (full results are here):

object_name	Type	% When compressed
TransactionHistory_int	PAGE	35.14
TransactionHistory_int	ROW	56.87
TransactionHistory_sequentialguid	PAGE	56.21
TransactionHistory_guid	PAGE	67.58
TransactionHistory_guid	ROW	73.42
TransactionHistory_sequentialguid	ROW	73.03

As I expected page compression when using integers would produce the most compressed data, but this is almost twice as compressed as the page compression obtained using GUIDs. This example also showed that for row compression there is no benefit in having sequential GUIDs.

Filed under: NEWSEQUENTIALID(), GUID, NEWID(), Compression

How parameter datatypes can effect caching
12 July 09 07:00 PM | MartinBell | with no comments

Plan caching is something that most DBAs would probably never look at until it becomes a problem on a live system. I thought this recent post by Simon Sabin on his blog was very interesting, as it shows that something which may seem so insignificant during developement could have a huge adverse effect.

I tested this myself and found that if you try the collation solution with a database_default collation the problem does not occur, which may be a more portable solution. If you use a UDT equivalent to nvarchar(max) the problem will still occur.

Filed under: CACHING

NOCOUNT and linked servers
11 July 09 04:16 PM | MartinBell | 2 comment(s)

The other day I was looking at calling remote stored procedures on a linked server when I hit upon this problem or feature, depending on you point of view!!

An example is if you create a loopback linked server called localserver and a procedure such as:

USE AdventureWorks;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Production].[usp_getLongestManufactureProducts]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [Production].[usp_getLongestManufactureProducts]
GO

CREATE PROCEDURE [Production].[usp_getLongestManufactureProducts]
AS
BEGIN
      SET NOCOUNT ON;

      SELECT TOP 10 ProductID, Name, ProductNumber, DaysToManufacture
      FROM [Production].[Product]
      ORDER BY DaysToManufacture DESC

END
GO

The procedure will SET NOCOUNT ON, and if you execute this procedure in the query window of management studio (which will SET NOCOUNT OFF by default):

EXEC [AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

you do not get the number of rows affected message, but if you run this on the linked server using the command:

EXEC [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

the number of rows affected message is returned. I tried this on SQL 2008, SQL 2005 and SQL 2000 and all exhibited the same behaviour.

This of course would cause a problem if you have a client application that didn’t expect the DONE_IN_PROC message to be returned.
 

I wanted to use synonyms to reference the remote procedure so that the application would not require any changes when accessing a remote database, but it looked like the solution would not be that simple! e.g.

CREATE SYNONYM [Production].[usp_getLongestManufactureProducts] FOR [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

(NOTE: this is where using a loopback server in the example can cause problems, create the synonym in a different database or schema) .

MVP Erland Sommarskog pointed out to me that If you did a similar thing with an insert statement, you will not get a rows affected message. Therefore the message is not generated from the procedure itself, but because it is using the linked server.

Unfortunately there is no settable option for the linked server to suppress the rows affected message, therefore the only way to suppress the message would be to SET NOCOUNT ON locally i.e.

SET NOCOUNT ON ;
EXEC [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;
SET NOCOUNT OFF ;

One possible solution would be if you could set this as an option on the linked server and this would mean the least amount of work for the DBA (always a good reason!), so I raised a connect item for this additional feature. This does have some drawbacks as it would be similar to setting the default connection properties so that NOCOUNT is always ON and as I described in my previous article, this is not always possible. So what is the solution?

My final solution was to create a shell procedure that contained two statements, the first was SET NOCOUNT ON, the second called the remote procedure e.g.

CREATE PROCEDURE [Production].[usp_getLongestManufactureProducts]
AS
BEGIN
      SET NOCOUNT ON ;
      EXEC [LocalServer].[Adventureworks].[Production].[usp_getLongestManufactureProducts] ;
END
GO

Here is a script to create shell procedures for all remote procedures, it uses template parameters which you change change in SSMS using the “Specify Values for Template Parameters” on the Query menu.

Filed under: NOCOUNT, Linked Server