Wednesday, December 31, 2008

Another Year Gone - 2008 in Review

It is really amazing to look back on 2008, and see what all I have accomplished both personally as well as professionally.  At the start of 2008, I was completely unknown in the SQL Server Community and online.  I had asked a grand total of 4 questions on the forums and had never once answer someone else's question.  I had never written a blog post, or technical article online.

To recap this year, I have replied over 5000 times to questions on MSDN forums, SQL Server Central Forums, and Microsoft Newsgroups.  I have posted 128 blog posts, written 2 articles for SQL Server Central and 28 articles on the SQL Examples Site on Code Project.  I also did a Live Meeting for the PASS DBA Special Interest Group, and presented at 3 user groups and 4 different conferences this year. 

If you are reading this and you haven't done a presentation, written a blog post, or online article, then hopefully this will motivate you to get your start.  If you think you don't know enough, or that people won't be interested, then try and do the presentation at your local user group first.  You'll be surprised with the outcome and amount of interest that there may be, I can almost guarantee it. 

Monday, December 29, 2008

ISNUMERIC functions differently SQL 2000 to SQL 2005

Did you know that the ISNUMERIC function works differently between SQL Server 2000 and SQL Server 2005?  I didn't realize it until the other day when a post on the forums made me dig into it a little bit.

The difference is documented in the books online for sp_dbcmptlevel.

SQL Server 2000 and SQL Server 2005 Compatibility Level 80
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant.

For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.

SQL Server 2005 Compatibility Level 90
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored.

For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.

Pretty interesting, only the way to maintain the functionality if you actually expect it to return a zero (0) for commas means that you lose the ability to use things like CROSS APPLY, PIVOT, UNPIVOT, Common Table Expressions (CTE's), SQL CLR, and numerous other features in SQL Server 2005.  The only real way to go around this would be to change code to function differently and test for the commas or do a bulk update of the column to replace commas with a different symbol to force a failure or do an inline REPLACE in the SELECT.

Tuesday, December 23, 2008

Other Blogs that you should be Watching

Following my blog post from yesterday where I listed the major blogs from the SQL Server Team that you should follow, today, I am going to list some "unknown" blogs that you might consider following.  This post is more of a "pay it forward" type of post since Jason Massie, did a similar post and listed my blog in his list of ten blogs to follow and it tripled traffic to my blog. 

In no particular order:

Aaron Alton is a rising star in the SQL Server Community, and a fellow Moderator in the MSDN SQL Server Forums.  His blog can be found at  http://thehobt.blogspot.com.  Look to find posts similar to mine where the focus is around questions commonly asked on the MSDN forums.

Sankar Reddy is another Moderator from the MSDN SQL Server Forums as well who blogs at http://sankarreddy.spaces.live.com/.  Again his posts should be similar in nature to my own, and follow common problems seen in the community.

Gail Shaw is a fellow MVP, and a consistent top performer on the SQL Server Central forums.  She covered the PASS Summit live from PASS Summit, and is definitely worth following.

Monday, December 22, 2008

Microsoft Security Advisory (961040): Vulnerability in SQL Server Could Allow Remote Code Execution

This has been a hot topic online recently, and Jason Massie brought it up last week, and I have seen it blogged a few different places, but now it has a MSRC Advisory for it, and chances are, that your server is affected and you can't do much about it at the current point in time:

http://www.microsoft.com/technet/security/advisory/961040.mspx

Since these Advisories are constantly updated to reflect the newest information, I am not going to rehash it here, but make sure that you read the Advisory and at least know whether you are affected and what your level of risk is.

Blogs you should be watching

If you are reading this, I hope that you are watching my blog, or that you have subscribed to it in a RSS feed reader.  However, if you haven't or you don't that is no matter, and isn't the real topic of this posting.  The real purpose of this post is to make you aware of a specific set of blogs at Microsoft that you should be following if SQL Server is your primary area of responsibility.

Number one of the list has to be the Microsoft SQL Server Release Services blog.  This is the primary location to find out about new releases happening regarding SQL Server, from major releases, to service packs and cumulative updates, it is announced here.  This is how I stat up to date with when releases occur for SQL Server.

Number two on the list would be the CSS SQL Server Engineers blog for SQL Server.  A few people post here, all from the Customer Service and Support team for SQL Server, but the posts are generally centered around problems that the CSS team is seeing in their day to day work.  You can get a good heads up on solutions to popular problems by simply monitoring this blog.

Number three on the list would be the SQL Server Storage Engine blog.  This is one of my favorite SQL Server Team blogs, mainly because it has to deal with the side of SQL that I deal with primarily.  However, in the grand scheme of things, for most people, it is probably the third most important blog to watch behind the above two.

If you are a developer, and not a DBA, swap the above number three with the following number four which is SQL Programmability & API Development Team Blog.  This blog has pointers about how to use the programmability features of SQL Server, as well as how to performance tune code, and the SQL instance for specific scenarios.

Number five on my list would be the Microsoft SQL ISV Program Management Team blog.  A note here is that if you watch the SQL Programmability & API Development Team Blog as well, you will probably see a bit of overlap since these two teams seem to work hand in hand with each other to solve ISV problems at times.  The ISV team may also solve a problem that the Programmability team finds interesting enough to share with their followers and they will repost it, but there are non-overlapping posts between the two that make both worth following.

The last SQL Team blog to watch is the SQL Server SQLOS team blog.  I place this one last only because it doesn't have the posting frequency that other blogs do. The information on this blog is just as important, it just isn't updated with any regular frequency.  However, if an update is posted, you probably would be good to know about it.

That's all folks.  If you are not following the above blogs, I highly recommend it.

Friday, December 19, 2008

Service Broker External Activator Documentation

In October, the SQL Server 2008 Feature Pack became available and included in it a new External Activator for Service Broker.  Documentation is lack luster in the Books Online, but documentation actually comes with the installation that you would probably never find unless you knew about it.  Thanks to The Bit Bucket (Greg Low) from SQL Down Under who pointed out that documentation ships with the External Activator in the C:\Program Files\Service Broker folder.  You can find a Word document called SSBEA.docx in this path that has the documentation.

To download the Feature Pack see:

Download details: Microsoft SQL Server 2008 Feature Pack, October 2008

Wednesday, December 17, 2008

SQL Toolset: SqlQueryStress

Have you ever had  need to hammer on a database server to build load against it?  I recently had the need to do this to test some stuff out, and I was going to write a small C# application to do it.  Instead I found a great tool online for free by SQL Server MVP Adam Machanic:

Adam Machanic : New version of SQLQueryStress released

This is a pretty useful tool and you can't beat the price tag, FREE.  It has a easy to use interface, and allows for a good amount of configuration.  The database configuration page has two different connections, one for the Base Load run, and one to pull Parameters from:

image

The parameterization settings allow you to pull a data set to be used for parameter values for the workload being run, and you can pull them from the same database on the same server, or specify a different connection and database.

The main screen is really user friendly and provides large controls that provide information as the tests are running:

image

If you provide a query with parameters in the code, you can click the Parameter Substitution button to specify the values to map to those parameters from another query:

image

This is a really useful tool in my opinion and it has a number of uses for a DBA.  If you want to know how potentially problematic a piece of code may be, you can run a series of tests against the server.  If you want to see just how much load the server can take, you can easily stress it out with this tool.  As a point of reference, the settings in the images above are sufficient to run a 2 socket dual core server with 4GB RAM to 87% CPU sustained across all 4 cores.

Connect Item: Enhancements to XEvents page_split Event

One of my "things to blog" was how to use Extended Events in SQL Server 2008 to monitor page splits inside SQL Server.  This was a hot topic amongst MVP's recently, and I thought I would have time to get to it, but I didn't, and another MVP did.  Rather than post the same kind of thing over again, I'll send you to Eladio Rincon's blog post:

Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits

As I have said before, I think that Extended Events are one of the best kept secrets at Microsoft, and I am glad to see that others feel the same way.  However, like any product in its infancy, there is room to grow.  The page_split event is an extremely powerful event to capture, if only it could return the necessary information to make it really useful.  I submitted a connect feedback item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=388482

which is targeted at getting the page_split event enhanced.  Now, I can't personally take credit for the idea behind this.  Paul Randal, Adam Machanic, and Greg Linwood all contributed to this information in the MVP groups, I came along after the fact, but it was suggested by a Developer at Microsoft that I put the connect item in to get votes on it, since connect items with votes get attention.

How can this help you out?  Glad you asked.  The connect item is seeking to add additional columns to the events default payload, which is very inexpensive to do if the information is already available at the point in which the event is being fired.  Currently the only columns returned by the event are file_id and page_id.  Both pretty useful, but not to the level that the event could be.  Missing is database_id, which requires a synchronous action to be added to the event payload to collect.  If the allocation_id were added to the event, then we could do a lookup in sys.partitions and easily get not only the object_id, but also the index_id that is being split.  Using these, we can create aggregations across time to identify which indexes in our database(s) are causing the most splits.  Now lets add to this the split_point (begin, middle, end), and split_level (leaf or internal) and we now have a really good idea of what is happening inside our allocation structures, and we can begin to find problems that we might not have other wise found.

Tuesday, December 16, 2008

Getting Windows Search to work inside Code Files

Windows Search is a great tool, and the fact that you can search inside of documents for specific text helped me numerous times in college since I could generic search a folder of source information to find matches without having to actually scan (read) the documents myself.  You can see how to do this in the following image:

image

However, since I started with SQL I have never been able to get it to search inside of my .sql/.vb/.cs code files.  With the forums migration on MSDN today, I spent most of the day goofing around in the NNTP Newsgroups for SQL where someone asked about searching inside code files.  A quick google search turned up the following:

With Windows XP the default behaviour for searching within files will ignore .SQL files.

To get around this you can add a registry setting which will force Windows to treat .SQL files as text so it can search within the file.

  1. Open regedit
  2. Go to HKEY_CLASSES_ROOT\.sql
  3. Add a new key PersistentHandler
  4. Set the default value for the key to {5e941d80-bf96-11cd-b579-08002b30bfeb}

That’s it. You can do this for any other file type as well.

This information is thanks to the following blog post by Perry Stathopoulos:

http://pstatho.wordpress.com/2007/08/20/getting-windows-to-search-within-sql-files/

I had to log out and log back in for it to take effect, but one of our developers didn't have the same issue, so if it doesn't work, give a logoff or reboot a shot.

Monday, December 15, 2008

SQL Server 2005 SP3 is available!

I am sure that I am behind on this but SP3 is now available for download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

Drop All Indexes and Stats in one Script

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation.  As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk.  Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post.  I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

SQL Server 2000 Code

DECLARE @ownername SYSNAME
DECLARE
@tablename SYSNAME
DECLARE
@indexname SYSNAME
DECLARE
@sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR

SELECT
sysindexes.name, sysobjects.name, sysusers.name
FROM sysindexes
JOIN sysobjects ON sysindexes.id = sysobjects.id
JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE indid > 0
 
AND indid < 255
 
AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
 
AND sysobjects.TYPE = N'U'
 
AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name)
ORDER BY sysindexes.id, indid DESC

OPEN
dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET
@sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
 
PRINT @sql
 
EXEC sp_executesql @sql  
 
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE
dropindexes
DEALLOCATE dropindexes

GO
DECLARE @ownername SYSNAME
DECLARE
@tablename SYSNAME
DECLARE
@statsname SYSNAME
DECLARE
@sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR

SELECT
sysindexes.name, sysobjects.name, sysusers.name
FROM sysindexes
JOIN sysobjects ON sysindexes.id = sysobjects.id
JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE indid > 0
 
AND indid < 255
 
AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 1
 
AND sysobjects.TYPE = N'U';

OPEN dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET
@sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
 
EXEC sp_executesql @sql  
 
--PRINT @sql
 
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE
dropstats
DEALLOCATE dropstats

The above script will work for SQL 2005 and 2008 also, but only because compatibility views have been carried forward in code by Microsoft. The correct code for doing this in SQL 2005 and 2008 is as follows:

SQL Server 2005/2008

DECLARE @ownername SYSNAME
DECLARE
@tablename SYSNAME
DECLARE
@indexname SYSNAME
DECLARE
@sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR

SELECT
indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
 
AND indexes.index_id < 255
 
AND objects.is_ms_shipped = 0
 
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY objects.OBJECT_ID, indexes.index_id DESC


SELECT
* FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET
@sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
 
PRINT @sql
 
EXEC sp_executesql @sql  
 
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE
dropindexes
DEALLOCATE dropindexes

GO
DECLARE @ownername SYSNAME
DECLARE
@tablename SYSNAME
DECLARE
@statsname SYSNAME
DECLARE
@sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR

SELECT
stats.name, objects.name, schemas.name
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
 
AND stats.stats_id < 255
 
AND objects.is_ms_shipped = 0
ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN
dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET
@sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
 
EXEC sp_executesql @sql  
 
--PRINT @sql
 
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE
dropstats
DEALLOCATE dropstats

Hope it helps someone out.

Thursday, December 11, 2008

SQL Server Management Studio Object Explorer Autohide Delay

One of the most popular pages on my blog for some reason is the Setting Auto-Recovery/AutoSave in SQL Server Management Studio post I made back in July.  I don't usually recommend that someone hack their registry manually, but in some cases it is the only way to make the changes because the tools available in the application don't allow for it.  SQL Server Management Studio is one of those applications whose configuration options in the registry are not adequately exposed in the tools or options menus.

Since that little hack was so popular, I figured I would post another one, but first the needed warnings:

