November 7, 2014 at 10:51 am
I want to review settings of overall system which includes , does anyone have standard list of things that i should be looking for . I do have some in mind but they are all over the place. Would like to know what others look for when analyzing a server.
November 7, 2014 at 11:05 am
Server, Instance & database settings will will depend on the environment, and once set don't change.
So, I'm not clear on what you are looking for.
November 7, 2014 at 2:33 pm
homebrew01 (11/7/2014)
Server, Instance & database settings will will depend on the environment, and once set don't change.So, I'm not clear on what you are looking for.
If you are given a Physical server which has sql installed on it with some highly transactional db's and asked to find out what could we do to improve overall performance? What specific things you would look for? Where would you start from. For now keep query tuning on the side.
November 7, 2014 at 3:29 pm
First, check for RAM pressure/bottlenecks. If you have (way) too little RAM, nothing else you do will matter much.
Then check for I/O pressure/bottlenecks.
Finally look for CPU pressure/bottlenecks. These are somewhat less likely now due to increased number and speed of processors.
Finally, look at index usage stats and index missing stats on large and/or heavily used tables. Pay particular attention to getting the best clustered index key column(s) for every table. Hint: it's usually not identity.
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".
November 7, 2014 at 4:16 pm
Keeping query tuning to the side is like asking to create a sentence without verbs.
Queries are what puts the pressure on systems. Knowing which queries hurt can point you towards hardware and design issues. You can use Perfmon to look for pressure and bottlenecks, but until you already have the baselines established you don't usually start there. You start where it hurts.
My first step when looking over a system? Top ten queries in cost for time, I/O, and CPU. Pull the wait statistics for the server. While those reports run, I ask the devs "Where's the pain the users whine the most about?". You have to inspect the queries, the plans, and look at their pain points during runtime.
Even if you're not going to rewrite a single line of code, the queries and data usage are what tell you where to go to fix things.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 7, 2014 at 5:58 pm
Check to see some one is doing something stupid like shrinking files.
Check if statistics are being kept up to date?
Are indexes fragmented?
Is there some kind of index maintenance?
Are there sort warnings in the default trace (memory pressure or tempdb issues)
November 7, 2014 at 7:53 pm
Evil Kraig F (11/7/2014)
Keeping query tuning to the side is like asking to create a sentence without verbs.Queries are what puts the pressure on systems. Knowing which queries hurt can point you towards hardware and design issues. You can use Perfmon to look for pressure and bottlenecks, but until you already have the baselines established you don't usually start there. You start where it hurts.
My first step when looking over a system? Top ten queries in cost for time, I/O, and CPU. Pull the wait statistics for the server. While those reports run, I ask the devs "Where's the pain the users whine the most about?". You have to inspect the queries, the plans, and look at their pain points during runtime.
Even if you're not going to rewrite a single line of code, the queries and data usage are what tell you where to go to fix things.
Oh, i completely agree with you. I do have list of queries which are kinda "bad" one's, i was looking for some must to have kinda of list of things on a given server. Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply