SQL Query is Slow - Service Restart required

Question

4.00/5 (1 vote)

See more:

Hello,

We are having a data base with around 600,000 rows running on SQL server 2008. We are running a LAN application, bought recently from market of very high speed server. But some times SQL Server is getting very slow. For ex:- on Monday morning when we return to work [I assue that sql server is idle for about 48 hours... and may be services are down or some thing...]. If we restart the SQL server Service from control panel again it is working as it should. Can anybody help me in this regard????

This is only a database server, except this we are nothing running on this machine.

Configuration :- Multi core processor
Ram : - 32 GB

LAN Card :- 10/1000

LAN :- Optical fibre

chowdary.

Posted 11-Oct-12 13:28pm

Charles Shob

Add a Solution

Comments

[no name] 11-Oct-12 19:59pm

Did you profile the server to see what is causing the "slow down"?

Mehdi Gholam 12-Oct-12 2:27am

You really have to quantify "slow" between the two states. 600k rows is nothing for a 32gb sql server box.

Herman<T>.Instance 12-Oct-12 4:46am

true,but wthat are states if the indexes? does he use non clustered or clustered indexes, and so on After a reboot indexes are reorganised.

Charles Shob 14-Oct-12 23:12pm

digimanus,

So what would you sugget??? Clustedred or non clustered index????

Regards...

Herman<T>.Instance 15-Oct-12 4:35am

no non clustered indexes. And the clustered index is your primary key. So maybe you have to add fields or change your primary key. (don't know you db diagram)

Charles Shob 15-Oct-12 4:57am

digimanus,
I am already having a primary key in my table with RowID autogenerated.
Regards..

Herman<T>.Instance 15-Oct-12 5:19am

and what are you looking for in that tables when using queries, views, functions or stored procedures or ORM's?

Charles Shob 15-Oct-12 6:01am

digimanus,
The following is my query.

Select PtName, AcctNumber, Status from Accout where AcctNumber = 'RTEDFGER000' and Duplicate =0;

The above selectr is taking 11 seconds to fetch.

Regards...

Herman<T>.Instance 15-Oct-12 6:04am

if you have no Identity field, which fields make the row unique? at least AcctNumber should be in you Primary Key and maybe a datetime field.
Is Duplicate a bit field that can be NULL? --> and isnull(duplicate, 0) = 0

Charles Shob 16-Oct-12 23:34pm

Yes digimanus. Actually i do not have enough field to make as Primary key. Because, The data is received from out side party and we are migrating that to our database. Actually from Client point of view AcctNumber is unique. But, Client is sending those records twice, thrice or even more when ever he receives payments from his principal. So, from our DB point of view we cannot use acctNumber as Primary Key. Coming to Duplicate, is some scenarios client is sending same records which are previously received. So, rather than deleting them for future accountability we are make tham as Duplicate records. This is a bit field. Hope, i explined myself clearly.

Regards...

Herman<T>.Instance 17-Oct-12 7:39am

well than make your primary key your autogenerated rowid and your ACCTNumber. You will see a performance increase.

Corporal Agarn 18-Oct-12 12:27pm

Are you backing up and truncating your transaction log?

Charles Shob 18-Oct-12 23:37pm

No. i am not.

1 solution

Add a Solution

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)

Aadhar Joshi · Answer 1 · 2012-10-18T02:14:00

Please optimize your database, stored procedure because such configuration runs good for just only 6000000 rows.. as well can u use profiler to check where exactly it takes time and paste those queries here..

As well as u can use Transaction Isolation Level OR Lock Hints to improve performance.

Regards,
Keep helping nature..