DISCLAIMER:

Editing the registry is not generally recommended, and/or supported.  If you choose to do so, you are doing it at your own risk.  I am not responsible for damage caused by you editing the registry manually.

Coming once again from the MSDN Forums, this hack involves changing the speed with which the Object Explorer auto hide occurs.  A post back in October commented that the delay for the autohide was to long in SQL Server Management Studio, and wanted to know how to change the time it took to be faster.  Of course this is not documented anywhere online, so I went back to my July post, and started looking at the various registry keys that were available for Management Studio in one of my VPC's.  What I found is the following:

You can edit the following Registry Key:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AnimationSpeed

The higher the value, the faster it will close.  At Hex value 20 it is almost immediately closed.

You can disable the Animations completely by changing:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\Animations

and setting it to a value of 0.

If it helps you out, cool, but if you break it, don't email any scathing comments to me about editing your registry manually.  I am only going to refer you back to the Disclaimer above.

Passed MCITP - 2008 Database Developer

A while back I posted about Beta Exams for SQL Server 2008 where Microsoft basically paid your way to take the certification exams for SQL Server 2008.  The trade off was, you needed to provide feedback on the exam material to help them better create the final questions.  Back in October I was invited by Microsoft to fly to Redmond and sit in a round table discussion on one of the exams I took to review the questions but responsibilities of life simply got in the way of that.  To bad, because it gave me a chance to meet and greet some people I know online, where I might not have been able to otherwise.

Tonight I got an email letting me know that I had passed both the MCTS and MCITP 2008 Database Developer Examinations (if only I had known about the beta exams when the Admin ones were out).  Now all I have left is my MCITP 2008 Administrator Exam since I passed the MCTS at PASS Summit.  Should be able to knock that one out in January sometime.  Professionally, it doesn't mean a whole lot to me as certifications are not a requirement in my current position, but personally it is a great validation that for all the hours I put in playing with the CTP's and RC0, that I actually learned something.

My take on the exams?  Well if you really know your stuff on SQL Server 2005, the gap isn't very big to 2008.  If you are still on SQL Server 2000, you have a lot of catching up to do.  SQL Server 2008 brings a number of new things into the mix, Grouping Sets, XML enhancements, Geography datatype, Geometry datatype, HierarchyID, sparse columns, filtered indexes, Policy Based Management, Performance Data Warehouse and the Data Collectors, and Extended Events to name a few.  However, learning the fundamentals of these new items is far easier than learning the additions to SQL Server 2005 plus the additions to SQL Server 2008.

Wednesday, December 10, 2008

Troubleshooting locking in the database with DMV's

Anyone following my blog knows full well that most of my blog posts come from the MSDN Forums. So it should be no surprise to you if you follow my blog, that this one is yet again another question from the forums.

This one deals with locking in the database engine and how to identify what the index and object names are for locks that are taken and reported in sys.dm_tran_locks.  The resource_type column in this DMV provides information about what lock is taken, and for PAGE and RID types the resource_description column will have a page identifier like 1:2453977.  However for KEY types, the resource_description is a somewhat "cryptic" value like (52004e8d59a4). 

The question posed was how to get the index/objectid from this value.  The answer is simply, you don't/can't.  The assumption that the resource_description column is the key to the object is wrong.  The actual key to the object is the resource_associated_entity_id which is the sys.partitions DMV's hobt_id.  By joining these two views together, it is very easy to get back to the index and object since the sys.partitions DMV carries the object_id and index_id for the allocation unit being locked.  The following query demonstrates how to get the information details for a lock out of the storage engine:

SELECT dm_tran_locks.request_session_id,
      
dm_tran_locks.resource_database_id,
      
DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
      
CASE
          
WHEN resource_type = 'object'
              
THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
          
ELSE OBJECT_NAME(partitions.OBJECT_ID)
      
END AS ObjectName,
      
partitions.index_id,
      
indexes.name AS index_name,
      
dm_tran_locks.resource_type,
      
dm_tran_locks.resource_description,
      
dm_tran_locks.resource_associated_entity_id,
      
dm_tran_locks.request_mode,
      
dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
 
AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id

In researching this question, I actually learned a good bit more about object identification in SQL Server 2005 and 2008.  The sys.partitions table is actually a key table in deadlock analysis as well, which I will blog about in another entry, since deadlocks are one of my favorite subjects to

Tuesday, December 9, 2008

SQL Quiz #1

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/12/09/sql-quiz-1.aspx

SQL Quiz Part 2

