Drastic slowdown on SQL Server

One of our customers has SBS Server 2003 and after 5-6 hours of use the
server seems to slow down dramatically so that just retrieving records from
a small table takes sometimes 30 seconds when it normally takes less that 1.
We have hourly differential backups but they only take 5-10 seconds to run.
Can anyone guide me on what I can do to avoid this? It acts like all of the
memory is used up, but when I go into Taks Manager, the CPU useage is only
3%. Thanks.
David
are there too many open connections to the server ?
also can you try to see the server response time without the differential
backups.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>
|||Since it is SBS I have to assume you have other apps running on the server
than Sql Server. As such you should set a MAX memory setting in SQL Server
to assure the other apps and the OS have at least some memory to use. I
rarely see SBS servers where the apps are not fighting each other for
memory. How much do you have and how is the memory configured for SQL
Server?
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>
|||sounds like resource blocking or deadlocks to me.
Have you checked sp_Who2 to see if blocking is the culprit?
Greg Jackson
PDX, Oregon
|||The memory is 1.5GB
I did not setup their server so I'm not sure of memory configuration. I
would guess they used whatever it defaults to.
David
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Since it is SBS I have to assume you have other apps running on the server
> than Sql Server. As such you should set a MAX memory setting in SQL
> Server to assure the other apps and the OS have at least some memory to
> use. I rarely see SBS servers where the apps are not fighting each other
> for memory. How much do you have and how is the memory configured for SQL
> Server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>
|||Where do I check for open connections?
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||One more thing I should mention. This was NOT happening when they were
running SBS 2000. if that helps any.
David
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:e42sRn3GFHA.576@.TK2MSFTNGP15.phx.gbl...
> are there too many open connections to the server ?
> also can you try to see the server response time without the differential
> backups.
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>
|||If it is the defaults then sql server will attempt to use all 1.5GB if there
is enough data. Then it will constantly compete with the other apps and the
OS for memory. Have them try setting the MAX Memory setting to SQL Server
to say 1.0 GB and see if that helps some. Depending on what the other apps
are doing you may even want to go lower.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OVTIxz3GFHA.2620@.tk2msftngp13.phx.gbl...
> The memory is 1.5GB
> I did not setup their server so I'm not sure of memory configuration. I
> would guess they used whatever it defaults to.
> David
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
>
|||sp_Who2 is a good start
Greg Jackson
PDX, Oregon
|||sp_Who2 was a great help! Turns out, someone had checked the AutoShrink
checkbox and it was running and grinding everything to a halt.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Drastic slowdown on SQL Server

One of our customers has SBS Server 2003 and after 5-6 hours of use the
server seems to slow down dramatically so that just retrieving records from
a small table takes sometimes 30 seconds when it normally takes less that 1.
We have hourly differential backups but they only take 5-10 seconds to run.
Can anyone guide me on what I can do to avoid this? It acts like all of the
memory is used up, but when I go into Taks Manager, the CPU useage is only
3%. Thanks.
Davidare there too many open connections to the server ?
also can you try to see the server response time without the differential
backups.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||Since it is SBS I have to assume you have other apps running on the server
than Sql Server. As such you should set a MAX memory setting in SQL Server
to assure the other apps and the OS have at least some memory to use. I
rarely see SBS servers where the apps are not fighting each other for
memory. How much do you have and how is the memory configured for SQL
Server?
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||sounds like resource blocking or deadlocks to me.
Have you checked sp_Who2 to see if blocking is the culprit?
Greg Jackson
PDX, Oregon|||The memory is 1.5GB
I did not setup their server so I'm not sure of memory configuration. I
would guess they used whatever it defaults to.
David
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Since it is SBS I have to assume you have other apps running on the server
> than Sql Server. As such you should set a MAX memory setting in SQL
> Server to assure the other apps and the OS have at least some memory to
> use. I rarely see SBS servers where the apps are not fighting each other
> for memory. How much do you have and how is the memory configured for SQL
> Server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>|||Where do I check for open connections?
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||One more thing I should mention. This was NOT happening when they were
running SBS 2000. if that helps any.
David
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:e42sRn3GFHA.576@.TK2MSFTNGP15.phx.gbl...
> are there too many open connections to the server ?
> also can you try to see the server response time without the differential
> backups.
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>|||If it is the defaults then sql server will attempt to use all 1.5GB if there
is enough data. Then it will constantly compete with the other apps and the
OS for memory. Have them try setting the MAX Memory setting to SQL Server
to say 1.0 GB and see if that helps some. Depending on what the other apps
are doing you may even want to go lower.
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OVTIxz3GFHA.2620@.tk2msftngp13.phx.gbl...
> The memory is 1.5GB
> I did not setup their server so I'm not sure of memory configuration. I
> would guess they used whatever it defaults to.
> David
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
>|||sp_Who2 is a good start
Greg Jackson
PDX, Oregon|||sp_Who2 was a great help! Turns out, someone had checked the AutoShrink
checkbox and it was running and grinding everything to a halt.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

