Wednesday, April 29, 2009

SQL Saturday Atlanta Recap

I had a great time at SQL Saturday Atlanta last weekend. I drove from Orlando to Atlanta on Friday with Andy Warren and took advantage of the long ride to talk about PASS, user groups, volunteering, blogging, networking etc. Andy's a great guy to talk shop with because of the experience and perspective that he has on things. We arrived in time for the speaker's dinner Friday night where we had the chance to meet up with Stuart Ainsworth (@stuarta), Robert Cain (@arcanecode), Louis Davidson (@drsql), Geoff Hiten (@sqlcraftsman), Paul Waters (@pswaters), Joe Celko, and many others. After dinner we headed to another local spot for drinks with Peter Shire (@peter_shire) and the SQL Sentry crew to finish out the day.

Saturday's event was held at Microsoft in Alpharetta, GA. I thought the facility was perfect for the event: Plenty of parking right outside the doors, open spaces to mingle, a comfortable speaker's room, and rooms set up with projectors, lighting, and hardware designed for presentations. There were 3 smaller rooms for roundtable discussions and 3 larger rooms with walls that retracted to form one large room that was used for the opening comments, lunchtime presentation by Joe Celko, and closing raffles.

I spent most of my morning in the speaker's room preparing for two afternoon presentations and had the chance to meet up with K. Brian Kelley (@kbriankelley) and Kevin Kline (@kekline). I had a bit of a hiccup before my first session (XML in SQL Server) when my user profile on my laptop went corrupt and I couldn't log in. I lost about 10 minutes recovering but I still managed to cover the most important points. Fortunately my second session (Disk Performance) was all slides and I didn't need to jump through any hoops so it went better. I finished out the day by meeting up with Stuart and a couple of other attendees for dinner at an ale house a short distance from the event.

Other random thoughts about the weekend:

IMG_0208

  • Stuart and the AtlantaMDF crew did a fantastic job putting on the event. I've been to several SQL Saturdays and this ranks near the top in terms of location and execution.
  • I bought and used a Logitech 2.5GHz Cordless Presenter based on Steve Jones's recommendation and really liked it. The built in timer and laser pointer are very handy. The forward\back buttons are great if your presentation is mostly slides but otherwise useless if you're doing a lot of demos because you tend to be behind a keyboard pushing buttons already.
  • SQLBatman's influence was ever-present (see picture on right)
  • I need to work on my networking skills. While I managed to meet up with several people I've talked to on Twitter I didn't do such a good job of meeting someone I've never talked to before.
  • It's a long drive from ORL to ATL. While the conversation with Andy was great, I'll probably consider flying next time.
  • Presentation tip: learn how to keep an audience captive in the event that something goes horribly wrong with your presentation. I did my best to interact with people while I worked out my corrupt user profile issue but it would have helped to have some discussion points in mind ahead of time.

3 days, 1,000+ miles, 20+ hours of driving, and good times: All in all a great weekend. Next up: SQL Saturday #15 in Jacksonville on May 2 where I'll be presenting all 4 topics I submitted for the 2009 PASS Summit.

Slide decks and demos for my presentations from ATL are below.

Download: Disk Performance and Configuration How & Why.pptx
Download: XML Features in SQL2005.zip

Thursday, April 23, 2009

PASS Summit Sessions Submitted

It's official – I've submitted my abstracts for the 2009 PASS Summit and received my confirmation. Now I wait to see if the abstract selection committee decides if I am worthy. As I promised in my interview with Brent Ozar, I'm publishing my submissions for all to see. If you happen to be at SQL Saturday Atlanta this weekend you can catch my presentations on Disk Performance and XML. If you're headed to SQL Saturday Jacksonville on May 2 I'm on the schedule to present all four.


Disk Performance and Configuration: How & Why

Would you buy a million dollar race car then show up to the track on a clear day with rain tires that aren't properly inflated and wonder why you're only doing 180 when the manufacturer says it can do 220? Let's suppose that your million dollar race car is really your DB server and your tires are your hard drives. Do you know the right configuration to use to get the best performance out of them? This session will examine the results of extensive testing to reveal how partition offset, RAID stripe size, allocation unit size, controller caching, and failed disks affect OLTP performance on local disks and disk arrays.

Session Goals:

  • Identify optimal RAID stripe size, allocation unit size, and partition offset to use for OLTP data and log files
  • Understand how failed drives impact OLTP performance
  • Understand how controller caching impacts OLTP performance


<title>Working With XML in SQL Server</title>
XML in SQL 2000 wasn't very exciting and it was easy to dismiss it altogether by just relying on developers to handle any XML needed within the application. SQL 2005 changes the game and this session will show you how to use the XML data type, do basic operations on XML data, and showcase the changes to FOR XML that make it so much more useful. This isn't a session to convince you to use XML, but to show you what you can do with XML if you do need to use it!

Session Goals:

  • Learn how to use the FOR XML PATH and WITH XMLNAMESPACES clauses
  • Understand the use of the XML data type, XML schema collections, how to use XML DML to manipulate XML data, and how to use FLWOR to query XML data
  • Learn how to create indexes on XML data


Transactional Replication: Beyond The Basics
At some point in every DBA's career they'll be probably be asked to work with transactional replication but most DBAs don't look under the covers once they're done walking through the setup wizards. In this session we'll take a deeper look at setting up, monitoring, and calibrating transactional publications plus share some tricks and tips gleaned from years of experience working with high volume, multiple datacenter topologies.

Session Goals:

  • Understand core concepts of transactional replication including: replication topologies, publishers, distributors, agents, & agent profiles. Learn how to configure advanced options such as vertical and horizontal filtering
  • Demonstrate how to monitor transactional replication performance
  • Learn advanced troubleshooting techniques to apply when problems occur


Configuring Secure SQL Access for the Web Developer\Administrator
By now we all know that we should use Windows Authentication to connect to SQL Server from our IIS applications...but most of us don't know how. This session aims to clear up the confusion by demonstrating the ways that ASP & ASP.NET applications can be configured to make secure connections from different versions of IIS so that we can keep our connections secure and our DBAs happy.

Session Goals:

  • Understand the concept of impersonation
  • Learn how ASP and ASP.NET applications can be configured to use impersonation to make secure connections to SQL Server
  • Learn how to configure IIS 5, 6, & 7 and Windows XP, 2003, and 2008 to support impersonation

Tuesday, April 21, 2009

Register A Server More Than Once In SSMS

In yesterday's post about using SQL 2008 Express as a Central Management Server I mentioned that you can't register a server more than once in Object Explorer. Technically that's correct, but I meant to say Registered Servers. It turns out that you can register a server more than once in Registered Servers - the catch is that you have to add the server to different groups when you register it. Thanks to Mladen Prajdić (@MladenPrajdic on Twitter) for pointing out my mistake.

Monday, April 20, 2009

Use SQL 2008 Express As A Central Management Server

I had the chance to catch Brent Ozar's webcast last week on creating a poor man's CMDB. In it he talked at length about the Central Management Server feature in SQL 2008. While I use SSMS 2008 my only SQL 2008 database installs so far are running inside Virtual PCs that aren't always on. Fortunately, there is another option – SQL 2008 Express. It's lightweight, free, and best of all it can be used as a CMS. The only catch is that if you put your install on anything other than your local machine (which you're going to do since you're using it as a CMS, otherwise what's the point?) you'll have to enable remote connections. No worries, it's actually an easy task to do – just follow these step by step instructions and you'll be all set.

Update – It didn't take me long after I posted this to realize that there are cases for running SQL Express locally in order to take advantage of a CMS. One example is if you frequently run multi-server queries and you want to create logical groups of servers where a server belongs to more than one group. Another is if you're a one person shop and you want to use Brent's CMDB scripts to gather data. That makes me wonder, though – why didn't Microsoft just add the ability to register a server in more than one group into Object Explorer in the first place? Hmm, I smell a Connect request cooking!

Update 2 – OK I am a dolt. In my first update I meant to say Registered Servers, not Object Explorer. I've posted a correction here.

Wednesday, April 15, 2009

Full Text Catalog Scripting "Gotcha" in SSMS 2008

With every new version of SQL Server comes a set of deprecated features. Usually Microsoft gives fair warning that these features will be removed in a future version of SQL Server but they continue to work for the time being. In some cases, though, arguments for statements are left in place but have no effect on the current version of SQL Server. For example, take a look at the syntax for CREATE FULLTEXT CATALOG in SQL 2008. It's clearly stated that the ON FILEGROUP and IN PATH clauses have no effect in SQL 2008.

I use SSMS 2008 to manage my servers so that I can take advantage of the UI improvements that it offers. A common task I perform is to script objects from Object Explorer; since most of my production servers are running SQL 2005 I set my scripting options to target 2005 (Tools –> Options –> SQL Server Object Explorer –> Scripting). I haven't run into any problems with this until today. Even though my target is SQL 2005, SSMS 2008 is ignoring the ON FILEGROUP and IN PATH clauses when scripting out a full text index since they don't matter in 2008.

Here's what my scripted index looks like when I use SSMS 2008:

CREATE FULLTEXT CATALOG [Example Search Catalog]
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION
[dbo]
GO

And here's what the index looks like when I use SSMS 2005:

CREATE FULLTEXT CATALOG [Example Search Catalog]
IN PATH N'E:\FTData'
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION
[dbo]
GO

The same behavior occurs if you use the Scripting Wizard as well. As a general practice I do not run auto generated scripts without reviewing them first, and in this case I'm glad I did because my catalog wouldn't have been created where I wanted it to. Fair warning - there are probably more of these "gotchas" in SSMS 2008. If I find any more I will post them.

Wednesday, April 1, 2009

Undocumented Trace Flag Improves Performance By Up To 25%

As a production DBA I love to tweak things to optimal perfection. After all, if I paid for the hardware and software shouldn't I get the best possible performance out of it? In that vein, I've shifted my focus in the last few weeks from disk performance to tweaking SQL by playing with trace flags. While some trace flags can let you do some pretty ridiculous things (e.g. trace flag 1807 – do you really want to create a database on a UNC share?), there are others which are quite useful and can improve performance in certain conditions.

There also happens to be a number of undocumented trace flags. I Googled to find as many as I could but I was unable to get a comprehensive list of all of them so I decided to do some testing and create a list of my own. I had a server handy and a trace of a few thousand commands that had been run on one of our more heavily used production servers. I used the following SQL to create a list of startup commands:

SELECT 'DBCC TRACEON (' + convert(varchar(10), number) + ')' AS [TraceCmd]
FROM MASTER.dbo.spt_values
GROUP BY number
ORDER BY number

Then I tested each option out by turning on the trace flag and replaying the trace from my production server. I had another trace running to keep track of the total reads, writes, CPU activity, and duration of the replay. As expected, I hit on some already documented undocumented trace options, but surprisingly I came across one trace flag that improved my overall performance by 25%. I was so astonished that I restarted the server and re-ran the test to make sure it wasn't a fluke; sure enough, I was able to reproduce my results.

Here's the trace flag that I found:

DBCC TRACEON(040109)

I haven't figured out exactly what the flag causes the query engine to do differently, but whatever it is I'm not going to argue with the free performance I'm seeing. I'm so stoked about finding this that I'm going to start rolling it out to our production boxes ASAP. I would normally give our my standard disclaimer here and advise that you test this out in a non-production environment first, but this is so huge that you might want to consider going straight to prod with it. And by the way, Happy April Fool's Day! :-P