| Author |
 Topic  |
|
sr_erick
Senior Member
USA
1318 Posts |
 Posted - 27 October 2003 : 18:04:35
|
| Just curious. Is there any way to limit it's cpu usage? This is on MSSQL 2000. Thanks. |
Erick
Snowmobile Fanatics
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 28 October 2003 : 04:00:53
|
No, but then it shouldn't really be using any more cpu than the rest of the forum.
are you referring to dllhosts cpu usage or the sql servers? |
 |
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 28 October 2003 : 08:44:09
|
| SQL Server. Whenever someone does a search it maxes out the CPU on it for the length of the search. |
Erick
Snowmobile Fanatics
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 28 October 2003 : 09:07:03
|
| what sort of spec ram/cpu is your sql server ? is it on the same machine as IIS, this is not normal behaviour, my sql server never gets maxed at 100% and it is running a lot of databases, many of them snitz forums. |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 28 October 2003 : 15:52:20
|
| 1.2 gHz Pentium III dedicated SQL server. 2 gb's of RAM. My database is about the only thing on it right now that uses it. IIS is on another machine. |
Erick
Snowmobile Fanatics
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 28 October 2003 : 17:04:40
|
| not sure then, it certainly shouldn't use that kind of cpu useage, have you tried running the sql profiler to see if adding any indexes helps. |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 28 October 2003 : 17:29:37
|
| No, you can do that from the Management Console? I will give it a try tonight. |
Erick
Snowmobile Fanatics
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 28 October 2003 : 19:41:25
|
| yes, basically you need to leave a trace running for several hours and save it to a file, you can then load the file into the profiler and see what it says. |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 28 October 2003 : 20:36:30
|
| What do I need to log for specifically? Just transactions? |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 28 October 2003 20:37:47 |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 29 October 2003 : 02:22:22
|
| Well I ran it for about an hour and a half but the collection file filled up too quick. I am going to limit it to only the things I really need tommorow night and do it again. I did the profiler thing and selected which tables I wanted it to examine and it told me something about a 22% speed improvement form the information that was collected. I applied the new indexes. Is this something I should be running on like say a weekly basis? Either way, I will give it another whirl tommorow night for about a 6 hour period and see what happens. |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 29 October 2003 02:29:59 |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 29 October 2003 : 03:32:09
|
| once it has been put through the profiler a few times, you shouldn't need to do it again, howvere, depending on the 'fill factor' used when creating the indexes, you should re-index your topic/reply tables every now and then. |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 29 October 2003 : 11:52:00
|
| if your post count rises at a fairly high rate (not just one or two a day) then yes it is a good idea |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 29 October 2003 : 11:56:51
|
Someone (I think ruirib ?) did a search.asp page mod specifically for Sql Server that performs alot better, try a search  |
The UK MkIVs Forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 29 October 2003 : 12:39:10
|
| Interesting...I thought I tried this one before but it didn't make any difference at all. Now when I test it, there was a big difference. From one search on the old it was 17.92 seconds with a much more broad results listing, two pages. With the new one and the exact same search terms it was 8.2 seconds with must more narrowed results, under one page. I think the re-indexing in combination with this new search helped. I am going to re-index a couple more times and call it good for now. |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 29 October 2003 12:44:14 |
|
|
|
Topic |
|
Topic Locked
