May 2009 - Posts

Are you making good use of your helpdesk?
13 May 09 09:51 AM | MartinBell | with no comments

If the only figure that managers are interested in, is the number of outstanding calls or the number of calls cleared, then they are probably only scratching the surface of what it can do!

Pro-active analysis of your helpdesk calls may highlight an area of the system that generates an high number of problems, this could indicate something needs refactoring or may be one where SQL Server 2008 has functionality that will make it easier. If your system has something like this, then fixing it will save you money, not fixing it will require you to continue to keep throwing money at working around it!

Sometimes resolving helpdesk incidents can be like a black hole and suck in people from all areas of the business including developers and DBAs. Whether these costs are formally accounted for or not, they will detract from the person’s current project and could have a significant impact.

It’s always good to factor into a release changes which will make a system more reliable and require less support, but far too often I have seen the onus put on the “bells and whistles” or new features rather than the underlying maintainability. Hopefully in the current economic climate things may change, but somehow I doubt it.

Many faiths and cultures have a proverb equivalent to Matthew 7:24-27 “The foolish man built his house on sand” but also remember Ashanti of Ghana “By the time the fool has learned the game, the players have dispersed.”

Filed under:
When are you upgrading to SQL Server 2008?
13 May 09 08:13 AM | MartinBell | 2 comment(s)

I’ve done quite a few talks on the many new features of SQL Server 2008. At the start of each I usually ask the audience “Who is already using SQL Server 2008 and taking advantage of all the new features it offers?”.

It’s no longer a surprise to see only a couple of hands in the air, the number of hands increase when I ask “Who has any plans for upgrading to SQL Server 2008?” , but usually the majority of the audience either have no plans at all or no immediate plans.

Although we are in a recession (as validated by the number of contractors “resting”), I do find it hard to believe that (to my perception) the uptake of SQL Server 2008 has been so slow. Articles like the leader in last week’s Computer Weekly “IT cost cuts are redolent of self-harm” or from the inside pages "Businesses and government are willing to make risky IT cuts" and "IT mistakes today are business mistakes tomorrow" would indicate that the lessons learned from previous recessions are been foolishly ignored for the sake of cutting costs.

Whilst a SQL Server 2008 upgrade is not going to have the same benefits for everyone, there are many attractive features that can give almost instant ROI; whilst others may require a development cycle and take a little longer. Even if you don’t intend an upgrade in the near future, you should be looking at things like which features are being deprecated, and if alternatives already exist (in your current version of SQL Server), these changes should be built into the release cycle. This will make sure that you are in a much better position when an upgrade eventually gets the go-ahead.

A good place to start is to download the SQL Server 2008 Upgrade Advisor and run it against a copy of your live system.

So when next time I ask “Who has any plans for upgrading to SQL Server 2008?” I will expect a room full of hands will be in the air!

IDENTITY a fraud?
12 May 09 06:58 PM | MartinBell | 1 comment(s)

One of the tips Tony gave during his talk at last weeks Manchester User Group was not to start your identity values at the default (zero) but start them at the lowest possible value for the datatype you are using. This is a great tip as it doubles the range of possible values and it happened to coincide with some thoughts I had for a connect suggestion.

Reaching the maximum is not necessarily the end of the world, if you are using a smaller datatype it is possible to change the datatype of the column to be one that is larger e.g. change an int to a bigint. But is not always that straight forward as you may have to drop primary keys, indexes and foreign keys and change the data type of any referencing columns and also change any variables or temporary tables in stored procedures or triggers etc. etc…

Another alternative is to set the identity seed back to the minimum possible value and start to use the values you missed. This raises the question… “Why didn’t we do that in the first place?” Of course when you hit zero you will start to have problems again, but it will give you time to think and plan.

Although bigint can hold 2^64 values which according to Brian Moran in his SQL Server Magazine article will take 58,494,241 years to fill at 10,000 inserts per second, starting at zero will halve that time, but you’re not going to be around to see it run out… if all goes to plan!! (I have seen some post-migration issues because seed values have been incorrectly set, so be warned!!)

If you have an integer identity value there are 2^32 possible values, so at the rate in Brian’s example it will only take approximately 0.0136 years to fill which is less than 4.98 days even if you start at the lowest possible value.

