An Informal Look at Database Performance

Question

Post reply

An Informal Look at Database Performance

TimothyAWiseman

SSCrazy Eights

Points: 8819
More actions
September 7, 2010 at 11:40 pm

#223646

Comments posted to this topic are about the item An Informal Look at Database Performance
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mortenmaate SSC Rookie Points: 39 More actions · Answer 2

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 SSC Guru Points: 1003857 More actions · Answer 3

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. 😛

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

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. 😉

[font="Arial Black"]"Tune the Code... that's where the performance is!" [/font]🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 5

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 Ten Centuries Points: 1310 More actions · Answer 6

Hi,

Nice article.

You can discover a lot more about DMVs in this forthcoming book http://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 - SSC Editor SSC Guru Points: 734434 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 734434

September 8, 2010 at 9:31 am

#1218044

Great job, and a very nice read.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

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 SSC-Insane Points: 20039 More actions · Answer 9

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Nadrek (9/8/2010)
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.

This is VERY helpful for anyone who has UDFs in their system too, since it is the ONLY way you can see how bad they really are.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

I will add as a minor point that non-clustered index fragmentation (at least the extent kind) usually doesn't matter that much since the seeks tend to be random in nature anyway.

Nice coverage of many of the things to look out for.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Nice article. Thanks Tim.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events