Expansion Diary Part 3

Tue Jan 27, 2004 by Travis Laborde

I think someone could make it their whole job to work with SQL Server. Grin. OK, I know many people actually do :) But for my company, I do it when I'm not doing other stuff. I guess that sort of tells you how much room for optimization we probably have.

I've been pushing hard to get us focused on one simple fact: Doubling the user load and increasing the data being worked with by a staggering factor (from 200 new records per day to 10,000) within the next six months means that the system will probably DIE :) We need to prepare, plan, purchase, optimize, etc. Of those "choices" believe it or not my company would prefer to "purchase." If just buying a "big bad mother-of-all-servers" machine would do the trick, they would probably go for it. But, you know, I want to actually *learn* this stuff :)

So, I've managed to get approved a huge thing: We're signing up for some in-house training with Solid Quality Learning. In case you don't know them, this is a consultancy consisting of some of the "big names" in the SQL Server training world. These guys "write the books" and "do the talks" etc. Go to their website at http://www.solidqualitylearning.com/ to see more about them. Anyway, we're working on getting someone out to our site!

Instead of going and taking a class, I'll have someone right here looking at my own database, in my own environment. I'm sure it will be as embarrassing as it is productive :) I can't wait!

In the meantime, I've been working my way through the book I mentioned in an earlier post. () What a great book. I'm working my way through learning the Profiler, and then to be able to actually optimize a query once I know which one to optimize, etc. Basically, so far I run the Profiler for an hour or so, saving the trace to a database table. Then I analyze the output by looking at the top 5 hitting queries in these categories: CPU, Reads, Duration, and Frequency. Then I look at any queries that are in the top 5 of all four categories. Then I mess with those queries on my development machine until I think I have something good. Like McDonald's says "I'm lovin it!"

Legacy Comments


Mike Swaim 2004-01-27	re: Expansion Diary Part 3 What's your hardware like? Are you using the same machine as a web server and database server? Are you using RAID, or striping your disks? Is your log on the same logical disk as your database file(s)? All of that can impact performance. There's also a question of what else is going on in the database, such as triggers. I had a process that would write ~500,000 records in blocks of 10,000. Rewriting the audit trigger on the table noticably sped things up. Also, when you mention 10,000 records, is that just rows in a database, or something more complicated? (For example, in some systems that I've worked on, one transaction might result in 20 inserts into the database.) Depending on what else is going on, 10,000 records shouldn't be too bad. Especially if you can buy recent hardware for it. Mike

Damian 2004-01-27	re: Expansion Diary Part 3 You know, Graz also does training. He just doesn't do a good job of advertising it on his site http://cleardata.biz/ Maybe have a chat to him Damian

Travis Laborde 2004-01-27	re: Expansion Diary Part 3 Well Mike, for a 2gig database, with 60 users hitting steadily throughout the day, our hardware is like this: We basically have two close to identical servers. Dual Xeon, 4gig RAM, scsi drives. We are not separating out our database, log, tempdb, etc onto separate disks. We are working on paring down our triggers, and in general making our app "smarter." It seems to me that the biggest part of our problem is a thing we call "Work Queues." I'll give a quick description here, because I think an entire post could be dedicated to these monsters :) A work queue is a list of accounts, or people, or helpdesk items, etc. We have tons of defined "sets" of data. Things like "Leads Not Contacted In The Last 7 Days." We have to have these nice "menu" screens where a rep can go in and see all of his queues, with two numbers next to each: Total Count, and # Needing Work Today. Then, clicking on one of those numbers brings you to a framework which allows listing, or paging through from one record to another, etc. We're really going to have to work hard to make these things more efficient.

AjarnMark 2004-01-28	re: Expansion Diary Part 3 The Solid Quality Learning folks certainly do have some impressive names there. I remember it being introduced a year ago at PASS (2002), which I think is when they were forming the company. They've done some good marketing moves with their company that I am planning to talk about in upcoming blogs. I'd be interested to hear about your experience with them when it's over.

Mike Swaim 2004-01-28	re: Expansion Diary Part 3 OK. Here's what I'd probably do. (Assuming you have the time/money.) The log, tempdb, and database should be on seperate disks. (I'm not sure how much this'll matter, since your RAM's larger than your DB.) The DB and web server should be seperate machines (and if the web server's the bottleneck, you can add more boxes relatively easily). You should be using paremeterized SQL/stored procedures everywhere. (I used to consider this obvious, but I don't anymore.) If you're doing a lot of work on the client or in triggers, you might consider offloading it onto a daemon. Typically the client/trigger puts an entry into a queue table that the daemon monitors. It then does the appropriate things to generate the update. All queries should use indexes. If you have stored procedures that are taking a noticable amount of time, look at what they're doing. You might be able to speed things up using temp tables, or processing the data that you're interested in as a set, rather than on a row by row basis. That's all that I can think of at the top of my head.