By now, you use identity values in your database it may have dawned on you that perhaps you should be checking the values that are in your identity columns and may be planning for them being full? I would hazard a guess that very few DBAs actually actively monitor these values.

Although DBCC CHECKIDENT can give you the current maximum value and the max possible value for an identity column, and IDENTITY_INCR will give you the current increment (which may not be 1!).  I don’t know of any system function that will tell you how many rows you can insert before getting errors, so I wrote a little script that would give me this information.

Finally here is a link to the connect item I raised vote for it if you think it is a good idea!

Filed under:
Table-valued parameters in SQL Server 2008 (Part 2)
11 May 09 03:00 PM | MartinBell | 4 comment(s)

One advantage of the TVP is that you can build up the data for the TVP within a client application. There several examples of this available online, such as on Guy Burstein's blog  and on Mike Taulty's blog there is a short video showing how to use a DataTable as a table-valued parameter. You can also use DbDataReader or IList<SqlDataRecord> for the input parameter in the client application

For my table-valued parameter presentation, I extended Mike's example to include another dataGridView that displays the values in the destination table. This was very easy to set up using the Visual Studio wizards and means you can see the effects immediately without switching back to SQL Server Management Studio. This dataGridView is populated using the procedure dbo.Get_All_Nodes which can be found in the TVP-Client.sql script. This script will also create the TVPDEMO database, udt_HospitalHierarchy user defined type, the destination table dbo.HospitalHierarchy and the procedure which takes the TVP as a parameter dbo.Insert_Residents_From_Table_Valued_Parameter. The type for this parameter is SqlDbType.Structured. Source and code for the project is in TVP-Project.zip. To run the project you will need .NET framework 3.5.

In the TVPForm class you will see I have created a Datatable

   private DataTable table;

Which is then used in the InitializeData method. All the columns in the table are strings, so there is an implicit convertion to the hierarchyid, so when this value is input I specify an ordpath.

    private void InitializeData()

    {

        table = new DataTable();

        table.Columns.Add("node", typeof(string));

        table.Columns.Add("name", typeof(string));

        table.Columns.Add("type", typeof(string));

        dataGridViewIn.DataSource = table.DefaultView;

        dataGridViewIn.Columns[0].Width = 100;

        dataGridViewIn.Columns[1].Width = 200;

        dataGridViewIn.Columns[2].Width = 130;

    }

Of couse it wouldn't be a demo application without the big button and that is where the procedure dbo.Insert_Residents_From_Table_Valued_Parameter is executed.

    private void button1_Click(object sender, EventArgs e)

    {

        using (SqlConnection con = new SqlConnection(

              "server=(local);database=tvpdemo;integrated security=sspi"))

        {

            con.Open();

            using (SqlCommand com = new SqlCommand("dbo.Insert_Residents_From_Table_Valued_Parameter", con))

            {

                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.AddWithValue("tvp", table );

                com.ExecuteNonQuery();

            }

            con.Close();

           //Clear down the input grid

            while (dataGridViewIn.Rows.Count > 1)

                dataGridViewIn.Rows.RemoveAt(0);

//Re-populate the Output          

this.get_All_NodesTableAdapter.Fill(this.tVPDEMODataSet1.Get_All_Nodes);

        }

    }

The setup script will enter the root node for Seattle Grace Hospital, so if you run the application the initial screen should look like:

TVP-Example1.png

You can enter data into the top grid (being careful to get the ordpath values right!)

TVP-Example2.png

And pressing the Insert Data button..

TVP-Example3.png 

I can see that TVPs are going to be a useful feature in SQL Server 2008, they are easy to code and will help reduce the number of round trips to the server. TVPs are not the only method for passing an array or structure to a stored procedure, so if you already have a solution will it be worthwhile changing the implementation to take advantage of this new feature? In my next blog in this series I will try to look at how TVP perform compared to other methods and try and find an answer! To see the first blog post go here

Filed under: ,
Table-valued parameters in SQL Server 2008 (Part 1)
10 May 09 10:05 PM | MartinBell | 6 comment(s)
At the inaugural meeting of the Manchester SQL Server User Group I gave a talk on Table-valued parameters (TVPs), so for my first post I thought I’d share you some of the information from that session.