In early November Chris Shaw started a chain blog of sorts called SQL Quiz where he called out two well known members of the SQL Community Brent Ozar and Jason Massie to post about their two biggest mistakes as SQL Server DBA's.  I subscribe to both of their blog feeds and thought it was interesting to read about other peoples mistakes.  Chris's intent was to create a chain of posts from well known people in the SQL Community that the community could learn from.  The chain circled around a fairly large group of people including SQLBatman.  It did not however, include me, which I was just fine with.

Well our would be phantom of the night decided to call me out on Chris Shaw's new SQL Quiz Part 2.  The Questions for this quiz…What are the largest challenges that you have faced in your career and how did you overcome those?

Here are my answers...

The largest challenge I have faced in my career has to be making the decision to leave a stable job where I wasn't being allowed to progress in my career as I wanted to, and find another job. I tend to be a very loyal person, almost to a fault.  I stick it out with through thick and thin, and dedicate myself 100% to anything that I do.  I can always find the half full side of the glass, though it may not seem like it is there.  When I made the decision to leave my last job, it was at the start of the real estate market decline, and moving jobs also meant moving cities, and having to sell my condo. 

After contacting a recruiter, I immediately had two great interviews, one with a Christian motivational speaking and marketing company, and one with my current employer OSI Restaurant Partners.  The first company was immediately interested and made an offer on the spot that paid more than the job I was leaving, but the environment felt like the same old same old to me.  OSI on the other hand had a excellent atmosphere, much larger environment, and I would be treading new path as the first SQL DBA they had hired, but they wanted to interview other candidates before deciding anything.

No real big deal there, I still had my job, and I wasn't really in a rush since I was leaving under my own terms.  Recruiters being what they are, they wanted to close the deal and place me in the first job that presented an offer.  I informed them that I would like to wait until the following Monday and see if OSI had made any decisions yet.  This was on a Wednesday, but I still got a call asking if I had decided about the job offer on Thursday, Friday, and Saturday because the company needed to interview other candidates if I wasn't interested.  Same story every call, same answers from me each time, though I got more and more nervous about my decision with each and every call.

Monday morning, and yet another call, at which point I decided I wasn't interested in this company, just because I really wanted to know if I could get a job at a major corporation like OSI at this point, so I told the recruiter thanks, but I would wait for an answer from OSI.  Fast forward two hours, and I get a call back again, this time I'm kind of aggravated because I am at work during the day, and all these calls are distracting.  Turns out OSI decided to offer me the job which I immediately accepted and turned in my two weeks notice, which later had to be extended to three weeks so I could complete my current development project.

A lesson learned from this was not to be wow'd by additional money, and that if you really want something, then you will be willing to wait for it.  It was definitely hard to wait for an offer when I already had one.  Did I happen to mention that my wife kept saying to just take the other job?  I try not to be at odds with my wife, and this caused even more turmoil in the decision process.

My Second Anecdote:

The second hardest thing I have encountered in my career is actually maintaining balance between being a company man, a family man, a military man, an entrepreneur, and a all around geek.  Most people who know me, and all that I am involved with don't know how I keep up with it all.  Setting priorities, and scheduling things appropriately is one of the greatest challenges I have after 4:30pm everyday (I am at work until 4:30pm). 

First thing of importance is my family, and I almost always get my daughter from school (daycare) on my way home.  Then it is off to the house to cook dinner before mommy gets home from grad school.  Sometimes we get side tracked and go to the playground or I chase her around the neighborhood on her Barbie Powerwheel and we go feed the ducks.  While dinner cooks, I jump online and catch up on email from the drive home, trust me it stacks up pretty fast sometimes, responses to Newsgroups and forums postings, and new entries in my blog reader.  After dinner is bath time which my wife and I trade off nights for, but my daughters favorite nights are when I put on my swimsuit and play in the tub with her.  Then we do Dora and Diego, book/playtime, and bedtime.  After bedtime is when I usually get to do most of my playing online, consulting/contract work, and learning while my wife studies and works on her papers and homework. 

Trust me, there is never a dull moment in my life.  One weekend a month I do Army Reserves training, and in 2009, I will be away for 2 months in school for the Army.  The trick for me is, I have fun doing it all.  If it wasn't fun, I wouldn't be doing it.

As for this SQL Quiz thing, I am not sure that I have actually followed the intent of the challenge, but to me those are the two biggest challenges in my career so far.

Now I am going to call out Kendal Van Dyke and Deepak Ranagarajan.

SQL Server 2008 Extended Events - Reference List

One of the best kept secrets of SQL Server 2008 has to be the new Extended Events architecture that was added to the troubleshooting toolset. The Extended Events Engine is the foundation on which Extended Events provide detailed information from inside the database engine. I wrote good coverage of Jerome Halmans session at PASS on them for Universal Thread at:

