Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
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 14:28pm
Comments
Wes Aday at 11-Oct-12 19:59pm
   
Did you profile the server to see what is causing the "slow down"?
Mehdi Gholam at 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 at 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 at 14-Oct-12 23:12pm
   
digimanus,
 
So what would you sugget??? Clustedred or non clustered index????
 
Regards...
digimanus at 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 at 15-Oct-12 4:57am
   
digimanus,
I am already having a primary key in my table with RowID autogenerated.
Regards..
digimanus at 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 at 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...
digimanus at 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 at 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...
digimanus at 17-Oct-12 7:39am
   
well than make your primary key your autogenerated rowid and your ACCTNumber. You will see a performance increase.
djj55 at 18-Oct-12 12:27pm
   
Are you backing up and truncating your transaction log?
chowdary2005 at 18-Oct-12 23:37pm
   
No. i am not.

1 solution

Rate this: bad
good
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.
 
Regards,
Keep helping nature..
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2014
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