Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts

Friday, March 23, 2012

Radically different Transactions/sec behavior in 2005

Our Transactions/sec counter jumped quite a bit when we moved to SQL
Server 2005. The move coincided with increased load so we didn't think
anything of it until recently. Upon further review, the counter just
seems too high.
There was an article in SQL Server magazine a few years ago by Brian
Moran where he states, "Transactions/sec doesn't measure activity
unless it's inside a transaction. Batch Requests/sec measures all
batches you send to the server even if they don't participate in a
transaction." He goes on to say that Transactions/sec will be skewed
lower because it is a subset of Batch Requests/sec. (http://
www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html)
The article was written for SQL Server 2000. We conducted tests in
2000 and found what he said to be right on the money. SELECT
statements increased Batch Requests/sec, but not Transactions/sec.
UPDATE/INSERT/DELETE statements increased both in lockstep. Makes
perfect sense so far.
We conducted the same tests in 2005 and found a radically different
story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE
statements showed Transactions/sec skyrocket 2-10x more than Batch
Requests/sec for the duration of the statement. In other words, a
single transaction submitted by our application fires off
exponentially more transactions than the one we submitted. I was
unable to pinpoint exactly what these "hidden" transactions were
actually doing. Is this something that occurred in 2000 but simply
wasn't reported? Or is it new behavior in 2005?
While trying to answer these questions we noticed a second strange
behavior in 2005. When no queries are being executed the Transactions/
sec counter still jumps every six seconds like clockwork. And these
phantom transactions number in the thousands. We tried to use profiler
to capture what SQL was being executed, but nothing shows up in any
SQL Statement or Batch event. However, when we turned on the
SQLTransaction event we found it, sort of. An object called
GhostCleanupTask runs every six seconds causing thousands of
transactions. We don't know exactly what it is doing, but we noticed
that it ran consistently on some databases, but never on other
databases, even though both sets of databases have identical
properties.
So, all of this investigation leads me with three final questions.
1. What is behind all the extra transactions caught by perfmon when I
submit a single transaction?
2. What is GhostCleanupTask and why does it take so many transactions?
(And why does it only run on certain databases?)
3. If a potential customer asks for our Transactions/sec count, is it
accurate to give them the big number, knowing that our application is
only actually submitting a fraction of that? On the other hand, the
system apparently is actually doing that many transactions. (For
instance, on our production server during peak, Batch Requests/sec is
about 4,000, while Transactions/sec hits 26,000.)
I have searched high and low and been unable to find any references to
this particular situation. Any insight would be much appreciated.
Jerry Foster
CTO
Plexus Systems
www.plex.com
jfos@.plex.com
On Mon, 06 Aug 2007 15:28:11 -0700, Jerry <jfos@.plex.com> wrote:

>So, all of this investigation leads me with three final questions.
>1. What is behind all the extra transactions caught by perfmon when I
>submit a single transaction?
>2. What is GhostCleanupTask and why does it take so many transactions?
>(And why does it only run on certain databases?)
>3. If a potential customer asks for our Transactions/sec count, is it
>accurate to give them the big number, knowing that our application is
>only actually submitting a fraction of that? On the other hand, the
>system apparently is actually doing that many transactions. (For
>instance, on our production server during peak, Batch Requests/sec is
>about 4,000, while Transactions/sec hits 26,000.)
>I have searched high and low and been unable to find any references to
>this particular situation. Any insight would be much appreciated.
1. Don't know, very interested. I do know that 2005 breaks big SPs
into many smaller compilation units than did 2K, but I would not have
expected these to be visible as separate trans.
Actually, that may be a question - what if you count SP begins on both
systems?
2. Ghost traditionally does space recovery on tables that lack
clustered keys and have had deletes, and is generally suppressed in
profiler - or at least I've never seen it there.
3. The transaction number sounds bogus as now reported, I'd look for
something else, batches, SPs, etc.
In general, coming up with a baselining algorithm for SQLServer
performance management, has always been a challenge, and is best done
differently depending on local applications concerns.
Interested to see what else you learn about this.
Josh
|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise
www.beyondsql.blogspot.com
|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise
www.beyondsql.blogspot.com
|||In addition to the other posts:
I think that (at least in 7.0 and 2000) it was ghost cleanup that did shrink. So perhaps some of
your databases has autoshrink?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jfos@.plex.com> wrote in message
news:1186439291.965094.120600@.z24g2000prh.googlegr oups.com...
> Our Transactions/sec counter jumped quite a bit when we moved to SQL
> Server 2005. The move coincided with increased load so we didn't think
> anything of it until recently. Upon further review, the counter just
> seems too high.
> There was an article in SQL Server magazine a few years ago by Brian
> Moran where he states, "Transactions/sec doesn't measure activity
> unless it's inside a transaction. Batch Requests/sec measures all
> batches you send to the server even if they don't participate in a
> transaction." He goes on to say that Transactions/sec will be skewed
> lower because it is a subset of Batch Requests/sec. (http://
> www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html)
> The article was written for SQL Server 2000. We conducted tests in
> 2000 and found what he said to be right on the money. SELECT
> statements increased Batch Requests/sec, but not Transactions/sec.
> UPDATE/INSERT/DELETE statements increased both in lockstep. Makes
> perfect sense so far.
> We conducted the same tests in 2005 and found a radically different
> story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE
> statements showed Transactions/sec skyrocket 2-10x more than Batch
> Requests/sec for the duration of the statement. In other words, a
> single transaction submitted by our application fires off
> exponentially more transactions than the one we submitted. I was
> unable to pinpoint exactly what these "hidden" transactions were
> actually doing. Is this something that occurred in 2000 but simply
> wasn't reported? Or is it new behavior in 2005?
> While trying to answer these questions we noticed a second strange
> behavior in 2005. When no queries are being executed the Transactions/
> sec counter still jumps every six seconds like clockwork. And these
> phantom transactions number in the thousands. We tried to use profiler
> to capture what SQL was being executed, but nothing shows up in any
> SQL Statement or Batch event. However, when we turned on the
> SQLTransaction event we found it, sort of. An object called
> GhostCleanupTask runs every six seconds causing thousands of
> transactions. We don't know exactly what it is doing, but we noticed
> that it ran consistently on some databases, but never on other
> databases, even though both sets of databases have identical
> properties.
> So, all of this investigation leads me with three final questions.
> 1. What is behind all the extra transactions caught by perfmon when I
> submit a single transaction?
> 2. What is GhostCleanupTask and why does it take so many transactions?
> (And why does it only run on certain databases?)
> 3. If a potential customer asks for our Transactions/sec count, is it
> accurate to give them the big number, knowing that our application is
> only actually submitting a fraction of that? On the other hand, the
> system apparently is actually doing that many transactions. (For
> instance, on our production server during peak, Batch Requests/sec is
> about 4,000, while Transactions/sec hits 26,000.)
> I have searched high and low and been unable to find any references to
> this particular situation. Any insight would be much appreciated.
> Jerry Foster
> CTO
> Plexus Systems
> www.plex.com
> jfos@.plex.com
>
sql

Radically different Transactions/sec behavior in 2005