Drastic slowdown on SQL Server

One of our customers has SBS Server 2003 and after 5-6 hours of use the
server seems to slow down dramatically so that just retrieving records from
a small table takes sometimes 30 seconds when it normally takes less that 1.
We have hourly differential backups but they only take 5-10 seconds to run.
Can anyone guide me on what I can do to avoid this? It acts like all of the
memory is used up, but when I go into Taks Manager, the CPU useage is only
3%. Thanks.
Davidare there too many open connections to the server ?
also can you try to see the server response time without the differential
backups.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||Since it is SBS I have to assume you have other apps running on the server
than Sql Server. As such you should set a MAX memory setting in SQL Server
to assure the other apps and the OS have at least some memory to use. I
rarely see SBS servers where the apps are not fighting each other for
memory. How much do you have and how is the memory configured for SQL
Server?
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
> One of our customers has SBS Server 2003 and after 5-6 hours of use the
> server seems to slow down dramatically so that just retrieving records
> from a small table takes sometimes 30 seconds when it normally takes less
> that 1. We have hourly differential backups but they only take 5-10
> seconds to run. Can anyone guide me on what I can do to avoid this? It
> acts like all of the memory is used up, but when I go into Taks Manager,
> the CPU useage is only 3%. Thanks.
> David
>|||sounds like resource blocking or deadlocks to me.
Have you checked sp_Who2 to see if blocking is the culprit?
Greg Jackson
PDX, Oregon|||The memory is 1.5GB
I did not setup their server so I'm not sure of memory configuration. I
would guess they used whatever it defaults to.
David
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Since it is SBS I have to assume you have other apps running on the server
> than Sql Server. As such you should set a MAX memory setting in SQL
> Server to assure the other apps and the OS have at least some memory to
> use. I rarely see SBS servers where the apps are not fighting each other
> for memory. How much do you have and how is the memory configured for SQL
> Server?
>
> --
> Andrew J. Kelly SQL MVP
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>> One of our customers has SBS Server 2003 and after 5-6 hours of use the
>> server seems to slow down dramatically so that just retrieving records
>> from a small table takes sometimes 30 seconds when it normally takes less
>> that 1. We have hourly differential backups but they only take 5-10
>> seconds to run. Can anyone guide me on what I can do to avoid this? It
>> acts like all of the memory is used up, but when I go into Taks Manager,
>> the CPU useage is only 3%. Thanks.
>> David
>|||One more thing I should mention. This was NOT happening when they were
running SBS 2000. if that helps any.
David
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:e42sRn3GFHA.576@.TK2MSFTNGP15.phx.gbl...
> are there too many open connections to the server ?
> also can you try to see the server response time without the differential
> backups.
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>> One of our customers has SBS Server 2003 and after 5-6 hours of use the
>> server seems to slow down dramatically so that just retrieving records
>> from a small table takes sometimes 30 seconds when it normally takes less
>> that 1. We have hourly differential backups but they only take 5-10
>> seconds to run. Can anyone guide me on what I can do to avoid this? It
>> acts like all of the memory is used up, but when I go into Taks Manager,
>> the CPU useage is only 3%. Thanks.
>> David
>|||If it is the defaults then sql server will attempt to use all 1.5GB if there
is enough data. Then it will constantly compete with the other apps and the
OS for memory. Have them try setting the MAX Memory setting to SQL Server
to say 1.0 GB and see if that helps some. Depending on what the other apps
are doing you may even want to go lower.
--
Andrew J. Kelly SQL MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OVTIxz3GFHA.2620@.tk2msftngp13.phx.gbl...
> The memory is 1.5GB
> I did not setup their server so I'm not sure of memory configuration. I
> would guess they used whatever it defaults to.
> David
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uVMitp3GFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Since it is SBS I have to assume you have other apps running on the
>> server than Sql Server. As such you should set a MAX memory setting in
>> SQL Server to assure the other apps and the OS have at least some memory
>> to use. I rarely see SBS servers where the apps are not fighting each
>> other for memory. How much do you have and how is the memory configured
>> for SQL Server?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David C" <dlchase@.lifetimeinc.com> wrote in message
>> news:uLL4GY3GFHA.3272@.TK2MSFTNGP10.phx.gbl...
>> One of our customers has SBS Server 2003 and after 5-6 hours of use the
>> server seems to slow down dramatically so that just retrieving records
>> from a small table takes sometimes 30 seconds when it normally takes
>> less that 1. We have hourly differential backups but they only take 5-10
>> seconds to run. Can anyone guide me on what I can do to avoid this? It
>> acts like all of the memory is used up, but when I go into Taks Manager,
>> the CPU useage is only 3%. Thanks.
>> David
>>
>

Double Quote in database record

In my asp.net page when I run a query against the database the datagrid get populated with only the records that starts with the first letter for example A good day. But none of the records that starts like this "A fine day" or "A nice day" displays in the datagrid. I tried to replace the Double Quote but it still returns only the records without " quotes. My query looks like this:

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like @.ArticleHeading) ORDER BY [Articles].[ArticleHeading]"

You need to do the replace on both the column AND the value you are comparing, otherwise they will never be equal.
eg.

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel]<> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like REPLACE(@.ArticleHeading),'chr(34)') ORDER BY [Articles].[ArticleHeading]"|||

I tried it but still it does not produce the right results.

My code:

Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like (REPLACE(@.ArticleHeading, 'chr(34)', ''))) ORDER BY [Articles].[ArticleHeading]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_articleHeading As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_articleHeading.ParameterName = "@.ArticleHeading"
dbParam_articleHeading.Value = Request.QueryString("Alphabet")& "%"
dbParam_articleHeading.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_articleHeading)

|||What are you trying to do with: REPLACE([Articles].[ArticleHeading], 'chr(34)', '') ?|||Some records starts with a double qoute and a need to list all for example that starts with A but that needs to include something like "A nice day"|||

Using a sql query I manage to get the right results but what do I need to do in asp.net to get the right results. My SQL code that replaces the ":

SELECT *
FROM Articles
WHERE (REPLACE(ArticleHeading, '"', '') LIKE 'A%')
ORDER BY ArticleHeading

|||

Change:

REPLACE([Articles].[ArticleHeading], 'chr(34)', '')

To:

REPLACE([Articles].[ArticleHeading], chr(34), '')

Doing a SELECT x FROM y WHERE z IN (@param) in stored procedure?

Hello,

I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.

How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.

Any tips highly appreciated! If I'm not being clear I'll gladly post more details.

Thanks,

Noc

PS SQL 2000, ASP.NET 1.1, VS 2003.Have a look at the following article:

http://www.sqlteam.com/item.asp?ItemID=11499|||Many thanks! Looks like I was on the righ track with the temporary table idea but it sure is a lot easier to implement with that sample code.