http://www.utcoverage.com/PASS/2008/

However, that only slightly begins to cover the subject. I spent over a month figuring out Extended Events while working on the Extended Events Manager application that won the SQL Heroes contest. Back then the information on Extended Events was very limited. There were numerous errors in the Books Online, and only Bob Beauchemin had blogged about it. (BTW, I owe a great debt of gratitude to Bob for his willingness to test my app, and provide feedback early on. He also provided me some excellent recommendations along the way.)

However as time goes on, things change, and good information is fairly readily available on the subject today. First Paul Randal, recently published an article in Technet Magazine that you can find through his blog post. Then coming in the February/March 2009, the topic will be covered in the Microsoft® SQL Server® 2008 Internals (Pro - Developer) which is available for pre-order now. (Watch for a quick review from me when it actually ships. I plan to dedicate my time to reading this.)

Some web references on XEvents (Extended Events) are:

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/12/using-etw-for-sql-server-2005.aspx
Introducing SQL Server Extended Events
MSDN Webcast: SQL Server 2008 Advanced Troubleshooting with Extended Events (Level 200)
Debugging slow response times in SQL Server 2008

Monday, December 8, 2008

SET ROWCOUNT functionality changes

Lately it seems to me like I need to put some time and effort into actually reading the updated versions of the Books Online to learn about what I have missed, either as changes coming, or changes that have occured. Take for instance SET ROWCOUNT. I learned how to use this in SQL 2000, and it was by someone else, so I have never actually read the BOL entries for it, until recently.

If you look back at my first post on the Transaction Log - Managing Size, one of the examples I posted for batched deletes uses SET ROWCOUNT to limit the size of the delete batches. This is actually a commonly posted example on the forums, and other places online, and in answering a question on the Newsgroups, I posted this same example as a response. Not long after another person on the Newsgroups, Plamen Ratchev, who pointed out that SET ROWCOUNT will cease to function for INSERT, DELETE, and UPDATE in a future release of SQL Server.

No problem, although this was news to me, so I took a peak at the SQL Server 2008 BOL topic for SET ROWCOUNT and sure enough it is documented in there:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

So I looked back in time to see where this was introduced, and it actually is listed in the SQL Server 2000 BOL topic as well. So it must be being carried forward due to compatibility issues and the amount of code that could be potentially impacted because this really is a common thing to find online or in code. I did a quick search of the code in some of our vendors databases, and almost every one that does a purge job uses SET ROWCOUNT in them. Not to worry, I was nice and emailed a few of them that I had contacts with the DBA support/development staff members to let them know about it so they can update it in a future service pack if they choose.

Thursday, December 4, 2008

SQL Server Backup Fundamentals - Mirrored Backup vs Striped Backup

SQL Server Backup isn't necessarily the easiest thing in the world to do.  I've seen a few posts recently where the poster is performing a restore operation and encounters the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'ServerName'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

What is really sad is that the person encountering this error is often in the midst of a disaster recovery, and unfortunately, they are learning the hard way why testing your backups early, and testing your backups often is a recommended best practice for a reason.  Unfortunately, if you find yourself with this error and this is the only backup of the database you need to restore, you are in serious trouble, because just like a RAID 0 disk, there is no way to rebuild your database without all the backup files in the media set present.  At this point, you might not believe me, and you certainly have that right, but I am presenting the cold hard truth and you can validate this by doing a Google Search or a Windows Live Search

So how exactly does this particular error come about?  For more than a few people it has been caused by SQL Server Management Studio, and confusion about the UI for backing up a database.

image

To someone new to SQL Server, this looks like it might be performing a backup to c:\Sandbox.bak and making a duplicate or mirrored copy to d:\Sandbox.bak.  In reality, this is not the case.  Instead this is performing a striped backup similar to a RAID 0 disk which will write the data round robin to all of the files listed.  Striping backups can be used to improve performance of the backup operation, especially for VLDB's using multiple drives with dedicated I/O channels to each of the drives.  The backup TSQL command from the above scripted out would be:

BACKUP DATABASE [Sandbox] 
TO DISK = N'c:\Sandbox.bak',
DISK = N'd:\Sandbox.bak'
WITH FORMAT,
NAME = N'Sandbox-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


To create a mirrored backup, you can't use the UI in SQL Server Management Studio, you actually have to use TSQL Scripts following the Book Online entry for BACKUP DATABASE.  The MIRROR TO option is used to create the mirrored backup as follows:



BACKUP DATABASE [Sandbox] 
TO DISK = N'c:\Sandbox.bak'
MIRROR TO DISK = N'd:\Sandbox.bak'
WITH FORMAT,
NAME = N'Sandbox-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


The bad thing as I stated previously in this post is that someone doesn't realize the mistake until it is to late.  When was the last time that you tested your backups?  Testing would have caught this problem well ahead of it actually being a problem. 

Wednesday, December 3, 2008

Creating an Indexed View with a Self-Join (Kinda)

The following is based on a post on the MSDN Forums regarding building an indexed view in SQL Server.

Indexed Views are another tool in the toolset for squeezing performance out of SQL Server.  When applied correctly they can be very powerful, but they have so many limitations, that they really are very difficult to use.  The poster on the above thread hit one of these limitations that really seems kind of nonsensical when you are looking at the code, that being that a Indexed View can't have a self-join in its definition.  If you would like to know all about indexed views and how they work, as well as some examples you can play with in AdventureWorks take a look at the following whitepaper on MSDN:

Improving Performance with SQL Server 2005 Indexed Views ...

First to reproduce the problem we'll need to create some tables and data.  Don't worry, these will be reusable for this whole exercise:

SET NOCOUNT ON;
GO
USE tempdb
GO
SET ANSI_NULLS ON
SET
ANSI_PADDING ON
SET
ANSI_WARNINGS ON
SET
CONCAT_NULL_YIELDS_NULL ON
SET
NUMERIC_ROUNDABORT OFF
SET
QUOTED_IDENTIFIER ON
SET
ARITHABORT ON
GO
CREATE TABLE dbo.dimColor (ColorID INT IDENTITY PRIMARY KEY, ColorName VARCHAR(10))
GO
INSERT INTO dbo.dimColor VALUES ('red')
INSERT INTO dbo.dimColor VALUES ('blue')
INSERT INTO dbo.dimColor VALUES ('black')
INSERT INTO dbo.dimColor VALUES ('silver')
GO
CREATE TABLE dbo.dimManufacturer (ManufacturerID INT IDENTITY PRIMARY KEY, ManufacturerName VARCHAR(10))
GO
INSERT INTO dbo.dimManufacturer VALUES ('Ford')
INSERT INTO dbo.dimManufacturer VALUES ('Chevrolet')
GO
CREATE TABLE dbo.Cars (CarID INT IDENTITY PRIMARY KEY, ManufacturerID INT, FirstColorID INT, SecondColorID INT)
GO
INSERT INTO dbo.Cars VALUES (1, 1, 1)
INSERT INTO dbo.Cars VALUES (1, 1, 2)
INSERT INTO dbo.Cars VALUES (1, 1, 3)
INSERT INTO dbo.Cars VALUES (1, 1, 4)
INSERT INTO dbo.Cars VALUES (1, 2, 1)
INSERT INTO dbo.Cars VALUES (1, 2, 2)
INSERT INTO dbo.Cars VALUES (1, 2, 3)
INSERT INTO dbo.Cars VALUES (1, 2, 4)
INSERT INTO dbo.Cars VALUES (1, 3, 1)
INSERT INTO dbo.Cars VALUES (1, 3, 2)
INSERT INTO dbo.Cars VALUES (1, 3, 3)
INSERT INTO dbo.Cars VALUES (1, 3, 4)
INSERT INTO dbo.Cars VALUES (1, 4, 1)
INSERT INTO dbo.Cars VALUES (1, 4, 2)
INSERT INTO dbo.Cars VALUES (1, 4, 3)
INSERT INTO dbo.Cars VALUES (1, 4, 4)
INSERT INTO dbo.Cars VALUES (2, 1, 1)
INSERT INTO dbo.Cars VALUES (2, 1, 2)
INSERT INTO dbo.Cars VALUES (2, 1, 3)
INSERT INTO dbo.Cars VALUES (2, 1, 4)
INSERT INTO dbo.Cars VALUES (2, 2, 1)
INSERT INTO dbo.Cars VALUES (2, 2, 2)
INSERT INTO dbo.Cars VALUES (2, 2, 3)
INSERT INTO dbo.Cars VALUES (2, 2, 4)
INSERT INTO dbo.Cars VALUES (2, 3, 1)
INSERT INTO dbo.Cars VALUES (2, 3, 2)
INSERT INTO dbo.Cars VALUES (2, 3, 3)
INSERT INTO dbo.Cars VALUES (2, 3, 4)
INSERT INTO dbo.Cars VALUES (2, 4, 1)
INSERT INTO dbo.Cars VALUES (2, 4, 2)
INSERT INTO dbo.Cars VALUES (2, 4, 3)
INSERT INTO dbo.Cars VALUES (2, 4, 4)
GO

To reproduce the original error reported in the forums post, we'll first create a view that has the "self-join" in its definition:

CREATE VIEW dbo.CarDetails
WITH SCHEMABINDING
AS
SELECT
c.CarID,
 
m.ManufacturerName,
 
pc.ColorName AS [FirstColor],
 
sc.ColorName AS [SecondColor]
FROM dbo.Cars c
JOIN dbo.dimManufacturer m ON c.ManufacturerID = m.ManufacturerID
INNER JOIN dbo.dimColor pc ON c.FirstColorID = pc.ColorID
INNER JOIN dbo.dimColor sc ON c.SecondColorID = sc.ColorID
GO
CREATE UNIQUE CLUSTERED INDEX CarDetails_CarID ON dbo.CarDetails (CarID)
GO

If you try to create this view, you will get an exception like the following

Msg 1947, Level 16, State 1, Line 1 Cannot create index on view "tempdb.dbo.CarDetails". The view contains a self join on "tempdb.dbo.dimColor".

So what exactly is it complaining about, after all there isn't a self join in the normal way that we would think of it? Well the self join statement is somewhat ambiguous, as is the self join limitation as listed in the whitepaper on MSDN. You actually can't join the same table two times in a indexed view, even if it is through another table. So what do you do if you have an actual structure like the one posted above where you have two foreign key columns to the same table and you need the double join to actually make sense of the data?

I personally was confused by the error, so I built the above example and posted it to the MVP private groups to see if someone else could help shed some light onto the problem.  Assistance and a solution were provided by Steve Kass and Aaron Bertrand, and the following example will show you how to code around this limitation.

CREATE TABLE dbo.Two (i INT)
INSERT INTO dbo.Two VALUES (1)
INSERT INTO dbo.Two VALUES (2)
GO
CREATE VIEW dbo.CarDetails_Imed
WITH SCHEMABINDING
AS
SELECT
  
c.CarID,
  
m.ManufacturerName,
  
CASE WHEN i = 1 THEN 'FirstColor' ELSE 'SecondColor' END AS whichColor,
  
CASE WHEN i = 1 AND c.FirstColorID = pc.ColorID THEN pc.ColorName
       
WHEN i = 2 AND c.SecondColorID = pc.ColorID THEN pc.ColorName END AS [Color]
FROM dbo.Cars c
JOIN dbo.dimManufacturer m ON c.ManufacturerID = m.ManufacturerID
CROSS JOIN dbo.Two
INNER JOIN dbo.dimColor pc ON (c.FirstColorID = pc.ColorID OR c.SecondColorID = pc.ColorID)
WHERE
  
CASE WHEN i = 1 AND c.FirstColorID = pc.ColorID THEN pc.ColorName
       
WHEN i = 2 AND c.SecondColorID = pc.ColorID THEN pc.ColorName END IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX CarDetailsImed_CarID ON dbo.CarDetails_Imed (CarID, ManufacturerName, whichColor, Color)
GO
CREATE VIEW dbo.CarDetails_Final
WITH SCHEMABINDING
AS
SELECT
CarID, ManufacturerName, [FirstColor], [SecondColor]
FROM
(
SELECT
  
CarID,
  
ManufacturerName,
  
whichColor,
  
Color
FROM dbo.CarDetails_Imed WITH (NOEXPAND)
)
src
PIVOT
(
MAX(Color)
FOR whichColor IN ([FirstColor], [SecondColor])
)
pvt
GO

The first thing Steve did was add a new table called dbo.Two into the mix.  This table has, you guessed it, two rows holding values 1 and 2 respectively.  Then he rewrote the query to use a cross join, so the output is going to be double the size in rows.  A slight error in code was corrected by Aaron initially, and then Steve provided the fix as well with the idea that you could pivot the data to get the output to match that of the original view since the cross join is going to be a normalized return instead of a denormalized output as in the original view.

This view can now be indexed and used as an intermediate view to the actual results view which will pivot the data to provide the formatted output that would come from the original view that could not be indexed. One nice thing is that you can put the table hint WITH(NOEXPAND) directly into the output view DDL definition so that queries don't have to use this option explicitly. It has already been defined in the view itself. Since the intermediate view is now indexable, you can also create any needed covering indexes to satisfy the queries against the output view and assist with the pivot.

I know this is a big hack to make something work, but it is one way to solve the problem. One final note is that since this uses the PIVOT operator it is not possible as coded above in SQL Server 2000. However, you can write a 2000 compliant pivot query that would still make use of the indexed intermediate view.