CPU Pegging

I have
Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
I am noticing the processors are pegging quite a bit, cpu for the
sqlservr.exe process is running around 50% constantly with lots of pegs up
to 100%. Is there something I should be looking at before I throw more
hardware at it? Is this normal, knew sql server sucked memory but not the
cpu. The memory for the process is around 1.6 gig. It is a production
machine so it is hit pretty hard during the day.
John"John Cantley" <jcstrider@.hotmail.com> wrote in message
news:uc2glj1vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I have
> Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> I am noticing the processors are pegging quite a bit, cpu for the
> sqlservr.exe process is running around 50% constantly with lots of pegs up
> to 100%. Is there something I should be looking at before I throw more
> hardware at it? Is this normal, knew sql server sucked memory but not the
> cpu. The memory for the process is around 1.6 gig. It is a production
> machine so it is hit pretty hard during the day.
> John
>|||Well, you might want to look at profiler and see if there are specific
queries that can be better tuned or rewritten
"John Cantley" <jcstrider@.hotmail.com> wrote in message
news:u4sV1n1vDHA.1424@.tk2msftngp13.phx.gbl...
> "John Cantley" <jcstrider@.hotmail.com> wrote in message
> news:uc2glj1vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > I have
> >
> > Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> >
> > I am noticing the processors are pegging quite a bit, cpu for the
> > sqlservr.exe process is running around 50% constantly with lots of pegs
up
> > to 100%. Is there something I should be looking at before I throw more
> > hardware at it? Is this normal, knew sql server sucked memory but not
the
> > cpu. The memory for the process is around 1.6 gig. It is a production
> > machine so it is hit pretty hard during the day.
> >
> > John
> >
> >
>|||Oh, and as an aside, if your pegging CPU, one thing to look for is cursors.
"John Cantley" <jcstrider@.hotmail.com> wrote in message
news:u4sV1n1vDHA.1424@.tk2msftngp13.phx.gbl...
> "John Cantley" <jcstrider@.hotmail.com> wrote in message
> news:uc2glj1vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > I have
> >
> > Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> >
> > I am noticing the processors are pegging quite a bit, cpu for the
> > sqlservr.exe process is running around 50% constantly with lots of pegs
up
> > to 100%. Is there something I should be looking at before I throw more
> > hardware at it? Is this normal, knew sql server sucked memory but not
the
> > cpu. The memory for the process is around 1.6 gig. It is a production
> > machine so it is hit pretty hard during the day.
> >
> > John
> >
> >
>|||| I have
|
| Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
|
| I am noticing the processors are pegging quite a bit, cpu for the
| sqlservr.exe process is running around 50% constantly with lots of pegs up
| to 100%. Is there something I should be looking at before I throw more
| hardware at it? Is this normal, knew sql server sucked memory but not the
| cpu. The memory for the process is around 1.6 gig. It is a production
| machine so it is hit pretty hard during the day.
--
Hi John,
The most important question you need to ask yourself as a DBA: are your
users complaining about slowness or lack of responsiveness? If not, then
things are fine. The old adage goes, "if it ain't broke, don't fix it."
High CPU utilisation, as long as it's not always running at 100%, is
actually a GOOD THING. That means that the CPU, the most expensive
component of a computer system, is doing it's job. If you see CPU spiking
to 100% on occasion, that means there are no bottlenecks in your computer
system.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Eric,
Ok just wondering then.
I have a smaller server that runs our website content and customer order
data, I coded the application and made heavy use of stored procedures,
triggers and such. The cpu is typically under 10% and mem usage is about 1/2
gig and connections are usually in the 50 range. The site is heavily used
for order tracking and other stuff.
The one that is causing me problems is a bigger faster machine with two
processors, I have no control of the frontend application that is used in
our production process, it uses 0 stored procedures, 0 triggers, nothing
nada zilch. I am thinking that since this is true then I am not getting a
good rate of the sql statements compiled, since so many varying queries are
hitting it. I have also noticed 3 connections opening just upon startup of
the application. Couldn't these factors account for the higher cpu usage. I
preformed a backup on it and noticed that the cpu usage dropped to a lower
rate. I am thinking that maybe the transactions are left dangling by the 3rd
party product and the backup process terminates them.
john
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:pfOA846vDHA.3532@.cpmsftngxa07.phx.gbl...
> | I have
> |
> | Dell 6450 with 2 Pentium 3 Xeon 700mhz processors and 2 gig of ram
> |
> | I am noticing the processors are pegging quite a bit, cpu for the
> | sqlservr.exe process is running around 50% constantly with lots of pegs
up
> | to 100%. Is there something I should be looking at before I throw more
> | hardware at it? Is this normal, knew sql server sucked memory but not
the
> | cpu. The memory for the process is around 1.6 gig. It is a production
> | machine so it is hit pretty hard during the day.
> --
> Hi John,
> The most important question you need to ask yourself as a DBA: are your
> users complaining about slowness or lack of responsiveness? If not, then
> things are fine. The old adage goes, "if it ain't broke, don't fix it."
> High CPU utilisation, as long as it's not always running at 100%, is
> actually a GOOD THING. That means that the CPU, the most expensive
> component of a computer system, is doing it's job. If you see CPU spiking
> to 100% on occasion, that means there are no bottlenecks in your computer
> system.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:
> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||artunc,
lose the parentheses around your column list in your select statement
-st|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...
The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:

> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>
|||artunc,
lose the parentheses around your column list in your select statement
-st
|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:

> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>
|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:

> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table
.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list> )
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

counting based on bit flag

I have a table with an id field (int) and a bit flag. example below

id flag

1 true

1 true

1 false

1 true

2 true

2 false

I am looking for a query that will provide me the following results if possible

id true false

1 3 1

2 1 1

Any and all help is appreciated.

My efforts so far aren't worth sharing. I am looking for completely new approaches.

Thanks a ton

Use something like this:

Code Snippet

DECLARE @.MyTable table
( ID int,
Flag smallint
)

INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 1, 0 )
INSERT INTO @.MyTable VALUES ( 1, 1 )
INSERT INTO @.MyTable VALUES ( 2, 1 )
INSERT INTO @.MyTable VALUES ( 2, 0 )

SELECT
[ID],
True = sum( CASE Flag WHEN 1 THEN 1 ELSE 0 END ),
False = sum ( CASE Flag WHEN 0 THEN 1 ELSE 0 END )
FROM @.MyTable
GROUP BY [ID]

ID True False
-- -- --
1 3 1
2 1 1

|||Excellent!! Thank you so much!