SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Production Nightmare
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simsekm
Starting Member

18 Posts

Posted - 04/13/2012 :  03:00:28  Show Profile  Reply with Quote
Hi,

We use SQL Server 2005 Express at production area. I develop production software which reads and writes to SQL. I'm new user for SQL and i created a table according to advices which i found at internet.

I have serious problem about "UPDATE" query. This my table :

 Column Name          Type         Feature
  ID                 bigint     Index-Clustered
  MifSeriNo          nchar(14)  Unique Key-NonClustered
  3x Some Columns    Date/time        -
  108x Some Columns  Binary(16)       -
  8x Some Columns    nchar(255)       -


So totally there are 121 Columns at my table and 12 client softwares connected to table. Our production has 2 steps and my problem is at first step. First of all i check whether if Serial Number of product is at table or not(I stored serial numbers of product at MifSeriNo Column ). Then if serial number of product is not at table then I UPDATE value of "MifSeriNo" Column (that it's Unique Key-NonClustered) according to serial number of product. This step works at first 2-3 hours perfectly(UPDATE query estimates less than 1 second when it works perfectly). But after 2-3 hours, update query estimates nearly 5 or 6 seconds.

When it works badly, i stop all client software. I restart SQL service and even i restart to SQL Server computer. Then i execute
"Rebuild All" command from SQL Server Management software. When i do these steps, sometimes SQL turns out to normal condition or sometimes i have to do these steps 2-3 times.

This morning i had same problem. I did corrective steps which i explained above. It worked normally. After 10 minutes i checked fragmentation of ID and MifSeriNo column, ID was 0 and MifSeriNo was 51(i think it's expected).

I need your dear advices. May be we should buy SQL Server instead of Express Edition?

PS: Sorry for my bad English

Best Regards


Edited by - simsekm on 04/13/2012 09:56:19

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/13/2012 :  03:15:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try to reorganize your primary key with the ONLINE option.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

simsekm
Starting Member

18 Posts

Posted - 04/13/2012 :  03:27:58  Show Profile  Reply with Quote
I will try it. But as i told although i reorganize index column(ID) manually, sometimes it doesn't return to normal condition. ONLINE option does extra something?

Regards
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/13/2012 :  05:00:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It allows for normal operation while reorganizing the index.
However, I am not sure this option is available for Express Edition.

Have a look in Books Online.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 04/13/2012 :  07:19:57  Show Profile  Visit mfemenel's Homepage  Reply with Quote
2 things stood out for me in your post, First, your table has 121 columns. It's very rare that I've seen large tables like that end up being the right approach. Can that be broken down and further normalized (that's long term). Second, that you're running this on 2005 express. 2005 express is limited to 1 CPU, 1 GB of memory for the buffer pool and 4 GB max size. How big is the table in terms of MB? you can use sp_spaceused to find this. Are those 12 clients concurrently connected (meaning all at once) or is it 12 possible connections at various times of day?
It really sounds like you've reached the limitations of your server, your database edition or your design... or a combination of all 3 of those items. If you can post more details we can help you refine your solution further.

Mike
"oh, that monkey is going to pay"
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 04/13/2012 :  07:20:29  Show Profile  Visit mfemenel's Homepage  Reply with Quote
2 things stood out for me in your post, First, your table has 121 columns. It's very rare that I've seen large tables like that end up being the right approach. Can that be broken down and further normalized (that's long term). Second, that you're running this on 2005 express. 2005 express is limited to 1 CPU, 1 GB of memory for the buffer pool and 4 GB max size. How big is the table in terms of MB? you can use sp_spaceused to find this. Are those 12 clients concurrently connected (meaning all at once) or is it 12 possible connections at various times of day?
It really sounds like you've reached the limitations of your server, your database edition or your design... or a combination of all 3 of those items. If you can post more details we can help you refine your solution further.

Mike
"oh, that monkey is going to pay"
Go to Top of Page

simsekm
Starting Member

18 Posts

Posted - 04/13/2012 :  09:39:31  Show Profile  Reply with Quote
Hi,

This is information of Table size :

Rows		Reserved	Data		  Index Size      Unused
146000     	591448 KB	584008 KB	   7288 KB	   152 KB


And 12 clients concurrently connected to this table. What do you think?

What can be diffreneces if we upgrade to MS SQL 2008 Enterprise?
Go to Top of Page

simsekm
Starting Member

18 Posts

Posted - 04/14/2012 :  06:06:29  Show Profile  Reply with Quote
Nothing else?

Edited by - simsekm on 04/14/2012 06:08:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000