January 30, 2010 at 12:48 pm
My company has two main clients and a few others. We finished our application and first iteratin of the DB and placed a client on it. Things went okay. I could see room for improvement.
We just placed a larger client (say 20% larger) so we just more than doubled the data and users. I recomended stress testing prior to rollout and that cost money for the tools, so that was shot down. Now we need tools to analyse our live "stress testing"
Getting all sorts of errors when users all run reports at the same time.
I made adjustments for the parallel threads threshold and max limit which helped things overall when maxed out, but slowed things a little when running light.
I'm currently using perfmon and the SQL activity monitor... I'd like trace info as the activity monitor can be pretty vague, but if the DB is that hard hit, it couldn't handle a trace on top of that.
So do we have an advice for detective work in this situation? Maybe a monitoring tool that works well and doesn't get in the way of DB work? I've been looking at ignight, but I'm afraid putting that on the live server might be like when I tried to set up a trace on the live server when it was busy... and it's far busier now.
The server is a 8 core, 15k hard drives (Log and Data on same drive!) with 16gb ram. Moving to a 16 core, 10k hard drives (but log and data on different drives) and with 256gb ram. I know this is going to help out and give us more breathing room, but I really need to get to the bottom of the SQL and settings issues.
I'm kinda making do treading water, but not getting ahead or a real handle on performance here. Any advice or tools would be a great help!
January 30, 2010 at 2:10 pm
There's no silver bullet. There's no app that will make your problems go away. There's no setting that will make everything better.
If this is critical and costing you reputation with your client (and maybe even money) and there's no one inhouse that can do SQL performance tuning, I would strongly suggest you get someone in to help you, advise you and teach you.
If that's not an option, then you need to sit down and learn how to performance tune SQL. It's not something trivial to learn. This short series may give you an idea where to start in finding the problem queries (and finding is more than half the battle)
It's going to be a repetitive process of finding the most problematic queries, fixing queries, tuning indexes or redesigning database to get them to perform better, then finding the next most problematic queries and repeating. Keep going until server performance is good.
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
January 30, 2010 at 2:52 pm
Thank you for taking the time to give your take. I know there's no one thing wrong or one thing that could fix things.
At this point a solid view into what's happening when things go south. Perfmon has been a great help to see that the CPU or io waits have general issues. I know page splitting is way higher than it should be and I'm looking into that.
What I'm really missing is when wait times are up and odd wait types pop on, what is causing the issues. I'm seeing symptoms, but not what's slow and causing those issues. SQL activity viewer shows a lot in terms of waits and blocks, but not always what exactly is running.
That's were I was thinking an external tool minght help show me what's going right and wrong at a point in time.
I'm definitely going to check out those links and be asking some questions around here as I fix issues and hit roadblocks.
January 30, 2010 at 3:11 pm
I would read those links suggested by Gail. Also, there is a script for download called sp_activity. That also helps to give some insight into blocking chains. It could prove useful for you.
There are some tools to help diagnose poorly performing sql queries as well as tools for targeting queries associated with wait types. However, you would still need to understand how to read an exec plan and perf tune the queries. Being able to associate a query to a specific action performed in the app that is causing pain is where you will get the best bang for your buck.
I would also recommend getting somebody in house that knows how to perf tune and troubleshoot.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 30, 2010 at 11:30 pm
STherrien-978820 (1/30/2010)
What I'm really missing is when wait times are up and odd wait types pop on, what is causing the issues.
You need to run a trace, bottom line. There are lots and lots of 3rd party tools you can buy that will run the traces for you and pinpoint what's wrong, but you can do it yourself without too much difficulty.
Personally I feel that people should do it themselves initially so that later, when they use 3rd party tools, they know what they're looking at.
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
January 31, 2010 at 5:12 pm
On a phone yesterday so I didn't get time to look over those links. Just did a scan of the first one and this is exactly what we need to find out what's causing the problems. I might be back on just to ask for advice with a more specific problem.
About hiring an expert to come in... Employers had bad experiences in the past and I'm not hot with the idea of someone coming in and fixing things behind a curtain and then like a magic trick everything is working better... until it isn't and you need to call them back. I've been trying to get my employers to get someone like this in, but to not only work their tricks, but to also teach us their tricks. But so far management hasn't approved it and I'm not sure how many of these magicians like sharing their tricks.
Thank you very much for this start, I'll post again a more specific post if I have questions about these links
January 31, 2010 at 9:30 pm
This is where contracts come into play. You bird-dog the contractor and get them to document everything. In the meantime you are learning what they are doing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2010 at 9:42 pm
STherrien-978820 (1/31/2010)
About hiring an expert to come in... Employers had bad experiences in the past and I'm not hot with the idea of someone coming in and fixing things behind a curtain and then like a magic trick everything is working better...
That's not the way everyone works. As I said in my first post, you want someone who can come in and fix the problem, teaching you at the same time. Its one of the things that you ask when evaluating the person/company before signing contracts and you get written into the contract that training must be done.
Plus, as I said earlier, there's no magic involved. No one's going to be able to come, fix one thing and magically everything's better. From my experience of poorly performing systems, there's going to be lots (months probably) of tuning work to get things optimal.
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
February 1, 2010 at 6:50 am
Employers had bad experiences in the past and I'm not hot with the idea of someone coming in and fixing things behind a curtain and then like a magic trick everything is working better... until it isn't and you need to call them back. I've been trying to get my employers to get someone like this in, but to not only work their tricks, but to also teach us their tricks. But so far management hasn't approved it and I'm not sure how many of these magicians like sharing their tricks.
This is known as mentoring. Some consultants are super-great a perf tuning but really cannot transfer their knowledge to in-house staff. But as you allude to, that is by far the most important thing. Some of us take great pride in the fact that when we finish an engagement the 'locals' are MUCH better at interacting with SQL Server than when we came on board that that pays dividends forever.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply