September 15, 2014 at 2:22 am
I'm just curious what points people look at when they're asked to evaluate a new database for its performance, etc?
September 15, 2014 at 2:26 am
New as in 'in development' or new as in 'here's an existing system you've never seen before'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2014 at 3:11 am
GilaMonster (9/15/2014)
New as in 'in development' or new as in 'here's an existing system you've never seen before'?
Should have mentioned that sorry, it's 'here's an existing system you've never seen before'
September 15, 2014 at 3:15 am
In that case, I usually take profiler traces (well, server side traces) for a couple of hours of peak usage, pull wait stats, locking stats, perfmon counters and whatever more I can get my hands on, and analyse that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2014 at 7:15 am
First five chapters of my book.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2014 at 12:57 pm
I first look at SQL's usage stats (incl. row counts), operational stats and missing index info. Be aware they get reset when SQL stops and restarts, so you get a better feel if SQL has been up continuously for at least 30 days.
Also, look at wait stats and other performance metrics SQL maintains (unless someone has, yikes, turned some of them off for some reason -- if so, immediately re-enable them). As above, they are reset at restart.
Some of these are running totals, so you'll need to capture them at intervals (bihourly, hourly, half-hourly?, whatever) and then take differences to get a feel for the amount of activity during those time periods. You'll be able to see patterns of activity during the day as well.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2014 at 2:11 pm
ScottPletcher (9/16/2014)
I first look at SQL's usage stats (incl. row counts), operational stats and missing index info. Be aware they get reset when SQL stops and restarts, so you get a better feel if SQL has been up continuously for at least 30 days.Also, look at wait stats and other performance metrics SQL maintains (unless someone has, yikes, turned some of them off for some reason -- if so, immediately re-enable them). As above, they are reset at restart.
Some of these are running totals, so you'll need to capture them at intervals (bihourly, hourly, half-hourly?, whatever) and then take differences to get a feel for the amount of activity during those time periods. You'll be able to see patterns of activity during the day as well.
Thanks for that Scott. How do you look at wait stats only for a particular database rather than the instance as a whole? i.e. OS_WAIT_STATS just gives it to me for the instance as a whole.
September 16, 2014 at 2:36 pm
PhilipC (9/16/2014)
ScottPletcher (9/16/2014)
I first look at SQL's usage stats (incl. row counts), operational stats and missing index info. Be aware they get reset when SQL stops and restarts, so you get a better feel if SQL has been up continuously for at least 30 days.Also, look at wait stats and other performance metrics SQL maintains (unless someone has, yikes, turned some of them off for some reason -- if so, immediately re-enable them). As above, they are reset at restart.
Some of these are running totals, so you'll need to capture them at intervals (bihourly, hourly, half-hourly?, whatever) and then take differences to get a feel for the amount of activity during those time periods. You'll be able to see patterns of activity during the day as well.
Thanks for that Scott. How do you look at wait stats only for a particular database rather than the instance as a whole? i.e. OS_WAIT_STATS just gives it to me for the instance as a whole.
That's mostly included in the sys.dm_db_index_operational_stats view I alluded to before. There's row_lock, page_lock, page_latch, page_io_latch and tree_page_* wait counts and times.
For the other common waits, such as parallelism (cxpacket), I don't know of any easy way to isolate those by db. Particularly on initial exposure to a system, I personally don't want to go thru a (detailed) trace, as it's just too time confusing and hit-and-miss.
For performance, the key issues are, in rough order of commonality/criticality:
1) RAM: if you're too low on RAM, nothing else you do will help much)
2) tempdb: insure enough data files; insure enough pre-allocated data and log space so that tempdb files never autogrow (although leave autogrow, in case of a freaky situation)
3) CPU: less of an issue than it used to be, with today's processors, except for MAXDOP setting being too high.
4) key tables, clustered indexes: very often on the wrong column(s) (typically identity, which some people mistakenly consider automatic, without any consideration of actual table usage).
5) key tables, nonclustered indexes: missing / extra / combining, but only after clustering indexes are reviewed, obviously.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 17, 2014 at 1:22 am
Thanks Scott 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply