To be quite honest, the best thing I’ve learned has not been anything technical at all, but rather the mere and simple fact that community is king. Like many I’m sure, I went for a long time in my career believing that if the documentation couldn’t help, I’d have to figure it out for myself. Knowing that I can learn from others, and that – even more astoundingly – others can learn from me, has been cemented by PASS like no other event in my life. The best thing about this annual gathering is that the relationships and camaraderie persist far beyond the time boundaries of the conference itself, and continue today to give me that feeling of belonging to something special. And that, my friends, is far more valuable than realizing the meaning behind that obscure trace flag or DMV column.

I have had a few occasions where I have been using Management Studio, and am suddenly and bluntly informed by Windows that I am demanding too much of the application.  Windows is probably right; and it is only partially due to the fact that SSMS can be a resource hog at times, and partially due to the fact that I could have 30 or 40 active query windows at any one time.  So what ends up happening is the app stops responding, and "(Not Responding)" gets painted onto the title bar.  The temptation is usually to just kill the application via Task Manager, but this can be bad for a variety of reasons; most importantly:

1. Do you really know what active queries are running, and what will happen if you forcibly shut the application down?  There can be a big difference between killing an app with idle query windows, and killing an app that is currently in the middle of various transactions on several servers.
    
2. While SSMS has adopted nice Office-esque auto-save and recovery features, how much do you trust them?  I am a little too paranoid to take them for granted.

So, instead of blindly killing the application and exposing myself to these risks, I typically open a new SSMS window, and then try to figure out through that instance of the app which (if any) of my queries are making the original instance hang.  In a lot of cases, it is none of them, but I have used this technique to free up SSMS and make it get out of its (Not Responding) state. First, I run this query (making sure to set results to text using Ctrl+T):

SELECT 
  COALESCE
  (
   QUOTENAME(DB_NAME(t.[dbid])) 
     + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
     + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
   '/*
   ' + t.[text] + '
    */', '
  -- DBCC INPUTBUFFER(' + RTRIM(s.session_id) + ')'
  ) + '
  -- current task : ' 
  + COALESCE(r.[status] + ',' + r.command, '?') + '
  -- % complete : ' 
  + COALESCE(RTRIM(r.percent_complete), 'N/A') + '
  -- KILL ' + RTRIM(s.session_id)
FROM
   sys.dm_exec_sessions AS s
LEFT OUTER JOIN
   sys.dm_exec_requests AS r
   ON s.session_id = r.session_id
OUTER APPLY
   sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
   s.[host_name] = '<my workstation name>'
   AND s.session_id <> @@SPID;

Note the OBJECT_SCHEMA_NAME() function and the database_id parameter for the OBJECT_NAME() function were added in SQL Server 2005 SP2. Hopefully you're there or above, right?  :-)

What this gives me is a list of sessions that are currently connected from my workstation.  I take these results and paste them into the top pane of a query window.  Now I can scroll through and, for each session, it tells me either:

1. the exact procedure or function that is being executed;
   
2. if 1. is unknown (e.g. ad hoc SQL), it gives the entire text from sys.dm_exec_sql_text;
3. if 1. and 2. are unknown, it provides a DBCC INPUTBUFFER statement that I can use to try and figure it out myself.
   

In addition, it provides the percent_complete from sys.dm_exec_requests, if the request is active and the statement is one that is measured, and it provides a KILL statement that I can highlight and execute.  After each kill, I bring focus back to the hanging copy of SSMS to see if responsiveness has been restored.  Typically this is quick work, as most of the sessions are inactive and the hang is within SSMS itself.  In fact in almost every case so far, this has been SQLPrompt or the native IntelliSense being frozen due to obtaining or updating metadata.  The bonus here is that KILL only severs the connection, and your query windows remain intact.  So when you get to the culprit you can go in, save your query windows, and then consider closing SSMS (or even rebooting) and starting over again.

In any case, hopefully this approach will help you at some point, should you ever find yourself in this situation.