A question that commonly occurs in the SQL Server news groups is "How do you pass and array to a stored procedure?" You can tell that it is a very common question, because several MVPs have dedicated time and effort to add pages to their web sites just to cover this issue for instance Aaron Bertrand on aspfaq.com has an article and Narayana Vyas Kondreddi has an article as well on his website; but the daddy of them all has to be the pages Erland Sommarskog has put together on sommarskog.se. There are actually many solutions to this problem and it will depend on which version of SQL Server you are using as to which ones are feasible. Erland investigates the performance of many of the different methods and shows his finding at http://www.sommarskog.se/arrays-in-sql-perftest.html. The results make interesting reading; for example, he found the performance using XML attributes better than using XML elements and choosing which method to use will depend on if you have fixed length values or not.

At the moment there is no in-depth study using SQL Server 2008 which adds a new option to pass multiple values, that is a tabled-valued parameter.

You have always been able to use temporary tables created outside a stored procedure such as (code):

-- Create procedure referencing temporary table which has not been created yet!
 
CREATE PROCEDURE dbo.Get_Residents_From_Tmp_Table
AS
                                SELECT hid.ToString() AS node,
                                                hid.GetLevel() AS [level],
                                                name,
                                                [type]
                                FROM #tmphierarchy
                                WHERE [type]  = 'Resident'
GO

-- Create temporary table using new HierarchyId data type
CREATE TABLE #tmphierarchy ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
                                                                name varchar(30) not null,
                                                               [type] char(10) not null
                                                )
GO

INSERT #tmphierarchy ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
                'Seattle Grace Hospital',
                'Hospital' ),
                ( hierarchyid::Parse('/1/'),
                'Cardiology',
                'Department' ),
                ( hierarchyid::Parse('/1/1/'),
                'Preston Burke',
                'Consultant' ),
                ( hierarchyid::Parse('/1/2/'),
                'Cristina Yang',
                'Resident' ),
                ( hierarchyid::Parse('/1/3/'),
                'Erica Hahn ',
                'Consultant' );
GO

EXEC Get_Residents_From_Tmp_Table
GO

INSERT #tmphierarchy ( hid, name, type)
VALUES  ( hierarchyid::Parse('/2/'),
                'General Surgery',
                'Department' ),
                ( hierarchyid::Parse('/2/1/'),
                'Richard Webber',
                'Consultant' ),
                ( hierarchyid::Parse('/2/2/'),
                'Miranda Bailey',
                'Consultant' ),
                ( hierarchyid::Parse('/2/3/'),
                'Meredith Grey ',
                'Resident' );
GO

EXEC dbo.Get_Residents_From_Tmp_Table
GO

But that is not a great solution if the stored procedure requiring the array is called directly from a client application. This example is slightly different to just passing an array, as I am looking at a more complex data structure including the use of the hierarchyid data type which is also new in SQL Server 2008 and that is a whole blog entry unto itself. To be able to pass an array of structures rather than a homogenous array would obviously require some modification to the methods described by Erland.

If you want to use tabled valued parameters the first thing you will need to do is declare a user defined type for the table type such as:

CREATE TYPE udt_HospitalHierarchy AS TABLE ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
                                                                                                name varchar(30) not null,
                                                                                                [type] char(10) not null
                                                                                                )
GO

