April 20, 2011 at 12:46 pm
Hello All,
I just started working for a new company that has this billing application that has a database that contains about 100 tables (built for an application). I basically was hired to write reports against this dbase and after running a couple of simple SELECT statements that joins about 5 tables, they both take about 1hr to complete and return about 4mill records.
Obviously there is no way i can write against this. The server is offsite but its connected to our site over a fiber connection. When i ping the server the response time is under 2ms so that is not the problem. I am looking at the hardware as well to see if this could be the problem. What are other suggestions that you might have to troubleshoot the server and find the root of the problem.
I also was thinking about running an overnight replication of the server to another local server and run the reports from there? Any suggestions are very welcome.
Thanks!
April 20, 2011 at 12:58 pm
I assume you WANT 4 million records when you run these?
The real question here is if you don't return results (it's in your tools-options) and run these queries, how long do they take? Is the optimization necessary in the communications pipeline, or on the server itself? If it's the server, is it the indexing, drive I/O, memory issues... you get the drift.
You're jumping a step. Yes, in general, a local server will probably serve you better and faster if it was the same hardware just less network between the two stops. However, loading up a 500 gig db onto your local RAID 5 vs. the RAID 10 that's SAN driven is probably going to slow you down more then speed you up. It's a tradeoff.
You need more information as to why it's slow. How long does simply selecting 4 million records across the pipe take without doing joins and the like? That's your network baseline. Then figure out the difference.
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
April 20, 2011 at 2:02 pm
Ok, one question first.
What are you going to do with 4 million rows?
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
April 20, 2011 at 2:21 pm
I am trying to do some more research as of what happened to this server. Apparently reporting was ran off of this before and everything was ok at the time where the same view that i am trying to run now that takes an hour, used to take no more than 30 seconds. This server was supposed to be a BI server that now seems that has been overloaded to no end and now its pretty unusable.
The 4 million records need to be extracted so I can start with that and start doing some filtering from there. It is a historical data from ALL customers that i need to report different things from.
What i need is a way that i can troubleshoot and find the cause of the root. I am no DBA and I have no idea where to start, so I can go back to my boss and tell him exactly what is going on and give him some suggestions as to what to do to get it fixed and/or suggest other ways to run reporting from it.
April 20, 2011 at 2:42 pm
RenzoSQL (4/20/2011)
I am trying to do some more research as of what happened to this server. Apparently reporting was ran off of this before and everything was ok at the time where the same view that i am trying to run now that takes an hour, used to take no more than 30 seconds. This server was supposed to be a BI server that now seems that has been overloaded to no end and now its pretty unusable.
To confirm, pumping 4 million records as a select * from view used to take 30 seconds to your current location and is now taking an hour?
The 4 million records need to be extracted so I can start with that and start doing some filtering from there. It is a historical data from ALL customers that i need to report different things from.
Each report should be making it's own specifically chosen data call to the server. The only time you'd want to do what you're describing is when you're going to build a cube so you can figure out which question to ask of the data.
What i need is a way that i can troubleshoot and find the cause of the root. I am no DBA and I have no idea where to start, so I can go back to my boss and tell him exactly what is going on and give him some suggestions as to what to do to get it fixed and/or suggest other ways to run reporting from it.
There is no easy button. You have to determine network lag, actual need, how the view was ran previously (my guess is that it was select * from view WHERE customerID = @a) and now to determine differences, dig into the statistics and indexing on the tables to see if you had a seek/scan tip, figure out if there's memory, cpu, or disk pressure that didn't exist before, check for concurrency issues....
You get the idea.
I would start, however, with a few simple things.
1) Instead of pulling the entire world across the network then filtering the report, let the report call a proc that does its filtering on the source server via parameters. That's usual best practice anyway.
2) Determine if it's the query or the network that's taking so long. Do a direct pull of a similar record count directly off a table. If it's similar in time then you're dealing with a network/data display issue. If it's faster, it's view optimization time.
3) Rip the covers off the view and take a look at the optimization plan, look for anything that's not hitting indexes as you'd expect, assuming you know the design of the database.
If this seems like a lot to do, it is. It's a troubleshooting problem, and you need someone familiar enough with SQL Server to do troubleshooting. You might broach the subject with your boss to hire a contract DBA for a week or so to dig in and give you a health check on the server, design, and his/her recommendations to correct the current known issue.
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
April 21, 2011 at 5:03 am
Usually others say this, but it's my turn. It really sounds like you're completely over your head here. I'd suggest hiring, even part time, a pro, someone who does this stuff for a living. They can come in, suss out whether you have serious performance issues or just some process issues, help you address either and leave you in a better spot. Based on the stuff you've said, there is so much that has to be looked into, it's going to be very hard to walk you through all of it through a series of forum posts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2011 at 8:23 am
Grant Fritchey (4/21/2011)
Usually others say this, but it's my turn. It really sounds like you're completely over your head here. I'd suggest hiring, even part time, a pro, someone who does this stuff for a living. They can come in, suss out whether you have serious performance issues or just some process issues, help you address either and leave you in a better spot. Based on the stuff you've said, there is so much that has to be looked into, it's going to be very hard to walk you through all of it through a series of forum posts.
I would be one of those "others". There are UMPTEEN reasons why performance can tank on a server or why individual queries can start taking 2 orders of magnitude longer to run. Professional tuners can quickly isolate and then offer recommendations on mitigating those causes. You could spend weeks going back and forth on forums (assuming anyone wanted to continue to offer up suggestions for that long) and still not really know what is going on. Or you could get a good pro in and have a solution in potentially a few hours.
Without any other information I will make one guess: virtual machine that is now on overloaded host. Second guess for free: SAN based IO that is likewise now overloaded. Hey, a free third guess: someone changed a configuration on sql server (max memory to 100MB??) or within the database (such as dropping key index(es)).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 22, 2011 at 1:31 pm
Thanks all for your suggestions. After getting access to the documentation and the actual server i found out that it is definitely a hardware issue. Its a 2003 Windows Server Standard 32bit Virtual Machine with 4GB of RAM. Someone set the Page file to 8GB! and without running much on it, it is at a normal 4.5GB of memory utilization.
I know at least where i have to start before doing anything else on this server. I already sent my suggestions to my boss and lets hope that they do something fast.
Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply