September 24, 2012 at 8:20 am
Hi
We are looking to upgrade our current SQL and Server, at the minute a lot of our larger tables (2-3 million rows) are timing out when I attempt to do simply queries such as selecting the transactions from the current year. We currently have a 6 year old server, could the timing out be due to the number of the rows? I have just looked and the server only has 4 GB of RAM would increasing this allow for more Memory to process the query as well as the whole running of the server? Or would it be simpier and more effective to get a new Server and keep the old as a backup or File Server?
Thanks for any advice offered.
September 24, 2012 at 8:33 am
martin 81121 (9/24/2012)
HiWe are looking to upgrade our current SQL and Server, at the minute a lot of our larger tables (2-3 million rows) are timing out when I attempt to do simply queries such as selecting the transactions from the current year. We currently have a 6 year old server, could the timing out be due to the number of the rows? I have just looked and the server only has 4 GB of RAM would increasing this allow for more Memory to process the query as well as the whole running of the server? Or would it be simpier and more effective to get a new Server and keep the old as a backup or File Server?
Thanks for any advice offered.
The hardware MAY be some of the issue but there are a lot of other things involved here. You say "selecting the transactions from the current year", this indicates your query has a where clause. Now we have a whole new realm of possible reasons for performance degradation. We have indexes, SARGability, statistics etc etc etc to think about. Throwing hardware at performance issues will possibly get you some short term gains but it is a long term loss. You need to fix the underlying issues.
See Gail's post here about how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 11:09 am
It's hard to make real substantive suggestions based on general information. Bigger, faster hardware always helps some. Depending on how much you upgrade the hardware, you can mask really bad problems with T-SQL or database design. But usually, tuning the code and structures are the easiest way to maximize throughput of the system. If you're just getting started with query tuning, I'd suggest getting a copy of my book linked below.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply