SQL Saturdays...

Looks like I'm going to be speaking at SQL Satuirday in Tampa in only a few weeks; then in Jacksonville a couple of months later; and possibly, in San Juan in between. (Also thinking about Manhattan in August... )

Be sure to come over & say "Hi!"

(Register here:  www.sqlsaturday.com )

performance?

A recent forum post pushed enough of my buttons that I chose to respond, and the topics themselves were interesting, so I'm copying the post here.

 

1) Science vs. Art

People frequently define tuning as an art rather than a science, but what is scientific method? You postulate a theory, test it, and repostulate if necessary. That's what we do when we tune.

 

2) What's a valid baseline?

I remember shocking a group of about 100 recent IVY grads at a talk I did a while back when I stated that business is not like a college exam; there is no "right" or "wrong," there is instead "the solution works" or "the solution doesn't work" ... there may be multiple correct (i.e. working) answers.

In this case, you need to be able to answer questions along the lines of "Are all of the potential bottlenecks on our system wide enough to accommodate the current expected usage and identified expected growth?" If the answer is yes, you're in good shape. If you're looking for harder numbers, my personal preference is not to exceed 70% of resources at normal peak demand; if you are, then you've got room for unexpected peaks; if you're above 80%, you need to tune or shop. (this is a VERY general rule and needs to be applied with knowledge of your system of course.)

If you can't answer these questions you need to reexamine your work so that you can do predictive analysis

3) Where do we start tuning?

About 6-7 years ago, after doing little but tuning for 15 years, I've changed my approach. Before interviewing users about their issues, or looking at perfmon which gives us a snapshot view of a very tight time slice, I install a tool (the one I currently use is Confio Ignite -- subjectivity warning, here, as we resell this tool, but I examined a dozen others before choosing Ignite). After the tool starts collecting, I interview the users about what they think their problems are, and subsequently start looking at what the tool is collecting, starting at a macro scale and moving from there to the micro scale based upon findings at a higher level; this may lead me to anything from disk issues to specific queries run by specific users at specific times of the day.

 

Shrinking the transaction log

Every once in a while I look something up & want to keep referencing it... this article is one of those, I hope you find it as useful as I did:

http://www.mssqltips.com/sqlservertip/2097/how-to-shrink-the-transaction-log-file-in-sql-server-2008/

Transaction Log Space Utilization

A client asked me to investigate log space utilization on a specific database . It was  currently allocated at 53 gig, and the question was whether that was big enough or too big. I gave a pretty generic answer, which is that it needs to be big enough to contain all active transactions between dumps & during replication, plus a fudge factor. There’s a GREAT article here:

 

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

it also shows some loosely documented /undocumented commands to take a look.

At the time, they were using .22% (Yep, less than 1%) of the 53 gig transaction log. It’s impossible for me to tell how big it needs to be (without a deep understanding of the application, but a potential guideline is 25% bigger than the biggest transaction log backup you have; so, if those are in the 1-2 gig range, they had a lot of room to spare here.

Note that with SQL 2005 (their version), you can not shrink the log file below its initial size (this is corrected in SQL 2008 & later).

SQL Saturday

My most common response to Orlando's SQL Saturday "What could the speaker do differently to improve?" was "Give him more time!"

Thank you all who attended, it was a pleasure meeting you in person. I'll be doing the SAME seminar in Nashville in 2 weeks... check out www.sqslsaturday.com and register!

Along these lines, we do webinars about monthly, send me an email if you'd like to be added to the email list. In Orlando, a gentleman came up to me & claimed my webinar changed his life... your mileage WILL vary, but there's always lots of great information.

Thank you all again for coming out to see us!

Jeff

SQL Saturday 151 in Orlando

I'm speaking this Saturday, September 29, 2012, please be sure to come over & say "Hi" in our booth... register at www.sqlsaturday.com

Speaking at PASS DBA Virtual chapter on Wednesday!

                Registration: https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=mqpxrl7f7x8cxgvm

                Meeting: https://www.livemeeting.com/cc/8000181573/join?id=FBT42K&role=attend

 

Registration is not required to attend the meeting but if people want to be included in the drawing for a $50 Amazon Gift Card, they must register no later than 5:00 PM Eastern on Tuesday, September 25^th.