How to tweak SQL Server 2008 to increase query performance?
-
I have a mvc3.0 application that is using a database on SQL Server 2008 R2 on a server machine that has Windows SQL Server 2008 OS. It seems that the performance is not as good as I expected to be at least when compared to MYSQL. Do you have any recommendations on how to improve the system or some best practices? Thank you in advance :)!
-
Answer:
There are a few best practices that you can follow to ensure good performance in SQL Server: Put it on a machine by itself Give it as much memory as you can and let it manage that memory on it's own Separate the storage of your database files so that data is separate from the logs and both are separate from the operating system Increase the cost threshold for parallelism to a number higher than the default of 5 Follow best practices with TSQL Use foreign key constraints, unique constraints, primary key constraints The best way to improve performance in SQL Server is to understand where your performance bottlenecks are in the first place. You can look at monitoring systems that will help you a lot there (my company Red Gate Software has a good one, especially for new people and smaller shops). You can also look at using the Dynamic Management Views to gather information. sys.dm_exec_query_stats will show you aggregate performance metrics of the queries in cache. sys.dm_os_wait_stats will show what things are waiting on in your system. From there you can start to determine what the issues are on your server.
Grant Fritchey at Quora Visit the source
Other answers
Become familiar first with SQL Server Profiler - a tool that can help you isolate slow performing queries. Try to rank & attack the worst offenders first. Does your database schema design feature single-column monotonically increasing primary keys, and foreign keys from dependent tables (I hope so...). If not, you need a DBA. Assuming you have foreign keys defined, are there indexes defined on those foreign key columns? Unfortunately, SQL Server performance tuning is not an "introductory" topic that can be easily covered in a forum like this - there are so many questions to ask about your design/application/hardware, and far too many things that can be overlooked but important factors. I would start following blogs at http://SQLSkills.comm, or at Brent Ozar's web site. Good luck.
Joel Askey
Related Q & A:
- Can I create a second filestream container on an existing SQL Server 2008 database without going offline?Best solution by Database Administrators
- Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008?Best solution by Database Administrators
- How to Restrict Database for One User in SQL Server 2008 R2?Best solution by Database Administrators
- How to add primary key from multiple table as a foreign key in a table in sql server 2008?Best solution by stackoverflow.com
- How to enable remote connections in SQL Server 2008?Best solution by Server Fault
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.