Click here to Skip to main content
13,195,564 members (58,031 online)
Rate this:
Please Sign up or sign in to vote.
See more:

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

Posted 11-Oct-12 13:28pm
Wes Aday 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.
digimanus 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.
chowdary2005 14-Oct-12 23:12pm

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

digimanus 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)
chowdary2005 15-Oct-12 4:57am
I am already having a primary key in my table with RowID autogenerated.
digimanus 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?
chowdary2005 15-Oct-12 6:01am
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.

digimanus 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
chowdary2005 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.

digimanus 17-Oct-12 7:39am
well than make your primary key your autogenerated rowid and your ACCTNumber. You will see a performance increase.
djj55 18-Oct-12 12:27pm
Are you backing up and truncating your transaction log?
chowdary2005 18-Oct-12 23:37pm
No. i am not.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.

Keep helping nature..

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171019.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100