Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Informal Look at Database Performance


An Informal Look at Database Performance

Author
Message
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 920
Comments posted to this topic are about the item An Informal Look at Database Performance

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19117 Visits: 13250
Great article. It gives me a nice overview and a bunch of very useful links.
Now I just have to find the time to read them all Smile


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
mortenmaate
mortenmaate
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
Thanks a lot for the great overview !

One thing I would like to mention additionally: Look a the SQL Server version. In my experience many companies upgrade their SQL Server too rarely, and especially in the MS family of SQL Servers, great improvements in the product during the last 10 years can be an easy gain (like factor 10 improvements or even more in some scenarios). So get rid of that SQL Server 2000 bastard now! (Believe me: They are still out there).
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53467 Visits: 40382
mortenmaate (9/8/2010)
Thanks a lot for the great overview !

One thing I would like to mention additionally: Look a the SQL Server version. In my experience many companies upgrade their SQL Server too rarely, and especially in the MS family of SQL Servers, great improvements in the product during the last 10 years can be an easy gain (like factor 10 improvements or even more in some scenarios). So get rid of that SQL Server 2000 bastard now! (Believe me: They are still out there).



Heh... I love the older versions of SQL. That's were I learned all the tricks I know to make stuff work. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53467 Visits: 40382
Well done, Timothy. Definitely "brief case" time for this article. Thanks for taking the time to make it so complete.

The idea of having a baseline and the idea that most of the performance problems are in code are two very important subjects that I try to stress where some stress that hardware is the key. If you buy a server that's twice as fast, the best your code will do is run twice as fast. Tweek the code or rewrite it, and it can run literally 100's of times faster. Why is that important? Heh... look around this forum folks! It's filled with cries for help to make code faster or solve time-outs.

The other item that people seem to stress is that the server needs to be tuned. Just like buying the proper hardware, tuning the server properly is certainly important but most of the performance problems folks run across just can't be solved by additional tuning of the server. You have to fix the code and this article stresses that.

The same goes for indexing. Although you can get great gains from proper indexing (even some triangular joins can benefit), you can get even more from your indexes if your code is actually capable of using them. ;-)

"Tune the Code... that's where the performance is!" :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PFlorenzano-641896
PFlorenzano-641896
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 535
Nice job Timothy! Your article certainly covers all the bases, troubleshooting, monitoring, & performance tuning.

Taking snapshots of a server throughout the day (under normal conditions) is a great idea so that if performance were to degrade, there would be documented evidence to compare the performance degradation snapshot with the normal snapshot.
ianstirk
ianstirk
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 1037
Hi,

Nice article.

You can discover a lot more about DMVs in this forthcoming book www.manning.com/stirk. Chapter 1 can be downloaded for free and includes scripts for:

A simple monitor
Finding your slowest queries
Find your missing indexes
Identifying what SQL is running now
Quickly find a cached plan

Thanks
Ian
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: Administrators
Points: 41386 Visits: 18876
Great job, and a very nice read.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 920
mortenmaate (9/8/2010)
Thanks a lot for the great overview !

One thing I would like to mention additionally: Look a the SQL Server version. In my experience many companies upgrade their SQL Server too rarely, and especially in the MS family of SQL Servers, great improvements in the product during the last 10 years can be an easy gain (like factor 10 improvements or even more in some scenarios). So get rid of that SQL Server 2000 bastard now! (Believe me: They are still out there).



That is a very good point to add.

I know from direct testing that the optimizer works much better in 2005 then it did in 2000. I have not tested the query performance going from 2005 to 2008 or R2, but I would be shocked if it got worse.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1236 Visits: 2698
Nice overview; I'd like to encourage the addition of looking at SQL Profiler CPU, Reads, Writes, and Duration columns on SQL:BatchCompleted events.

Not only do these show aggregates, if you do this on a regular basis, you'll get a good, trained idea of when a query is using "too much" of any of them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search