|
|
|
|
|
|
|
|
|
|
|
|
| Greg Wilson's thoughts from the trenches of TSQL and C# development |
5/5/2008Here are the downloadable demos and slides from the "Becoming the SQL Guy" presentation that I did at the Iowa Code Camp. It was a great session, and I had a great audience. I will be doing a lot of updating to this blog in the next few days, so stay tuned!
Downloadable .Zip File 1/21/2008Looks like my spring will be fairly busy speaking at various events. In just a few weeks (February 6th), I am heading to Omaha to speak to the Omaha SQL Users' Group. In March, I'm going to be doing (and recording) an on-line presentation for the programmer community at Arstechnica.com. Then, April 5th I will be in speaking at the Twin Cities Code Camp. Then, of course, I'll be speaking at the Iowa Code Camp on May 3rd. It will be a busy spring.
My plan for the spring is to develop the presentation that I've always wanted to give: "How to become 'The SQL Guy' in your organization." It is a definite departure from the typical "look at me and how much advanced stuff I know" type presentation that I tend to see at conferences. I've heard lots of feedback from attendees, especially on SQL topics that they see too much fancy stuff that they won't get to use in their daily jobs, and not enough of what they need every day. My goal is to create the presentation for the average developer who learned a little bit about SQL in college or while learning .NET, but never really advanced beyond the basics. I'm going to try to make it a very example/problem based presentation, where (when possible) I'll take something you might expect to see a novice developer create and then "fix" the code with better TSQL and a better data access layer. I think it should be a lot of fun, and I'm hoping it is something that developers can use right away when they get back to their offices on Monday.
I'm crossing my fingers that I will be able to re-use big chunks from presentation to presentation, and avoid my personal propensity to keep rewriting my presentations and adding new content. 1/11/2008After scratching around the idea of an Iowa Code camp since last October, we finally are moving forward with a plan. We (the DSM .NET community) and the Cedar Rapids community are going to team up to do some code camps. We are planning the first one on May 3rd at the University of Iowa's new conference center. We are looking at 4 tracks of 5 sessions each. Chris Sutton is handling the facilities and logistic, Javier Lazano is handling sponsorship and acquiring free lunch for the attendees, and I will be in charge of speakers. If you are interested in speaking please let me know ASAP. (Note: In the first 72 hours of planning this, we already have about 50% of the needed speakers.)
I will be keeping this post updated with the most recent information on the event. 1/10/2008I realized it has been a while since my last post, but I figured it would be a great time to get back to adding some new content. Last week I wrapped up an 18+ month contract with Wells Fargo and on Monday I am starting a new full time position with Ontuet doing some pretty interesting work with the supply chain of the hardware industry. The workplace looks pretty interesting and I'm looking forward to working with them on their upcoming projects. They are small Microsoft shop with a pretty tight-knit group of employees and a casual environment. I've got several other posts that I will probably get up in the next few days. I've got some cool info on some local code camps and other stuff that I've been working on. 10/19/2007Thanks to all the 200+ people who attended my breakout session at the HDC. This has been a really great conference, and hope everybody enjoyed it. All the code and the slides can be downloaded here. (Topic: Expanding your development environment with SQL 2005)
I'll post more later, but I''m watching a great talk by Scott Guthrie about ASP.NET 2008, and I'm going to get back to listening to him. 10/16/2007Just a quick post while I'm hammering out the last details to my HDC presentation: I learned something new about service broker that I thought should get posted. Admittedly, it probably will not ever be useful in a production environment, but it may certainly help out some presenter somewhere. If you use service broker on a SQL server that uses Windows authentication, and you are on a domain machine that is NOT currently connected to the network, service broker will send all messages to the Transmission queue (the undeliverable queue) until you reconnect to the network. Quite obviously, production machines are not likely to need to disconnect from the network and be unable to communicate with the domain controller. But, laptops that you plan on using for demos at conferences do. And that has led me to panic for the last couple hours to determine why my demo that worked so pristinely for so many test runs suddenly hurls when I took my laptop off-network to practice my demo.
So, for the record, SQL service broker cannot deliver messages if it is using Windows authentication and cannot connect to the domain controller.
Once HDC is over, I'll get back to writing Part 2 of CTEs and performance, but I thought it would be useful post this for any other poor presenters who might be struggling with the same issue. 9/18/2007When I first read about Common Table Expressions in SQL 2005, I saw mentioned that they could increase performance, because the query optimizer could take advantage of existing indexes while a SQL 2000 style temp table could not. For the first several months I used CTEs, I never really investigated this idea. I just assumed that since CTEs were new, they would be faster. (Pretty bad assumption on my part.) After spending all the time that I have on optimizing my production database, I've found that CTEs generally change performance anywhere from a 90+% increase in speed to a order of magnitude decrease in speed.
First off, I did find some situations you could just about guarantee a healthy speed increase: recursive queries. The application I have been optimizing has a lot of tree structured data. I had several heavily called user defined functions and stored procedures that were originally written in SQL 2000 with while loops and temp tables. Moving them over to recursive queries showed consistent performance increases, from 60-90% increase in speed.
Beyond that, CTEs were hit and miss as far as performance goes. More on that in Part 2 9/15/2007
Just thought I'd make sure to publicize the fact that, once again, the Iowa State Cyclones (who came in as 17.5 point underdogs) defeated an over-confident squad of Iowa Haweyes 15-13. Watching the game, the game was not nearly as close as the score indicated. The Cyclones dominated most every statistic, and the Hawkeye linemen were getting pushed all over the field at the whim of the Cyclones. More details here.
A Hawkeye friend of mine said, "But next year we'll beat you for sure!" To which I replied, "If you do, and you beat us again the next year, and the next year, and the next year after that, the Hawkeyes could get back to 500 since '97!" 'Nuff said.
Not SQL related, but I couldn't help it. 9/11/2007I've had a lot of SQL thoughts over the last few weeks that I thought I ought to get written down. I've been working on a large client server application that was going through a hardware upgrade. The performance of the application before the upgrade was a tiny bit subpar and the company had made a huge financial commitment to shore up the application. There was one unforeseen issue: The company policies dictated that the new SQL server 2005 cluster would have its production environment in its Minneapolis and its Test cluster in Arizona, instead of having the database local to the user base. We quickly discovered in the test environment that the performance hit of the added latency of having the server clusters in other cities dwarfed the performance increase of having a faster dedicated server cluster over our old shared, 4-CPU single box locally.
So, the heat was on. The execs had spent into the 7 figures replacing hardware and paying the server groups to install and configure them, and the net result was a much slower configuration. Suddenly, all our the SQL optimizations that we had always thought about doing, but never had time to do were top priority. I went to work for several weeks trying every trick I've learned about SQL 2005 and testing for improvements. To my surprise, while many of the new tricks increased performance, other did nothing for performance, and sometime radically slowed down queries. For the next couple of weeks, I am going to try to discuss as many of the main points that I found out in a series of blog posts.
As a quick teaser, I found at least one SQL 2005 technique that was just a performance abomination: CROSS APPLYs. In four separate instances I took queries that were running in the 10-20 second range using cursors to perform the operation of applying a function result row by row. In all four of them, the performance tanked into the 1-5 minute range. I have no idea how these beasts can be as harmful to performance as they proved to be. But, sadly, it looks like that if performance is important, we will be leaving CROSS APPLYs in the classroom, and will continue to write SQL 2000-style cursors and temp tables to apply the function results row by row. 7/9/2007By popular request, I'm going to post my mnemonic device I routinely use when I work with Service Broker. Until a few people pointed it out to me, I didn't realize that I always write it on the whiteboard when I give presentations, and therefore it isn't in any of my downloadable presentations. So here it is:
THE QUESTION: With so many various components needed to make a service broker implementation function, how do you remember all of the objects that you need to setup?
HOW TO REMEMBER: Imagine you are a 20-something bachelor (This shouldn't be a stretch for many of us. . . .) One day, the unthinkable happens: You get a date! Beyond that, she agrees to a second date, and a third! You find that you are suddenly officially "dating." And then the miraculous event happens: You walk into your (normally filthy) computer room, and she has cleaned it for you!!! So Quickly She Cleans My Computer Room Regularly!
THE ANSWER:
- Server: Make certain that the server has service broker enabled on the database. Although it is enabled by default, it is fairly common to see DBAs and system administrators who have followed various internet security guides to turn off every single feature of the database. I've even downloaded various demo databases that have service broker turned off. To check that it is on, do a SELECT * FROM sys.databases and check the is_broker_enabled value and make sure it is set to 1. If not, execute an ALTER DATABASE database_name SET ENABLE_BROKER from the Master database. Make sure the database has no active connections when you execute the command.
- Queue: You need to create a queue object with a CREATE QUEUE command.
- Service: You need to make a service to deliver messages to the queue. A server always services a single queue, but theoretically you could have multiple services on a single queue. In practice, I've never seen a reason to make multiple services on a single queue. They are made with CREATE SERVICE statements.
- Conversations: Services talk to each other via conversations. They are created with CREATE DIALOG CONVERSATION commands, and ended with END CONVERSATION commands.
- Message Types: Conversations consist of messages. These messages are defined by Message Types that are created by CREATE MESSAGE TYPE commands.
- Contracts: The types of message types that can be used in a conversation is dictated by contracts. They are defined by CREATE CONTRACT
commands are tied to services when they are created. (You might have noticed that in the docs for CREATE SERVICE)
- Receivers: You need something to read the messages from the queue. It can be a windows service, a console app, a stored procedure, etc. Typically, it uses the RECEIVE command to read the messages on the queue.
- Routes: If you are using the service broker between databases, between instances, or between computers, you need to specify the routes for the messages to take. If you are going between databases, you simply need to do a CREATE ROUTE command on each database. If you are going between databases, don't forget to CREATE ENDPOINT
on each instance, and CREATE ROUTE in each instances MSDB database, because all incoming service broker messages are always received by the MSDB database and then routed to the appropriate database from the routing table in that database. And, of course, if you are going between machines, make sure that appropriate ports are opened in all involved firewalls.
Quite obviously, this little mnemonic device is not a comprehensive guide to service broker, but it has helped me with remembering what I need to set up tremendously. And, judging from the e-mails I've received from people I've given presentations to, it has helped some of you guys, too.
As you go through the check list, don't forget that for several new features (Event Notifications, SqlDependancy, etc.) several of the above are automatically taken care of for you, so you don't have to do much to set them up. |
|
|
|
|
|
|
|
 |
|
|
|
|