November 17, 2009 at 8:11 am
I've got an application's database on an SQL 2000 (sp4) box. I was watching the performance of the server when I got an alarm on the processor usage. The dual processor (dual core) box was at 100% for about 15 mins. I could see the app's sql causing the problems. When I asked the dev for a bit more information I was told ' Its a large table it would cause a high processor usage' they further recommended that I could partition the database so that it only used a single processor so it wouldn't affect the other databases.
I've never heard of such a thing. I know we can partion processors on sql instances but I haven't heard of being able to do it against databases.
November 17, 2009 at 8:23 am
Processors can only be 'affinitised' per instance. What I would suggest is that you 'encourage' the developer to optimise his queries. Large table does not automatically mean high processor usage. Inefficient query on large table means high processor usage.
These may help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 8:27 am
I am not sure if there exists a feature in SQL Server 2000.
Database partitioning is available in SQL 2005 / 2008, but it does not partition the processor workload.
Resource Governor a new feature in SQL 2008 can give a similar functionality.
Eager to know if such a thing exists in SQL 2000. 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 17, 2009 at 8:38 am
GilaMonster (11/17/2009)
Processors can only be 'affinitised' per instance. What I would suggest is that you 'encourage' the developer to optimise his queries. Large table does not automatically mean high processor usage. Inefficient query on large table means high processor usage.
Agree with Gail, Many times a poorly written query or the appropriate Indexes / Stats for the long running query are missing or out of date.
I have seen a Database having the same issue of 100% CPU for over a hour, and finally turned out to be missing Indexes and stats.
Look at the following forum thread.
http://www.sqlservercentral.com/Forums/Topic817390-392-1.aspx#bm817424
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 17, 2009 at 12:23 pm
Bru Medishetty (11/17/2009)
Database partitioning is available in SQL 2005 / 2008, but it does not partition the processor workload.Resource Governor a new feature in SQL 2008 can give a similar functionality.
Neither of those limits processor usage by database. Table partitioning is just breaking a table into multiple chunks, possibly on different filegroups, resource governor controls resource usage based on which pool a connection is part of, that's based on properties of the connection at the time of login.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 12:44 pm
GilaMonster (11/17/2009)
Neither of those limits processor usage by database. Table partitioning is just breaking a table into multiple chunks, possibly on different filegroups, resource governor controls resource usage based on which pool a connection is part of, that's based on properties of the connection at the time of login.
I do agree with you Gail.
I was mentioning Database partitioning and Resource Governor to quote the similar features that the user may look at in SQL 2005 and 2008 (Though none of them enable to Partition by Processor usage)
Bru Medishetty (11/17/2009)
Eager to know if such a thing exists in SQL 2000. 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply