Day 3 of Devconnections for me started with Brian Kelley’s Knowing what to look for in SQL Server security.  Brian started by describing the different types of attacks in terms of stealing data, manipulating website or applications, and also by creating a Launchpad for other attacks.   He went over SQL injection, listed many informative hacking websites and provided ways to prevent it from occurring.  Some of his recommendations were to use code review, using a red team (team of hired hackers), and by using various tools too.  He went over a lot of the tools used by hackers and how we can learn more about them, so we can be better prepared to defend against them.  This was a very interesting session and I wish we had more time to dive deeper.  All in all Brian is an expert on security and he helped gleam a boat load of information to help us secure our servers a lot better.

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.

After such a great Day 1, and after meeting two of the worlds certified database masters, I was very eager to jump right in and learn more.  The first session I went to was Locking and Blocking made simple with Joe Webb.  Joe started the session explaining the processing of deadlocks, which is when two processes each have shared locks on an object and are then both stuck waiting indefinitely trying to escalate their locks.  He then went over some DMV’s to investigate locks and proceeded by describing the tipping point for escalation.  Joe discussed many of the basics of locking, why blocking occurs and some mechanisms he uses to diagnose them when issues arise. 

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.

I had someone ask me today how do you edit records in SSMS like you could in Enterprise Manager?  It’s pretty easy by right clicking the table and editing the rows.  The question the user had though, was what do you do when the table contains more then 200 rows?  It may not be so obvious to us all, so I thought it would be a good little tidbit that could come in handy someday. 

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. 

I know it’s been a while since I posted anything in my blog so I thought I would put something up today that gave me a little more trouble today then I thought it would.   There wasn’t much help on the internet for it either so perhaps this post will help others that have the same issue as I did today.  I was investigating a SQL Agent job that simply executed a stored procedure on a remote server via a linked server call.  The job was extremely sporadic.  It was running fine for weeks and then other times it would fail every day.  The query in the job when run manually in SSMS executes just fine in about 5 minutes.  The only consistent thing I was seeing was that it would only fail if it was running over 5 minutes and 37 seconds.  The error I was getting in the job failure was as follows:

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. 

I ran into this error today while diagnosing a sql alert that was sending mail utilizing xp_sendmail on a clustered SQL Server 2000 machine.  The funny thing was that I was told that when SQL gets failed over to the other node it worked fine.  That seemed really strange to me…so I decided to investigate to see if I could get it fixed once and for all.  Sure enough when I tried to send a test xp_sendmail message I got the following error:

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.”

If your reading this error, your probably experiencing what I did today when trying to run an SSIS package on a 64 bit machine that has a connection manager to Excel.  It seems that the Excel, and Access Jets have issues communicating with SQL Server 2008.  There is a work around fortunately and it involves running the package using DTExecute via the X86 Program Files Directory.  The way I handled it was to create a batch file to run the package similar to the below script and save it as a BAT file:

“D:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe” /f “D:Test.dtsx” /X86

Another way to do it is schedule it to run as a job and then check the Use 32 bit Runtime checkbox in the execution options of the job like below: