June 28, 2011 at 11:16 am
TheSQLGuru (6/28/2011)
I see you have been going back and forth for days on this issue. You REALLY should have gotten in a professional consultant initially. This type of problem is not what forums are really good at helping you with.BTW, did you by any chance upgrade your sql server version at the same time you did this hardware upgrade?
The consultant is in as soon as was possible. The entire upgrade process was bad. Begining to End. The timeline was dictated by operations, and 100% inflexible. Last minute changes were mandataed. I'm doint he best i can with what i am required to work with. If i had my way, the server would have been installed, configured, and load tested for at leaast a month before promotion to production. The business dictated otherwise. As they are the ones paying my salary there is a certain point where i have to stop pushing back and moved in the direction they are dictating.
I have also been faced with resistance from my server and network adminstrators who insist everything is right, and have been unwilling to consider the possibility of a configuration issue. It took my CIO weighing in to get the external manufacturer support to come in.
It could very well be a SQL configuration issue, and that is what iu am turning to the community and forum for help with. For past experiences of members of the cumminity when faced with this difficulty, and areas to look at that i may not have thought of.
I'm sorry you do not approve of my asking the community for guidance, but when it's my only option what recourse do i have?
Greg Roberts
June 28, 2011 at 12:52 pm
Greg Roberts-134214 (6/28/2011)
TheSQLGuru (6/28/2011)
I see you have been going back and forth for days on this issue. You REALLY should have gotten in a professional consultant initially. This type of problem is not what forums are really good at helping you with.BTW, did you by any chance upgrade your sql server version at the same time you did this hardware upgrade?
The consultant is in as soon as was possible. The entire upgrade process was bad. Begining to End. The timeline was dictated by operations, and 100% inflexible. Last minute changes were mandataed. I'm doint he best i can with what i am required to work with. If i had my way, the server would have been installed, configured, and load tested for at leaast a month before promotion to production. The business dictated otherwise. As they are the ones paying my salary there is a certain point where i have to stop pushing back and moved in the direction they are dictating.
I have also been faced with resistance from my server and network adminstrators who insist everything is right, and have been unwilling to consider the possibility of a configuration issue. It took my CIO weighing in to get the external manufacturer support to come in.
It could very well be a SQL configuration issue, and that is what iu am turning to the community and forum for help with. For past experiences of members of the cumminity when faced with this difficulty, and areas to look at that i may not have thought of.
I'm sorry you do not approve of my asking the community for guidance, but when it's my only option what recourse do i have?
1) You still didn't answer my question about upgrading SQL Server version
2) HP consultants may well not know jack about your SQL Server issues, nor how to even determine what SQL Server is seeing for IO stalls or IO patterns. Quite a few times I have shown that it really is the SAN and not SQL Server to blame when others say otherwise.
3) Have you done an IO stall analysis from within SQL Server?
4) I don't recall saying you shouldn't have used forums - just that this type of complex problem is not what they are really helpful for.
5) Your CIO is to blame here. He should never have allowed this debacle to happen or proceed. And once it did should have had qualified people onsite to address the issue(s). You have my sympathies.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 29, 2011 at 6:55 am
I'm sorry i took an overly defensive tone yesterday, it was one of those very bad days.
1) You still didn't answer my question about upgrading SQL Server version
Yes, we upgraded from SQL Server 2005 to SQL Server 2008 R2. But we had been running 2008 R2 in our test environment for > 60 days. And had run several load tests. Our testing indicated no degredation caused by the upgrade.
As a side note, our test server is also an HP DL580 with 4x 6-Core CPU's but only 64GB of RAM. Compared to 128GB on the produciton server. Also, the test environment is using NAS Storgae (via iSCSI)) vs. SAN in production.
2) HP consultants may well not know jack about your SQL Server issues, nor how to even determine what SQL Server is seeing for IO stalls or IO patterns. Quite a few times I have shown that it really is the SAN and not SQL Server to blame when others say otherwise.
We have a followup call on Friday, the one yesterday was unproductive. More info gathering than anything else. They have promised to provide us with a good consultant reference that is familiar with their products and SQL server.
3) Have you done an IO stall analysis from within SQL Server?
Still trying to dig that info out of the server and our monitoring tool. (Idera SQL Diagnostic Manager) I have been able to dig out the Average Milliseconds per Read/ Write/ Transfer. They are typically 20 - 25, which seems high to me, but not extraordinary. I do see where the times spike during file growth operations. (Over 600) That is a major problem, but could be due to my database file sizes. There are a couple of files that are over 300 GB. I have disabled autogrowth on those files and added new files to the file group with limits of 200 GB.
4) I don't recall saying you shouldn't have used forums - just that this type of complex problem is not what they are really helpful for.
Sorry, I missunderstood. I agree it's a very complex problem, and the forums are not the best place to solve it. But, I am out of ideas where to look to gather more information to identify the root cause. The forums have a wealth of experience in that regard, and have been very helpful.
Thank you everyone for your help. It is highly appreciated.
Greg Roberts
June 29, 2011 at 7:41 am
I agree the file growth is fixed, but 20 ms is probably not to good?
just curious I am guessing this SAN is fiber not ISCSI?
sounds like the consultant is needed, so he can come in tell you, what you already know...should have ran SQLIO, should have tested.....blah blah
and the CIO will say how did we let this happen? and you will say in your mind of course... No how did YOU let this happen...cause everyone is always in ah Hurry and its the CIO job to push back!
good luck let us know how it goes ounce the consultant leaves.
July 3, 2011 at 6:10 pm
I'm just jumping in to stir stuff up 🙂
First, I'm goin g to agree with Kevin that an HP consultant is a nice way to spend money without getting results. Good luck, and please prove us wrong.
Also, there is a big difference between write time monitoring and read time monitoring. They have very different targets: 2ms write, 20ms read. Keep those numbers separate.
If file growth is taking a long time, that means you are not taking advantage of Instant File Initialization, which is provided by Windows if you have the permissions set right. It uses NTFS Sparse File tech to simply provide the allocation without fully allocating all the bits, and is darn near instant, ragrdless of the size of the allocation. Google/Bing/MSDN search Instant File Initialization to get the settings right.
-Eddie
Eddie Wuerch
MCM: SQL
July 5, 2011 at 8:00 am
Eddie, thanks for the advice on the Instant File Initialization. I'll check with the server admins and see what we can do.
Also, thanks for the info on the times. The numbers you posted are what i expect to see, unfortunately we are looking at 20ms Write averages.
The HP is consultant is part of our support contract right now. So no extra $$. Yet. 😉 Once we have to go out of house, then we will be into the $$. And the very hard part comes in. Finding a good consultant.
Greg Roberts
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply