Re: Very Small CPU use by SQL Server 2000 on Win2003
From: Mike Epprecht \(SQL MVP\) (mike_at_epprecht.net)
Date: 11/12/04
- Next message: Don: "Re: Manually truncate a LOG file"
- Previous message: David Gugick: "Re: enterprise manager cost?"
- In reply to: Iain: "Very Small CPU use by SQL Server 2000 on Win2003"
- Next in thread: David Browne: "Re: Very Small CPU use by SQL Server 2000 on Win2003"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Nov 2004 23:35:44 +0100
Hi
Run profiler on the server an see at what rate the queries are coming
through.
Also, run Windows Performance monitor and check if you are not maxing out
your disks.
Generally on SQL Server installations:
High CPU = Disks keep up
Low CPU = Disks can't keep up
RAID-5 is not the best for databases, especially the transaction log. Logs
should be on a mirror as RAID-5 and sequential writes hate each other.
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Iain" <iain@teamsimms.com> wrote in message
news:cfaeb850.0411121428.661d90fd@posting.google.com...
> The problem...
> -----------------
> We recently go a new Windows 2003 Server, with SQL Server 2000 on it.
> It's a RAID 5 XEON 2.8GHz HT with 1GB ram....
>
> I use a Dell Latitude d600 for development that runs win2k pro and sql
> server 7 with 512 of RAM, that's dying under a load of 30+
> simulatenous windows.
>
> We run a complex set of web sites and web based applications.
>
> I wrote a basic VB6 application to run
> - An insert
> - retun the identity
> - run a second insert using that identity
> ....in 2 separate hits (executes) to the server.
>
> Running the VB app on my dev machine, looping thru the statements 1000
> times takes of the order of 26 secs to run.
>
> The same app, same data structure, VERY basic SQL Statement takes 35
> secs on the server.
>
> Both use a DSN refering to local (rather than network named) machine.
>
> BUT, what i do notice is, on my dev machine, CPU usage goes to 100%.
> On the server, sqlsevr.exe draws barely 2% of CPU.
>
>
> What I have tried so far....
> ------------------------
> I tried all of the following as a result of a variety of MS tips, and
> group postings...nothing gave me any better performance.
>
> I have upped the number of worker threads to 500
> Boosted the prioroty in windows task manager
> Flipped between tcp/ip and named pipes
> Set a min of 500mb memory occupation
> Parrellism: set to 1 cpu only
>
>
> What I am looking for...
> ---------------------
> Help! I have limited scope for change in the sql and data
> configuration as we have a MASSIVE web based app and i really don't
> want to spend the next 6 months rebuilding it.
>
> But, to my mind (tho i'm no SQL MVP), the fact that the queries and
> data structures are the same, and that the CPU occupation is so
> drastically different, tells me there's something in the Win2003
> management of tasks/proprities that is screwed.
>
> Any tips would be greatly appreciated. But i am one of those guys that
> knows something about everything....rather than everything about
> something...so, if you're gonna put a post on here that gets right
> into the inner works of SQL or Win2003, pls be quite deliberate in
> your explanations!
>
> I have included the sql statements and table definitions because i
> know someone will ask for them....but really, i think to go down that
> path is not the answer, and will cause me other issues. (NOTE: objIMS
> is a custom object that runs queries etc.)
>
> Thanks
>
> Iain
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[ims_inbox_custom_store]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[ims_inbox_custom_store]
> GO
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[ims_sent_custom_store]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[ims_sent_custom_store]
> GO
>
> CREATE TABLE [dbo].[ims_inbox_custom_store] (
> [msg_id] [int] IDENTITY (1, 1) NOT NULL ,
> [msg_from] [varchar] (100) NOT NULL ,
> [msg_to] [varchar] (100) NOT NULL ,
> [msg_body] [varchar] (1000) NOT NULL ,
> [spid_lock_id] [int] NULL ,
> [date_created] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ims_sent_custom_store] (
> [sent_id] [int] IDENTITY (1, 1) NOT NULL ,
> [msg_from] [varchar] (100) NOT NULL ,
> [msg_to] [varchar] (100) NOT NULL ,
> [msg_body] [varchar] (1000) NOT NULL ,
> [replied_to_inbox_id] [int] NOT NULL ,
> [date_created] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[ims_inbox_custom_store] WITH NOCHECK ADD
> CONSTRAINT [DF_ims_inbox_custom_store_date_created] DEFAULT
> (getdate()) FOR [date_created],
> CONSTRAINT [PK_ims_inbox_custom_store] PRIMARY KEY NONCLUSTERED
> (
> [msg_id]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[ims_sent_custom_store] WITH NOCHECK ADD
> CONSTRAINT [DF_ims_sent_custom_store_replied_to_inbox_id] DEFAULT (0)
> FOR [replied_to_inbox_id],
> CONSTRAINT [DF_ims_sent_custom_store_date_created] DEFAULT
> (getdate()) FOR [date_created],
> CONSTRAINT [PK_ims_sent_custom_store] PRIMARY KEY NONCLUSTERED
> (
> [sent_id]
> ) ON [PRIMARY]
> GO
>
> strSql = "insert into ims_inbox_custom_store(" & _
> "msg_to" & _
> ", msg_from" & _
> ", msg_body" & _
> ") values (" & _
> "'" & strTo & "'" & _
> ", '" & strFrom & "'" & _
> ", '" & Replace(strMsg, "'", "''") & "'" & _
> ")"
>
> lngId = 0
> lngId = objIMS.RunSqlReturnIndentity(strSql)
>
> strSql = "insert into ims_sent_custom_store(" & _
> "msg_to" & _
> ", msg_from" & _
> ", msg_body" & _
> ", replied_to_inbox_id" & _
> ") values (" & _
> "'" & strFrom & "'" & _
> ", '" & strTo & "'" & _
> ", '" & Replace(strRtnMsg, "'", "''") & "'" & _
> "," & lngId & _
> ")"
>
> lngRecs = objIMS.RunSqlReturnNumRecsAffected(strSql)
- Next message: Don: "Re: Manually truncate a LOG file"
- Previous message: David Gugick: "Re: enterprise manager cost?"
- In reply to: Iain: "Very Small CPU use by SQL Server 2000 on Win2003"
- Next in thread: David Browne: "Re: Very Small CPU use by SQL Server 2000 on Win2003"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
