x

memory or CPU constrained database/servers?

I tried to find an answer on google for this one but decided to ask the community for a good answer. I would be grateful if someone could tell me or point me to and article on the net.

If you have worked on an application and are satisfied that the correct indexes are in place, stats are up to date partitioning has been applied if necessary.

What definitive diagnostic tests can be applied to deteremine if a database is slow because the cpu is too slow or you need to add more memory to the system?
more ▼

asked Sep 03 '11 at 03:19 AM in Default

half fast dba gravatar image

half fast dba
152 8 9 10

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Although Brent's blitz script is a good one, most of it is about checking sp_configure, database backups, if DBCC has run, alerts to setup, etc. The next to the last script checks the memory usage per database but that is about it, at least last time I looked at it.

The only real definitive diagnostic tests (IMOH) you can do is collecting performance information, from either within SQL Server or using Perfmon jobs (or both). If you collect your baseline over a few days, or even weeks depending on your business, you can analyze that to know what your "normal" is. Then when things start to go every-which-way but good, you collect that performance information again you will be able to see where things may have changed.

If you don't have a baseline to go against, you don't have to guess necessarily, just collect the information and see what areas seem to have high/low numbers (depending on what is a suggested number). However remember that the suggested numbers, are just suggestions. Some counters show different numbers in different environments, and it is perfectly fine for that environment. You just have to get familar with them in your environment. You might find from this that the CPU and RAM are fine in your server, the developers just need to tweak a query here and there, or adjust an index on a table.

Brent does have an excellent video on SQL Performance counters and how to make them look pretty in an Excel worksheet. You can check his performance page for other stuff here.

A good book to look into as well is Glenn Berry's SQL Server Hardware. It is on my wish list to buy and has some good explanations about what effects adding more memory or CPU can have, among other things. Brent actually left a [review][3] on it and makes me want to trade my kid in so I can buy it, jk.

[3]: http://www.amazon.com/SQL-Server-Hardware-Glenn-Berry/product-reviews/1906434638/ref=dp_top_cm_cr_acr_txt?ie=UTF8&showViewpoints=1
more ▼

answered Sep 03 '11 at 09:35 AM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 17 21 29

Shawn, although this isn't the same book I am sure there is some overlap of information in Glenn Berry's SQL Server Hardware Choices Made Easy this is a free ebook by RedGate
Sep 03 '11 at 01:18 PM Tim
I thought there was a free smaller version somewhere...That is the ebok based on the blog post series he wrote earlier this year.
Sep 03 '11 at 07:24 PM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left
The quickest way would be to go to www.brentozar.com and get his 60 minute SQL Server take over script. It has comments in the script an logically goes through testing various metrics and will point you towards the weak point(s) on your server http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/
more ▼

answered Sep 03 '11 at 04:24 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left

I'd short circuit even these excellent suggestions. Go to the sys.dm_os_wait_stats and see what is causing the system to wait on resources the most. The top 5 longest waits there will tell you roughly where your system is stuck. Then you have to determine if it is in fact hardware, structure or code.

Yes, the best thing you can do is set up monitoring through perfmon.exe and a server-side trace, collect the data and be able to compare it over time so that you can see how the CPU, memory and disk are behaving. But if you don't have that set up (get started now), the wait stats are the single best measure to determine what is causing things to run slow.
more ▼

answered Sep 04 '11 at 04:48 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53
x12

asked: Sep 03 '11 at 03:19 AM

Seen: 1363 times

Last Updated: Sep 03 '11 at 03:19 AM