Our Transactions/sec counter jumped quite a bit when we moved to SQL
Server 2005. The move coincided with increased load so we didn't think
anything of it until recently. Upon further review, the counter just
seems too high.
There was an article in SQL Server magazine a few years ago by Brian
Moran where he states, "Transactions/sec doesn't measure activity
unless it's inside a transaction. Batch Requests/sec measures all
batches you send to the server even if they don't participate in a
transaction." He goes on to say that Transactions/sec will be skewed
lower because it is a subset of Batch Requests/sec. (http://
www.sqlmag.com/Article/ArticleID/26...rver_26380.html)
The article was written for SQL Server 2000. We conducted tests in
2000 and found what he said to be right on the money. SELECT
statements increased Batch Requests/sec, but not Transactions/sec.
UPDATE/INSERT/DELETE statements increased both in lockstep. Makes
perfect sense so far.
We conducted the same tests in 2005 and found a radically different
story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE
statements showed Transactions/sec skyrocket 2-10x more than Batch
Requests/sec for the duration of the statement. In other words, a
single transaction submitted by our application fires off
exponentially more transactions than the one we submitted. I was
unable to pinpoint exactly what these "hidden" transactions were
actually doing. Is this something that occurred in 2000 but simply
wasn't reported? Or is it new behavior in 2005?
While trying to answer these questions we noticed a second strange
behavior in 2005. When no queries are being executed the Transactions/
sec counter still jumps every six seconds like clockwork. And these
phantom transactions number in the thousands. We tried to use profiler
to capture what SQL was being executed, but nothing shows up in any
SQL Statement or Batch event. However, when we turned on the
SQLTransaction event we found it, sort of. An object called
GhostCleanupTask runs every six seconds causing thousands of
transactions. We don't know exactly what it is doing, but we noticed
that it ran consistently on some databases, but never on other
databases, even though both sets of databases have identical
properties.
So, all of this investigation leads me with three final questions.
1. What is behind all the extra transactions caught by perfmon when I
submit a single transaction?
2. What is GhostCleanupTask and why does it take so many transactions?
(And why does it only run on certain databases?)
3. If a potential customer asks for our Transactions/sec count, is it
accurate to give them the big number, knowing that our application is
only actually submitting a fraction of that? On the other hand, the
system apparently is actually doing that many transactions. (For
instance, on our production server during peak, Batch Requests/sec is
about 4,000, while Transactions/sec hits 26,000.)
I have searched high and low and been unable to find any references to
this particular situation. Any insight would be much appreciated.
---
Jerry Foster
CTO
Plexus Systems
www.plex.com
jfos@.plex.comOn Mon, 06 Aug 2007 15:28:11 -0700, Jerry <jfos@.plex.com> wrote:

>So, all of this investigation leads me with three final questions.
>1. What is behind all the extra transactions caught by perfmon when I
>submit a single transaction?
>2. What is GhostCleanupTask and why does it take so many transactions?
>(And why does it only run on certain databases?)
>3. If a potential customer asks for our Transactions/sec count, is it
>accurate to give them the big number, knowing that our application is
>only actually submitting a fraction of that? On the other hand, the
>system apparently is actually doing that many transactions. (For
>instance, on our production server during peak, Batch Requests/sec is
>about 4,000, while Transactions/sec hits 26,000.)
>I have searched high and low and been unable to find any references to
>this particular situation. Any insight would be much appreciated.
1. Don't know, very interested. I do know that 2005 breaks big SPs
into many smaller compilation units than did 2K, but I would not have
expected these to be visible as separate trans.
Actually, that may be a question - what if you count SP begins on both
systems?
2. Ghost traditionally does space recovery on tables that lack
clustered keys and have had deletes, and is generally suppressed in
profiler - or at least I've never seen it there.
3. The transaction number sounds bogus as now reported, I'd look for
something else, batches, SPs, etc.
In general, coming up with a baselining algorithm for SQLServer
performance management, has always been a challenge, and is best done
differently depending on local applications concerns.
Interested to see what else you learn about this.
Josh|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise
www.beyondsql.blogspot.com|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise
www.beyondsql.blogspot.com|||In addition to the other posts:
I think that (at least in 7.0 and 2000) it was ghost cleanup that did shrink
. So perhaps some of
your databases has autoshrink?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jfos@.plex.com> wrote in message
news:1186439291.965094.120600@.z24g2000prh.googlegroups.com...
> Our Transactions/sec counter jumped quite a bit when we moved to SQL
> Server 2005. The move coincided with increased load so we didn't think
> anything of it until recently. Upon further review, the counter just
> seems too high.
> There was an article in SQL Server magazine a few years ago by Brian
> Moran where he states, "Transactions/sec doesn't measure activity
> unless it's inside a transaction. Batch Requests/sec measures all
> batches you send to the server even if they don't participate in a
> transaction." He goes on to say that Transactions/sec will be skewed
> lower because it is a subset of Batch Requests/sec. (http://
> www.sqlmag.com/Article/ArticleID/26...rver_26380.html)
> The article was written for SQL Server 2000. We conducted tests in
> 2000 and found what he said to be right on the money. SELECT
> statements increased Batch Requests/sec, but not Transactions/sec.
> UPDATE/INSERT/DELETE statements increased both in lockstep. Makes
> perfect sense so far.
> We conducted the same tests in 2005 and found a radically different
> story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE
> statements showed Transactions/sec skyrocket 2-10x more than Batch
> Requests/sec for the duration of the statement. In other words, a
> single transaction submitted by our application fires off
> exponentially more transactions than the one we submitted. I was
> unable to pinpoint exactly what these "hidden" transactions were
> actually doing. Is this something that occurred in 2000 but simply
> wasn't reported? Or is it new behavior in 2005?
> While trying to answer these questions we noticed a second strange
> behavior in 2005. When no queries are being executed the Transactions/
> sec counter still jumps every six seconds like clockwork. And these
> phantom transactions number in the thousands. We tried to use profiler
> to capture what SQL was being executed, but nothing shows up in any
> SQL Statement or Batch event. However, when we turned on the
> SQLTransaction event we found it, sort of. An object called
> GhostCleanupTask runs every six seconds causing thousands of
> transactions. We don't know exactly what it is doing, but we noticed
> that it ran consistently on some databases, but never on other
> databases, even though both sets of databases have identical
> properties.
> So, all of this investigation leads me with three final questions.
> 1. What is behind all the extra transactions caught by perfmon when I
> submit a single transaction?
> 2. What is GhostCleanupTask and why does it take so many transactions?
> (And why does it only run on certain databases?)
> 3. If a potential customer asks for our Transactions/sec count, is it
> accurate to give them the big number, knowing that our application is
> only actually submitting a fraction of that? On the other hand, the
> system apparently is actually doing that many transactions. (For
> instance, on our production server during peak, Batch Requests/sec is
> about 4,000, while Transactions/sec hits 26,000.)
> I have searched high and low and been unable to find any references to
> this particular situation. Any insight would be much appreciated.
> ---
> Jerry Foster
> CTO
> Plexus Systems
> www.plex.com
> jfos@.plex.com
>

Radically different Transactions/sec behavior in 2005

Our Transactions/sec counter jumped quite a bit when we moved to SQL
Server 2005. The move coincided with increased load so we didn't think
anything of it until recently. Upon further review, the counter just
seems too high.
There was an article in SQL Server magazine a few years ago by Brian
Moran where he states, "Transactions/sec doesn't measure activity
unless it's inside a transaction. Batch Requests/sec measures all
batches you send to the server even if they don't participate in a
transaction." He goes on to say that Transactions/sec will be skewed
lower because it is a subset of Batch Requests/sec. (http://
www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html)
The article was written for SQL Server 2000. We conducted tests in
2000 and found what he said to be right on the money. SELECT
statements increased Batch Requests/sec, but not Transactions/sec.
UPDATE/INSERT/DELETE statements increased both in lockstep. Makes
perfect sense so far.
We conducted the same tests in 2005 and found a radically different
story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE
statements showed Transactions/sec skyrocket 2-10x more than Batch
Requests/sec for the duration of the statement. In other words, a
single transaction submitted by our application fires off
exponentially more transactions than the one we submitted. I was
unable to pinpoint exactly what these "hidden" transactions were
actually doing. Is this something that occurred in 2000 but simply
wasn't reported? Or is it new behavior in 2005?
While trying to answer these questions we noticed a second strange
behavior in 2005. When no queries are being executed the Transactions/
sec counter still jumps every six seconds like clockwork. And these
phantom transactions number in the thousands. We tried to use profiler
to capture what SQL was being executed, but nothing shows up in any
SQL Statement or Batch event. However, when we turned on the
SQLTransaction event we found it, sort of. An object called
GhostCleanupTask runs every six seconds causing thousands of
transactions. We don't know exactly what it is doing, but we noticed
that it ran consistently on some databases, but never on other
databases, even though both sets of databases have identical
properties.
So, all of this investigation leads me with three final questions.
1. What is behind all the extra transactions caught by perfmon when I
submit a single transaction?
2. What is GhostCleanupTask and why does it take so many transactions?
(And why does it only run on certain databases?)
3. If a potential customer asks for our Transactions/sec count, is it
accurate to give them the big number, knowing that our application is
only actually submitting a fraction of that? On the other hand, the
system apparently is actually doing that many transactions. (For
instance, on our production server during peak, Batch Requests/sec is
about 4,000, while Transactions/sec hits 26,000.)
I have searched high and low and been unable to find any references to
this particular situation. Any insight would be much appreciated.
---
Jerry Foster
CTO
Plexus Systems
www.plex.com
jfos@.plex.comOn Mon, 06 Aug 2007 15:28:11 -0700, Jerry <jfos@.plex.com> wrote:
>So, all of this investigation leads me with three final questions.
>1. What is behind all the extra transactions caught by perfmon when I
>submit a single transaction?
>2. What is GhostCleanupTask and why does it take so many transactions?
>(And why does it only run on certain databases?)
>3. If a potential customer asks for our Transactions/sec count, is it
>accurate to give them the big number, knowing that our application is
>only actually submitting a fraction of that? On the other hand, the
>system apparently is actually doing that many transactions. (For
>instance, on our production server during peak, Batch Requests/sec is
>about 4,000, while Transactions/sec hits 26,000.)
>I have searched high and low and been unable to find any references to
>this particular situation. Any insight would be much appreciated.
1. Don't know, very interested. I do know that 2005 breaks big SPs
into many smaller compilation units than did 2K, but I would not have
expected these to be visible as separate trans.
Actually, that may be a question - what if you count SP begins on both
systems?
2. Ghost traditionally does space recovery on tables that lack
clustered keys and have had deletes, and is generally suppressed in
profiler - or at least I've never seen it there.
3. The transaction number sounds bogus as now reported, I'd look for
something else, batches, SPs, etc.
In general, coming up with a baselining algorithm for SQLServer
performance management, has always been a challenge, and is best done
differently depending on local applications concerns.
--
Interested to see what else you learn about this.
Josh|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise:)
www.beyondsql.blogspot.com|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise:)
www.beyondsql.blogspot.com|||In addition to the other posts:
I think that (at least in 7.0 and 2000) it was ghost cleanup that did shrink. So perhaps some of
your databases has autoshrink?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jfos@.plex.com> wrote in message
news:1186439291.965094.120600@.z24g2000prh.googlegroups.com...
> Our Transactions/sec counter jumped quite a bit when we moved to SQL
> Server 2005. The move coincided with increased load so we didn't think
> anything of it until recently. Upon further review, the counter just
> seems too high.
> There was an article in SQL Server magazine a few years ago by Brian
> Moran where he states, "Transactions/sec doesn't measure activity
> unless it's inside a transaction. Batch Requests/sec measures all
> batches you send to the server even if they don't participate in a
> transaction." He goes on to say that Transactions/sec will be skewed
> lower because it is a subset of Batch Requests/sec. (http://
> www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html)
> The article was written for SQL Server 2000. We conducted tests in
> 2000 and found what he said to be right on the money. SELECT
> statements increased Batch Requests/sec, but not Transactions/sec.
> UPDATE/INSERT/DELETE statements increased both in lockstep. Makes
> perfect sense so far.
> We conducted the same tests in 2005 and found a radically different
> story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE
> statements showed Transactions/sec skyrocket 2-10x more than Batch
> Requests/sec for the duration of the statement. In other words, a
> single transaction submitted by our application fires off
> exponentially more transactions than the one we submitted. I was
> unable to pinpoint exactly what these "hidden" transactions were
> actually doing. Is this something that occurred in 2000 but simply
> wasn't reported? Or is it new behavior in 2005?
> While trying to answer these questions we noticed a second strange
> behavior in 2005. When no queries are being executed the Transactions/
> sec counter still jumps every six seconds like clockwork. And these
> phantom transactions number in the thousands. We tried to use profiler
> to capture what SQL was being executed, but nothing shows up in any
> SQL Statement or Batch event. However, when we turned on the
> SQLTransaction event we found it, sort of. An object called
> GhostCleanupTask runs every six seconds causing thousands of
> transactions. We don't know exactly what it is doing, but we noticed
> that it ran consistently on some databases, but never on other
> databases, even though both sets of databases have identical
> properties.
> So, all of this investigation leads me with three final questions.
> 1. What is behind all the extra transactions caught by perfmon when I
> submit a single transaction?
> 2. What is GhostCleanupTask and why does it take so many transactions?
> (And why does it only run on certain databases?)
> 3. If a potential customer asks for our Transactions/sec count, is it
> accurate to give them the big number, knowing that our application is
> only actually submitting a fraction of that? On the other hand, the
> system apparently is actually doing that many transactions. (For
> instance, on our production server during peak, Batch Requests/sec is
> about 4,000, while Transactions/sec hits 26,000.)
> I have searched high and low and been unable to find any references to
> this particular situation. Any insight would be much appreciated.
> ---
> Jerry Foster
> CTO
> Plexus Systems
> www.plex.com
> jfos@.plex.com
>

