| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2005-08-15 : 12:31:43
|
| I'm looking for a command to run to give me a point-in-time snapshot of all the tables/indexes that have pages in RAM and how many pages are cached.DBCC MEMUSAGE would work if it returned all, instead of just the top 20 ...ThanksJay White |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-18 : 06:45:27
|
| OK, lemme ask the question a different way ...Someone is claiming that my database is marking cache buffers as "hot" and not allowing the lazy writer to flush those buffers to make room for other stuff in Mem. Kinda, as if the tables were quasi-pinned. As a result the amount of data cache space available to the application is reduced and that is causing elevated disk i/o due to memory paging. They want me to DROPCLEANBUFFERS between each step of a nightly ETL to resolve the problem. How do I prove/disprove their theory?Jay White |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 07:04:46
|
| Does Performance Monitor give you that sort of info?Kristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-18 : 10:25:30
|
| There's no supported method of doing it. Has anyone used DBCC PINTABLE?Basically, you should not try to interfere with the buffer pool's LRU and lazywriter mechanisms - if it's retaining a page in the buffer pool there's a very good reason for it. Various allocation bitmaps and upper levels of hot b-trees will be retained in memory. Without trying to sound arrogant, the buffer pool will do a better job of managing what pages are in memory than a human can. You should not use DROPCLEANBUFFERS a that will definitely cause more IO as the buffer pool warms up again.Its more likely that the buffer pool doesn't have enough memory for the load you're placing on it. How much memory is on the box, how big is the database and how much data are you processing during your ETL process?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-18 : 10:34:00
|
| Paul, I agree with you 100% ... the trouble is I need to prove it ...It's a 70GB DB, and 20GB or RAM allocated to SQL. But during this ETL, only a portion of the DB is touched. No, the tables are not pinned. I think I'll have to resort to a simplified test where as a control group, I run the ETL as it is and as an experiment group, I'll DROPCLEANBUFFERS between each step. I'll then compare run times ... I'd prefer something more scientific, oh well.Jay White |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-18 : 10:58:43
|
| Jay - have you asked your colleague why he thinks that SQL Server is suffering during the ETL? Is there an app running at the same time that's slowing down?If the buffer pool fills up with dirty pages during the ETL, it doesn't matter whether you do a DROPCLEANBUFFERS or something else that needs to read in different pages - the number of IOs due to reading the different pages will be exactly the same, but in the case of DROPCLEANBUFFERS you'll take the extra penalty of having to read in all the hot pages as well.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-18 : 11:25:48
|
| My "colleague" is a third party developer that has sold my client some seriously junk code. The ETL process works 100% out of app side DataSets and does singleton DML via the dataset.updatecommand in an interative manner. My client is pissed about poor performance and this third party is carring the "we need more RAM" flag as an attept to cover up a bad architecture. Politically, I need to be careful that I politely dispute the third parties contention, rather than walk around blabbing about how my client spent millions on junk. (If anyone is reading this and thinks they know who I am and who I work for and they think they are an employee of my client or an employee of this third party, you are wrong ... or at least I'll never admit you are right :))Jay White |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-18 : 11:35:05
|
| Paul, I appreciate your remarks ... I've read extensively about SQL Memory Management and I've conveyed to my client that I am suspect of the "hot" theory. It's a my story vs. thiers situation, so I need to perform an experiment to show them. I'm honored to speak with a "Dev Lead, Microsoft SQL Server Storage Engine", though :)Jay White |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2005-08-18 : 11:50:02
|
| This is a tough situation. Sometimes it's easier to do what they ask ie. add more ram so you can politely prove they don't know what they're doing without being labeled as "hard to work with" or "confrontational" by your peers and management. The hard part is biting your tongue eventhough you know you're right. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 11:52:21
|
"My "colleague" is a third party developer that has sold my client some seriously junk code"It wasn't me  "and they think they are an employee ... of this third party"Ah ... I'm not so sure now Would a DROPCLEANBUFFERS and then watch Target Memory growing in Perf.Mon. do any good? If you wind-up with less than 20GB RAM used (Wow! How much? ) your luck is in an it'll be your round at the SQL Team Yak Coral Bar!Kristen |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-18 : 12:11:19
|
quote:
Originally posted by peterlemonjello
This is a tough situation. Sometimes it's easier to do what they ask ie. add more ram so you can politely prove they don't know what they're doing without being labeled as "hard to work with" or "confrontational" by your peers and management. The hard part is biting your tongue eventhough you know you're right.
It has taken years to learn how to lay it on the line everytime while still being diplomatic and polite with people and learning how to avoid the told-you-so moments (had 2 yesterday and my tongue is till bleeding). The bottom line is that people pay you for your expertise and that is what you must deliver even if an occassional toe is stepped on. Be right all the time and admit when you are wrong and do not gloat or run in either circumstance and your co-workers will respect you.====================================================Regards,Sean RoussyThank you, drive through |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 12:19:48
|
| I'm right behind you Sean.No point the company buying another 20GB of RAM if its going to make diddly-squat difference ... 3rd party company then gets fired [maybe :-( ], but you are still going to take a hit selling 20GB of RAM on eBay Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 12:29:44
|
| "My "colleague" is a third party developer that has sold my client some seriously junk code"Not sure if that's relevant Brett, but you might like to consider it Kristen |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2005-08-18 : 13:33:50
|
| I totally agree with you Sean. But the correct & professional approach can be hard to do if the party that's disagreeing with you starts embellishing the truth and has something to hide. Especially if your client believes what they're saying. I guess the more experience you have with these situations the more you learn how to deal with them. |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-18 : 14:00:24
|
| Uncle Bill gave us Profiler and Performance monitor and stress testers to bring empirical proof to the bosses. Tables that can be converted to pretty charts and excel sheets to satisfy those who rely on visual imagery because their eyes cross when presented with numbers.====================================================Regards,Sean RoussyThank you, drive through |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-18 : 14:36:43
|
| I would fire up pssdiag (http://support.microsoft.com/default.aspx?scid=kb;en-us;830232) and see what it tells you. You can run the blocker output through sherlock (http://www.sqlteam.com/item.asp?ItemID=21737) and process the profiler traces using the Read80Trace application (http://support.microsoft.com/default.aspx?scid=kb;en-us;887057)Read80Trace generates a very nice report that should make it pretty clear what is going on.This is now my standard practice when troubleshooting a difficult performance problem. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 15:29:17
|
| "But the correct & professional approach can be hard to do if the party that's disagreeing with you starts embellishing the truth and has something to hide"Sorry, but that's crap. If your professional experience tells you the third party is talking crap then better to tell your client that it "looks like crap, sounds like crap, and ... Yuck! ... smells like crap"Otherwise what's the point?Now ... if you don't have enough knowledge to know for sure than better keep your mouth shut.I work on the basis that if I'm right 80%, 90%, 95%, 99%, 99.9999% ... then my clients will respect that - and allow me to be wrong once in a while - particularly if I admit it when I am wrong.Well anyway, IMHO, "Thats My View".Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-18 : 16:20:56
|
quote:
Originally posted by KristenSorry, but that's crap. If your professional experience tells you the third party is talking crap then better to tell your client that it "looks like crap, sounds like crap, and ... Yuck! ... smells like crap"
Cheech: Hey stop, that looks like shitChong: Yeah it doesCheech: Let's see, does it smell like shit?Chong: (sniffs) Yeah it smells like shitCheech: Does it feel like shit?Chong: (touches) Yeah it feels like shit.Cheech: Does it taste like shit?Chong: (pokes and licks finger) Yeah it tastes like shit.Cheech: Wow man, it must be shit, good thing we didn't step in it.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-19 : 03:16:09
|
| I've just gotta pick myself up off the floor .... Should the last line have been:Cheech:Wow man, does it smoke like shit? Kristen |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2005-08-19 : 10:28:26
|
| ROFLMAOThen...Chong: Wow man, this is some good shit? |
|
|
|
Next Page
|
Objects in Data Cache