Over on my company web site today, I wrote about some general ideas for speeding up Management Studio (when it's not hanging).

I think that people have been lulled into the false sense of security that you can set up a SQL Server database, leave all the defaults in place, and never have to do anything.  While it is true that Microsoft has added many features to SQL Server that make the "hard" parts of being a DBA easier, there is no question in my mind that many people have over-compensated and adopted an attitude of "set it and forget it."  I can't really blame them, I guess, but I am still amazed when these same people get bitten by lack of maintenance or planning, and have no idea how to resolve the situation.

The most common scenario is when users first set up a database and accept the defaults (e.g. FULL RECOVERY), because it sounds good and must be the best option.  They may or may not set up full nightly or backups (or that may be "someone else's job"), but very rarely do they understand the importance of log backups.  So a few weeks or months go by, and everything seems to be working great, and then all of a sudden the drive that their log is on runs out of disk space.  (And as an aside, this is often C:\, and is often also the drive that their data is on.)  This is because their data file has only grown to 500 MB, but their log file is now 40 GB.  I wish I was exaggerating, but I see this all the time.  And in most cases these people are looking for the "quick fix" : tell me how to shrink the file, so I can have my space back, then I'll go back to ignoring the situation.  They don't realize (and don't want to understand) that shrinking the log file in and of itself is not going to fix anything.  It gives them temporary breathing room, but that just means they are going to have to run the SHRINKFILE command in a repetitive loop, days or weeks apart.  Tibor has some great advice about this here and here - the latter is a bit long but, trust me, it is really worth the read.

For a long time I tried to figure out how people got into these situations in the first place.  Some of them are involuntary DBAs, for sure - they need an app, which needs a database, and they can't become a full-fledged DBA just to support this one app, and they can't always predict their long-term file growth needs or understand why they may need disaster recovery or how it can be accomplished.  I then realized that SQL Server actually encourages this behavior in the "New Database" wizard.  Since I always use CREATE DATABASE DDL to create databases, I didn't realize that the UI populates so many defaults for you (though, granted, some of those defaults are the same if you use DDL and omit those options).  Why can't SQL Server be more helpful in this case?  Most of these involuntary DBAs are using the UI to create a database, and the defaults are horrible, if we're being honest.  Here are a couple of ways that I think the defaults could be much better:

General tab

Initial Size

2 MB data file, and 1 MB log file?  I guess this is okay if you are planning to store a single table that never grows.  But for most applications, this is not going to be sufficient, and you are going to start experiencing autogrowth events almost immediately.  And if your data is growing significantly, both your data and log files are going to have these events occurring quite frequently.  Why would you want to make your transactions wait for these events all the time, instead of picking a better starting size?  Couldn't the default be generated dynamically by some formula of free space on the drive, the size of other databases on the system, and globally observed usage practices?

Autogrowth

1 MB for data, 10 percent for log?  Terrible.  I think this should be dynamic and based on the initial size you specified for the files, and % should never, EVER, EVER be a default for data or log.  The problem is kind of like doubling your kids' allowance every week; eventually, it will put you in the poorhouse.  If you grow by 10% every time, that chunk gets bigger and bigger, and unless you are on zippy SSDs or similar, the growth event takes longer and longer - even with instant file initialization.  Of course all transactions have to wait for this file growth to complete, so making that period more predictable is in your best interests.

Path

Personally, I think the drive letters should be dynamically selected based on free space available, or even better, if an HBA is detected or a SAN is otherwise present, users should be reminded that that is likely the best location.  If you still choose smallish drives (and especially C:\), this should pop up a warning, giving some guesstimate about how long the database will "survive" on this drive if the data grows at, say, 1MB/day or 10MB/day.  Same for log.  This may help to prevent some people from just plopping data or log or both on a small C:\ when there is a much bigger D:\ or E:\ available.  Of course C:\ is the default based on the initial installation of SQL Server, and they are getting better about this in the setup wizard, but have a long way to go.  There should be much better guidance on placing data and log on different drives.

This is the big one, in my opinion.  The option to place the database in full recovery by default should be accompanied with, at the very least, a reminder that disaster recovery is important.  Also it could be useful to explain WHY full is preferential, and a link to documentation describing the different recovery models (and why log backups are important).  It would be better to have a tab that allows them to set up both a full and log backup schedule, before the database can even be created.  By default the schedule could be a full backup every night at midnight, and log backups throughout the day, every 15 or 20 minutes.  This should be a very easy set of options and should work much more predictably than the maintenance plans (the main problem with maintenance plans is that when you add a database not all plans automatically pick it up).  Finally, the backups should *not* be allowed to go to the same drive as the data or log; another poor default in the maintenance plan wizard.  There should actually be a way to provide SQL Server with a default network share for backups for all new databases.

So why is disaster recovery an afterthought?  Part of it is a need for better education.  But part of it is because the defaults encourage people to use techniques which are nowhere near the realm of "best practice."  Personally I think some very minor tweaks to the "New Database" wizard could make some giant strides in reducing the number of people who get surprised and burned by ginormous log files.

Here are the Connect items I'd like to draw your attention to this week.  I have abandoned the idea of trying to keep track of vote counts and overall rating.  Being lazy might be a small part of it, but it is mostly because I just don't think it makes sense to assume that my postings are all that influence Connect behavior.  I know I have some impact on the visibility of certain items, but I'm going to leave it at that.

====================================

SQL Server 2008 connection pooling problems

Something to watch out for if you are using connection pooling and SQL Server 2008:

====================================

Statistics on partitions

As partitioning becomes more of a viable and beneficial option, new requirements are creeping out of the woodwork, such as the ability to update statistics per partition instead of for the entire object:

#468517 : Update Statistics at the partition level

====================================

DBCC SHRINKFILE() flexibility

While personally I think the need is not all that dire, some people want to be able to shrink a database file to a size smaller than the original allocation (which is currently prevented by SQL Server).  Joe Sack made the request formal:

#467285 : Shrink files beneath originally allocated space

====================================

Distributed transactions

Erland has filed a couple of interesting issues regarding distributed transactions:

#466739 : There should be a system function to tell whether the current transaction is a distributed transaction

#466749 : Cannot call stored procedures when a distributed transaction has been rolled back

====================================

Clickable URLs in error messages

Finally, Adam is asking for URLs in the messages pane (e.g. those you could embed in custom messages or ad hoc within RAISERROR) to be clickable.  I think it makes a lot of sense, though it is currently resolved as "won't fix"...

====================================

Please remember, I am not trying to coerce you to vote for issues you don't care about, just trying to raise awareness for some items that might have slipped under your radar...

As I mentioned in last week's digest, I want to make it clear that I am not presenting Connect issues in this format in an attempt to tip the scales or pimp influence votes.  I am merely creating exposure for Connect items (bugs or suggestions) that you may not otherwise come across in your daily travels.  Please only vote for issues that you feel strongly about, and not using some excuse like, "well, Aaron told me to." 

Of course you can apply your own rules and filters for which issues you vote on, for example you may give a lot more weight to a bug that doesn't affect you directly than to a suggestion for a feature you know you will never use.  More importantly, I hope that my efforts here raise visibility for Connect itself, as it is a great way to provide feedback directly to the dev team, and in some cases learn about workarounds that aren't published elsewhere.

Anyway, I am a day early, but here are some items for this week that you may want to investigate:

====================

More SSMS bugs

There have been plenty of Management Studio items in previous digests, but this week fellow MVP Razvan Socol has discovered a few pretty serious bugs, including one thrown onto the already huge pile of problems with the table designer:

#462042 : Incorrect "Duplicate key" error with unique filtered index
Current rating: 4.7 (4 votes)

#462046 : Cannot rename a default constraint for a table in a schema which is not dbo
Current rating: 4.4 (4 votes)

#462053 : The filter expression of a filtered index is lost when a table is modified by the Table Designer
Current rating: 4.7 (4 votes)

====================

Another TOP 100 PERCENT ... ORDER BY issue

Sankar Reddy reported an issue with UPDATE STATISTICS which causes many Sort Warnings on the server.  The reason?  UPDATE STATISTICS generates a subquery that uses TOP 100 PERCENT ... ORDER BY.  Yuck!

#457024 : Update statistics, top 100 percent and Sort warnings
Current rating: N/A (0 votes) 

====================

Locking and deadlocks

Fellow MVP James Rowland-Jones has pointed out several possible enhancements to the database engine, tools and documentation that would enhance the process of tracking down and troubleshooting locking/blocking/deadlock issues.

#458076 : Make %%lockres%% a documented feature
Current rating: 4.7 (10 votes) 

#458080 : Lock Resource Hash Value not visible in Deadlock Graph Graphical View
Current rating: 4.8 (8 votes) 

#458084 : Improve content in BOL for Deadlock Diagnosis
Current rating: 4.4 (6 votes)

#458091 : Change Lock Resource Hashing Algorithm to Reduce Likelihood of Collisions
Current rating: 4.6 (11 votes) 

====================

============================================================

Results from last week:

#459383 : Add assertion clause to DML statements
4.2/6 to 4.3/11 (+5)

#459208 : SSMS : Object Explorer fails to show database list during attach
NA/1 to 4.2/4 (+3)

#423019 : SQL Server 2008 Activity Monitor Enhancements

4.7/13 to 4.7/14 (+1)

#352596 : SSMS: Activity Monitor is no longer in Treeview 

4.7/6 (no change)

#361102 : [SSMS 2008 RTM] Restore 2000 Activity Monitor
3.8/6 to 3.8/7 (+1)
 
#350734 : SSMS : Activity Monitor column headers are too fragile

4.3/3 (no change)

#350736 : SSMS : Activity Monitor queries replace white space with ugly boxes
3.9/2 to 3.7/4 (+2)

#350726 : SSMS : Activity Monitor "Kill Process" more dangerous than it sounds

NA/1 to 4.1/3 (+2)

#350729 : SSMS : Activity Monitor process details dialog hard to use

NA/1 (no change)

As a "SQL Server guy" first and foremost, I often have to defend my use of a Mac both as a travel and presentation laptop, and as my primary development workstation.  Today over on my work-related blog, I penned a story explaining my experiences, why I chose to switch platforms for much of my work, and what kind of problems this has caused for me.  It occurred to me tonight that, since I am doing a good portion of my SQL Server development from a Mac, that it might be interesting to some of the readers over here on at least one angle.  If you are thinking about getting a MacBook for travel, or a Mac Pro / iMac for day-to-day usage, or just want to laugh at what a helpless fanboy I've become, please feel free to read my story.

If you want to comment or ask questions here instead of on my employer's web site, that is fine too.  You can also e-mail me directly at aaron DOT bertrand @ gmail.  (Sorry, but I'm still scared of dirty, rotten e-mail harvesters.)