This is also new for SQL Server 2008. Being a table you can obviously have a more complex definition than simple delimited string arrays and you can also create a primary key on a table type. If you want to look at the user defined table types the catalog view sys.table_types e.g. for the above definition you will get back the row (I've split this for readability)

Name

system_type_id

user_type_id

schema_id

principal_id

max_length

precision

scale

udt_HospitalHierarchy

243

257

1

NULL

-1

0

0

 

collation_name

is_nullable

is_user_defined

is_assembly_type

default_object_id

rule_object_id

is_table_type

type_table_object_id

NULL

0

1

0

0

0

1

37575172

One of the problems with user defined types which has always been a bit of an issue, is that once they have been referenced you can't drop them (see the "DROP TYPE" topic in Books Online), there is also no ALTER TYPE commands, so once it is defined and used you had better hope it is right!!

Another thing you can't do with user defined table type is define a computed column using a method e.g. the hierarchyid's level method can not the used:

CREATE TYPE udt_HospitalHierarchy_with_level AS TABLE ( hid HierarchyId NOT NULL,
                                                                                                [Level] AS hid.GetLevel() ,
                                                                                                name varchar(30) not null,
                                                                                                [type] char(10) not null
                                                                                                )
GO

This returns error 2785 indicating methods on CLR types can not be used in this context.

Once the user defined type has been declared it can then be used as a parameter to a stored procedure:

-- Create procedure using a table-valued paremeter
CREATE PROCEDURE dbo.Get_Residents_From_Table_Valued_Parameter (
@tvp udt_HospitalHierarchy READONLY
)
AS
                                SELECT hid.ToString() AS node,
                                                hid.GetLevel() AS [level],
                                                name,
                                                [type]
                                FROM @tvp
                                WHERE [type]  = 'Resident'
GO

The parameter has to be declared as READONLY. This restriction means that the values in the TVP can not be updated, inserted or deleted. It also can't be the target of a SELECT INTO or INSERT EXEC statement within the procedure.

The following example shows the TVP in use, anyone who saw my talk at SQLBits IV may recognise my "favourite" hierarchy! (code)

-- Create a table variable using the UDT
DECLARE @h1 udt_HospitalHierarchy
INSERT @h1 ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
                'Seattle Grace Hospital',
                'Hospital' ),
                ( hierarchyid::Parse('/1/'),
                'Cardiology',
                'Department' ),
                ( hierarchyid::Parse('/1/1/'),
                'Preston Burke',
                'Consultant' ),
                ( hierarchyid::Parse('/1/2/'),
                'Cristina Yang',
                'Resident' ),
                ( hierarchyid::Parse('/1/3/'),
                'Erica Hahn ',
                'Consultant' ) ;

/* Just show what is in there! */

SELECT hid.ToString() AS node,
                                hid.GetLevel() AS [level],
                                name,
                                [type]
FROM @h1

/* Execute the procedure */

EXEC dbo.Get_Residents_From_Table_Valued_Parameter @h1
GO

Calling this procedure will return Cristina Yang.

And to prove it is adaptable you can use a different table variable and pass that to the procedure:

DECLARE @h2 udt_HospitalHierarchy
INSERT @h1 ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
                'Seattle Grace Hospital',
                'Hospital' ),
                ( hierarchyid::Parse('/1/'),
                'General Surgery',
                'Department' ),
                ( hierarchyid::Parse('/1/1/'),
                'Richard Webber',
                'Consultant' ),
                ( hierarchyid::Parse('/1/2/'),
                'Miranda Bailey',
                'Consultant' ),
                ( hierarchyid::Parse('/1/3/'),
                'Meredith Grey ',
                'Resident' );

SELECT hid.ToString() AS node,
                hid.GetLevel() AS [level],
                name,
                [type]
FROM @h2

EXEC dbo.Get_Residents_From_Table_Valued_Parameter @h2
GO

Calling the procedure this time will return Meredith Grey as the resident. So that is the basics of using TVPs in my next post I'll look into how they can be used with a .NET client, and in the final part I look at performance

Have I missed the party?
08 May 09 11:58 AM | MartinBell | 4 comment(s)

As a SQL Server MVP since 2003 I have been heavily involved in the UK SQL Server community, but I only realised after the last SQLBits Community Conference in Manchester, that I was the only member of the organising committee not to have a blog or website!

I've been wary of blogging; my main excuse not to do it myself was thinking I would not have time to produce something worthwhile. I've seen many bloggers start off enthusiastically enough but then the postings wither away to nothing. Feel free to give me a prompt if you think that the blog has gone quiet!

I have posted before on the Technet Industry Insiders blog, but I have not really had anywhere where I can post my own slide decks and the follow up questions and ideas that we get from User Group Meetings etc.

I intend to post both technical content as well as community information and may be some of my own views/take on things although I don’t want to turn into a grumpy old man; we already have one Grumpy Old DBA and that’s more than enough!

More Posts « Previous page

This Blog

SQL Blogs

Syndication