Odds and Ends - Zach Nichter

SQL Server 2005 Migration

A friend of mine, Darshan Singh, wrote this article for SQL Server magazine.  It's a great article for those of you that are looking to make the migration from 2000 to 2005.  The article covers two methods of migrating SQL Servers (in-place upgrade) and databases (side-by-side upgrade). 

Enjoy the article, it's very well written, clear, and has some great content.

Enjoy,
Zach

DMV Research

I have been doing a great deal of research on the DMV's, functions, stored procs and tables that are shipped in 2005.  During my adventure I have learned a lot along the way.  I have collected a ton of great information and have had some luck finding information in a lot of great places.  Some of the information that I have picked up has been a result of just trying to understand exactly what it is I'm looking at when I query a DMV.  This stuff can be mind boggling when your not a developer and there is not a bit of information available about it on the web.

I have been collecting the information and breaking the DMV's, functions and stored procs up into categories and trying to derive what the object is doing, then determining how it could be used and maybe giving some query examples. 

Look for some of the results soon.

-Zach 

DMV's - Query Troublshooting

I read the SQL Server Engine Tips blog this morning about troubleshooting SQL statements in 2005.  If you don't read this blog you really should consider checking it out.  The blog uses the new T-SQL features and DMV's to retrieve information about queries that have run through the server. 

There's a lot of great content in the post.

Enjoy,
Zach

PASS 2005 - Day 2

Wow is all I can really say.  I only got to show up today for PASS for a couple of hours but all I can say is WOW!  This was my first PASS conference and man was it great.  I got to see all of my old co-workers at Scalability Experts today and that was great, I really enjoyed seeing them.  I saw all of the great booths and new products in the SQL Server market, which was a lot of fun.  Met some new people and hopefully new friends.  I really enjoyed the social side of the gathering.  It was great spending some time with ex-coworkers of mine Randy Dyess, Rick Heiges, Erin Welker, Darshan Singh and Larry Chesnut.  I met Louis Davidson and talked with him for a bit (very nice guy). And I got to stay for almost all of one session. 

I went and saw Bob Dorr's session about the SQL Server 2005 OS Engine.  Man was it great, the content was excellent and there was a lot of talk about how to use the system dmv's that PSS will use for trouble shooting issues, how to get to a sessions last error message, all of the statistics (OS, SQL, thread, etc.) for that thread when the error occured and so on.  Man it was great and I wish that I could have stayed for the whole thing, but it was great while it lasted.  Some of the DMV's he covered were...

• dm_os_ring_buffer
• dm_os_sys_info
• dm_os_hosts
• dm_os_stacks
• Dm_os_wait_tasks
• Dm_os_workers
• Dm_os_schedulers
• Dm_os_buffer_descriptors

 SIDE NOTE:  Have I ever told you how much I love OneNote? Dang, I love that app.  It's a great app for taking notes at things like technical sessions.

There were awsome topics beinng covered and it was a lot of fun.  Hope everyone that is there enjoys Friday.

Thanks PASS, you all did a fantastic job!!

-Zach

PASS 2005

There are a lot of great blog posts about the events and things going on at PASS this year.  I get to go today for about half the day which will be exciting (Thanks Tom!!).  I am really looking forward to seeing everyone there. 

SQL Server 2005 - DMV's

This is a topic that I am extremely insterested in.  I learned more about SQL Server internals from Microsoft than any other source by diving into the shipped system tables and stored procedures.

This is a big thing in SQL Server 2005, because, as I'm sure you're aware, we no longer have access to the system tables within SQL Server 2005.  There is a great blog by Randy Dyess that talks about this topic in particular. 

Along the lines of looking into system stored procedures, the sp_helptext stored procedure is replaced with object_definition() function.  The object_definition function is an undocumented function that will return the definition of a given object.

EXAMPLE:
select object_definition(object_id('sys.objects'))

The example above will give you the code behind creating that view, and will more than likely reference resource objects that you can't get to unless you are using the dedicated ADMIN connection.  The 'sys.objects' object is a system view, this object can be replaced with the name of a stored procedure to return the source text of that procedure or the name of a funtion to return the text of a function. 

This will be huge in SQL 2005 to determine how the MS Dev team is using SQL Server to give you the information that you need, to discover how and where SQL is getting and putting its information. I will blog some more on this topic tomorrow as I get into the actual DMV's and what information they are providing.

Good luck in the SQL 2005 hunt,
Zach

P.S. If you are going to PASS, have fun!! Hopefully I'll get a day this week to go.