SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4134 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
SSC Guru
SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)

Group: General Forum Members
Points: 124423 Visits: 13344
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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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 (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430555 Visits: 43458
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 (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430555 Visits: 43458
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
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 602
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-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 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 Guru
SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)

Group: Administrators
Points: 281017 Visits: 19904
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4134 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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8908 Visits: 2741
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