October 27, 2011 at 9:36 am
GilaMonster (10/27/2011)
TheSQLGuru (10/27/2011)
In any case I am not actually "in the running" for this non-job consulting gig either. I am (very thankfully) over-booked for months out.I wish I was. Most companies are starting to wind down for the year. Probably won't see much until Feb or so.
Well I'm fine here too, so you can take this one. 😉
October 27, 2011 at 3:06 pm
jshurak,
It has been my experience that if a database management system is performing a high number of physical reads, then it must need the data it's retrieving from disk, since it's not already in memory. That sounds obvious but the reason(s) for it may not be.
Consider that if you had more RAM, more indexes and rows would be memory resident, and therefore the server would require less physical read activity. If an entire database could be placed into RAM, there would be no need for physical reads. In most cases, that is not practical, so the goal should be to have enough RAM to support having the most frequently accessed rows in RAM. Usually, those rows need to be indexes, specifically non-clustered indexes. If more RAM is not an option, please read on.
In my experience on servers with marginally sufficient amounts of RAM, the most likely culprits for high numbers of physical reads have been:
1. Index scans on clustered indexes (table scans).
2. Index scans on non-clustered indexes of very large tables.
3. Both 1 and 2.
Fundamentally, the causes of the scans are the implementations of the queries executed against the database. Remedies include:
1. Add indexes to better support the queries.
2. Refactor the queries for more efficient execution and use of server resources.
3. Both 1 and 2.
If you haven't already, I suggest you install and use the tools in SQL Server Performance Dashboard to help you locate the most expensive queries and to identify missing indexes. Some caution on the last point. The missing indexes report must be used with an eye for practicality; you must not use its recommendations for indexes blindly. Some recommendations, particularly with regard to INCLUDE columns, are impractical.
The download site for SQL Server Performance Dashboard: http://www.microsoft.com/download/en/details.aspx?id=22602
Once you install the package, execute the "setup.sql" query. Then, you can launch the dashboard from Custom Reports in SQL Server Management Studio. There are a lot of things to look at in Performance Dashboard. I encourage you to spend some time looking at things to give you an idea of the resources available to you.
LC
October 27, 2011 at 4:11 pm
Performance dashboard is fine if you can use more of a shotgun approach (throw a lot of indexes out there and then come back to see what sticks).
When the server is already under way too much pressure you need a chirurgical strike as in =>
Ninja's_RGR'us (10/26/2011)
#1 get a copy of prod to your dev/qa machine.#3 Keep tuning with this. Group & Sort by highest reads / writes and tune the worst offenders untill the problem is solved.
October 27, 2011 at 5:05 pm
That's a shotgun criticism. Could you be a little more surgical? 😀
If you're educated about proper indexing, the information Performance Dashboard provides can be used in a highly selective manner.
LC
October 27, 2011 at 5:37 pm
Out of curiousity, OP... Usually the older systems were heavily tuned and something 'tipped', or something new exploded (which wouldn't be on 32bit, these days). Do you use a shotgun search query as a common interface?
Something like this: SELECT * from tbla/tblb/tblc WHERE (a.Col1= @Col1 OR @col1 IS NULL) AND (b.Col2 = @col2 OR @Col2 IS NULL) AND....
Those monsters usually are a killer to memory because it keeps having to do a shuffle depending on queries and keeps dumping the index in and out. Add to that if one of the tables 'tipped' and is now permanently scanning...
Just an idea, but your description and answers reminded of a quick hit fix I was able to do at a site once. Kinda shot myself in the foot with that one. 2 month project done in 2 days... At least I was able to help them get a solid re-architecture in while I was there.
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
October 27, 2011 at 6:03 pm
Lee Crain (10/27/2011)
That's a shotgun criticism. Could you be a little more surgical? 😀If you're educated about proper indexing, the information Performance Dashboard provides can be used in a highly selective manner.
LC
For most untuned system I'd be able to spend 1 hour / day TOPS. tuning the the 2-3 queries. Rinse repeat over 3-4 days and I'd get 50 to 90% improvement overall.
I've seen systems with 1000s of suggested indexes. Filtering that without knowing what querie(s), if any, will be helped and how they will be helped is not the most effective way, when not a waste of time.
I'm not saying it can't help, just not the best way to go.
October 27, 2011 at 6:41 pm
Evil Kraig F (10/27/2011)
....but your description and answers reminded of a quick hit fix I was able to do at a site once. Kinda shot myself in the foot with that one. 2 month project done in 2 days... At least I was able to help them get a solid re-architecture in while I was there.
Too funny. LOL
A DBA should always pace his successes....;-)
LC
October 27, 2011 at 6:43 pm
Ninja's_RGR'us (10/27/2011)
Lee Crain (10/27/2011)
That's a shotgun criticism. Could you be a little more surgical? 😀If you're educated about proper indexing, the information Performance Dashboard provides can be used in a highly selective manner.
LC
For most untuned system I'd be able to spend 1 hour / day TOPS. tuning the the 2-3 queries. Rinse repeat over 3-4 days and I'd get 50 to 90% improvement overall.
I've seen systems with 1000s of suggested indexes. Filtering that without knowing what querie(s), if any, will be helped and how they will be helped is not the most effective way, when not a waste of time.
I'm not saying it can't help, just not the best way to go.
Like I said, if you're educated about proper indexing, the information Performance Dashboard provides can be used in a highly selective manner.
LC
October 28, 2011 at 1:52 pm
Evil Kraig F (10/27/2011)
Out of curiousity, OP... Usually the older systems were heavily tuned and something 'tipped', or something new exploded (which wouldn't be on 32bit, these days). Do you use a shotgun search query as a common interface?Something like this: SELECT * from tbla/tblb/tblc WHERE (a.Col1= @Col1 OR @col1 IS NULL) AND (b.Col2 = @col2 OR @Col2 IS NULL) AND....
Those monsters usually are a killer to memory because it keeps having to do a shuffle depending on queries and keeps dumping the index in and out.
Out of curiosity, what do you recommend for a generalized search interface instead of a "shotgun" query, as you called it?
Thanks,
October 28, 2011 at 2:05 pm
aureolin (10/28/2011)
Evil Kraig F (10/27/2011)
Out of curiousity, OP... Usually the older systems were heavily tuned and something 'tipped', or something new exploded (which wouldn't be on 32bit, these days). Do you use a shotgun search query as a common interface?Something like this: SELECT * from tbla/tblb/tblc WHERE (a.Col1= @Col1 OR @col1 IS NULL) AND (b.Col2 = @col2 OR @Col2 IS NULL) AND....
Those monsters usually are a killer to memory because it keeps having to do a shuffle depending on queries and keeps dumping the index in and out.
Out of curiosity, what do you recommend for a generalized search interface instead of a "shotgun" query, as you called it?
Thanks,
One of the only times I recommend directed and particular use of Dynamic SQL query builds, or large IF structures with sub-executes.
Gail's said it so much better then I can explain, see this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
October 28, 2011 at 2:05 pm
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
October 28, 2011 at 2:31 pm
Thanks for the link - exactly what I was looking for.
October 28, 2011 at 4:15 pm
aureolin (10/28/2011)
Evil Kraig F (10/27/2011)
Out of curiousity, OP... Usually the older systems were heavily tuned and something 'tipped', or something new exploded (which wouldn't be on 32bit, these days). Do you use a shotgun search query as a common interface?Something like this: SELECT * from tbla/tblb/tblc WHERE (a.Col1= @Col1 OR @col1 IS NULL) AND (b.Col2 = @col2 OR @Col2 IS NULL) AND....
Those monsters usually are a killer to memory because it keeps having to do a shuffle depending on queries and keeps dumping the index in and out.
Out of curiosity, what do you recommend for a generalized search interface instead of a "shotgun" query, as you called it?
Thanks,
"shotgun" query + OPTION RECOMPILE if you are running on SQL 2008 SP1 CU5 or older.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply