Extreme slow SQL Server 2005 and high CPU usage - Casting of values

Hello sql and .net gurus :-)
I have a problem with my websitewww.eventguide.it. It's completly developed under .NET 2 and SQL Server 2005 Express. My problem is the folowing:
The server is a Intel 3Ghz HT processor with 1GB Ram. No other page on the running system is a CPU consuming site. We optimized the SQL statements, the code, the caching and many other parts of the website (pooling on SQL access), but the SQL Server uses about 50% to100% of the CPU and about 400MB RAM all the time. The whole site seems to be very, very slow. In fact there are many of SQL operations on every page request, but we cache a lot of them in different ways (page output caching, application caching). So I don't understand we have so much performance problems. Any suggestions for optimised code in general? I read nearly all of the MS .NET performance papers - but real world experience is the missing part :-)

It is better to cast the values of a SQL reader like this
Dim String1 as String = Ctype(DataReader.item(0), String)
Dim Integer1 as Intger = Ctype(DataReader.item(1), Integer)
or like this
Dim String1 as String = DataReader.item(0)
Dim Integer1 as Intger = DataReader.item(1)

Thanks a lot for your help!
FOX

I visited your site, and honestly I cant answer your question. Your SQL CPU usage seems way off, but the problem appears to be bandwidth entirely.

I pinged your site, and traced it with good solid pathways and quick responses. But going into the Foto's (photos) section resulted in pictures taking an extremely long time to load even after the page was rendered. Why type of up-pipe is that server pushing?

|||What do you mean with: "Why type of up-pipe is that server pushing?"|||

Sorry, horribly formed question:

How much bandwidth is available to the server over your connection, dedicated to "upload"?

|||The upload of the server is about 10Mbit - enough to deliver requests in a fast way.
Well - look now the speed of the entire site. Today I changed the SQL database index of a 2.000.000 rows counting table (counter for images and other things) and from one moment to another I reduced CPU usage dramatically! Now I try to optimize also other table indexes to increase the speed further.
Any suggestions to handle indexes in a good way?
Thanks for your time!!!|||I had close to the same issue. I added the port to the IP addresss in the connection string. Don't ask me why this fixed the issue, but it did. For the RAM issue, I also had this. The server would go up to almost 500-600MB of ram.. and it barely had anything in it!!

So I made a batch script to restart the server, and setup a windows scheduled task to run it every other day.

Here is my batch:
net stop MSSQLSERVER
echo y
net start MSSQLSERVER

net stop hmailserver
net start hmailserver

Basically, this will restart your SQL server. I do this at about 3AM. It keeps the server running top notch as far as I know.

I had to add hmailserver in there because if you don't restart it also, it for some reason will fail to verify accounts. If you don't have hmail, remove it.|||Because if you don't specify the port, it first tries to contact the SQL Server on one of the standard ports, and if it's firewalled, or blocked, then it takes a bit for it to realize it and then switch over. Also, if you a fairly large latency between the web and sql server, it can reduce the time significantly because there is one less connection that needs to be established, and with a normal TCP/IP connection that does a 3-way handshake to start up, it's many times the latency between the two machines.

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon Rista
Have you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>
|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com

>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's[vbcol=seagreen]
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
suggested I post[vbcol=seagreen]
perform a process on[vbcol=seagreen]
through Query
>
>.
>

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon RistaHave you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com
>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
>> I posted this on the ADO.NET group, and someone there
suggested I post
>> it here too. I appreciate any tips.
>> I'm using ADO.NET in a windows service application to
perform a process on
>> SQL Server 2000. This process runs very quickly if run
through Query
>
>.
>

extreme help with query of 2 tables into 1 long table

2 tables user id is key
Table (A) 05_Users
user_id | first_name |last_name|title|dept
64|John|Doe|director|cis
65|Jane|Doe|ceo|fina
and
Table(B) 05_Users_Details
user_id | detail_cd | group_cd | detail_value
64|06|awdM0|null
64|07|awdD0|null
64|2005|awdY0|null
64|FreeText|awdTxt0|I enjoy work
64|10|awdM1|null
64|09|awdD1|null
64|2004|awdY1|null
64|FreeText|awdTxt1|still here
64|local|pfmLEVL1|null
64|natial|pfmLEVL1|null
64|aapm|pfmAAPM1|null
64|FreeText|pfmFREE1|profess
65|etc
I'm trying to creat a query that will give me all user information into one
long table with the group_cd as a 'column title' and detail_cd as 'column
value', but if it finds the 'column value' of FreeText then detail_value
should be 'column value'.
So the table would look like this.
user_id | first_name
|last_name|title|dept|awdM0|awdD0|awdY0|awdTxt0|aw dM1|awdD1|awdY1|awdTxt1|pfmLEVL1|pfmLEVL1|pfmAAPM1 |FREE TEXT
64|John|Doe|director|cis|06|07|2005|I enjoy work|10|09|2004|still
here|local|natial|aapm|profess
65|Jane|Doe|ceo|fina etc
Some users have more information than other users and in these cases the
'column value' can be left as blank.
I don't need a webpage, but if it will help, will use.
IF YOU HAVE A BETTER WAY TO GET ALL THE INFORMATION ANY SUGGESTIONS WOULD BE
GREAT!
On Tue, 7 Jun 2005 10:26:02 -0700, BIGLU wrote:
(snip)
>IF YOU HAVE A BETTER WAY TO GET ALL THE INFORMATION ANY SUGGESTIONS WOULD BE
>GREAT!
Hi BIGLU,
First: The format you used to describe your data makes it very hard to
read and understand and almost impossible to reproduce. For future
postings, please include CREATE TABLE and INSERT statements for table
structure and sample data, as described here: www.aspfaq.com/5006.
Second: What you're trying to achieve looks like a pivot, or cross-tab
query. The front end/presentation layer is actually the best place for
that task. If you have to do it on the server, then try if you can adapt
the following to your needs:
SELECT u.UserID,
MAX(CASE WHEN d.DetailCD = 'awdM0' THEN d.detailValue END) AS
awdM0,
MAX(CASE WHEN d.DetailCD = 'awdD0' THEN d.detailValue END) AS
awdD0,
....
FROM Users AS u
INNER JOIN UserDetails AS d
ON d.UserID = u.UserID
GROUP BY u.UserID
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
When you say "front end/presentatio layer" can I import and do in access or
excel? If so, can you give me a link where I can do this in any of these? I
know how to export (I think I do.lol), but I'll need help with crosstab, etc.
Or is this something I can do in asp.net? or a third party component?
Thanks
"Hugo Kornelis" wrote:

> On Tue, 7 Jun 2005 10:26:02 -0700, BIGLU wrote:
> (snip)
> Hi BIGLU,
> First: The format you used to describe your data makes it very hard to
> read and understand and almost impossible to reproduce. For future
> postings, please include CREATE TABLE and INSERT statements for table
> structure and sample data, as described here: www.aspfaq.com/5006.
> Second: What you're trying to achieve looks like a pivot, or cross-tab
> query. The front end/presentation layer is actually the best place for
> that task. If you have to do it on the server, then try if you can adapt
> the following to your needs:
> SELECT u.UserID,
> MAX(CASE WHEN d.DetailCD = 'awdM0' THEN d.detailValue END) AS
> awdM0,
> MAX(CASE WHEN d.DetailCD = 'awdD0' THEN d.detailValue END) AS
> awdD0,
> ....
> FROM Users AS u
> INNER JOIN UserDetails AS d
> ON d.UserID = u.UserID
> GROUP BY u.UserID
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 9 Jun 2005 09:42:06 -0700, LU wrote:

>Hugo,
>When you say "front end/presentatio layer" can I import and do in access or
>excel?
Hi LU,
I must admit that I have little expertise with respect toi front end
applications. But as far as I know, Access has some builtin
functionality to create a cross-tab table (look up "TRANSFORM" and
"PIVOT" in the online help, or use the crosstab query wizard). And Excel
can do crosstab reports as well.

>Or is this something I can do in asp.net?
Probably, but you'd better ask in a group for asp.net! <g>

>or a third party component?
Some third party applications that might help you generate the crosstab
at the server (though I still recommend against it!) may be found near
the end of this page: http://www.aspfaq.com/show.asp?id=2462
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)