October 4, 2016 at 10:51 pm
HI
I need recommendations for better performance of sql .we have 32bit SQL servers and want to make sure we have them configured optimally for performance. We are unable to add any indexes etc, can any one suggest the options
October 5, 2016 at 2:43 am
ramyours2003 (10/4/2016)
HII need recommendations for better performance of sql .we have 32bit SQL servers and want to make sure we have them configured optimally for performance. We are unable to add any indexes etc, can any one suggest the options
This is very vague and almost impossible to provide any sensible answer without further information, can you please elaborate further?
😎
October 5, 2016 at 3:03 am
Get someone in to do a performance tuning exercise on your server. That's about all I can suggest with such little information.
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
October 5, 2016 at 4:23 am
Grab a copy of my book on performance tuning and start reading. Get a copy of Gail's book on being an Accidental DBA and start reading.
One system change I can recommend, even with this tiny amount of information. Change your cost threshold for parallelism away from the default value of 5. Either measure the costs and go 2 standard deviations above them, or, for an OLTP system set it to 50 and a reporting system, 30.
Otherwise, this is just far too big a topic. Set up monitoring so you're working from a position of knowledge. Figure out the pain points. Deal with them as required. That's about it.
"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
October 5, 2016 at 4:34 am
thanks for the mail , what is the reason of not creating index on 32 bit system?
October 5, 2016 at 4:44 am
ramyours2003 (10/5/2016)
thanks for the mail , what is the reason of not creating index on 32 bit system?
You tell us. Indexes are as vital to a 32 bit system as they are to a 64 bit system. I have no clue why someone would say "We have a 32 bit system, so no new indexes." It doesn't make any sense at all.
"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
October 5, 2016 at 5:11 am
ramyours2003 (10/5/2016)
what is the reason of not creating index on 32 bit system?
Eeuhhhmm ... perhaps someone WANTS bad performance?
More serious: some third party application have a rule in the license agreement that you are not allowed to add, change, or remove indexes. The main reasoning of the vendors appears to be that they are scared of what might happen if their customers can make changes here. I am convinced that, as long as you do not go overboard, it is impossible to break an application just by adding indexes. (Changing or removing indexes, especially unique indexes, is a different story).
If such an application performs bad, then the license agreement has robbed you of perhaps the most important tuning instrument, which is sad. Sometimes you can find a solution by talking to the vendor, sometimes you'll just have to suck it up. (And I have heard people admit that they'll add the indexes anyway, but be carerful because this is a violation of a legal agreement).
Indexes and 32- vs 64 bits are two completly disjunct subjects. So there is no relationship at all between running on 32 bits and creating (or not creating) indexes.
Finally, if you are running 32 bits then upgrading to 64 bits should be high on your priority list. If that cannot be done in the near future, *and* if the server has morer than 3GB RAM available, then please read up on enabling AWE. (And if the server has less than 3GB of RAM, then buying more RAM should be the highest priority).
October 5, 2016 at 6:53 am
If you are the DBA (or simply the person sitting closest to the database server), then probably the best thing you can do to improve performance is educate yourself about SQL Server architecture and optimization in general, instead of relying entirely on random drive-by advice. There are probably 100 different things you could do to optimize performance, and you need to take it one step at a time.
It's no different than if someone relies on morning TV news shows for information about health and nutrition. Without a grasp of the fundamentals to provide perspective, they will quickly become confused and overwhelmed by all the conflicting advice, much of which is simply wrong.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 5, 2016 at 5:58 pm
ramyours2003 (10/4/2016)
HII need recommendations for better performance of sql .we have 32bit SQL servers and want to make sure we have them configured optimally for performance. We are unable to add any indexes etc, can any one suggest the options
To be brutally honest, you're pretty much toast. Performance is in the code and indexes. If you can't touch either, then there will be little ROI on tweaking anything at the server level.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2016 at 6:42 pm
ramyours2003 (10/4/2016)
HII need recommendations for better performance of sql .we have 32bit SQL servers and want to make sure we have them configured optimally for performance. We are unable to add any indexes etc, can any one suggest the options
Like Jeff said, "Performance is in the code and indexes." Without control of that, you're in a bind.
At the server level, some of the more important things include:
Have plenty of memory and configure your memory settings correctly.
SQL Server needs and loves memory. Configure your server's min & max settings accordingly.
Make sure your disks are configured correctly and you have plenty of I/O.
This includes having separate, quality drives for your data files, log files, tempdb, backup drive, OS, programs, etc.
Avoid automatic data & log file growth.
Auto growth kills performance, size your files according.
-- Itzik Ben-Gan 2001
October 5, 2016 at 10:34 pm
Thanks to all for your suggestions ..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply