Sql server Database Optimization if using database continuously in server background windows service

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hello Friends

The problem is my database is very slow(SQL Server). I am using the DB continuously in background windows service for some operation. I cant stop the services moreover they need to be run continuously.
Now when I query my database using even simple select it takes 20 second to fetch 300 rows.

Please suggest.

Thanks in advance.

Posted 4-Sep-14 2:03am

darora85

Updated 4-Sep-14 2:46am

v3

Add a Solution

Comments

PhilLenoir 4-Sep-14 9:21am

Almost impossible to answer:
Is it on its own server (best approach)? If not, is something else hogging CPU, memory?
If it's on a VM, can you allocate more resources to the VM?
Give it more memory, SQL can pretty much use every byte you can throw at it
Do you have a maintenance plan on it and its databases (backup/truncate)?
Are its disks fragmented?
300 rows out of how many? Is the record source indexed, contain lots of joins? - read up on optimizing queries and using SQL Server performance tools to consider adding indexes. As a rule of thumb, all fields that you filter or join on should be indexed (which will slow down writes but spead up reads - if write performance becomes an issue, you can use transactions to delay indexing).

You can help yourself by looking at Task Manager or perfmon to see if it is CPU or I/O bound.

Mike Meinz 4-Sep-14 9:44am

Based on my experience, the most likely causes in priority order:
1. Lack of appropriate indexes
2. Using concatenated string SQL statements instead of SQLParameter class
3. Lack of memory on the SQL Server computer
4. A database design that requires overly complex SQL statements

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)