Training and Learning

August 18, 2008 at 8:06 am (SQL Server 2005, SQL Server 2008, Tools) (, , , )

I think these are usually two different things, but most people conflate the two. There was a great discussion over at SQL Server Central based on an editorial by Steve Jones. It’s worth reading through to see how people learn or get themselves trained.

One common theme is reading books. I’m looking at stacks of them all over my desk, so it would be hard to deny their use. If you too like to read, then I’ve got something for you. Red Gate is doing a promotion where, when you purchase SQL Tool Belt, you can also download five E-Books offered by Apress. Several of them look pretty interesting, so this is a good deal. Not to mention, if you’re not using some of the tools from SQL Tool Belt already, you’re in for a treat. SQL Compare and SQL Prompt must be open on my desktop most days and I use several of  the other tools every week. Get a good book and check out the great tools.

Updated to accurately reflect the offer.

Advertisements

Permalink Leave a Comment

SQL Server 2008 Install, cont.

August 15, 2008 at 1:30 pm (SQL Server 2008, Tools) (, , )

OK. Hopefully you’re all reading this stuff BEFORE trying to do the install and certainly BEFORE uninstalling everything in sight. The latest version of SQL Prompt, version 3.9, is 2008 compatible AND doesn’t cause this problem with the install.

So, I’ve got to reinstall the software. Time to track down my license. I hope I kept that email.

Permalink 1 Comment

SQL Server 2008 Install, cont.

August 15, 2008 at 1:20 pm (SQL Server 2008, Tools) (, , )

OK. I found some information. The first suggests uninstalling SQL Prompt. I tried it and that did the trick. The second suggest was to do a brute-force removal of the registry key: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90. I didn’t get there.  Install is running on the desktop now.

That seems a bit mean of Microsoft, requiring me to uninstall Red Gate’s SQL Prompt, especially since it’s such a great little tool. I wonder if I can install it on top of 2008? Time for some more research.

Permalink Leave a Comment

SQL Server 2008 Install

August 15, 2008 at 1:14 pm (SQL Server 2008) (, , )

I just finished the install of SQL Server 2008 RTM on my laptop. No real issues except that it wanted SP1 of Visual Studio installed before it would complete. Once I had that done, everything was up and running. The new install routines are nicer than the old ones if a bit more detailed.

My desktop is another matter. SQL Server 2008 objected to a SQL Server 2005 Express install. I removed that. It still thinks Express is installed. I’m removing all of SQL Server 2005 to see what happens.  The error reported was pretty non-helpful.

This is a bit odd since the laptop also has a copy of Express installed.

Well, uninstalling all things SQL Server didn’t work. Sql2005SsmsExpressFacet is failing. It simply states: “The SQL Server 2005 Express Tools are installed. To continue, remove the SQL SErver 2005 Express Tools.” Time for a bit of research.

Permalink 2 Comments

More on Table Valued Functions

August 15, 2008 at 7:29 am (TSQL) (, , , )

From the hits in the search results, this is a popular topic. That being so, I’d like to redirect you to another blog that has some more detailed tests available. These tests show more of the shortcomings of multi-statement table valued functions. Gail Shaw is an MVP and a regular at SQL Server Central. She’s worth tracking and this post shows why.

I want to be clear. I’m not suggesting that you never, ever, use multi-statement table valued functions. There may be places where their use is helpful. I’m saying that using them comes with a very heavy cost, so you better be sure that they are in fact needed in the situation, whatever it is.

There was a long discussion and debate over at SQL Server Central recently on “profane” methods in TSQL. These were defined as the methods that any reasonably experienced DBA or database developer will generally proscribe against, such as TSQL cursors, table valued user defined functions (of either variety), CLR, and a few others. The confusion seemed to arise between the statement “there are usually better ways to access the data than to use X” and the statement “THOU SHALT NEVER USE X.” Apparently saying the first somehow causes people to hear the second. I don’t know why and I kind of don’t care. I think there are perfectly valid uses for cursors and WHILE loops and CLR and triggers and UDF’s… But I also know these things have a cost and if you simply don’t use them, there may be the occasional place where you might have recognized a savings, but overall your database will be better off.

Permalink Leave a Comment

View vs. Table Valued Function vs. Multi-Statement Table Valued Function

August 13, 2008 at 8:36 am (TSQL) (, , , , , )

About five years ago, I was checking an app before it went to production. I hadn’t seen the app before then and a junior dba had worked with the developers designing and building the app. It didn’t use a single stored procedure or view. Instead, it was built entirely of multi-statement UDF’s. These UDF’s called other UDF’s which joined to UDF’s… It was actually a very beautiful design in terms of using the functions more or less like objects within the database. Amazing. It also would not, and could not, perform enough to function, let alone scale. It was a horror because they thought they were done and ready to go to production, but no one had ever tested more than a couple of rows of data in any of the tables. Of course, a couple of rows of data worked just fine. It was when we put in 10, 1000, a few million, that the thing came to a total and complete halt. We spent weeks arguing about the stupid thing. The developers instisted that since it was “possible” to do what they did, that, in fact, it was OK to do what they did.

Anyway, with the help of a Microsoft consultant, we finally cleaned up the app and got it on it’s feet. Ever since then, I’ve preached the dangers of the multi-statement table valued function. The thing to remember is, there are no statistics generated for these things. That means the optimizer thinks they return a single row of data. When they do only return a few rows, everything is fine. When they return even as little as a hundred rows, like the example I’m posting below, they stink.

Anyway, I boiled up this silly example because some developer accused me and several other DBA’s of spreading Fear, Undertainty, and Doubt because we suggested that the multi-statement UDF is something to avoid if possible. Actually, he pretty all but stated that we didn’t know what we were talking about. I was peeved. Hence this example. Feel free to check it out. Oh, and if you check the execution plans, note that the multi-statement UDF is marked as the least costly even though it actually performs twice as slow as the others. One more example of execution plans being wrong.

Here are the time results from one run of the view & UDF’s:

(99 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(99 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

(99 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.

And the code to test for yourself:

CREATE TABLE dbo.Parent
(ParentId int identity(1,1)
,ParentDate datetime)

CREATE TABLE dbo.Child
(ChildId int identity(1,1)
,ParentId int
,ChildDate datetime)

DECLARE @i int
DECLARE @j int
SET @i = 1
SET @j = 1
WHILE @i < 100
BEGIN
INSERT INTO dbo.Parent
(ParentDate)
SELECT GETDATE()
WHILE @j < 100
BEGIN
INSERT INTO dbo.Child
(ParentId
,ChildDate)
SELECT @i
,GETDATE()
SET @j = @j + 1
END
SET @i = @i + 1
END

CREATE VIEW dbo.vJoin
AS
SELECT p.ParentId
,p.ParentDate
,c.ChildId
,C.ChildDate
FROM dbo.Parent p
JOIN dbo.Child c
ON p.ParentId = c.ParentId

CREATE FUNCTION dbo.SingleUDF ()
RETURNS TABLE
AS
RETURN
(
SELECT p.ParentId
,p.ParentDate
,c.ChildId
,C.ChildDate
FROM dbo.Parent p
JOIN dbo.Child c
ON p.ParentId = c.ParentId
)

CREATE Function dbo.MultiUDF ()
RETURNS @Multi TABLE
(ParentId int
,ParentDate datetime
,ChildId int
,ChildDate datetime)
AS
BEGIN
INSERT INTO @Multi
(ParentId
,ParentDate
,ChildId
,ChildDate)
SELECT p.ParentId
,p.ParentDate
,c.ChildId
,C.ChildDate
FROM dbo.Parent p
JOIN dbo.Child c
ON p.ParentId = c.ParentId
RETURN
END

set statistics time on
select * from vJoin
select * from SingleUDF()
select * from MultiUDF()
set statistics time off

Permalink 6 Comments

SQLPLAN Glitch

August 8, 2008 at 9:24 am (SQL Server 2005, TSQL) (, )

While I was doing some work for Andy Warren (more on this after Sept 1st), I came across an interesting little glitch when saving XML execution plans out as SQLPlan files. It’s easy enough to replicate. Just get an XML execution plan from your query:

SET STATISTITCS XML ON;
SELECT…

Click on the link to open the XML plan. Click on the “File” menu and then the “Save As” menu item. It opens the familiar file save window. Click on the “Save as type” drop down and switch to “All Files (*.*)” Save the file with an extension of “.sqlplan.” Good. Now you’ve got an execution plan file that can be opened and viewed as a GUI execution plan. Without closing the XML, try to open this new plan. You should see an error that reads “Visual Studio has encountered an unexpected error.” Close that window and close the XML window. Now try reopening the .sqlplan file. Ta-da! Works fine.

I know this weird and not likely to be encountered much or cause serious problems, but I found it and thought I’d share it.

Permalink Leave a Comment

Deadlocks vs. Blocks

August 6, 2008 at 11:24 am (SQL Server 2005, SQL Server 2008, TSQL) (, )

It makes me crazy when I interview someone who has five or more years as a DBA, but they don’t know the difference between a block and a deadlock. It’s a complete showstopper for me. If you don’t know this, you’re an entry-level DBA, don’t talk to me about your years of experience. Sorry, but there it is.

Here’s someone that’s kinder than I am in every way. Not only have they cut people slack on this question, but he’s provided a well done answer to the question. For those who may interview with me in the future, go and read this and understand it.

Permalink Leave a Comment

A Horseless Carriage?

August 1, 2008 at 11:17 am (Misc) (, )

I’ve talked before about my concern that I’m manufacturing buggy whips.  Jason Massie over at StatisticsIO has posted a pretty convincing argument that cloud computing could be a horseless carriage coming down the road. Effectively we’re still looking Diesel’s first engine, but that could mean it’s just a matter of time. As Mr. Massie points out, the speed of change in IT is one heck of a lot faster than in other parts of the world.

So, when you do finally see that Stanley Steamer roar by, belching smoke and going half the speed of a good horse, don’t laugh and point. Someone is spending time & money on that thing and they’re not buying your buggy whip. Clouds are just like any other major technological shift (ORM anyone?) that could change DBA’s career paths. However, just like those other technological shfits, it could be simply reorienting where we spend our time and focus our efforts, rather than completely eleminating the job.

Anyone who has read a few of my pathetic ramblings on this blog know that I tend to cover a lot of territory, from basic database design, to store proc tuning, to Operations Manager configuration with bits and pieces of VBScript, C#, and other technologies tossed in. That’s because I like to keep my brain facile. I want to always be learning how to learn. The key to being in technology is always finding the next wave and riding it. I’ve been riding this DBA thing for a while and I don’t see an immediate end in sight, but I’m keeping my eyes open and so should you.

Permalink 1 Comment

SQL Server DBA’s Sound-Off

August 1, 2008 at 11:02 am (SQL Server 2005, SQL Server 2008)

This is a great read about the attitudes and beliefs of quite a few SQL Server DBA’s. It’s very interesting how certain beliefs and attitudes seem to be common. I was also reassured that my attitude about a certain DBA was accurate… less said the better. These are interesting and informative guys doing the same kind of work that I try to do. If you’re trying to, go read it and learn.

I read about it on StatisticsIO. Another place with good things to read.

Permalink 2 Comments