Just when I thought it wasn’t possible to make an already amazing conference better.  Brent comes along with this fantastic presentation on Virtualization and SAN basics for the SQL Server DBA.  You can watch an older version he did for SQL Bits here.

Brent started the session by describing how virtualization is about making things cheaper and easier to manage.  He also described a lot of ways to ensure we don’t leave performance on the floor while doing so.  Brent explained why it is better to start with 2 virtual CPU.  This helps to ensure we get the same machine each time.  Some other tips were to always run the latest hypervisor, minimize background services and applications, and frequently check and monitor the CPU speeds and cache.  He showed us one really great tool to check the speeds called CPUID.  Brent also emphasized getting memory reservations from the host and described how empty memory won’t be used.  It is important to set the min/max memory, avoid locking pages in memory while in virtualization, and monitor the memory levels to avoid issues with the balloon driver.  He went on to describe storage and VMOTION.  He also described the pros and cons of using virtualization for High Availability and Disaster Recovery.  A lot of really great material and he described it all like the true SQL Server master he is.

For the SAN section, Brent gave a very detailed explanation of how SANs work, the architecture and the importance to follow your vendor’s best practices.  All too often the SANS are configured incorrectly and that just leads to performance issues.  Brent described various RAID arrays and types of disc systems, and which were better suited for reads, writes etc. while maintaining the best performance and price in mind.  He went over shared and dedicated disk arrays, compression, and backup mechanisms using SAN snapshots, and replication.  He also described multipathing and how it can drastically improve SAN performance.  Brent finally described some tricks to monitor the SAN and analyze the throughput capabilities so we can know our machines limits.   

I could just go on and on, about how informative and helpful this session has been to me.  I really learned a lot and I am so thankful for Brent’s incite and expert advice.  I would highly recommend following Brent’s tips and I have already seen the fruits of his labor as I put some of them to work already.  What a great way to end an amazing conference.  I can’t wait for the next one now, but I will always remember SQL DEVConnection 2011 as being one of the best.  Thank you all for a great learning experience!

Next I went to Paul Randal’s Index Fragmentation, the Hidden Menace.  What better presenter is there than the man himself?  Paul was the original author of DBCC REINDEX, DBCC INDEXDEFRAG and a lot of the DBCC commands in SQL Server.  He gave us a deep dive into the internals of SQL Server index structures.  He described B-Trees and how SQL Server stores data, some of the DMV’s to analyze fragmentation and which items we really need to pay attention to.  He described logical fragmentation as well as page density, as being two of the most meaningful metrics to use in diagnosing the fragmentation.  Then he showed examples of how to prevent fragmentation from occurring as badly and how important it is to select the correct fill factor for each table.  Paul advised using index defrag on items that around 30% fragmented and recommended using Index rebuilds on items that are over 30% fragmented.  These are just general guidelines though and should be determined by your individual environments.  Paul also mentioned a great resource for managing fragmentation by using Ola Hallengren’s maintenance scripts.  Another great session and I learned a lot of really great material.

The next session was one of my favorites.  It was Maciej Pilecki’s Dude where’s my memory?  Maciej really outdid himself here.  How many of us really understand the memory structures of SQL Server?  Maciej is truly one of the few.  He started the session by describing the different types of memory and their use for various operations.  He described the use of the AWE memory and the 3GB switch.  It’s use in 32 bit machines, and why it isn’t needed on 64 bit machines with over 4GB of RAM.  He described working set trim, locking pages in memory, NUMA affinity and SQLOS(the caching framework.)  He went over the evaluation of clock hands and procedural cache stores.  He described how to optimize executions for ad hoc operations.  I was just amazed just at all of the material Maciej presented and how clear he made it to me even though it was really complex material.  He is truly a one of a kind presenter.

Finally the last session of the day was Follow the Rabbit with Paul Randal, Kimberly Tripp, Jonathan Kehayias, and Maciej Pilecki.  This session was mainly a question and answer session.  They went over implicit column conversion problems, plan cache, page compression, column store indexes, and extended events.  They also mentioned Jonathan’s extended events manager.

What a great conference!  It was just incredible.  I learned so much in so little time and I even got a lot of swag to go along with everything I learned too.

Next I went to the Optimizing Procedural code with Kimberly L. Tripp.  Kimberly went through a deep dive of how SQL Server handles procedural operations in SQL Server.  She described the need to run sp_recompile at the table level whenever we add new indexes.  This ensures that the optimizer has all of the latest statistics and removes old execution plans that are no longer valid.  She also went over an issue that happens in stored procedures that work with parameters that change a lot.  When SQL Server generates the execution plan it stores the values from the very first execution.  It makes a drastic difference in performance as the values can vary greatly from one execution to the next, and by using a plan that was generated on a smaller number of values can be painful later on after numerous growth in the underlying tables occur and you now have a lot more rows in the table then when we had during the first execution.  In Development/Testing environments she recommended to try using the execute with recompile statement when you are working with small procs and whenever they have varying parameters that change frequently.  Next she went over some other possibilities by using dynamic string execution or by using the OPTION optimize for (@param = ‘value’) solutions.  She also described using statement level recompile and modulizing the stored procedure by using a wrapper.  A lot of really great ideas here and she mentioned a really cool trick by using login-less users in her little bobby tables example.

Next I went to Brent Ozar’s Consolidation, Clustering and Virtualization session.  This was a high level session on the pros and cons of active/active clustering, instance stacking(numerous instances on clusters), consolidation, and virtualization scenarios.  He broke out the details into dashboard type comparisons and explained where you would benefit from one to the other and keeping in mind that we are always looking into cutting costs etc.  He discussed the granularity of each in terms of:

• Version Upgrade Plans
• Maintenance Windows
• Security
• Compliance Auditing
• Performance
• Licensing
• High Availability
• Disaster Recovery
• Backup and Restore

All in all, this was a fantastic session and it really helped me as I am currently going through a capacity planning and scalability project.  The information gained in this session was extremely enlightening.  I am now aware of all these aspects and I can make the decision a lot easier now having pondered all the options.

Another really great day.  I am shocked just how useful and informative all these sessions have been thus far.  This conference has been one of the best I have ever attended.  I can’t wait for Day 3.

One of the biggest and most exciting features is the Column Store index.  This index is a new type of columnar nonclustered index.  You can only have one column store index on a table but you can name as many columns as you want.  It uses the Vertipaq technology from PowerPivot which is a client side in memory structure that handles the pivoting of data into what are called segments.  These segments are groupings of 1 million values and the data are stored as blobs.  This type of technology is expected to yield at least 10 times the performance for returning data.  This efficiency does come with a few drawbacks though.  For now, the columnar indexes are suited for tables that have mostly static data.  You can also only use common SQL server data types.  You also can’t change the data afterwards…although there are a few workarounds for this.  You can of course drop and recreate the indexes, but another means to avoid this drawback is to partition your table and then bring new data in and out by swapping out the partitions.  Also you can just do a union all with other tables or subsets etc.  None the less I am very excited to start playing with the new columnar index.  I will follow up with more posts on the feature later.

Another new feature is AlwaysOn.  AlwaysOn is an enhancement on the database mirroring technology and merged it with clustering and then used for high availability.  Previous to mirroring, which came out in SQL Server 2005, you only had clustering, log shipping or replication to support your needs of scaling out our processing power and high availability needs.  Previous versions of the mirroring were basically just standby versions that you couldn’t query or use until you switched over to it…that is unless you were using snapshots on top of the mirror.  The snapshots worked, but the whole process of maintaining them over the mirrors were cumbersome, and you could only use them for two SQL servers.  Now with AlwaysON, it is like mirroring but you can use it on many different servers and databases via Availability Groups that you can query against and move over to individually and use for scaling out your performance intensive queries.  You can add in a node for availability, scaling out to many other nodes by having secondary or more that are kept up to date with synchronous updates across great distances.  This is really cool, but in a way it was really needed after they took away the read only log shipping capabilities in 2008.  All in all a great edition and I welcome Microsoft’s initiative to recognize the needs of having synchronous copies of our databases for use.  Brent Ozar has a more thorough review of the technology here

Microsoft is also starting to deploy what are called Parallel Data warehousing products as an “appliance”.  When I say as an appliance, I mean they are taking all of the installation, hardware setup, configuration, and best practices and then bundling it all together in one package that they sell you that also includes all the servers, data storage, backup devices etc.  They have a few of the largest vendors in their program, but HP is the currently the front runner.  Essentially, you just order everything in one big package as a whole and you should be ready to handle all your data warehousing from the one package as an appliance.  Supposedly, you just add data.  This was the first I heard of such a thing, but it has been in the works since the end of last year and unveiled it in SQL Server 2008 R2.  They also now have a Fast Track package available which is basically a road map or “Cook Book” if you will that uses various vendor offerings and they tell you how to set everything up yourself using all the best practices and vendor specific details.  This is just the “Recipe” for success and you build and support it yourself on your own hardware and environment.  You can read more about it here.

Also announced were numerous T-SQL enhancements.  Denali will add what are called “contained databases” These databases include all the user and login metadata information all together with the database.  Gone are the days of worrying about orphaned users and matching up the logins and users.  Pretty cool.  They added in Throw statements which are used with errors, a new paging syntax with fetch offset, sequence generators, and metadata discovery.  There were also a number of other enhancements and offerings described that are listed here.

The final session of the day was Maciej Pileski’s SQL Server User Defined Functions, The Good, The bad, and the Ugly.  I have to say first, that Maciej is one of the most charismatic and entertaining presenters I have ever met.  He described the material effortlessly and with such high caliber quality.  I couldn’t believe that someone could make a session so informative and exciting at the same time.  Maciej described all the kinds of functions we have in SQL Server and listed out some performance issue we need to be aware of in their use.  He described why using scalar functions can be very performance intensive and showed just how misleading an execution plan can be.  In example, the execution plan can show 0% cost but if you take a look at the statistics IO or profiler you will see boat loads of activity and it executes RBAR(Row by Agonizing Row) for its operations.  Next he described tabled value functions and their use when inline, multi-statement, or by using CLR.  When we use inline they act as a view with a parameter.  When we use multistatements they act like a stored procedure but they only assume one row is returned so they are basically a “BLACK BOX.”  When we use CLR TVF’s we can take advantage of regular expressions.  By far this was my favorite session of the day. 

All together this was an extremely great start to the conference.  I learned a lot about the new features and I am itching to get cracking on them as soon as I can.  To top the day off I had dinner with Brent Ozar and Maciej Pilecki.

What blew me away most was discovering just how incedibly passionate and devoted these folks truely are about databases.  I knew they were passionate and knowlegeable already, but they really eat, sleep, and breath databases all day long and it’s in everything that they do.   To accomplish what these guys do everyday, and the way they do it, is simply amazing.  It’s hard enough to find good database people in this world nowadays, but these two are really electrifying in their presentations as well.  Their deep level of understanding is second nature to them, and they make the most bland and mondane subjects, enjoyable for everyone no matter the knowledge and experience. They break down the most complex structures, so it’s so easy to absorb and remember, forever.  What was the most interesting thing for me though was listening to them talk about their families and their lives in general.

I am a family guy, married and I have two little girls.  I could never imagine making the level of commitment that these guys do everyday. From preparing interesting material for presentations, blogging, twitter, reaching out in the community, writing books, and not to mention all the traveling that they do, all while balancing and maintaining their family life too.  Amazing!  Maciej Pilecki has more airline miles, accumulated in three months alone, that I have ever had in my entire life!  And this is a guy is married to a lovely wife, whom I also met tonight, and they have three small children.  They are from Poland, which my wife is also from, so it was nice to chat about the old country for a while, and hear all about how the two of them met.  Brent has no children at the moment, but has been together with his girlfriend for 11 years.  He hasn’t tied the knot just yet, but I have a feeling it just may be coming soon.  We will see.

On to the sessions today…all in all, they were all really good.  They had really good presenters, and then they had just off the wall really great presenters like Brent and Maciej.   Today was a Microsoft Day, so it was focussed on enhancements and new features of SQL Server Denali, the next version of SQL Server.  Microsoft’s focus for this edition is going to be all about changing expectations.  And to do that, they added a number of features and offerings.   I was excited to learn more about SQL Server AlwaysON, the addition of a number of TSQL enhancements, like contained databases, parallel datawarehouse (Microsoft’s DW as an appliance) etc., but the most intriguing new feature for me was the new index type called columnstore.   The columnstore index is really going to be a game changer. 

I will write more about them all and the individual sessions too, later in greater detail, as I need to get to bed now and prepare for day 2.  I can’t believe it’s only been one day so far, and I still haven’t heard sessions with Paul Randal and Kimberly Tripp.   This conference has been really special thus far, and it really is amazing to know it has only begun!

In SQL Server Management Studio, you can do it three different ways:

1) We can write an update query.  This really doesn’t give the user what they are looking for even though it’s the most natural way for me.

UPDATE Table1
SET column1 = ‘blahblahblah’
FROM dbo.Table1
WHERE ID=201

2)  We can right click table–> select edit TOP 200 rows–> and then click the SQL button to edit the query to return more records then 200. 

3) We can also just change the default TOP N records for Edit under Tools–>Options–>SQL Server Object Explorer .  Setting it to 0 returns all records.

