SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy A Wiseman / An Informal Look at Database Performance / Latest Posts

RE: An Informal Look at Database Performance

SQLRNNR — Fri, 10 Sep 2010 23:35:04 GMT

Nice article. Thanks Tim.

RE: An Informal Look at Database Performance

TheSQLGuru — Wed, 08 Sep 2010 19:33:18 GMT

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.

RE: An Informal Look at Database Performance

TheSQLGuru — Wed, 08 Sep 2010 19:31:28 GMT

[quote][b]Nadrek (9/8/2010)[/b][hr]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.[/quote]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.

RE: An Informal Look at Database Performance

Nadrek — Wed, 08 Sep 2010 12:29:52 GMT

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.

RE: An Informal Look at Database Performance

timothyawiseman — Wed, 08 Sep 2010 09:39:24 GMT

[quote][b]mortenmaate (9/8/2010)[/b][hr]Thanks a lot for the great overview !One thing I would like to mention additionally: [b]Look a the SQL Server version[/b]. 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 [u]are[/u] still out there).[/quote]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.

RE: An Informal Look at Database Performance

Steve Jones - SSC Editor — Wed, 08 Sep 2010 09:31:45 GMT

Great job, and a very nice read.

RE: An Informal Look at Database Performance

ianstirk — Wed, 08 Sep 2010 08:03:19 GMT

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 monitorFinding your slowest queriesFind your missing indexesIdentifying what SQL is running nowQuickly find a cached planThanksIan

RE: An Informal Look at Database Performance

PFlorenzano-641896 — Wed, 08 Sep 2010 07:52:45 GMT

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.

RE: An Informal Look at Database Performance

Jeff Moden — Wed, 08 Sep 2010 06:45:15 GMT

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. ;-)[i][font="Arial Black"]"Tune the Code... that's where the performance is!" [/font][/i]:-)

RE: An Informal Look at Database Performance

Jeff Moden — Wed, 08 Sep 2010 06:32:36 GMT

[quote][b]mortenmaate (9/8/2010)[/b][hr]Thanks a lot for the great overview !One thing I would like to mention additionally: [b]Look a the SQL Server version[/b]. 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 [u]are[/u] still out there).[/quote]Heh... I love the older versions of SQL. That's were I learned all the tricks I know to make stuff work. :-P

RE: An Informal Look at Database Performance

mortenmaate — Wed, 08 Sep 2010 01:23:01 GMT

Thanks a lot for the great overview !One thing I would like to mention additionally: [b]Look a the SQL Server version[/b]. 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 [u]are[/u] still out there).

RE: An Informal Look at Database Performance

Koen Verbeeck — Wed, 08 Sep 2010 00:09:21 GMT

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 :)

An Informal Look at Database Performance

timothyawiseman — Tue, 07 Sep 2010 23:40:03 GMT

Comments posted to this topic are about the item [B]An Informal Look at Database Performance[/B]