Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Production Nightmare

Author  Topic 

simsekm
Starting Member

20 Posts

Posted - 2012-04-13 : 03:00:28
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-13 : 03:15:39
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

20 Posts

Posted - 2012-04-13 : 03:27:58
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

30421 Posts

Posted - 2012-04-13 : 05:00:49
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

1421 Posts

Posted - 2012-04-13 : 07:19:57
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

1421 Posts

Posted - 2012-04-13 : 07:20:29
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

20 Posts

Posted - 2012-04-13 : 09:39:31
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

20 Posts

Posted - 2012-04-14 : 06:06:29
Nothing else?
Go to Top of Page
   

- Advertisement -