I’m not sure a lot of people are aware of this or not, but I figured I’d just let everyone know just in case.  Microsoft has a program available to small business startups, called Bizspark.  Bizspark a global program that provides Microsoft software, support and visibility for small companies that is basically free of charge for 3 years.  You pay $100 once you exit the program, but before that it’s absolutely free!  As with everything there are a few caveats, but for must of us they are pretty hard to hit when you are just starting out. 

The restrictions are as follows:

• Your company has to be privately held
• Your company has to be less than three years old
• You have to make less than $1 million annually
• And you have to develop software or provide support services  (consulting etc.)

You also get publicity that can lead to potential investors, clients, and partnerships etc.  With the MSDN subscription you can download fully functional software, ie SQL Server 2008 R2, Visual Studio 2010, Office 2010…the list goes on and on.  This is one of the best deals I think I have ever run across, so if you are not currently aware of it, and you are a new startup, check it out today. 

Could not relay results of procedure ‘SSP_SAMPLE’ from remote server ‘LNKTEST’. [SQLSTATE 42000] (Error 7221)   [SQLSTATE 01000] (Error 7312).  The step failed.

At first glance I thought it must have been the remote query timeout setting for the server.   So in order to check this I took a look by running  sp_configure

The setting for remote query timeout (s) was set at 600.  This tells me I have the default setting which is a 10 minute timeout period for remote queries.  The query when ran manually runs for 5 minutes so that’s almost half the time of  the 10 minute timeout period so that wasn’t it.  What on earth could it have been then? Maybe it was missing a setting in the linked server or permissions, so I took a look at the security for the linked server.  Unfortunately it wasn’t going to be that easy for me either.  The login for the linked server was given the appropriate rights to execute the proc in the database and again, it was running manually just fine so it couldn’t have been permissions.  HMMMMM.  Maybe it was the RPC, or RPC Out permissions.  By default SQL sets those to false.  As you can see below though, they are set to true and again when run manually, it was working just fine.

I started investigating further and then I thought maybe it could be something else but that wasn’t it either.  I banged my head on my desk three times and then it hit me…..it was right there in front of my face.  Do you see it?  Look again?  It was the Query Timeout!  Someone set this to 337 in the linked server itself.  Normally by default this is set to 0.  I always keep it at 0 myself so I never thought to check it there.  Sometimes its just the silliest things that give us troubles, huh?  Funny thing was that there was no where on the internet where it was stated that this may be the cause.  It was just the fix for me, so if you are scowering the internet looking for a fix for the error above, and your using linked servers and can’t seem to find the issue…. just check the query timeout in the linked server itself.  It may be just the thing you were looking for. 

Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0×80040111

Since this was 2000, the mail must be configured to use a MAPI profile in order to send mail.  Because of this I decided I should take a look at the mail profile itself.  So I opened Outlook and took a look at the mail profile.  Everything seemed to be working fine for me.  That’s funny, it works for me, but when SQL fires it on one node it works, but on the other one it doesn’t.  What could be causing this to stop working?  I failed the cluster over and I took a look at the email profile over on the side where it worked.  Everything was the same on the email profile.  This got me thinking, that perhaps it is permissions.  So I looked at the SQL Server service account and compared that to the other node.  It turns out that they were using two different accounts for the SQL Server service.   When I logged in as the service account where the errors were occuring and I opened up Outlook, I got the error stating that the exchange server was unavailable.  This confirmed that is must have due to the login credentials of the SQL Server service account.  I changed the service account to use the same service account that worked on the other node and all the email alerts were back to normal again.  So, if you ever run into an  0×80040111 error with xp_sendmail, we should make sure that the sql server service account that is running has access to the mail account used in the MAPI.  Once that is set and the account can freely send and recieve mail, then everything should work beautifully.

UPDATE:

I got a great tip in an email from Tim Edwards BLOG|Twitter so I am copying it in here now:

“Unfortunately, because the MAPI protocol isn’t cluster aware, SQL Mail is not fully supported by Microsoft on Windows clusters (see http://support.microsoft.com/kb/298723/EN-US/).  If you have an SMTP server available, the preferable method in that situation would be to go send your alerts out via SMTP (good article from SQLServerCentral.com on how to accomplish that here: http://www.sqlservercentral.com/scripts/Miscellaneous/30510/).  The only downside is that this means you have to create an extra job step for notification since SMTP is not native in SQL Server 2000 and thus is not integrated with the SQL Agent notifications and alerts.”