May 7, 2010 at 6:52 am
I need to run SQL 2005 Developer Edition at a lower priority on my workstation. I need to build quite large indexes here, and also need to be able to work on other things while the disk churns away. I am optimizing queries and indexes, but do not have enough space on a Dev server to accomplish it anywhere else at this time.
As it is now, the performance is killing me, trying to wait on response from various apps, such as Firefox, or MS Word, while SQL builds the index. I know this will take a long time, but if I can get other things done in that time it isn't so bad.
Apparently SQL Server thinks it is too important to allow the changing of its priority from Task Manager. Can I adjust the service startup to change the priority?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 7, 2010 at 7:16 am
One thing you may want to do is change the memory allocation of your SQL instance. By default it will claim as much memory as you will allow it.
On my workstation at home I run a dev edition instance of SQL2K8 with 768MB allocated and I'm able to have Visual Studio, several browsers with multiple tabs, multiple IM clients, and a full-screen video game (yay dual monitors) all open at the same time. I have 4GB of RAM on a 32-bit system with a Core 2 Duo, so I'm not really running a super machine or anything.
To change your memory allocation, right click your instance name in SQL Server Management Studio, go to Memory in the left-hand menu then change the Maximum Server Memory. Restart the instance and all should be good.
May 7, 2010 at 7:58 am
It's already limited to 1G, as I only have 4G of RAM here also. The problem is the OS is giving SQL Server disk activity priority over everything else, and while that would be noble for a server, it is not necessary here and just frustrates me because I have to wait sometimes minutes before I can get an app to respond to me.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 7, 2010 at 11:09 am
Hmm, I'd hate to say it ... but it might be a good idea for you to consider having a dedicated disk drive for SQL use. In some of my workstations in the past that is what I've done simply due to size restrictions. Your page file might also be getting a healthy amount of usage, but separating the SQL files from the rest of your OS would probably be a good start... less contention for resources.
May 7, 2010 at 12:52 pm
Sure, I'd LOVE to have dedicated disk(s), but I'd also like to have space on a server so I don't have to do this on my company-issued notebook.
But this is the reason for this thread.
I might need to start another thread, as my 180M record table has been going for almost 7.5 hours now, to build a single-field clustered index. I'm thinking this is unreasonable since copying all of that data, across the network, only took about 2 hours.
OK, so it's gotta pull out that one field and reorder it, then go back and reorder the table per the index order. I just can't see it taking this long.
And on top of that, I created a non-clustered index on 4 fields of this table, as an experiment, yesterday, and it took just over an hour.
Getting ready to kill this index build, and after the rollback completes, sometime tomorrow, try creating a dupe of this table, putting the clustered index on it, then populating it with the data from the old table.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 7, 2010 at 1:18 pm
If you are loading the table from an outside source across the network, it would probably be faster to do it this way:
1. Truncate the table and drop all non-clustered indexes, but leave the clustered index.
2. Use DTS or SSIS to load the table in order by the clustered index. Make sure you keep the batch size fairly small, 1,000,000 rows or less, so that you don't generate a huge transaction.
3. Create the non-clustered indexes.
Also, you should consider running this as a SQL Agent job at night so that all the disk activity doesn't keep you from working.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply