SQL Server grinding to a halt .....

[StevenK]

We've recently set up SQL Server with an application developed in Visual Studio .NET (C#) on a client machine.
A couple of months in, and we're starting to experience difficulties with sluggishness of the system - with commands timing out (using the default timeout of 30 seconds).
We can resolve this (hopefully) by re-starting SQL Server on the server in question.
Why would we need to do this ?
What else could be slowing things down on SQL Server ?
We're tearing our hair out (and suffering some minor punches from our customer).
Thanks in advance
Steve

 

[StevenK]

The plot thickens ....
We restarted SQL Server and this made little difference to the application performance.
However, when we restarted the network server as a whole - this seemed to improve the performance again.
Can anyone comment ?
Steve

 

[SQLSister]

HAve you considered reindexing? Have you examined your indexing at all? Perhaps you need some differnt indexes.

Also, the code may be inefficent but this was not noticable until there were lots of records.

Questions about posting. See faq183-874

 

[camy123]

i deal with this type of problem all the time.. you need to elinate and identify where the problem is...
ok
Hardware CPu,Ram etc
software
database
client
stored procedures

 

[genomon]

How big is your log file?

 

[StevenK]

geonomon - what difference would the size of the log file make ?
How would we cater for this if it was an issue.
Since my original post it appears that the slow-down may have been down to the running of Exchange on the (small) Business server. So this has had the finger pointed at it.
Steve

 

[genomon]

When we switched to SQL server 2000 some time back, I overheard our DBA say that the log file had grown so large (wasn't getting purged regularly) that performance was suffering; one of the solutions he had us implement was to make sure NOCOUNT was set to ON in all our stored procedures (as well as cleaning out the log on a regular basis). Don't know if this applies to your situation tho.....

 

[StevenK]

Would the NOCOUNT issue have that much of an impact on the logging and performance ?
My understanding is that the only difference this would make would be that it doesn't do a COUNT on the records.
Furthermore, our understanding is that for a SELECT statement (where records are returned) the NOCOUNT would have no impact whatsoever - as it would always perform a count.
Any further thoughts.
Thanks,
Steve

 

[genomon]

Mr DBA says setting NOCOUNT OFF disables logging for the stored proc, but I admit I never researched it personally...

 

[SQLBill]

SET NOCOUNT ON/OFF has nothing to do with logging.

NOCOUNT just changes whether or not SQL Server counts and displays the number of rows that are affected by the command that was executed.

-SQLBill

 

[SQLBill]

Let me add further to my comment and maybe clarify what your DBA might have meant....

A script normally returns a comment about how many rows were affected. This is in addition to whatever else might be returned by the script. One thing to remember here is that the comment is NOT part of the values being returned. It is returned separately. So,

Value
------
1

(1 row affected)

The column header Value and the 1 are returned from the script. Then the (1 row affected) is returned.

If you have a stored procedure that includes several scripts, you will get one 'xx rows affected' message per script run. Along with any true values being returned this can cause high network traffic.

So you might have misunderstood your DBA when he was saying how NOCOUNT affects 'network traffic' or your DBA could have gotten it wrong.

Hope that clears up NOCOUNT. It impacts network traffic not logging.

-SQLBill