R

Our SQL Server 2005 - clustered - 64 bit - 4 CPU - 8 GB Memory - PerfMon counters:

Paging File:% usage peak (for the _Total instance) = 100%

Process:Page File Bytes Peak (for the sqlservr.exe process) = 8673640448

SQLServer:Buffer Manager:Total Pages = 617171

SQLServer:Memory Manager:Maximum Workspace Memory = 5780079

SQLServer:Memory Manager:Total Server Memory = 4954511

In the Windows Task Manager, the mem usage for sqlservr.exe goes up as high as 6 GB and then it drops down to 0. This process of going up and down repeats itself every 10 minutes or so. Appreciate any advice. Thanks.

Hi,

I see that the sql server process can consume as much memory as it requires. So the RAM or memory configurations seems to be done.

Can a query loads a huge data to memory. A full table scan with millions of records with GB's?

Eralper

R

Hi,

I have a bit of code written in class ASP that connects to a database with Command.Execute. It works fine with SQL 2000 but after an upgrade to SQL 2005 causes the asp page to hang while w3wp runs at at 100%

I'm running SQL 2005 SP1 on the same machine as IIS. I can't see anything in the event log or the SQL logs that would explain this (no errors in either). SQL profiler shows the stored procedure is called.

The code is below. Does anyone know of anything that might be causing this. I've spent two days so far trying to find a cause. The ODBC connection SDB1 is using SQL Native Client.

MyConn.Open "DSN=SDB1;Initial Catalog=database","username","password"

Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType = adCmdStoredProc
MyCommand.CommandText = "[owner].[procname]"
Set MyParam=MyCommand.CreateParameter("in_ip",adVarChar,adParamInput,10)
MyCommand.Parameters.Append MyParam
Set MyResult=MyCommand.CreateParameter("ip_country",adVarChar,adParamOutput,50)
MyCommand.Parameters.Append MyResult

MyCommand.Execute

Can anyone shed any light on this?

For anybody elses reference, I have found the cause of this.

The database user had connect permission but nothing else. This meant the user could connect to the database but not select/update or delete. Giving it the necessary rights solved the problem.

However, why this was causing IIS to run at 100% is still a mystery.

Regards,

Mark.

sql

Friday, March 9, 2012

Quick Question... How to use Default Values (after allowing NULL)

Hello,

I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?

Thanks,Look up CREATE TRIGGER in Books Online, and pay special attention to the INSERTED and DELETED virtual table concepts. Then within your trigger:

update YourTable
set YourValue = 0
from YourTable
inner join INSERTED on YourTable.PKEY = INSERTED.PKEY
where YourValue is null

But really, you should be doing your inserts through a stored procedure which uses ISNULL([NewValue], 0)|||Thx for the quick response.

I'll give it a shot.

Wednesday, March 7, 2012

Quick Expression Question

Hello,
I've done quite a bit of reporting services programming... and I know this
is possible... I'd like to simply hide two textboxes based ont he value of a
selected parameter value.
I've set expression value for visibility and tried various different
versions of this:Hi
This is supposed to be easy. If you select the textbox in layout view,
choose visibility in Properties, then you can see that "Hidden" has a default
value of false.
There you can write a nestet if expression:
=IIF(Parameters!YourParameter.Value = 1,True,False)
This means that if the value of your parameter returns 1 then the textbox
will be hidden, if not, it will be visible.
"Weston Weems" wrote:
> Hello,
> I've done quite a bit of reporting services programming... and I know this
> is possible... I'd like to simply hide two textboxes based ont he value of a
> selected parameter value.
> I've set expression value for visibility and tried various different
> versions of this:
>
>