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.
| Author |
Topic |
|
Heg
Starting Member
4 Posts |
 Posted - 2012-07-19 : 05:41:15
|
| Hello,I have got a perfomance problem. We are utilizing embedded systems for our program execution. The SQL Server 2005 Express Version runs on these machines. Unfortunately they don´t have a lot of processing power, hence they do have there limits. Lately new requirements came up, so that yet another task works with the database. The performance is now so poor, that some important queries get lost on the way.. I believe they fall for a timeout.The new task is just reading important values all 10 minutes, writes them into a data file and sends it over to another machine. The query it uses however is not supported by any indexes on the table. My idea is now to provide indexes to enhance the overall performance of the task, minimizing the time it is active on the database.Since the size limit of express databases is 4 GB, I thought about moving those additional indexes into a new file group. Is that a smart idea, or did I missunderstand something?Thanks in advance! |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-07-19 : 06:32:14
|
HAve you considered upgrading to 2008R2 express? Goes up to 10GB limit.Not sure if you *Can* add more filegroups in express - never used it.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
 |
|
|
Heg
Starting Member
4 Posts |
Posted - 2012-07-19 : 06:46:43
|
quote:
Originally posted by Transact Charlie
HAve you considered upgrading to 2008R2 express? Goes up to 10GB limit.Not sure if you *Can* add more filegroups in express - never used it.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
I think it does work, but you can´t use file groups for partitioning as far as I know. Like using more than one file for a big table to split it up over more hard drives.I didn´t consider that, because I didn´t know :-P That is good to know, thanks for the tip.But is anything wrong with my solution to put indexes into a sperate file? Another quick question: It is my understanding that I should create indexes to support queries. For example I should create an index over the columns used in the WHERE part of a SELECT statement, right? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-19 : 07:25:36
|
| >> For example I should create an index over the columns used in the WHERE part of a SELECT statement, right?Depends on the requirements but it's not a bad start. You have to test and look at the query plan.Not that adding indexes will slow down inserrts and updates.If you are pushed for space then it will only get worse. I would think about reducing the data held or upgrading to something that has a longer lifetime otherwise yoou will end up with maintenance issues.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Heg
Starting Member
4 Posts |
Posted - 2012-07-19 : 07:44:54
|
quote:
Originally posted by nigelrivett
>> For example I should create an index over the columns used in the WHERE part of a SELECT statement, right?Depends on the requirements but it's not a bad start. You have to test and look at the query plan.Not that adding indexes will slow down inserrts and updates.If you are pushed for space then it will only get worse. I would think about reducing the data held or upgrading to something that has a longer lifetime otherwise yoou will end up with maintenance issues.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Thanks for the answer. Well, I think that we are already experiencing maintenance issues.. The machines used are very limited and over time many people have come up with more and more ideas what the machine could do in addition to the tasks it was designed for. So yeah, that leads to problems right now. I think it is time for a big upgrade! Not just software, but hardware as well.Anyway it would be great if the current problems could be fixed by simply indexing better. The problem already made clear that there is an upgrade needed to the onces in charge, so I hope they will refer from adding new stuff for now. |
|
|
|
Heg
Starting Member
4 Posts |
Posted - 2012-07-20 : 02:15:09
|
| Quick update: I implemented the new indexes and the execution is now, as I hoped, extremly fast. Now I have to analyse whether or not the performance issues are resolved over the next week.For anyone who´s interested: It is possible to move whole tables or indexes into a different file in SQL Server Express. Only partitioning is not supported. A good tutorial can be found here:http://www.mssqltips.com/sqlservertip/1112/filegroups-in-sql-server-2005/ |
|
|
|
|
|
|
|
|
Poor Performance